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

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

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

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

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

Can someone see understand Chinese?Please contact me

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

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

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()