Problem updating database with da.Update

[dazlerd]

Hi

I have an XML file which contains 30 new "customers" that need adding to the database. Each <customer> record has a customerid, email and password. These fields get added into the datatable correctly.

I've tried the following but I get an error "column 'email' cannot be null" when calling da.Update(ds, "Customer"). I am using vb.net 2005 with a MySQL db.

     Private Sub InsertIntoCustomersTable(ByVal XMLString As String)
        Dim ds As New DataSet
        Dim da As New MySqlDataAdapter
        Dim sr As New System.IO.StringReader(XMLString)

        ' Set the data adapter statements
        getCustomerDataset(ds, da)
        ' Read in the XMLString to populate the table
        ds.ReadXml(sr, XmlReadMode.IgnoreSchema)
        ' Can we simply update db
        da.Update(ds, "Customer")

        ds.Dispose()
    End Sub

    Public Sub getCustomerDataset(ByRef result As DataSet, ByRef adapterCustomer As MySqlDataAdapter)

        Dim sql As String

        sql = "SELECT CustomerId, Email, password FROM customer WHERE 1 = 1"
        adapterCustomer = New MySqlDataAdapter(sql, CAS.DataConnection.getLocalConnection)
        If result.Tables.Contains("Customer") Then result.Tables("Customer").Clear()
        adapterCustomer.Fill(result, "Customer")

        sql = "INSERT INTO customer (Email, password) VALUES (@Email, @password); "
        sql = sql & "SELECT CustomerId, email, password FROM customer WHERE CustomerId = SCOPE_IDENTITY()"
        Dim iCmd As MySqlCommand = New MySqlCommand(sql, CAS.DataConnection.getLocalConnection)
        iCmd.Parameters.Add(New MySqlParameter("@EMAIL", MySqlDbType.VarChar, 50, "Email"))
        iCmd.Parameters.Add(New MySqlParameter("@password", MySqlDbType.VarChar, 100, "password"))
        adapterCustomer.InsertCommand = iCmd

        sql = "UPDATE customer SET [CustomerId] = @CustomerId, [email] = @Email, [password] = @password "
        sql = sql & "WHERE [CustomerId] = @CustomerId; "
        sql = sql & "SELECT CustomerId, email, password FROM customer WHERE CustomerId = SCOPE_IDENTITY()"
        Dim uCmd As MySqlCommand = New MySqlCommand(sql, CAS.DataConnection.getLocalConnection)
        uCmd.Parameters.Add(New MySqlParameter("@CustomerId", MySqlDbType.Int32, 0, "Customer_id"))
        uCmd.Parameters.Add(New MySqlParameter("@Email", MySqlDbType.VarChar, 50, "Email"))
        uCmd.Parameters.Add(New MySqlParameter("@password", MySqlDbType.VarChar, 100, "password"))
        adapterCustomer.UpdateCommand = uCmd
    End Sub

What could be wrong?

Thanks

Darren

 

[cjard]

edit the connection string in the settings..

 

[dazlerd]

Ive now removed all instances of the word 'BOA' which is the name of the MSSQL database.

Ive replaced DbObjectName="boa.dbo.orders" with DbObjectName="cas.orders". cas is the name of the mysql database.

When trying to preview data I get the following error:

mssqlds.CC_customer.Fill,GetData () could not be previewed.
 
Unable to find connection 'mainConnectionString' for object 'MySettings'.  The connection string could not be found in application settings, or the data provider associated with the connection string could not be loaded.

The mainconnection string is in settings and is valid. I know this as I can open the database associated with mainconnectionstring.

Any ideas please?

Darren

 

[cjard]

The connection string needs to be a MySQL one, not a SQLServer one with the name of the mysql server in it..

I dont expect that it will work in the designer; that was never the intent.

The idea is to get the designer to write a bunch of nice code for you using sql server as the base (which the designer understands) then rip out the code by taking the dataset.designer.vb file and making it as if you had written the code as your Data Access Layer, changing it for MySQL

I doubt it will work from design time, as the mySQL provider has no integration with the designer.. But its more the run-time functionality we are looking for


Hopefully youre starting to see that the "free" solution is actually very costly compared to the paid-for solution. This process is nobbling your ability to create a DAL significantly, to the point where you will spend weeks writing DAL code (or days modifying DAL code the IDE generated for another RDBMS) that the designer could have generated and managed in minutes..

As noted before, both MS and Oracle (IDE integrated) do free versions of their products, that the client can upgrade to full at own-cost should the need arise. If they cant afford the SQL Server license, they certainly cant afford the fees you will charge them as a developer for the extra time required by you to implement the free solution, unless you sell yourself short or dont get paid, neither is a pleasant result..