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 | Calendar | 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 July 30th, 2006, 04:36 PM
Registered User
 
Join Date: Jul 2006
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default dataset and autoincrement primary key

I'm creating a data entry form, and all my data is stored in in-memory datasets. I'd like to add functionality to add new records (i.e. rows) to my dataset and accordingly sent them to the underlying database table.

I'm wondering what the best practice is: first updating the dataset (new row) and secondly the database table, or vice versa? How do I for example handle primary keys that are set to autoincrement when first updating the dataset? Is there a call that I can use that auto-increments the primary key or do I first have to add the new record to the database table and then refresh the dataset?

Please your advise.

grtz,
Jouri

Reply With Quote
  #2 (permalink)  
Old August 4th, 2006, 05:11 AM
Friend of Wrox
 
Join Date: Sep 2004
Location: , , India.
Posts: 140
Thanks: 0
Thanked 0 Times in 0 Posts
Default

U need not to put any value in the Identey cols when u do a update your dataadapter can take care

Reply With Quote
  #3 (permalink)  
Old August 5th, 2006, 09:52 AM
Registered User
 
Join Date: Jul 2006
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thx for your reply.

but I still have some questions concerning autoincrement primary keys and dataset.

for example: I have an order master parent and an order line child table. Both are linked through the Order ID. Let's say the Order ID is an autoincrement primary key in the Order table.
I have created two datasets and would like to add a new order. I've written 2 functions: one to add the new record in the order table (on db level) and one to refresh my dataset. In the insert statements I don't provide a value for the order id, because the database takes care of the new value.

Now my problem: How can I fetch the new order id??? I need this order id as a foreign key in my order line table.

I hope above illustrates the problem I'm facing.

grtz,
Jouri

Reply With Quote
  #4 (permalink)  
Old August 7th, 2006, 06:41 AM
Friend of Wrox
 
Join Date: Sep 2004
Location: , , India.
Posts: 140
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Why are you creating 2 datasets u can have one dataset with 2 table and set the relation between the 2.

Reply With Quote
  #5 (permalink)  
Old August 7th, 2006, 06:55 AM
Registered User
 
Join Date: Aug 2006
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to mz121 Send a message via MSN to mz121
Default

ÓÐÈËÄÜ¿´¶®ÖÐÎÄÂð?ÇëÁªÏµÎÒ

Reply With Quote
  #6 (permalink)  
Old August 7th, 2006, 06:57 AM
Registered User
 
Join Date: Aug 2006
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to mz121 Send a message via MSN to mz121
Default

Can someone see understand Chinese?Please contact me


Reply With Quote
  #7 (permalink)  
Old August 8th, 2006, 02:40 PM
Registered User
 
Join Date: Jul 2006
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks;

my mistake: i see in my previous message that I refer to 2 datasets but I indeed mean 2 datatables within one dataset and both have a relationship.

grtz,
JG

Reply With Quote
  #8 (permalink)  
Old February 26th, 2009, 09:19 PM
Registered User
 
Join Date: Feb 2009
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default vb.net: Solution: get the autoincrement primary key

I have one table with an autoincrementing key and a second, related table that needs the key from the first table to create a new record.

I've found the solution to capturing the autoincrementing key.
After creating the new record in the autoincrementing key table.
Write that table from memory to storage, then force the TableAdapter to reread the table from storage, once that is done, you can read the new record number.

For Example:
' create the new record in the autoincrementing table
OrdersBindingSource.AddNew()
' put some data into it
OrdersBindingSource.Current("OrderDate") = #2/26/09#
' Now Save the record from memory to storage
Me.Validate()
Me.OrdersBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.MyDataSet)

' now force the TableAdapter to reread the data from storage
Me.OrdersTableAdapter.Fill(Me.MyDataSet.Orders)
' Move to the last record and read it
OrdersBindingSource.MoveLast()
Dim NewOrderID = OrdersBindingSource.Current("OrderID")

Now you have the new autoincrementing record ID and can use it to create the new record in the linked table

Reply With Quote
  #9 (permalink)  
Old July 19th, 2009, 12:09 PM
Authorized User
 
Join Date: Jun 2009
Posts: 13
Thanks: 2
Thanked 0 Times in 0 Posts
Default update tables using vb forms

Hi everyone
I have written the following code to update values in my database. I am using a VB front end but when i run execute the command nothing happens. Can anyone advise me on where i am going wrong. Please rewrite my code if you can. I will be so greatful.Look forward to hearing from you.

'connect to database
mySqlCommand.Connection = myConnection
mySqlCommand.CommandType = CommandType.Text
mySqlCommand.CommandText = " UPDATE AIRLINE_BOOKING" & _
"SET RESERVENO=@RESERVENO_UP,TELEPHONE=@TELEPHONE_UP,ST ARTAIRPORT=@STARTAIRPORT_UP," & _
"TRAVELDATE=@TRAVELDATE_UP,DEPARTTIME=@DEPARTTIME_U P,A_DESCRIPTION=@A_DESCRIPTION_UP," & _
"A_BOOKDATE=@A_BOOKDATE_UP,A_HELDUNTIL=@A_HELDUNTIL _UP,A_BASICCOST=@A_BASICCOST_UP," & _
" A_DEPOSIT=@A_DEPOSIT_UP " & _
" WHERE AIRLINEID=@AIRLINEID_UP"
'add the parameters
mySqlCommand.Parameters.AddWithValue("@AIRLINE_NAM E_UP", ANAME.Text)
mySqlCommand.Parameters.AddWithValue("@RESERVENO_U P", ARESNO.Text)
mySqlCommand.Parameters.AddWithValue("@TELEPHONE_U P", ATELEPHONE.Text)
mySqlCommand.Parameters.AddWithValue("@STARTAIRPOR T_UP", ASTARTAIRPORT.Text)
mySqlCommand.Parameters.AddWithValue("@TRAVELDATE_ UP", ATRAVELDATE.Text).DbType = DbType.Date
mySqlCommand.Parameters.AddWithValue("@DEPARTTIME_ UP", ADEPTIME.Text)
mySqlCommand.Parameters.AddWithValue("@A_DESCRIPTI ON_UP", ADESCRIPTION.Text)
mySqlCommand.Parameters.AddWithValue("@A_BOOKDATE_ UP", ABOOKDATE.Text).DbType = DbType.Date
mySqlCommand.Parameters.AddWithValue("@A_HELDUNTIL _UP", AHELDUNTIL.Text).DbType = DbType.Date
mySqlCommand.Parameters.AddWithValue("@A_BASICCOST _UP", ABASICCOST.Text)
mySqlCommand.Parameters.AddWithValue("@A_DEPOSIT_U P", ADEPOSIT.Text)
mySqlCommand.Parameters.AddWithValue("@AIRLINEID_U P", BindingContext(myDataView).Current("AIRLINEID"))
'OPEN CONNECTION
myConnection.Open()
Try
mySqlCommand.ExecuteNonQuery()
Catch MYERRORCHECKER As SqlException
MessageBox.Show(MYERRORCHECKER.Message)
End Try
myConnection.Close()
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
Problem with dataset autoincrement column Jayaram ADO.NET 0 December 4th, 2006 03:13 PM
Update Table with dataset without primary key ranakdinesh ASP.NET 2.0 Professional 1 September 28th, 2006 05:30 PM
Generated dataset and autoincrement dbradley ADO.NET 5 February 2nd, 2005 01:14 AM
FOREIGN KEY and PRIMARY KEY Constraints junemo Oracle 10 June 15th, 2004 01:00 AM



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


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