D
Dan Keeley
Hi All, I posted this m.p.d.l.vb but someone suggested I post here, so here
goes.
I've shortened it down to this simple example: I'm getting an error message
saying "Input string was not in a correct format"
I hope someone can help or spot the obvious mistake maybe?
I also wonder if there's any hints on debugging this - finding out exactly
what is wrong? What input string??
This example pretty much came out of a book, and i really can't see whats
wrong!
Imports System
Imports System.Drawing
Imports System.Collections
Imports System.ComponentModel
Imports System.Windows.Forms
Imports System.Data
'Imports System.Data.SqlClient
Imports System.Data.OleDb
Public Class SimpleInsertTest
Inherits System.Windows.Forms.Form
'Private dataGrid1 As System.Windows.Forms.DataGrid
Private myConnection As System.Data.SqlClient.SqlConnection
Private myDataSet As System.Data.DataSet
Private myDataAdapter As System.Data.OleDb.OleDbDataAdapter
Private myDataTable As DataTable
Friend WithEvents lbSuppliers As ListBox
#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
Dim conn As New System.Data.OleDb.OleDbConnection
conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & _
"C:\Documents and Settings\Owner\My Documents\supptool.mdb"
conn.Open()
' Create the data set and set a property
myDataSet = New DataSet
myDataSet.CaseSensitive = True
' Create the sql command and adssign the connection and the select
Dim Cmd As OleDbCommand = New OleDbCommand("Select TechnicalContact from
suppliers", conn)
' create the data adapter object and pass in the sql command object
myDataAdapter = New System.Data.OleDb.OleDbDataAdapter(Cmd)
myDataAdapter.TableMappings.Add("Table", "Suppliers")
' Initialize the custom update/delete/insert commands for this form.
InitializeCommands()
' Tell the myDataadabter to fill the dataset
myDataAdapter.Fill(myDataSet)
PopulateLB()
' display it in the grid
'dataGrid1.DataSource = myDataSet.Tables("Suppliers").Defaultview
End Sub
Private Sub PopulateLB()
myDataTable = myDataSet.Tables(0)
lbSuppliers.Items.Clear()
Dim dataRow As DataRow
For Each dataRow In myDataTable.Rows
lbSuppliers.Items.Add((dataRow("TechnicalContact")))
Next dataRow
End Sub
Private Sub AddParms(ByVal cmd As OleDbCommand, ByVal ParamArray cols() As
String)
'Add each parameter
Dim column As String
For Each column In cols
cmd.Parameters.Add("@" & column, SqlDbType.Char, 0, column)
Next column
End Sub 'AddParms
Private Sub InitializeCommands()
' Reuse the SelectCommand's Connection.
Dim connection As OleDbConnection =
CType(myDataAdapter.SelectCommand.Connection, OleDbConnection)
' Create an explicit reusable insert command
myDataAdapter.InsertCommand = connection.CreateCommand()
myDataAdapter.InsertCommand.CommandText = _
"Insert into Suppliers " & _
"( TechnicalContact ) " & _
"values ( @TechnicalContact )"
AddParms(myDataAdapter.InsertCommand, "TechnicalContact")
MsgBox(myDataAdapter.InsertCommand.CommandText)
End Sub
Protected Sub lbSuppliers_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles lbSuppliers.Click
Dim currentRow As DataRow = myDataTable.Rows(lbSuppliers.SelectedIndex)
txtTechnicalContact.Text = currentRow("TechnicalContact")
End Sub
Private Sub ClearFields()
txtTechnicalContact.Text = ""
End Sub
Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnClear.Click
ClearFields()
lbSuppliers.SetSelected(lbSuppliers.SelectedIndex, False)
End Sub
Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnNew.Click
Dim newRow As DataRow = myDataTable.NewRow()
newRow("TechnicalContact") = txtTechnicalContact.Text
' Add the new row
myDataTable.Rows.Add(newRow)
MsgBox(newRow.ItemArray.Length)
'Update the DB
Try
myDataAdapter.Update(myDataSet, "Suppliers")
myDataSet.AcceptChanges()
' Inform the user at this stage if necessary ( currently not )
Application.DoEvents()
'Repopulate the listbox
PopulateLB()
' Clear the fields
ClearFields()
Catch ex As Exception
myDataSet.RejectChanges()
MsgBox(ex.Message)
End Try
End Sub
End Class
goes.
I've shortened it down to this simple example: I'm getting an error message
saying "Input string was not in a correct format"
I hope someone can help or spot the obvious mistake maybe?
I also wonder if there's any hints on debugging this - finding out exactly
what is wrong? What input string??
This example pretty much came out of a book, and i really can't see whats
wrong!
Imports System
Imports System.Drawing
Imports System.Collections
Imports System.ComponentModel
Imports System.Windows.Forms
Imports System.Data
'Imports System.Data.SqlClient
Imports System.Data.OleDb
Public Class SimpleInsertTest
Inherits System.Windows.Forms.Form
'Private dataGrid1 As System.Windows.Forms.DataGrid
Private myConnection As System.Data.SqlClient.SqlConnection
Private myDataSet As System.Data.DataSet
Private myDataAdapter As System.Data.OleDb.OleDbDataAdapter
Private myDataTable As DataTable
Friend WithEvents lbSuppliers As ListBox
#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
Dim conn As New System.Data.OleDb.OleDbConnection
conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & _
"C:\Documents and Settings\Owner\My Documents\supptool.mdb"
conn.Open()
' Create the data set and set a property
myDataSet = New DataSet
myDataSet.CaseSensitive = True
' Create the sql command and adssign the connection and the select
Dim Cmd As OleDbCommand = New OleDbCommand("Select TechnicalContact from
suppliers", conn)
' create the data adapter object and pass in the sql command object
myDataAdapter = New System.Data.OleDb.OleDbDataAdapter(Cmd)
myDataAdapter.TableMappings.Add("Table", "Suppliers")
' Initialize the custom update/delete/insert commands for this form.
InitializeCommands()
' Tell the myDataadabter to fill the dataset
myDataAdapter.Fill(myDataSet)
PopulateLB()
' display it in the grid
'dataGrid1.DataSource = myDataSet.Tables("Suppliers").Defaultview
End Sub
Private Sub PopulateLB()
myDataTable = myDataSet.Tables(0)
lbSuppliers.Items.Clear()
Dim dataRow As DataRow
For Each dataRow In myDataTable.Rows
lbSuppliers.Items.Add((dataRow("TechnicalContact")))
Next dataRow
End Sub
Private Sub AddParms(ByVal cmd As OleDbCommand, ByVal ParamArray cols() As
String)
'Add each parameter
Dim column As String
For Each column In cols
cmd.Parameters.Add("@" & column, SqlDbType.Char, 0, column)
Next column
End Sub 'AddParms
Private Sub InitializeCommands()
' Reuse the SelectCommand's Connection.
Dim connection As OleDbConnection =
CType(myDataAdapter.SelectCommand.Connection, OleDbConnection)
' Create an explicit reusable insert command
myDataAdapter.InsertCommand = connection.CreateCommand()
myDataAdapter.InsertCommand.CommandText = _
"Insert into Suppliers " & _
"( TechnicalContact ) " & _
"values ( @TechnicalContact )"
AddParms(myDataAdapter.InsertCommand, "TechnicalContact")
MsgBox(myDataAdapter.InsertCommand.CommandText)
End Sub
Protected Sub lbSuppliers_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles lbSuppliers.Click
Dim currentRow As DataRow = myDataTable.Rows(lbSuppliers.SelectedIndex)
txtTechnicalContact.Text = currentRow("TechnicalContact")
End Sub
Private Sub ClearFields()
txtTechnicalContact.Text = ""
End Sub
Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnClear.Click
ClearFields()
lbSuppliers.SetSelected(lbSuppliers.SelectedIndex, False)
End Sub
Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnNew.Click
Dim newRow As DataRow = myDataTable.NewRow()
newRow("TechnicalContact") = txtTechnicalContact.Text
' Add the new row
myDataTable.Rows.Add(newRow)
MsgBox(newRow.ItemArray.Length)
'Update the DB
Try
myDataAdapter.Update(myDataSet, "Suppliers")
myDataSet.AcceptChanges()
' Inform the user at this stage if necessary ( currently not )
Application.DoEvents()
'Repopulate the listbox
PopulateLB()
' Clear the fields
ClearFields()
Catch ex As Exception
myDataSet.RejectChanges()
MsgBox(ex.Message)
End Try
End Sub
End Class