Oracle Insert statement

[CanMike]

Help!

I'm using VisualStudio.NET Academic. I'm only using VB.NET for now and I'm having trouble with the .Parameter.Add method. The Academic version does not include the Microsoft DB OLE Provider for Oracle driver, but I can successfully insert data if I hard code the values. The big problem is get VB to recognise that the placeholder '@in_name". Here's the code:


Dim cmd As OleDbCommand

conn.Open()
' this works?
cmd = New OleDBCommand("INSERT INTO testtable (c1, ta_name, ta_number)" & _
" VALUES('test4', 'Mike', 569)", conn)
' this fails because the value held by @in_name is not recognised
'cmd = New OleDbCommand("INSERT INTO testtable (c1, ta_name, ta_number)" & _
" VALUES('test4', @in_name, 569)", conn)

'cmd.Parameters.Add("@in_name", OleDbType.VarChar, 10, txtName.Text)

Try
cmd.ExecuteNonQuery()
MessageBox.Show("Row Inserted!")
Catch SQLExceptionErr As Exception
MessageBox.Show(SQLExceptionErr.Message)
End Try

conn.Close()

How do I get VB.NET to recognize the placeholder.

Thanks
Mike Parish

 

[Paszt]

In the overloaded method you are using: the fourth parameter of the Parameters.Add method (where you have txtName.Text) is looking for the SourceColumn.

I believe you want to use this:

cmd.Parameters.Add("@in_name", txtName.Text)

 

[CanMike]

No go. I keep getting the Oracle error 00936, "Missing Expression", which means the SQL command sent to Oracle is:

INSERT INTO testtable (c1, ta_name, ta_number) VALUES('test4', , 569)

Notice the blank where the value held by @in_name should be inserted into the statement.

Weird!

 

[vbkev]

I had a similar problem to this using the SQLCommand and SQLParameter objects.
Setting it's initial value to DBNull.Value sorted it out for me. could be a similar thing.

oParam = New SQLParameter("@ReturnedInt", DBNull.Value)
oParam.SqlDbType = System.Data.SqlDbType.Int
If Not sReturnedInt Is Nothing Then oParam.Value = sReturnedInt

 

[CanMike]

Thanks for the idea. I just got lucky. Here is the new code:

Dim myInsertStatement As String = "INSERT INTO testtable (c1, ta_name, ta_number) values (?, ? , 888)"
Dim mySQLCommand As New OleDbCommand(myInsertStatement, conn)

mySQLCommand.Parameters.Add("@in_test", OleDbType.VarChar, 5).Value = txtTest.Text
mySQLCommand.Parameters.Add("@in_name", OleDbType.VarChar, 10).Value = txtName.Text


conn.Open()

Try
mySQLCommand.ExecuteNonQuery()
MessageBox.Show("Row Inserted!")
Catch SQLExceptionErr As Exception
MessageBox.Show(SQLExceptionErr.Message)
End Try

conn.Close()

Duh! I'm now overloading the method correctly. This baby insert the values beautifully.

You can see I added another column from my test table.

I'm going to try your solution though. The more ways to access a Database the better.

I'm not convince .NET is a good solution for my clients (I'm still a JAVA guy), but today it make me rethink the package.

Thanks again and Thanks to all who responded. I'm sure to post more inquiries before I get a handle on VB.Net

Regards
Mike, Toronto, Canada