data adapter update failure

data adapter update failure

Postby rbirnesser » Fri 11 Mar 2005 16:43

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

            myDa.Update(myDs, "customers")
        Catch Err As Exception
            MsgBox("Customer Update Failed!")
        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
Posts: 37
Joined: Fri 11 Feb 2005 19:18

Postby Serious » Fri 11 Mar 2005 17:04

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
' myDa.Update(myDs, "customers")
cmdCustUpdate.Connection = myCon

this is funny

Postby rbirnesser » Sat 12 Mar 2005 00:16

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?
Posts: 37
Joined: Fri 11 Feb 2005 19:18

Postby rbirnesser » Sat 12 Mar 2005 00:19

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
Posts: 37
Joined: Fri 11 Feb 2005 19:18

Postby rbirnesser » Sat 12 Mar 2005 01:03

i tryed ur execute non query and it doesn't work that way but now it works the other way wierd
Posts: 37
Joined: Fri 11 Feb 2005 19:18

Postby Serious » Mon 14 Mar 2005 11:28

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);
        dataTable.Active = true;
        object [] values = {44, "w","s"};
        dataTable.Rows.Add( values);
        dataTable.Active = false;

hey oleg

Postby rbirnesser » Tue 15 Mar 2005 20:04

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

myCustDa.UpdateCommand = myCustCmb.GetUpdateCommand
myCustDa.Update(myDs, "customers")
Catch Err As Exception
MsgBox("Update Failed!")
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?
Posts: 37
Joined: Fri 11 Feb 2005 19:18

Postby Serious » Wed 16 Mar 2005 13:55

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

Postby rbirnesser » Wed 16 Mar 2005 15:48

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
Posts: 37
Joined: Fri 11 Feb 2005 19:18

Postby Serious » Wed 16 Mar 2005 16:06

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.


Postby rbirnesser » Fri 18 Mar 2005 23:18

thank you worked fine :)
Posts: 37
Joined: Fri 11 Feb 2005 19:18

Return to dotConnect for MySQL