data adapter update failure
Posted: Fri 11 Mar 2005 16:43
i have a module file that uses global ado .net objects and below is the code for them.
This is my insert command for the customers table which seems to be working ok when i add a new customer
This is my update command which is the problem i think but im not sure it seems to work but the problem is when i try to update a customer using the data adapter it only updates it like when i leave the form and come back to it and update it again then it sends the updates back to the database table its really wierd it works but it doesn't work like i need it to i need it to update it as soon as i make the changes and issue a update command not every other time.
This is the sub that i call that add the parameters to the command and issue the update method of the data adapter for the customers table
when i comment this line out " myDa.ContinueUpdateOnError = True" its gives me a error System.InvalidOperation.Exception
'@last_name' is missing at the statement but it does update the row when i uncomment that line im just wondering if the problem is the update command which seems to work every other time or if its me using one global data adapter to issue the updates any suggestions and comments will be greatley appreciated i need it to update it when as soon as the data is changed in that record
Code: Select all
Public myCon As New MySqlConnection
Public myCmd As New MySqlCommand
Public myDa As New MySqlDataAdapter
Public myDs As New ppbData
Code: Select all
Public Const strCustInsert As String = "INSERT INTO customers (seq, cust_no, cust_id, last_name, " _
& "first_name, creation_date, cust_category, salesperson, active_flag, company_name, " _
& "addr_1, addr_2, city, state, zip, county, township, phone_home, phone_work, " _
& "phone_work_ext, phone_mobile, fax, email, website, birth_date, sent_card, notes, " _
& "source, lit_req_label, lib_req_label_sent_date, lit_followup_date, lit_followup_printed, " _
& "quote_label, quote_label_sent_date, quote_followup_date, quote_followup_printed, " _
& "checked_out, check_out_pending, modified_date, modified_by, next_quote_number) VALUES " _
& "(@seq, @cust_no, @cust_id, @last_name, @first_name, @creation_date, @cust_category, " _
& "@salesperson, @active_flag, @company_name, @addr_1, @addr_2, @city, @state, @zip, @county, " _
& "@township, @phone_home, @phone_work, @phone_work_ext, @phone_mobile, @fax, @email, @website, " _
& "@birth_date, @sent_card, @notes, @source, @lit_req_label, @lib_req_label_sent_date, " _
& "@lit_followup_date, @lit_followup_printed, @quote_label, @quote_label_sent_date, " _
& "@quote_followup_date, @quote_followup_printed, @checked_out, @check_out_pending, " _
& "@modified_date, @modified_by, @next_quote_number)"
Code: Select all
Public Const strCustUpdate As String = "UPDATE customers SET " _
& "last_name=@last_name, first_name=@first_name, creation_date=@creation_date, " _
& "cust_category=@cust_category, salesperson=@salesperson, active_flag=@active_flag, " _
& "company_name=@company_name, addr_1=@addr_1, addr_2=@addr_2, city=@city, state=@state, " _
& "zip=@zip, county=@county, township=@township, phone_home=@phone_home, phone_work=@phone_work, " _
& "phone_work_ext=@phone_work_ext, phone_mobile=@phone_mobile, fax=@fax, email=@email, website=@website, " _
& "birth_date=@birth_date, sent_card=@sent_card, notes=@notes, source=@source, lit_req_label=@lit_req_label, " _
& "lib_req_label_sent_date=@lib_req_label_sent_date, lit_followup_date=@lit_followup_date, " _
& "lit_followup_printed=@lit_followup_printed, quote_label=@quote_label, quote_label_sent_date=@quote_label_sent_date, " _
& "quote_followup_date=@quote_followup_date, quote_followup_printed=@quote_followup_printed, checked_out=@checked_out, " _
& "check_out_pending=@check_out_pending, modified_date=@modified_date, modified_by=@modified_by, " _
& "next_quote_number=@next_quote_number WHERE cust_id=@cust_id"
Public cmdCustInsert As New MySqlCommand(strCustInsert, myCon)
Public cmdCustUpdate As New MySqlCommand(strCustUpdate, myCon)
Code: Select all
Public Sub UpdateCustomer()
cmdCustUpdate.Parameters.Add("@last_name", MySqlType.VarChar, 40, "last_name")
cmdCustUpdate.Parameters.Add("@first_name", MySqlType.VarChar, 40, "first_name")
cmdCustUpdate.Parameters.Add("@creation_date", MySqlType.Date, Nothing, "creation_date")
cmdCustUpdate.Parameters.Add("@cust_category", MySqlType.VarChar, 20, "cust_category")
cmdCustUpdate.Parameters.Add("@salesperson", MySqlType.Int, 9, "salesperson")
cmdCustUpdate.Parameters.Add("@active_flag", MySqlType.TinyInt, 1, "active_flag")
cmdCustUpdate.Parameters.Add("@company_name", MySqlType.VarChar, 80, "company_name")
cmdCustUpdate.Parameters.Add("@addr_1", MySqlType.VarChar, 80, "addr_1")
cmdCustUpdate.Parameters.Add("@addr_2", MySqlType.VarChar, 80, "addr_2")
cmdCustUpdate.Parameters.Add("@city", MySqlType.VarChar, 40, "city")
cmdCustUpdate.Parameters.Add("@state", MySqlType.Char, 2, "state")
cmdCustUpdate.Parameters.Add("@zip", MySqlType.VarChar, 10, "zip")
cmdCustUpdate.Parameters.Add("@county", MySqlType.VarChar, 40, "county")
cmdCustUpdate.Parameters.Add("@township", MySqlType.VarChar, 40, "township")
cmdCustUpdate.Parameters.Add("@phone_home", MySqlType.VarChar, 20, "phone_home")
cmdCustUpdate.Parameters.Add("@phone_work", MySqlType.VarChar, 20, "phone_work")
cmdCustUpdate.Parameters.Add("@phone_work_ext", MySqlType.VarChar, 8, "phone_work_ext")
cmdCustUpdate.Parameters.Add("@phone_mobile", MySqlType.VarChar, 20, "phone_mobile")
cmdCustUpdate.Parameters.Add("@fax", MySqlType.VarChar, 20, "fax")
cmdCustUpdate.Parameters.Add("@email", MySqlType.VarChar, 80, "email")
cmdCustUpdate.Parameters.Add("@website", MySqlType.VarChar, 80, "website")
cmdCustUpdate.Parameters.Add("@birth_date", MySqlType.Date, Nothing, "birth_date")
cmdCustUpdate.Parameters.Add("@sent_card", MySqlType.TinyInt, 1, "sent_card")
cmdCustUpdate.Parameters.Add("@notes", MySqlType.Text, Nothing, "notes")
cmdCustUpdate.Parameters.Add("@source", MySqlType.VarChar, 40, "source")
cmdCustUpdate.Parameters.Add("@lit_req_label", MySqlType.VarChar, 10, "lit_req_label")
cmdCustUpdate.Parameters.Add("@lib_req_label_sent_date", MySqlType.Date, Nothing, "lib_req_label_sent_date")
cmdCustUpdate.Parameters.Add("@lit_followup_date", MySqlType.Date, Nothing, "lit_followup_date")
cmdCustUpdate.Parameters.Add("@lit_followup_printed", MySqlType.TinyInt, 1, "lit_followup_printed")
cmdCustUpdate.Parameters.Add("@quote_label", MySqlType.VarChar, 10, "quote_label")
cmdCustUpdate.Parameters.Add("@quote_label_sent_date", MySqlType.Date, Nothing, "quote_label_sent_date")
cmdCustUpdate.Parameters.Add("@quote_followup_date", MySqlType.Date, Nothing, "quote_followup_date")
cmdCustUpdate.Parameters.Add("@quote_followup_printed", MySqlType.TinyInt, 1, "quote_followup_printed")
cmdCustUpdate.Parameters.Add("@checked_out", MySqlType.TinyInt, 1, "checked_out")
cmdCustUpdate.Parameters.Add("@check_out_pending", MySqlType.TinyInt, 1, "check_out_pending")
cmdCustUpdate.Parameters.Add("@modified_date", MySqlType.Date, Nothing, "modified_date")
cmdCustUpdate.Parameters.Add("@modified_by", MySqlType.SmallInt, 5, "modified_by")
cmdCustUpdate.Parameters.Add("@next_quote_number", MySqlType.SmallInt, 5, "next_quote_number")
cmdCustUpdate.Parameters.Add("@cust_id", MySqlType.VarChar, 12, "cust_id")
'myDa.ContinueUpdateOnError = True
myDa.UpdateCommand = cmdCustUpdate
Try
myCon.Open()
myDa.Update(myDs, "customers")
Catch Err As Exception
MsgBox("Customer Update Failed!")
MsgBox(Err.ToString)
Finally
myCon.Close()
End Try
End Sub
'@last_name' is missing at the statement but it does update the row when i uncomment that line im just wondering if the problem is the update command which seems to work every other time or if its me using one global data adapter to issue the updates any suggestions and comments will be greatley appreciated i need it to update it when as soon as the data is changed in that record