ERROR [07002] [MySQL][

M

ME

I am getting:

"ERROR [07002] [MySQL][ODBC 3.51 Driver][mysqld-4.1.0-alpha]SQLBindParameter
not used for all parameters"

when attempting to run the following code:

Private Sub DataGrid1_UpdateCommand(ByVal source As Object, ByVal e As
System.Web.UI.WebControls.DataGridCommandEventArgs) Handles
DataGrid1.UpdateCommand

Dim tb As TextBox
Dim r As dsetClients.ClientsRow
Dim key As String = DataGrid1.DataKeys(e.Item.ItemIndex).ToString()
Dim clientname As String
Dim contactname As String
Dim addressid As Integer
Dim returnval As Integer

r = DsetClients1.Clients.FindByID(key)

tb = CType(e.Item.FindControl("txtClientName"), TextBox)
clientname = tb.Text

tb = CType(e.Item.FindControl("txtContactName"), TextBox)
contactname = tb.Text

tb = CType(e.Item.FindControl("txtAddressID"), TextBox)
addressid = Val(tb.Text)

r.AddressID = addressid
r.ClientName = clientname
r.ContactName = contactname

' IT DIES RIGHT HERE \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/
returnval = adapterClients.Update(DsetClients1, "Clients")

DataGrid1.DataSource = DsetClients1
DataGrid1.EditItemIndex = -1
DataGrid1.DataBind()
End Sub

Obviously I am attempting to update a MySQL database. The connection is
provided by a odbc connection using a system DSN. My datagrid is bound to a
dataset at design time. The dataset contains only one odbc
(microsoft.data.odbc) data adapter called "Clients". It uses the following
as its update command text:

UPDATE Clients SET ID=?, ClientName=?, ContactName=?, AddressID=? WHERE
(ID=?) AND (ClientName=? OR ? IS NULL AND ClientName IS NULL) AND
(ContactName=? OR ? IS NULL AND ContactName IS NULL)

I have tried removing the '?' and replacing them with static data (replaced
the '?'s with real data. It worked fine. I have enabled tracing on MySQL
3.51 driver and found that it logs the following:
SQLExecDirect
Click to expand...
| >SQLPrepare
| | >_mymalloc
| | | enter: Size: 191
| | | exit: ptr: 82728f8
| | <_mymalloc
| | enter: UPDATE Clients SET ID=?, ClientName=?, ContactName=?, AddressID=?
WHERE (ID=?) AND (ClientName=? OR ? IS NULL AND ClientName IS NULL) AND
(ContactName=? OR ? IS NULL AND ContactName IS NULL)
| | info : Parameter count: 9
| | exit : SQL_SUCCESS
| <SQLPrepare
| >SQLExecute
| | enter: stmt: 0x21dc98
| | >SQLFreeStmt
| | | enter: stmt: 0x21dc98 option: MYSQL_RESET_BUFFERS
| | | >mysql_free_result
| | | | enter: mysql_res: 0
| | | <mysql_free_result
| | | exit : SQL_SUCCESS
| | <SQLFreeStmt
| | >insert_params
| | | >copy_error
| | | | error: code :506, state: 07002, err :(null)
| | | | exit : SQL_ERROR
| | | <copy_error
| | <insert_params
| | >do_query
| | | exit : SQL_ERROR
| | <do_query
| | exit : SQL_ERROR
| <SQLExecute
| exit : SQL_ERROR
<SQLExecDirect


So I guess my question is how do I make it work with the dynamic setup? I
am EXTREMLY new to dotnet as well as MySQL data connections. If there is
any other information you guys need please let me know.

Thanks,

Matt
 
M

ME

Just an FYI. I figured it out. I had to add the parameters to the Update
Command at design time. The number of params must match number '?'s in the
command text. The parameters must be listed in the order of they occur in
the commend text.

Hope that helps someone! If you would like more details on this I can
provide just e-mail me directly or post a reply to this message.
trash.trash@comcastDOTnet

Thanks,

Matt

ME said:
I am getting:

"ERROR [07002] [MySQL][ODBC 3.51 Driver][mysqld-4.1.0-alpha]SQLBindParameter
not used for all parameters"

when attempting to run the following code:

Private Sub DataGrid1_UpdateCommand(ByVal source As Object, ByVal e As
System.Web.UI.WebControls.DataGridCommandEventArgs) Handles
DataGrid1.UpdateCommand

Dim tb As TextBox
Dim r As dsetClients.ClientsRow
Dim key As String = DataGrid1.DataKeys(e.Item.ItemIndex).ToString()
Dim clientname As String
Dim contactname As String
Dim addressid As Integer
Dim returnval As Integer

r = DsetClients1.Clients.FindByID(key)

tb = CType(e.Item.FindControl("txtClientName"), TextBox)
clientname = tb.Text

tb = CType(e.Item.FindControl("txtContactName"), TextBox)
contactname = tb.Text

tb = CType(e.Item.FindControl("txtAddressID"), TextBox)
addressid = Val(tb.Text)

r.AddressID = addressid
r.ClientName = clientname
r.ContactName = contactname

' IT DIES RIGHT HERE \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/
returnval = adapterClients.Update(DsetClients1, "Clients")

DataGrid1.DataSource = DsetClients1
DataGrid1.EditItemIndex = -1
DataGrid1.DataBind()
End Sub

Obviously I am attempting to update a MySQL database. The connection is
provided by a odbc connection using a system DSN. My datagrid is bound to a
dataset at design time. The dataset contains only one odbc
(microsoft.data.odbc) data adapter called "Clients". It uses the following
as its update command text:

UPDATE Clients SET ID=?, ClientName=?, ContactName=?, AddressID=? WHERE
(ID=?) AND (ClientName=? OR ? IS NULL AND ClientName IS NULL) AND
(ContactName=? OR ? IS NULL AND ContactName IS NULL)

I have tried removing the '?' and replacing them with static data (replaced
the '?'s with real data. It worked fine. I have enabled tracing on MySQL
3.51 driver and found that it logs the following:
SQLExecDirect
Click to expand...
| >SQLPrepare
| | >_mymalloc
| | | enter: Size: 191
| | | exit: ptr: 82728f8
| | <_mymalloc
| | enter: UPDATE Clients SET ID=?, ClientName=?, ContactName=?, AddressID=?
WHERE (ID=?) AND (ClientName=? OR ? IS NULL AND ClientName IS NULL) AND
(ContactName=? OR ? IS NULL AND ContactName IS NULL)
| | info : Parameter count: 9
| | exit : SQL_SUCCESS
| <SQLPrepare
| >SQLExecute
| | enter: stmt: 0x21dc98
| | >SQLFreeStmt
| | | enter: stmt: 0x21dc98 option: MYSQL_RESET_BUFFERS
| | | >mysql_free_result
| | | | enter: mysql_res: 0
| | | <mysql_free_result
| | | exit : SQL_SUCCESS
| | <SQLFreeStmt
| | >insert_params
| | | >copy_error
| | | | error: code :506, state: 07002, err :(null)
| | | | exit : SQL_ERROR
| | | <copy_error
| | <insert_params
| | >do_query
| | | exit : SQL_ERROR
| | <do_query
| | exit : SQL_ERROR
| <SQLExecute
| exit : SQL_ERROR
<SQLExecDirect


So I guess my question is how do I make it work with the dynamic setup? I
am EXTREMLY new to dotnet as well as MySQL data connections. If there is
any other information you guys need please let me know.

Thanks,

Matt
Click to expand...
 
T

Tom Shelton

Just an FYI. I figured it out. I had to add the parameters to the Update
Command at design time. The number of params must match number '?'s in the
command text. The parameters must be listed in the order of they occur in
the commend text.

Hope that helps someone! If you would like more details on this I can
provide just e-mail me directly or post a reply to this message.
trash.trash@comcastDOTnet

Thanks,

Matt
Click to expand...

Matt...

Just wondering - have you tried out the ByteFX data provider for MySQL?
It is the standard data provider in the Mono distribution and it works
on MS .NET as well. I've been playing with it lately (on both Mono on
Linux and .NET on Windows) and it seems to work pretty well.
 
Top