Help!! problem Update Mysql database

[keon2005]

Hi everyone,
I have problem updating mysql databse. I need to export data from excel files and then update it to mysql database. I am using dataset and odbc dataadapter to do so. below is my coding. There is no error but the databse is nver updated..................I have stuck here for 2 days!!! Please help!! Below is my codes..


Dim cnn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\cheekon1\My Documents\Imported data\QIS.xls;Extended Properties=""Excel 8.0;HDR=YES;""")
Dim da As New OleDb.OleDbDataAdapter("Select part_asset_num, part_ag_model_num,part_serial_num,part_model_num,part_parent_serial_num from [Asset$]", cnn)


Dim mysql As New Odbc.OdbcConnection("DSN=mysql;DATABASE=tracking;UID=root;PASSWORD=admin1;OPTION=3;")
Dim da2 As New OdbcDataAdapter("SELECT * FROM part", mysql)
Dim ds As New DataSet
Dim ds2 As New DataSet
Dim i As Integer

MySql.Open()
da.Fill(ds, "TestExcel")

da2.Fill(ds2, "Part")
da2.FillSchema(ds2, SchemaType.Source, "Part")

For i = 0 To ds.Tables("TestExcel").Rows.Count - 1
ds2.Tables("Part").ImportRow(ds.Tables("TestExcel").Rows(i))
Next

Console.WriteLine(ds2.Tables("Part").Rows.Count)
da2.Update(ds2, "part")
MySql.Close()

[jmcilhinney]

First up, OleDb supports MySQL so there's no point using Odbc. You're using OleDb for Excel so you should use OleDb for MySQL too. If you do that then there's no need for two DataAdapters. You can simply use one.

Second, if the two DataTables have the same schema then what's the point of having two? If you've already got a DataTable with the correct schema then there's no point creating another.

The first problem you have is that you have no changes in your DataTable to save. When you retrieve the data from Excel all your DataRows have a RowState of Unchanged. When you import those rows into the second DataTable they still have a RowState of Unchanged. That means that when you try to save the changed rows there are no changed rows to save.

To fix that you need to set the AcceptChangesDuringFill property of the retrieving DataAdapter to False. In that case AcceptChanges will not be implicitly called when you call Fill, thus all DataRows will have a RowState of Added.

Now, that leads to yet another issue with your code. You have no InsertCommand so you therefore cannot insert any new records with your DataAdapter. If you have no SQL INSERT statement then how are the records to be inserted?

This is what you SHOULD do:

Dim excelConnection As New OleDbConnection("Excel connection string here")
Dim mySqlConnection As New OleDbConnection("MySQL connection string here")
Dim adapter As New OleDbDataAdapter("SELECT statement here", excelConnection)
 
adapter.InsertCommand = New OleDbCommand("INSERT statement here", mySqlConnection)
 
'Add parameters to InsertCommand here.
 
Dim table As New DataTable
 
da.Fill(table)
da.Update(table)

That's it. There's nothing to say that each command of a data adapter has to use the same connection. Follow the Data Access link in my signature if you don't know how to create parameters. Just note that, if I'm not mistaken, MySQL uses "?" as a parameter prefix rather than "@".

You'll have to change your MySQL connection string for OleDb too. See www.connectionstrings.com.

[keon2005]

Hi, Thanks for the suggestion. sorry ,I am still quite new in this so ignore the enhancing code part first.

I have add dataadapter.acceptchangesduringfill=false

now it give me an error saying that

Update requires a valid InsertCommand when passed DataRow collection with new rows.

So how do i write a valid insert statement ??

[jmcilhinney]

You are mistaken if you think that using a DataSet and using parameters is an either/or situation. A DataSet is just an in-memory store of data. Parameters are a way to insert data values into an SQL statement. If you're using a DataSet to store data before inserting it into a database then you will invariably have to use parameters.

Note that a DataSet is unnecessary in this case. A DataSet doesn't actually contain any data. It simply contains DataTables. If your DataSet is only going to contain one DataTable and no DataRelations then it serves no purpose. You may as well just use a DataTable on its own, as I have demonstrated.

Now, a DataAdapter cannot automatically generate an InsertCommand of its own. You can use a CommandBuilder to do so though, but that would require doing things slightly differently to what I suggested. The DataAdapter's SelectCommand is used as a basis, so you cannot target a different database. You should also note that this doesn't mean that you're not using parameters. It just means that the CommandBuilder is creating them for you.

Again, I suggest that you follow the Data Access link in my signature. It contains examples of using a CommandBuilder and also creating the commands yourself. Even if you don't do so this time, you really should know how to create parameters yourself. It's very easy and essential for anyone using ADO.NET.

[keon2005]

Hi jmcilhinney,
Below is the code i have modified.... but it gave me an error stating

The OleDbType enumeration value, 22, is invalid.
Parameter name: OleDbType

at the parameter there......I want to import datarow from the excel to the datatable to update in mysql database. So do i actually need to add parameter?

Dim excelcnn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\cheekon1\My Documents\Imported data\QIS.xls;Extended Properties=""Excel 8.0;HDR=YES;""")
Dim mySqlcnn As New OleDbConnection("Provider=MySQLProv;Data Source=tracking;User Id=root;Password=admin1;")

Dim da As New OleDbDataAdapter("Select part_asset_num, part_ag_model_num,part_serial_num,part_model_num,part_parent_serial_num from [Asset$]", excelcnn)
Dim da2 As New OleDbDataAdapter("", mySqlcnn)
Dim insert As New OleDbCommand("INSERT into part (part_asset_num, part_ag_model_num,part_serial_num,part_model_num,part_parent_serial_num) VALUES(" & _
"?part_asset_num,?part_ag_model_num,?part_serial_num,?part_model_num,?part_parent_serial_num", mySqlcnn)

Dim dt As New DataTable
Dim dt2 As New DataTable
Dim i As Integer

da.Fill(dt)

insert.Parameters.Add("?part_asset_num", SqlDbType.VarChar, 50, "part_asset_num")
insert.Parameters.Add("?part_ag_model_num", SqlDbType.VarChar, 25, "part_ag_model_num")
insert.Parameters.Add("?part_serial_num", SqlDbType.VarChar, 50, "part_serial_num")
insert.Parameters.Add("?part_model_num", SqlDbType.VarChar, 25, "part_model_num")
insert.Parameters.Add("?part_parent_serial_num", SqlDbType.VarChar, 25, "part_parent_serial_num")
da.InsertCommand = insert
da.MissingSchemaAction = MissingSchemaAction.AddWithKey

da2.FillSchema(dt2, SchemaType.Source)
For i = 0 To dt.Rows.Count - 1
dt2.ImportRow(dt.Rows(i))
Next

Console.Write(dt2.Rows.Count)
da2.Update(dt2)

[jmcilhinney]

You're creating OleDbParameters so you use the OleDbType enumeration. The SqlDbType enumeration is for creating SqlParameters. That's only for SQL Server via SqlClient.

[keon2005]

Hi jmcilhinney,

Dim excelcnn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\cheekon1\My Documents\Imported data\QIS.xls;Extended Properties=""Excel 8.0;HDR=YES;""")
Dim mySqlcnn As New OdbcConnection("DSN=mysql;Database=tracking;User Id=root;Password=admin1;")

Dim da As New OleDbDataAdapter("Select part_asset_num, part_ag_model_num,part_serial_num,part_model_num,part_parent_serial_num from [Asset$]", excelcnn)
Dim da2 As New OdbcDataAdapter("Select * from Part", mySqlcnn)
Dim insert As New OdbcCommand("INSERT into part (part_asset_num, part_ag_model_num,part_serial_num,part_model_num,part_parent_serial_num) VALUES(" & _
"?part_asset_num,?part_ag_model_num,?part_serial_num,?part_model_num,?part_parent_serial_num)", mySqlcnn)

Dim dt As New DataTable
Dim dt2 As New DataTable
Dim i As Integer

da.AcceptChangesDuringFill = False
da.Fill(dt)

insert.Parameters.Add("?part_asset_num", OdbcType.VarChar, 50, "part_asset_num")
insert.Parameters.Add("?part_ag_model_num", OdbcType.VarChar, 25, "part_ag_model_num")
insert.Parameters.Add("?part_serial_num", OdbcType.VarChar, 50, "part_serial_num")
insert.Parameters.Add("?part_model_num", OdbcType.VarChar, 25, "part_model_num")
insert.Parameters.Add("?part_parent_serial_num", OdbcType.VarChar, 25, "part_parent_serial_num")
da2.InsertCommand = insert
da2.MissingSchemaAction = MissingSchemaAction.AddWithKey
Console.WriteLine(insert.CommandText.ToString())
da2.FillSchema(dt2, SchemaType.Source)

For i = 0 To dt.Rows.Count - 1
dt2.ImportRow(dt.Rows(i))
Next

Console.Write(dt2.Rows.Count)
da2.Update(dt2)


I have modified my code after debugging, i get the error MYSqlProv is not registered i local machine. Therefore i used back odbc for my connection to mysql........ and my reward is this error below. Any1 know how to correct it?

ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-6.0.4-alpha-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'part_asset_num,'E9900-66504'part_ag_model_num,'E99006650446032320062900049''

[keon2005]

Thanks jmcilhinney

I finally solved this error.

Dim insert As New OdbcCommand("INSERT into part (part_asset_num, part_agilent_model_num,part_serial_num,part_model_num,part_parent_serial_num) VALUES(" & _"?,?,?,?,?)", mySqlcnn)

I debug and saw that there is syntax error in it..... and i change the values to just ? which is parameter symbol.I not sure thought y it behave as such. but i have inserted to my database nicely.....