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