Hi, I created a simple VB.NET 2003 application through Visual Studio
that connects to a MySQL database and loads a table into a Dataset, and
then displays that table information in a DataGrid on a form for
editing. The table fill works fine, the delete function works fine, but
when I try to update a row, the application fails with the following
error message:
An unhandled exception of type 'System.Data.Odbc.OdbcException' occurred
in system.data.dll
Additional information: System error.
'Data Access VB App.exe': Loaded
'c:\windows\assembly\gac\microsoft.visualbasic\7.0 .5000.0__b03f5f7f11d50
a3a\microsoft.visualbasic.dll', No symbols loaded.
Unhandled Exception: System.Data.Odbc.OdbcException: ERROR [07002]
[MySQL][ODBC 3.51 Driver][mysqld-4.0.21-debug]SQLBindParameter not used
for all parameters
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet)
at Data_Access_VB_App.Form1.btnUpdate_Click(Object sender, EventArgs
e) in C:\Documents and Settings\System Admin\My Documents\Visual Studio
Projects\Data Access VB App\Form1.vb:line 225
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventAr gs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons
button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProThe program '[2608] Data Access
VB App.exe' has exited with code 0 (0x0).
c(Message& m)
at System.Windows.Forms.ControlNativeWindow.OnMessage (Message& m)
at System.Windows.Forms.ControlNativeWindow.WndProc(M essage& m)
at System.Windows.Forms.NativeWindow.DebuggableCallba ck(IntPtr hWnd,
Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchM essageW(MSG&
msg)
at
System.Windows.Forms.ComponentManager.System.Windo ws.Forms.UnsafeNativeM
ethods+IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32
reason, Int32 pvLoopData)
at System.Windows.Forms.ThreadContext.RunMessageLoopI nner(Int32
reason, ApplicationContext context)
at System.Windows.Forms.ThreadContext.RunMessageLoop( Int32 reason,
ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at Data_Access_VB_App.Form1.Main() in C:\Documents and
Settings\System Admin\My Documents\Visual Studio Projects\Data Access VB
App\Form1.vb:line 1
The majority of the code was generated by Visual Studio and based on the
error message it would seem that I have to add SQLBindParameter
statements, but I am not sure the syntax that I should be using. I am
new to VB.NET (coming from Java/J2EE) and haven't been able to come up
with a workable solution after pouring over the MSDN manuals. Here is
the generated code:
Public Class Form1
Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As
Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents OdbcDataAdapter1 As
System.Data.Odbc.OdbcDataAdapter
Friend WithEvents OdbcConnection1 As System.Data.Odbc.OdbcConnection
Friend WithEvents DsTags1 As Data_Access_VB_App.dsTags
Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
Friend WithEvents btnUpdate As System.Windows.Forms.Button
Friend WithEvents btnLoad As System.Windows.Forms.Button
Friend WithEvents OdbcDataAdapter2 As
System.Data.Odbc.OdbcDataAdapter
Friend WithEvents OdbcSelectCommand2 As System.Data.Odbc.OdbcCommand
Friend WithEvents OdbcInsertCommand2 As System.Data.Odbc.OdbcCommand
Friend WithEvents OdbcConnection2 As System.Data.Odbc.OdbcConnection
Friend WithEvents OdbcDeleteCommand2 As System.Data.Odbc.OdbcCommand
Friend WithEvents OdbcUpdateCommand2 As System.Data.Odbc.OdbcCommand
Friend WithEvents OdbcSelectCommand1 As System.Data.Odbc.OdbcCommand
Friend WithEvents OdbcInsertCommand1 As System.Data.Odbc.OdbcCommand
Friend WithEvents OdbcDeleteCommand1 As System.Data.Odbc.OdbcCommand
Friend WithEvents OdbcUpdateCommand1 As System.Data.Odbc.OdbcCommand
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.OdbcDataAdapter1 = New System.Data.Odbc.OdbcDataAdapter
Me.OdbcConnection1 = New System.Data.Odbc.OdbcConnection
Me.DsTags1 = New Data_Access_VB_App.dsTags
Me.DataGrid1 = New System.Windows.Forms.DataGrid
Me.btnUpdate = New System.Windows.Forms.Button
Me.btnLoad = New System.Windows.Forms.Button
Me.OdbcDataAdapter2 = New System.Data.Odbc.OdbcDataAdapter
Me.OdbcDeleteCommand2 = New System.Data.Odbc.OdbcCommand
Me.OdbcConnection2 = New System.Data.Odbc.OdbcConnection
Me.OdbcInsertCommand2 = New System.Data.Odbc.OdbcCommand
Me.OdbcSelectCommand2 = New System.Data.Odbc.OdbcCommand
Me.OdbcUpdateCommand2 = New System.Data.Odbc.OdbcCommand
Me.OdbcSelectCommand1 = New System.Data.Odbc.OdbcCommand
Me.OdbcInsertCommand1 = New System.Data.Odbc.OdbcCommand
Me.OdbcDeleteCommand1 = New System.Data.Odbc.OdbcCommand
Me.OdbcUpdateCommand1 = New System.Data.Odbc.OdbcCommand
CType(Me.DsTags1,
System.ComponentModel.ISupportInitialize).BeginIni t()
CType(Me.DataGrid1,
System.ComponentModel.ISupportInitialize).BeginIni t()
Me.SuspendLayout()
'
'OdbcDataAdapter1
'
Me.OdbcDataAdapter1.DeleteCommand = Me.OdbcDeleteCommand1
Me.OdbcDataAdapter1.InsertCommand = Me.OdbcInsertCommand1
Me.OdbcDataAdapter1.SelectCommand = Me.OdbcSelectCommand1
Me.OdbcDataAdapter1.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping("Table", "epctag", New
System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("ID", "ID"), New
System.Data.Common.DataColumnMapping("OBJECT_EPC", "OBJECT_EPC"), New
System.Data.Common.DataColumnMapping("READER_EPC", "READER_EPC"), New
System.Data.Common.DataColumnMapping("DATE_TIME", "DATE_TIME"), New
System.Data.Common.DataColumnMapping("PAYLOAD", "PAYLOAD"), New
System.Data.Common.DataColumnMapping("GTIN_DOMAIN" , "GTIN_DOMAIN"), New
System.Data.Common.DataColumnMapping("GTIN_CLASS", "GTIN_CLASS")})})
Me.OdbcDataAdapter1.UpdateCommand = Me.OdbcUpdateCommand1
'
'OdbcConnection1
'
Me.OdbcConnection1.ConnectionString =
"STMT=;OPTION=3;DSN=EPCThingsNET;UID=root;SOCKET=; DESC=MySQL ODBC 3.51
Driver DSN;" & _
"DATABASE=thingsnet;SERVER=localhost;PORT=3306 "
'
'DsTags1
'
Me.DsTags1.DataSetName = "dsTags"
Me.DsTags1.Locale = New
System.Globalization.CultureInfo("en-US")
'
'DataGrid1
'
Me.DataGrid1.DataMember = "epctag"
Me.DataGrid1.DataSource = Me.DsTags1
Me.DataGrid1.HeaderForeColor =
System.Drawing.SystemColors.ControlText
Me.DataGrid1.Location = New System.Drawing.Point(8, 128)
Me.DataGrid1.Name = "DataGrid1"
Me.DataGrid1.Size = New System.Drawing.Size(584, 264)
Me.DataGrid1.TabIndex = 0
'
'btnUpdate
'
Me.btnUpdate.Location = New System.Drawing.Point(224, 48)
Me.btnUpdate.Name = "btnUpdate"
Me.btnUpdate.Size = New System.Drawing.Size(152, 23)
Me.btnUpdate.TabIndex = 1
Me.btnUpdate.Text = "Save Changes in Database"
'
'btnLoad
'
Me.btnLoad.Location = New System.Drawing.Point(56, 40)
Me.btnLoad.Name = "btnLoad"
Me.btnLoad.TabIndex = 2
Me.btnLoad.Text = "Load Data"
'
'OdbcSelectCommand1
'
Me.OdbcSelectCommand1.CommandText = "SELECT ID, OBJECT_EPC,
READER_EPC, DATE_TIME, PAYLOAD, GTIN_DOMAIN, GTIN_CLASS FR" & _
"OM epctag"
Me.OdbcSelectCommand1.Connection = Me.OdbcConnection1
'
'OdbcInsertCommand1
'
Me.OdbcInsertCommand1.CommandText = "INSERT INTO epctag(ID,
OBJECT_EPC, READER_EPC, DATE_TIME, PAYLOAD, GTIN_DOMAIN, G" & _
"TIN_CLASS) VALUES (?, ?, ?, ?, ?, ?, ?)"
Me.OdbcInsertCommand1.Connection = Me.OdbcConnection1
Me.OdbcInsertCommand1.Parameters.Add(New
System.Data.Odbc.OdbcParameter("ID", System.Data.Odbc.OdbcType.BigInt,
0, "ID"))
Me.OdbcInsertCommand1.Parameters.Add(New
System.Data.Odbc.OdbcParameter("OBJECT_EPC",
System.Data.Odbc.OdbcType.VarChar, 255, "OBJECT_EPC"))
Me.OdbcInsertCommand1.Parameters.Add(New
System.Data.Odbc.OdbcParameter("READER_EPC",
System.Data.Odbc.OdbcType.VarChar, 255, "READER_EPC"))
Me.OdbcInsertCommand1.Parameters.Add(New
System.Data.Odbc.OdbcParameter("DATE_TIME",
System.Data.Odbc.OdbcType.VarChar, 255, "DATE_TIME"))
Me.OdbcInsertCommand1.Parameters.Add(New
System.Data.Odbc.OdbcParameter("PAYLOAD",
System.Data.Odbc.OdbcType.VarChar, 255, "PAYLOAD"))
Me.OdbcInsertCommand1.Parameters.Add(New
System.Data.Odbc.OdbcParameter("GTIN_DOMAIN",
System.Data.Odbc.OdbcType.VarChar, 255, "GTIN_DOMAIN"))
Me.OdbcInsertCommand1.Parameters.Add(New
System.Data.Odbc.OdbcParameter("GTIN_CLASS",
System.Data.Odbc.OdbcType.VarChar, 255, "GTIN_CLASS"))
'
'OdbcDeleteCommand1
'
Me.OdbcDeleteCommand1.CommandText = "DELETE FROM epctag WHERE
(ID = ?)"
Me.OdbcDeleteCommand1.Connection = Me.OdbcConnection1
Me.OdbcDeleteCommand1.Parameters.Add(New
System.Data.Odbc.OdbcParameter("ID", System.Data.Odbc.OdbcType.BigInt,
0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "ID", System.Data.DataRowVersion.Original, Nothing))
'
'OdbcUpdateCommand1
'
Me.OdbcUpdateCommand1.CommandText = "UPDATE ecptag SET ID = ?,
OBJECT_EPC = ?, READER_EPC = ?, DATE_TIME = ?, PAYLOAD " & _
"= ?, GTIN_DOMAIN = ?, GTIN_CLASS = ? WHERE (ID = ?)"
Me.OdbcUpdateCommand1.Connection = Me.OdbcConnection1
Me.OdbcUpdateCommand1.Parameters.Add(New
System.Data.Odbc.OdbcParameter("ID", System.Data.Odbc.OdbcType.BigInt,
0, "ID"))
Me.OdbcUpdateCommand1.Parameters.Add(New
System.Data.Odbc.OdbcParameter("OBJECT_EPC",
System.Data.Odbc.OdbcType.VarChar, 255, "OBJECT_EPC"))
Me.OdbcUpdateCommand1.Parameters.Add(New
System.Data.Odbc.OdbcParameter("READER_EPC",
System.Data.Odbc.OdbcType.VarChar, 255, "READER_EPC"))
Me.OdbcUpdateCommand1.Parameters.Add(New
System.Data.Odbc.OdbcParameter("DATE_TIME",
System.Data.Odbc.OdbcType.VarChar, 255, "DATE_TIME"))
Me.OdbcUpdateCommand1.Parameters.Add(New
System.Data.Odbc.OdbcParameter("PAYLOAD",
System.Data.Odbc.OdbcType.VarChar, 255, "PAYLOAD"))
Me.OdbcUpdateCommand1.Parameters.Add(New
System.Data.Odbc.OdbcParameter("GTIN_DOMAIN",
System.Data.Odbc.OdbcType.VarChar, 255, "GTIN_DOMAIN"))
Me.OdbcUpdateCommand1.Parameters.Add(New
System.Data.Odbc.OdbcParameter("GTIN_CLASS",
System.Data.Odbc.OdbcType.VarChar, 255, "GTIN_CLASS"))
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(608, 398)
Me.Controls.Add(Me.btnLoad)
Me.Controls.Add(Me.btnUpdate)
Me.Controls.Add(Me.DataGrid1)
Me.Name = "Form1"
Me.Text = "Form1"
CType(Me.DsTags1,
System.ComponentModel.ISupportInitialize).EndInit( )
CType(Me.DataGrid1,
System.ComponentModel.ISupportInitialize).EndInit( )
Me.ResumeLayout(False)
End Sub
#End Region
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnUpdate.Click
OdbcDataAdapter1.Update(DsTags1)
MessageBox.Show("Database Updated!")
End Sub
Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnLoad.Click
OdbcDataAdapter1.Fill(DsTags1)
MessageBox.Show("Data Table Loaded Successfully!")
End Sub
End Class
That's the long and short of it! Appreciate any help.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
