Help!! problem Update Mysql database

[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)