SQLDataAdapter's Update Statement

bmains · December 12th, 2003, 04:21 PM

Hello,

I'm trying to write a function that calls the data adapter's update statement as such:

Code:

dim objConnection as new SQLConnection(m_strConnectionString)
dim objAdapter as new SQLDataAdapter

objAdapter.InsertCommand = new SQLCommand("Insert Books", objConnection)
objAdapter.InsertCommand.CommandType = CommandType.StoredProcedure
objAdapter.InsertCommand.Parameters.Add(New SQLParameter("@BookName", SQLDbType.VarChar, 50, "BookName"))
objAdapter.InsertCommand.Parameters.Add(New SQLParameter("@BookDescription", SQLDbType.VarChar, 1024, "BookDescription"))

objAdapter.UpdateCommand = new SQLCommand("Update Books", objConnection)
objAdapter.UpdateCommand.CommandType = CommandType.StoredProcedure
objAdapter.UpdateCommand.Parameters.Add(New SQLParameter("@BookID", SQLDbType.Int, 10, "BookID"))
objAdapter.UpdateCommand.Parameters.Add(New SQLParameter("@BookName", SQLDbType.VarChar, 50, "BookName"))
objAdapter.UpdateCommand.Parameters.Add(New SQLParameter("@BookDescription", SQLDbType.VarChar, 1024, "BookDescription"))

objAdapter.DeleteCommand = new SQLCommand("Delete Books", objConnection)
objAdapter.DeleteCommand.CommandType = CommandType.StoredProcedure
objAdapter.DeleteCommand.Parameters.Add(New SQLParameter("@BookID", SQLDbType.Int, 10, "BookID"))

objAdapter.TableMappings.Add("Books", "DataSetTable")
objConnection.Open()
objAdapter.Update(m_dstBooks, "Books")
objConnection.Close()

Is this the correct format? The reason I'm asking is I'm getting errors. I've tested the stored procedure calls, and they are working properly, and I can't figure out what the coding error may be.

Thanks,

Brian Mains

bmains · December 17th, 2003, 11:23 AM

Hello,

If anybody wants to know the answer to this question, you create a table mapping object (in the namespace system.data.common) as so:

'Creates a new table mapping object between the source table "books" and the dataset table "datasettable"
dim objMapping as new TableMapping("Books", "DataSetTable")
'Create a column mapping by supplying the source column "bookid" and the dataset column "datasetcolumnbookid"
objMapping.ColumnMappings.Add("BookID", "DataSetColumnBookID")
objMapping.ColumnMappings.Add("BookName", "DataSetColumnBookName")
objMapping.ColumnMappings.Add("BookDescription", "DataSetColumnBookDescription")

objAdapter.TableMappings.Add(objMapping)

That's how you create a mapping between the database and the data set.

Thanks.

kfls_83 · April 18th, 2004, 12:18 PM

Hello Every One
I need to Know What is table Mapping And What it Has to do with Data Adapters Update Commands

Fadi

melvik · April 19th, 2004, 12:07 AM

Well let me say a simple question like: How can u bind ur retrieved Data to DataSet ?! Exactly when u selected more than 1 table in yr SELECT. In other hand when u use SP, DataAdapter will bind data to a name like ur SP.

Always:),
Hovik Melkomian.

bmains · April 19th, 2004, 10:40 AM

Sometimes the data set names aren't always the same as the database table names, which is the reason for the data table mappings. My names in the dataset weren't the same as in the table, and the DataTableMapping creating a table/column mapping between the two.

Brian

kfls_83 · May 7th, 2004, 03:05 AM

Hello Sir ,, i Have the Answer Try The Following Queries they Don't give an Error

//Insert Command
            ins.Connection = sqlConnection1;
            ins.CommandText = @"INSERT INTO appointment(Appointmentno,examiningdate,patientsno ,etime,RoomOfExamination,DoctorNumber) values(@app,@exam,@pat,@e,@room,@doc);";
            ins.Parameters.Add("@app",SqlDbType.Int,4,"Appoint mentno");
            ins.Parameters.Add("@exam",SqlDbType.DateTime ,4,"examiningDate");
            ins.Parameters.Add("@pat",SqlDbType.Int,4,"patient sno");
            ins.Parameters.Add("@e",SqlDbType.DateTime ,8,"Etime");
            ins.Parameters.Add("@room",SqlDbType.Int,4,"RoomOf Examination");
            ins.Parameters.Add("@doc",SqlDbType.Int,4,"DoctorN umber");

            //Update Command
            upd.Connection = sqlConnection1;
            upd.CommandText = "update appointment set appointmentno=@app,patientsno=@pats,examiningDate= @exam,etime=@e,roomOfExamination=@room,doctornumbe r=@doc where appointmentno=@app_orig;";
            upd.Parameters.Add("@app",SqlDbType.Int,4,"Appoint mentno");
            upd.Parameters.Add("@pats",SqlDbType.Int,4,"Patien tsNo");
            upd.Parameters.Add("@doc",SqlDbType.Int,4,"DoctorN umber");
            upd.Parameters.Add("@exam",SqlDbType.DateTime ,4,"ExaminingDate");
            upd.Parameters.Add("@e",SqlDbType.DateTime ,4,"Etime");
            upd.Parameters.Add("@room",SqlDbType.Int,4,"RoomOf Examination");
            upd.Parameters.Add("@app_orig",SqlDbType.Int,4,"Ap pointmentno");
            upd.Parameters["@app"].SourceVersion = DataRowVersion.Current;
            upd.Parameters["@pats"].SourceVersion = DataRowVersion.Current;
            upd.Parameters["@doc"].SourceVersion = DataRowVersion.Current;
            upd.Parameters["@exam"].SourceVersion = DataRowVersion.Current;
            upd.Parameters["@e"].SourceVersion = DataRowVersion.Current;
            upd.Parameters["@room"].SourceVersion = DataRowVersion.Current;
            upd.Parameters["@app_orig"].SourceVersion = DataRowVersion.Original;
            //Delete Command
            del.Connection = sqlConnection1;
            del.CommandText = @"delete from appointment where appointmentno=@app";
            del.Parameters.Add("@app",SqlDbType.Int,4,"Appoint mentno");
            del.Parameters["@app"].SourceVersion = DataRowVersion.Original;

            da_ward.Fill(ds_ward,"Appointment");
            da_ward.InsertCommand = ins;
            da_ward.UpdateCommand = upd;
            da_ward.DeleteCommand = del;

Fadi