data adapter update failure

[rbirnesser]

i have a module file that uses global ado .net objects and below is the code for them.

Code: Select all

    Public myCon As New MySqlConnection
    Public myCmd As New MySqlCommand
    Public myDa As New MySqlDataAdapter
    Public myDs As New ppbData

This is my insert command for the customers table which seems to be working ok when i add a new customer

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

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.

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)

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

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

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

[Serious]

There is no need for you to use MySqlDataAdapter in this case.
Just execute your command instead of using MySqlDataAdapter.Update() method

Code: Select all

myCon.Open() 
' myDa.Update(myDs, "customers") 
cmdCustUpdate.Connection = myCon
cmdCustUpdate.ExecuteNonQuery()

[rbirnesser]

today i reinstalled your new release .04 and what an improvement in speed for me considering my dataset is rather large and i wanna say thanks. Also the it fixed the updating thing to everytime i change some data and goto another form and do a sql query its updating everytime now. Your method looks interesting will that work real good since im using a parameterized query with alot of columns?

[rbirnesser]

i just wish there was a easier way to add the paremeters and write the update insert logic then righting it out like hand by that because the next table i need to do is the quotes table and thats like 30,000 rows plus and probally bout almost a hundred columns. i am using a strongly typed dataset with a xml schema file and like 200 relations or more with all the table and column mappings

[rbirnesser]

i tryed ur execute non query and it doesn't work that way but now it works the other way wierd

[Serious]

Try to use MySqlDataTable. It represents a single object that provides all of the functionality needed to retrieve and manipulate data from a MySQL server data source.

Code: Select all

      MySqlConnection connection = new MySqlConnection("host=localhost;database=test;user id=root;");
      MySqlCommand selectCommand = new MySqlCommand("dept", connection); 
      selectCommand.CommandType = CommandType.TableDirect;
      MySqlDataTable dataTable = new MySqlDataTable(selectCommand);
      connection.Open();
      try
      {
        dataTable.Active = true;
        object [] values = {44, "w","s"};
        dataTable.Rows.Add( values);
        dataTable.Update();
      }
      finally
      {
        dataTable.Active = false;
        connection.Close();
      }

[rbirnesser]

oleg i see that you have the msql datatable object there i thought about using it but i have 15 tables in my dataset with 200 relationships defined between the tables and i didn't know if u could use relationships with the datatable object i got the datadapter to update the information like i need it to using the commandbuilder which worked great here is the code i used... the endedit on the dataviewrow fixed it from updating every other time

Dim myCustIdx As Integer = BindingContext(myDs, "customers").Position
myCustDv.Item(myCustIdx).Row.EndEdit()

Try
myCustCon.Open()
myCustDa.UpdateCommand = myCustCmb.GetUpdateCommand
myCustDa.Update(myDs, "customers")
Catch Err As Exception
MsgBox("Update Failed!")
MsgBox(Err.Message)
Finally
myCustCon.Close()
End Try

And the command builer object wrote all the update and insert commands and parmeters for me which i wish i would have known before i wrote those long sql commands out by hand ;x only problem i have now if the field was updated to the database with text orginally and i delete that text and update it again it writes a blank value i wanted it to write a null how do i go about doing that?

[Serious]

If you are using DataGrid component you can insert NULL value with Ctrl+0 shortcut.

[rbirnesser]

in not using a datagrid unfortunately im using a textbox bindings i had to keep the design simiiar to the existing program. Also i tryed the command builder on my quotes table it works great and my quotes table is massive it has 466 columns and over 32,000 rows and i tried using parameters for the null value thing that didn't work its only when i have a field with something in it and i make that field empty then update is where it just puts a blank value in the database instead of a null value

[Serious]

To insert NULL value use following syntax

Code: Select all

cmd.Parameters["p1"].Value = DBNull.Value;

Earlier in the code you can check if this parameter value is empty string.

[rbirnesser]

thank you worked fine