Updating db using updatecommand

[flbos]

Hi,

I've got a dataadapter named 'daAdapter' that I want to use to update the Mysql database it is attached to. I've filled ad dataset named 'dsTabellen' with the dataadapter earlier (after changing a combo box in case that matters). The table I filled it with is named 'prijstabelregels'. I attached 'dstabellen' to a datagrid on my form using the datasource property. 'm_pCon' is the connection to the database.

When I close the form the code at the bottom of this message runs. I want to update the Mysql database with any changes made in the datagrid (only update not delete and insert for the time being).

I thought this was the right solution but it doesn't work. Everything works until the moment that Mysql server is going to process the update query that is passed.
This concerns this line of code":
daAdapter.Update(dsTabellen, "prijstabelregels")

I get the following error message:
"MySql.Data.MySqlClient.MySqlException: #42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'prijstabelregels' at line 1"

Apparently something went wrong when the update query was generated and so the query now contains a syntax error. However I don't know what exactly went wrong. Someone who has a clue?
I thought it would be a good start to view the update statement that's generated and then analyze the syntax but I don't know how I can make VB.net show me the syntax of the query that's being processed.



Dim _updateSqlCommand As New MySqlCommand
_updateSqlCommand.CommandType = CommandType.TableDirect
_updateSqlCommand.CommandText = "prijstabelregels"

_updateSqlCommand.Parameters.Add("@bedrnr", MySqlDbType.Int16, 11, "bedrnr")
_updateSqlCommand.Parameters.Add("@tabelnummer", MySqlDbType.Int16, 11, "tabelnummer")
_updateSqlCommand.Parameters.Add("@regelnummer", MySqlDbType.Int16, 11, "regelnummer")
_updateSqlCommand.Parameters.Add("@prijs", MySqlDbType.Double, 5, "prijs")
_updateSqlCommand.Parameters.Add("@aantal", MySqlDbType.Int16, 11, "aantal")
_updateSqlCommand.Parameters.Add("@eenheid", MySqlDbType.VarChar, 20, "eenheid")

_updateSqlCommand.Connection = m_pCon
daAdapter.UpdateCommand = _updateSqlCommand
daAdapter.Update(dsTabellen, "prijstabelregels")

 

[chrissie1]

the commandtype and commantext don't seem right.

comaandtype should be text or whatever it's called.

commandtext should be something like this

"update prijstabelregels set bedrne = @bedrnr, tabelnummer = @tabelnummer,..."

fill in the ...



Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]

 

[flbos]

Thanks for the answer, it works fine now! However I need to know one more thing. The update procedure is carried out when the form closes. It's possible that the form is closed before the datagrid is filled with data.

Of course in this case the user hasn't edited any data so the update procedure shouldn't run. In the current situation it does run and produces an error because the table to which I point can not be found.

I thought that checking whether the datasource property of the datagrid is set or not would be a solution to this problem. If the datasource is not set that would mean that no data currently is binded to the datagrid and therefore the update procedure can be skipped.

But how can I check whether the datasource property is set or not???