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]

Do us a favour, chuck some code tags into that post?
Oh, and switch into basic mode with

button first, else it makes a mess trying to syntax highlight

 

[mowafy]

xml

i see your private msg

i use this code and it give me a good result

[SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ds [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet, Sql [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]""[/COLOR][/SIZE][SIZE=2], cmd [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlCommand
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] da [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlDataAdapter([/SIZE][SIZE=2][COLOR=#800000]"select * from commpany"[/COLOR][/SIZE][SIZE=2], db)
da.AcceptChangesDuringUpdate = [/SIZE][SIZE=2][COLOR=#0000ff]False ' this line very important
[/COLOR][/SIZE][SIZE=2]da.Fill(ds, [/SIZE][SIZE=2][COLOR=#800000]"commpany"[/COLOR][/SIZE][SIZE=2])
[/SIZE][SIZE=2][COLOR=#008000]' بيانات الاصناف
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] System.IO.File.Exists([/SIZE][SIZE=2][COLOR=#0000ff]My[/COLOR][/SIZE][SIZE=2].Application.Info.DirectoryPath & [/SIZE][SIZE=2][COLOR=#800000]"\items.xml"[/COLOR][/SIZE][SIZE=2]) [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]ds.ReadXml([/SIZE][SIZE=2][COLOR=#0000ff]My[/COLOR][/SIZE][SIZE=2].Application.Info.DirectoryPath & [/SIZE][SIZE=2][COLOR=#800000]"\items.xml"[/COLOR][/SIZE][SIZE=2], XmlReadMode.ReadSchema)
cmd.Connection = db
cmd.CommandText = [/SIZE][SIZE=2][COLOR=#800000]"INSERT INTO [Items]"[/COLOR][/SIZE][SIZE=2] _
& [/SIZE][SIZE=2][COLOR=#800000]" ([ItmCode],[ItmBarCode],[ItmArabName],[ItmEngName],[ItmPrice1],[ItmPrice2]"[/COLOR][/SIZE][SIZE=2] _
& [/SIZE][SIZE=2][COLOR=#800000]" ,[ItmPrice3],[ItmPack],[ItmUnit],[ItmUnitQuantity],[ItmAverageCost],[ItmLastCost],[ItmOldAvrage]"[/COLOR][/SIZE][SIZE=2] _
& [/SIZE][SIZE=2][COLOR=#800000]" ,ItmUnitWeight,Itmdiscount,ItmOprCom,ItmSalCom,ItmDescripe,ItmUse,ItmNote,ItmImagePlace "[/COLOR][/SIZE][SIZE=2] _
& [/SIZE][SIZE=2][COLOR=#800000]" ,ItmType,ItmCategory,ItmSCategory,ItmSSCategory,OrderLevel,CLink)"[/COLOR][/SIZE][SIZE=2] _
& [/SIZE][SIZE=2][COLOR=#800000]" values (@ItmCode,@ItmBarCode,@ItmArabName,@ItmEngName,@ItmPrice1,@ItmPrice2,@ItmPrice3,"[/COLOR][/SIZE][SIZE=2] _
& [/SIZE][SIZE=2][COLOR=#800000]" @ItmPack,@ItmUnit,@ItmUnitQuantity,@ItmAverageCost,@ItmLastCost, @ItmOldAvrage"[/COLOR][/SIZE][SIZE=2] _
& [/SIZE][SIZE=2][COLOR=#800000]" ,@ItmUnitWeight,@Itmdiscount,@ItmOprCom,@ItmSalCom,@ItmDescripe,@ItmUse,@ItmNote "[/COLOR][/SIZE][SIZE=2] _
& [/SIZE][SIZE=2][COLOR=#800000]" ,@ItmImagePlace,@ItmType,@ItmCategory,@ItmSCategory,@ItmSSCategory,@OrderLevel,@CLink) "
[/COLOR][/SIZE][SIZE=2]cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@ItmCode"[/COLOR][/SIZE][SIZE=2], SqlDbType.NVarChar, 25, [/SIZE][SIZE=2][COLOR=#800000]"ItmCode"[/COLOR][/SIZE][SIZE=2])
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@itmBarCode"[/COLOR][/SIZE][SIZE=2], SqlDbType.NVarChar, 20, [/SIZE][SIZE=2][COLOR=#800000]"itmbarcode"[/COLOR][/SIZE][SIZE=2])
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@ItmArabName"[/COLOR][/SIZE][SIZE=2], SqlDbType.NVarChar, 150, [/SIZE][SIZE=2][COLOR=#800000]"ItmarabName"[/COLOR][/SIZE][SIZE=2])
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@ItmEngName"[/COLOR][/SIZE][SIZE=2], SqlDbType.NVarChar, 150, [/SIZE][SIZE=2][COLOR=#800000]"ItmEngName"[/COLOR][/SIZE][SIZE=2])
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@ItmPrice1"[/COLOR][/SIZE][SIZE=2], SqlDbType.Float, 8, [/SIZE][SIZE=2][COLOR=#800000]"ItmPrice1"[/COLOR][/SIZE][SIZE=2])
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@ItmPrice2"[/COLOR][/SIZE][SIZE=2], SqlDbType.Float, 8, [/SIZE][SIZE=2][COLOR=#800000]"ItmPrice2"[/COLOR][/SIZE][SIZE=2])
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@ItmPrice3"[/COLOR][/SIZE][SIZE=2], SqlDbType.Float, 8, [/SIZE][SIZE=2][COLOR=#800000]"ItmPrice3"[/COLOR][/SIZE][SIZE=2])
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@ItmPack"[/COLOR][/SIZE][SIZE=2], SqlDbType.SmallInt, 4, [/SIZE][SIZE=2][COLOR=#800000]"ItmPack"[/COLOR][/SIZE][SIZE=2])
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@ItmUnit"[/COLOR][/SIZE][SIZE=2], SqlDbType.NVarChar, 20, [/SIZE][SIZE=2][COLOR=#800000]"ItmUnit"[/COLOR][/SIZE][SIZE=2])
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@ItmUnitQuantity"[/COLOR][/SIZE][SIZE=2], SqlDbType.Float, 8, [/SIZE][SIZE=2][COLOR=#800000]"ItmUnitQuantity"[/COLOR][/SIZE][SIZE=2])
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@ItmAverageCost"[/COLOR][/SIZE][SIZE=2], SqlDbType.Float, 8, [/SIZE][SIZE=2][COLOR=#800000]"ItmAverageCost"[/COLOR][/SIZE][SIZE=2])
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@ItmLastCost"[/COLOR][/SIZE][SIZE=2], SqlDbType.Money, 8, [/SIZE][SIZE=2][COLOR=#800000]"ItmLastCost"[/COLOR][/SIZE][SIZE=2])
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@ItmOldAvrage"[/COLOR][/SIZE][SIZE=2], SqlDbType.Money, 8, [/SIZE][SIZE=2][COLOR=#800000]"ItmOldAvrage"[/COLOR][/SIZE][SIZE=2])
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@ItmUnitWeight"[/COLOR][/SIZE][SIZE=2], SqlDbType.NVarChar, 50, [/SIZE][SIZE=2][COLOR=#800000]"ItmUnitWeight"[/COLOR][/SIZE][SIZE=2])
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@Itmdiscount"[/COLOR][/SIZE][SIZE=2], SqlDbType.Float, 8, [/SIZE][SIZE=2][COLOR=#800000]"Itmdiscount"[/COLOR][/SIZE][SIZE=2])
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@ItmOprCom"[/COLOR][/SIZE][SIZE=2], SqlDbType.Float, 8, [/SIZE][SIZE=2][COLOR=#800000]"ItmOprCom"[/COLOR][/SIZE][SIZE=2])
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@ItmSalCom"[/COLOR][/SIZE][SIZE=2], SqlDbType.Float, 8, [/SIZE][SIZE=2][COLOR=#800000]"ItmSalCom"[/COLOR][/SIZE][SIZE=2])
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@ItmDescripe"[/COLOR][/SIZE][SIZE=2], SqlDbType.NVarChar, 4000, [/SIZE][SIZE=2][COLOR=#800000]"ItmDescripe"[/COLOR][/SIZE][SIZE=2])
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@ItmUse"[/COLOR][/SIZE][SIZE=2], SqlDbType.NVarChar, 4000, [/SIZE][SIZE=2][COLOR=#800000]"ItmUse"[/COLOR][/SIZE][SIZE=2])
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@ItmNote"[/COLOR][/SIZE][SIZE=2], SqlDbType.NVarChar, 4000, [/SIZE][SIZE=2][COLOR=#800000]"ItmNote"[/COLOR][/SIZE][SIZE=2])
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@ItmImagePlace"[/COLOR][/SIZE][SIZE=2], SqlDbType.NVarChar, 300, [/SIZE][SIZE=2][COLOR=#800000]"ItmImagePlace"[/COLOR][/SIZE][SIZE=2])
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@ItmType"[/COLOR][/SIZE][SIZE=2], SqlDbType.NVarChar, 50, [/SIZE][SIZE=2][COLOR=#800000]"ItmType"[/COLOR][/SIZE][SIZE=2])
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@ItmCategory"[/COLOR][/SIZE][SIZE=2], SqlDbType.BigInt, 8, [/SIZE][SIZE=2][COLOR=#800000]"ItmCategory"[/COLOR][/SIZE][SIZE=2])
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@ItmSCategory"[/COLOR][/SIZE][SIZE=2], SqlDbType.BigInt, 8, [/SIZE][SIZE=2][COLOR=#800000]"ItmSCategory"[/COLOR][/SIZE][SIZE=2])
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@ItmSSCategory"[/COLOR][/SIZE][SIZE=2], SqlDbType.BigInt, 8, [/SIZE][SIZE=2][COLOR=#800000]"ItmSSCategory"[/COLOR][/SIZE][SIZE=2])
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@OrderLevel"[/COLOR][/SIZE][SIZE=2], SqlDbType.BigInt, 8, [/SIZE][SIZE=2][COLOR=#800000]"OrderLevel"[/COLOR][/SIZE][SIZE=2])
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@CLink"[/COLOR][/SIZE][SIZE=2], SqlDbType.Bit, 1, [/SIZE][SIZE=2][COLOR=#800000]"CLink"[/COLOR][/SIZE][SIZE=2])
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] lm [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][SIZE=2] = ds.Tables([/SIZE][SIZE=2][COLOR=#800000]"items"[/COLOR][/SIZE][SIZE=2]).Rows.Count
da.TableMappings.Add([/SIZE][SIZE=2][COLOR=#800000]"Items"[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#800000]"Items"[/COLOR][/SIZE][SIZE=2])
da.InsertCommand = cmd
da.Update(ds.Tables([/SIZE][SIZE=2][COLOR=#800000]"Items"[/COLOR][/SIZE][SIZE=2]))
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE]

 

[dazlerd]

Thanks for the reply Mowafy.

Unfortunatley for me my code still give the same "Column 'email' cannot be null" error.

My code now looks like:

    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
 
        adapterCustomer.AcceptChangesDuringUpdate = False ' this line very important
 
        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
 
        ' Table mappings
        Dim tableMapping As New System.Data.Common.DataTableMapping
        tableMapping.SourceTable = "Customer"
        tableMapping.DataSetTable = "Customer"
        tableMapping.ColumnMappings.Add("CustomerId", "CustomerId")
        tableMapping.ColumnMappings.Add("Email", "Email")
        tableMapping.ColumnMappings.Add("password", "password")
        adapterCustomer.TableMappings.Add(tableMapping) 
    End Sub

Any other ideas?

Thanks

 

[cjard]

make sure youre not trying to shove a null into the email column!

 

[dazlerd]

good thinking !!

I have already, every record in the datatable has a field in it.

ds.Tables("Customer").Rows.Item(2).Item ("email")
always returns a record from 0 to 29.

 

[dazlerd]

This is getting even stranger. I renamed all the columns in the dataadapter to exactly the same case as the mysql database. Then I renamed the xml elements to this case as well. They are all in camelCase except for customer_id.

When I run the code now i still get the null error but on the first column im trying to insert and thats customer_id.

If I do
? ds.Tables("Customer").Rows.Item(2).Item("customer_id")
it returns 2903.

And If I change insert statment to always insert '0' then the error fails on email as that the second column.

So the value is in the datatable but doesnt seem to go into the insert statement. Is there anything wrong with me parameter statements?

    Public Sub getCustomerDataset(ByRef result As DataSet, _
                                    ByRef adapterCustomer As MySqlDataAdapter)
        Dim sql As String
 
        adapterCustomer.AcceptChangesDuringUpdate = False ' this line very important
 
        sql = "SELECT customer_id, email, password FROM customer WHERE customer_id = 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 (customer_id, email, password) "
        sql = sql & " VALUES (@customer_id, @email, @password); "
        Dim iCmd As MySqlCommand = New MySqlCommand(sql, CAS.DataConnection.getLocalConnection)
        iCmd.Parameters.Add(New MySqlParameter("@customer_id", "customer_id"))
        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 [customer_id] = @customer_id, [email] = @email, [password] = @password "
        sql = sql & "WHERE [customer_id] = @customer_id; "
        Dim uCmd As MySqlCommand = New MySqlCommand(sql, CAS.DataConnection.getLocalConnection)
        uCmd.Parameters.Add(New MySqlParameter("@customer_id", MySqlDbType.Int16, 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
 
        ' Table mappings
        Dim tableMapping As New System.Data.Common.DataTableMapping
        tableMapping.SourceTable = "Customer"
        tableMapping.DataSetTable = "Customer"
        tableMapping.ColumnMappings.Add("customer_id", "customer_id")
        tableMapping.ColumnMappings.Add("email", "email")
        tableMapping.ColumnMappings.Add("password", "password")
        adapterCustomer.TableMappings.Add(tableMapping)
 
    End Sub

Thanks

 

[cjard]

More likely your table/source column mappings..

The other thing I note is that your insert command has a mismatch on the case of the email parameter name..

 

[cjard]

How come youre writing the Data Access Layer yourself?

 

[dazlerd]

Its using a MYSQL database and vs.net doesnt let you connect to other dbs than mssql or oracle when making a connection for a query adapter. You can if you use ODBC but we dont want to .

 

[cjard]

you know, I would actually be tempted to create an empty table in SQLs and and link to that, then transport the generated code to mysql, to see if you can deduce the error. The other thing I saw, on MySQL site, was that some mysql library could be used in the designer..

http://www.mysql.com/products/connector/net/

hot air?

 

[dazlerd]

Hi Cjard

I use the MySQL Connector/Net to connect to the db and it works no problem. It doesnt however let you connect to the db from a dataset.

Thats why Im having to write the data access layer myself.

http://forums.mysql.com/read.php?38,7523,7523#msg-7523


"I would actually be tempted to create an empty table in SQLs and and link to that, then transport the generated code to mysql, to see if you can deduce the error."
Sorry Im confused as to what you mean, could you explain in mor detail please?

 

[cjard]

sorry, i should have said "create an empty table in SQLS" - i.e. sql server, something the IDE can wizard to...

 

[cjard]

the link you provided:

Hi Guys.

I have had a number of issues trying to get mysql to work with Visual Studio .NET and Crystal Reports. FYI it is based on the fact that all three different companies (and in particular Microsoft) do things differently. Microsoft doesn't want to help people who have their VS.NET software if they are not going to buy MS-SQL server, and have no intention of helping you anyway unless you pay their overbloated prices just to give THEM some information.

The SERVER EXPLORER was written with only Microsoft and Oracle based databases in mind, but that doesn't mean that you cant do a run-around.

Once you deal with making the connection available in server explorer you will be faced with another issue which I shall explain later.

However, to make the connection in server explorer you need to do the following:

* first of all you need to install the MyODBC connector 3.51 (or latest) on the development machine (NB. you can find this at [www.mysql.com] )

* Create a datasource in Control Panel/Administrative Tools with a connection to your database. This data source is going to be used purely for Server Manager and you dont need to worry about creating the same data source on your clients PC when you have made your VS.NET application (Unless you want to) - I dont want to cover this in this answer, too long. For the purpose of this explanation I will pretend that you created a MyODBC data source called 'AADSN' to database 'noddy' on mysqlserver 'SERVER01' and have a root password of 'fred'. The server can be either the Computer Name (found in Control Panel/System/Computer Name), or alternatively it can be the IP Address.
NB. Make sure that you test this connection before continuing with this explanation.

* open your VS.NET project

* go to server explorer

* right-click on 'Data Connections'

* select 'Add Connection'

* In DataLink Properties, go to the provider tab and select "Microsoft OLE DB Provider For ODBC drivers"

* Click Next

* If you previously created an ODBC data source then you could just select that. The disadvantage of this is that when you install your project application on the client machine, the same data source needs to be there. I prefer to use a connection string. This should look something like:

DSN=AADSN;DESC=MySQL ODBC 3.51 Driver DSN;DATABASE=noddy;SERVER=SERVER01;UID=root;PASSWORD=fred;PORT=3306;SOCKET=;OPTION=11;STMT=;

If you omit the password from the connection string then you must make sure that the datasource you created (AADSN) contains a password.
I am not going to describe what these mean, you can look in the documentation for myodbc for that, just ensure that you get a "Connection Succeeded" message when you test the datasource.

*OK, so the database should appear in the server manager, now for the next problem.
*open the item/data connection. Your tables should appear in a list.
*Try to drag and drop a table onto the dataset.
*Eh, error:
The wizard detected the following problems when configuring the data adapter:

Details: Error in select clause: Expression Near ""
Error in From clause: Expression Near ""
Unable to parse text

I had this issue for some time and had to use a Microsoft Service Call in the process! So, since they charge us for information because they don't supply the answers in their 'Knowledge Base', I am going to relieve you of this problem and hopefully it will give me some good karma in the future.

The reason that you cannot dragdrop your item onto the dataset is because when the table description is passed to the dataset from server explorer, server explorer uses double quotes instead of mysql's single quotes. The way around this is to restart the mysql server using the --ansi-quotes parameter, so the mysqld statement should look something like this:

mysqld -uroot -ppassword --ansi-quotes --console

Obviously this is just an example to show you how to insert the parameter into the mysqld statement. Your mysqld command may look different. Another issue is that if you have sql statements in your code they may only accept the double quotes, not the single quotes, so expect more of the & chr(34) & episode's typical of Visual basic.

Anyway, once you restart the mysql server and try and drag drop the item from server explorer onto the dataset you still get the same message but the item does appear on the dataset. Ignore this error every time you have to do this - the problem is errored to screen even though the item does appear in the dataset.

So, theres your answer. There are a few other items that should be brought to your attention:

* it is advisable to use the mysql.connector.net v1 class if you wish to write a database application in visual studio. There is currently no GUI interface for it, but its the only one I have felt happy with so far, and its free from the mysql website: [dev.mysql.com]

* If you are planning on writing a database that needs to retain data in different character sets (French, Greek, Italian, Hungarian, Danish, Chinese, Japanese), beware! I have had to store these as BLOB fields because the Unicode characters in Windows do not match the MySQL unicode characters and so cannot be reproduced correctly if you try and store unicode characters using a Visual Studio.NET application. This issue is very messy. The disadvantage of storing these items as a blob is that if you wish to run a where clause or order by on a blob field you will get incorrect results. If this issue is resolved then I would consider using MySQL, but for now there is little being done to integrate MySQL with Visual Studio in the case of Unicode and standards.

Sorry for the bigggg explanation
Hope this helps
Nick.

Try this, but at the end of it, we can maybe pull some tricks.. For example, we can take the code it generated and move it to our own class, replacing all the OdbcType.Varchar with MySqlDbType.Varchar or whatever...

We might even be able to hack the XML that comprises the dataset so that it generates correct code.. Have alook at the XML and youll see its pretty easy; i find myself hacking it often in order to correct mistakes the designer has made with scalar queries

 

[dazlerd]

oh right, substitute the mysql db to mssql and see if my code works with this. Good idea ill try later.

Thanks