Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > .NET > Other .NET > ADO.NET
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
ADO.NET For discussion about ADO.NET.  Topics such as question regarding the System.Data namespace are appropriate.  Questions specific to a particular application should be posted in a forum specific to the application .
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ADO.NET section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 12th, 2003, 04:21 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default SQLDataAdapter's Update Statement

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
__________________
Brian
Reply With Quote
  #2 (permalink)  
Old December 17th, 2003, 11:23 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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.
Reply With Quote
  #3 (permalink)  
Old April 18th, 2004, 12:18 PM
Registered User
 
Join Date: Apr 2004
Location: Peshawar, NWFP, Pakistan.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to kfls_83
Default

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

Fadi
Reply With Quote
  #4 (permalink)  
Old April 19th, 2004, 12:07 AM
Friend of Wrox
Points: 3,558, Level: 25
Points: 3,558, Level: 25 Points: 3,558, Level: 25 Points: 3,558, Level: 25
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: California, USA
Posts: 996
Thanks: 2
Thanked 11 Times in 11 Posts
Send a message via Yahoo to melvik
Default

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.
Reply With Quote
  #5 (permalink)  
Old April 19th, 2004, 10:40 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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
Reply With Quote
  #6 (permalink)  
Old May 7th, 2004, 03:05 AM
Registered User
 
Join Date: Apr 2004
Location: Peshawar, NWFP, Pakistan.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to kfls_83
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
update multiple columns in an update statement debbiecoates SQL Server 2000 1 August 17th, 2008 04:01 AM
UPDATE statement takwirira ASP.NET 2.0 Basics 7 March 7th, 2007 07:48 PM
Error when trying to use an UPDATE statement wayne62682 Access VBA 2 April 4th, 2006 07:50 PM
Update Statement some error thas123 ADO.NET 1 January 8th, 2006 10:30 AM



All times are GMT -4. The time now is 01:01 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.