Updating Access DB

[sradel]

I found this code at http://www.startvbdotnet.com/ado/msaccess.aspx but for some reason i can not get it to work. I am using Visual Studio 2005 with Vista 32. I have all the patches for Studio installed.

I get the error

A first chance exception of type 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll

on this line.

Str = "insert into table1 values(" & CInt(TextBox1.Text) & ",'" & TextBox2.Text & "','" & TextBox3.Text & "')"

I am new to DB Programming so any help is appreciated.
thanks
Scott


Here is the entire code.

Imports System.Data.OleDb
PublicClass Form1
Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
Dim icount AsInteger
Dim str AsString

PrivateSub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\emp.mdb;")
cn.Open()
Str = "insert into table1 values(" & CInt(TextBox1.Text) & ",'" & TextBox2.Text & "','" & TextBox3.Text & "')"
'string stores the command and CInt is used to convert number to string
cmd = New OleDbCommand(Str, cn)
icount = cmd.ExecuteNonQuery
MessageBox.Show(icount)
'displays number of records inserted
Catch
EndTry
cn.Close()
EndSub
EndClass

 

[Administrator]

I believe the problem is your SQL statement (which is usually where the problem lies). You need to specify your field names:

INSERT INTO Table1 (Field1, Field2, Field3) VALUES (...as you have...)

 

[lingsn]

Yes, the error might be from the SQL line there, I see you used the string concatenation to generate the SQL and you tried to cast a string in the textbox to the integer. I expect the error might from here. You can try

Str = [SIZE=2][COLOR=#a31515]"insert into table1 values("[/COLOR][/SIZE][SIZE=2] & [/SIZE][SIZE=2]TextBox1.Text & [/SIZE][SIZE=2][COLOR=#a31515]",'"[/COLOR][/SIZE][SIZE=2] & TextBox2.Text & [/SIZE][SIZE=2][COLOR=#a31515]"','"[/COLOR][/SIZE][SIZE=2] & TextBox3.Text & [/SIZE][SIZE=2][COLOR=#a31515]"')"[/COLOR][/SIZE]

 

[cjard]

Str = "insert into table1 values(" & CInt(TextBox1.Text) & ",'" & TextBox2.Text & "','" & TextBox3.Text & "')"

Dont write SQLs like this; it's been a bad way to do it ever since parameterized queries were invented (20+ years ago, maybe?)

Take a read of the DW2 link in my signature

 

[Administrator]

Dont write SQLs like this

Give him an example, not send to read a book on ADO.NET which is DW2...

 

[cjard]

It's the most legendary online tutorial ever..

As Neal requested.. some examples:
http://www.vbdotnetforums.com/showthread.php?p=56447#post56447