Problems doing an insert

[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

 

[Dan Keeley]

Ah, ok, I've found a way to make it work, much easier using
OleDbCommandBuilder!

( same as the ado one too.. )

Im still intrigued why this didnt work though, so any one up for a challenge
feel free to let me know !

Thanks!
Dan