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.
What could be wrong?
Thanks
Darren
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.
VB.NET:
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 SubWhat could be wrong?
Thanks
Darren
Last edited:
