DataAdapter Insert Problem? 1

[MFritts253]

I'm using Stored Procedures with SQL server. I have specified the InsertCommand,DeleteCommand, UpdateCommand for my dataadapter.
the i go and call this code.

dsTrial.GetChanges()

If dsTrial.HasChanges Then
daTrial.Update(dsTrial, "Trial")


Catch ex As Exception

dsTrial.RejectChanges()
MessageBox.Show(ex.Message & ex.HelpLink) '& "Rejected Changes")

End Try
End If
dsTrial.AcceptChanges()

my problem is that the dataadapter only wants to do Inserts
so if i had a order record(details like list each item order in the grid) already for a customer. If i Save the records it won't try to do an update it only does Inserts and try to insert records with have already been saved to the DB at a previous time.

Sincerely,
Fritts

 

[chrissie1]

Sounds to me like you are having an identity crisis. I think that somewhere along the line you forget to use the correct identity probably somewhere in the order record.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[MFritts253]

Christian,
I'm not quite sure what you mean?

 

[chrissie1]

the dataadapter makes the decision on what to use, if he considers he needs an insert he will use the insertcommand otherwise he will use the updatecommand or the deletecommand. He does this by looking at the identity field, if he trouves the identity in the table then he will use the update to update the record if he doesn't find the identity or unique field he will insert a new record in the table. That's why I think you have an identity crisis.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[MFritts253]

Thank you Christiaan,
So where do my put my code for the dataadapter so that it reads whatever command it needs.

I have this code for Insert,Delete, and Update
but where do i put it in my program so that it makes the right decision.

daTrial.InsertCommand = New SqlCommand
daTrial.InsertCommand.CommandType = CommandType.StoredProcedure
daTrial.InsertCommand.CommandText = "PKA_InsertPacking"
daTrial.InsertCommand.Connection = conMain
daTrial.InsertCommand.Parameters.Add("@EmpID", SqlDbType.VarChar, 3).Value = EmpID
daTrial.InsertCommand.Parameters.Add("@WorkDate", SqlDbType.DateTime, 8).Value = TDBDate.Value
daTrial.InsertCommand.Parameters.Add("@Item", SqlDbType.VarChar, 35, "Item") '.Value = dsRow.Item(0)
daTrial.InsertCommand.Parameters.Add("@TimeEach", SqlDbType.Int, 4, "TimeEach") '.Value = dsRow.Item(2)
daTrial.InsertCommand.Parameters.Add("@QtyPacked", SqlDbType.Int, 4, "QtyPacked") '.Value = dsRow.Item(3)

daTrial.DeleteCommand = New SqlCommand
daTrial.DeleteCommand.CommandType = CommandType.StoredProcedure
daTrial.DeleteCommand.CommandText = "PKA_InsertPacking"
daTrial.DeleteCommand.Connection = conMain
daTrial.DeleteCommand.Parameters.Add("@EmpID", SqlDbType.VarChar, 3).Value = EmpID
daTrial.DeleteCommand.Parameters.Add("@WorkDate", SqlDbType.DateTime, 8).Value = TDBDate.Value
daTrial.DeleteCommand.Parameters.Add("@Item", SqlDbType.VarChar, 35, "Item")

daTrial.UpdateCommand = New SqlCommand
daTrial.UpdateCommand.CommandType = CommandType.StoredProcedure
daTrial.UpdateCommand.CommandText = "PKA_InsertPacking"
daTrial.UpdateCommand.Connection = conMain
daTrial.UpdateCommand.Parameters.Add("@EmpID", SqlDbType.VarChar, 3).Value = EmpID
daTrial.UpdateCommand.Parameters.Add("@WorkDate", SqlDbType.DateTime, 8).Value = TDBDate.Value
daTrial.UpdateCommand.Parameters.Add("@Item", SqlDbType.VarChar, 35, "Item")
daTrial.UpdateCommand.Parameters.Add("@TimeEach", SqlDbType.Int, 4, "TimeEach")
daTrial.UpdateCommand.Parameters.Add("@QtyPacked", SqlDbType.Int, 4, "QtyPacked")

Sincerely,
Michael Fritts

 

[chrissie1]

the dataadapter does it automatically. But I believe the error is here "PKA_InsertPacking"
You are using the same storedprocedure for update insert and delete. I think you should make a separate one for each command. One that does the insert with an insert command, one that does the update with an update command an one that does the delete with a delete command.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[MFritts253]

Wow,
I really over looked that one. I was using the PKA_InsertPacking for every type of procedure. I did have Update, and Delete stored procedures. I think that was a huge mistake of Copying and pasteing. Thanks for the the help.

Sincerely,
Michael