Error: ORA-01008: not all variables bound in VB.Net

Question

0.00/5 (No votes)

See more:

I need help here...I'm not good in using Oracle Connection in VB.Net. I encountered the above error: ORA-01008: not all variables bound.
Below are my codes in VB.Net. Appreciate if you could help me to resolve the problem. Thank you.

VB

Dim iRet As Integer = 0
sSql = "INSERT INTO RECIPEINFO VALUES('" & sRecipeName & _
"','" & sMcNo & "','" & sMcType & "','" & sLotNo & "','" & sDevice & _
"','" & sRecipeStatus & "','" & sEmpNo & "','" & sIndicator & "','" & sMode & "',:DateTime,:Data)"
'cmd = New SqlCommand(sSql, myConnection)
cmd = New OracleCommand(sSql, myConnection)

cmd.Parameters.Add(New OracleParameter(":Data", OracleDbType.Blob, 500, "Data"))
cmd.Parameters.Item(":Data").Value = buffer

'executing the command and assigning it to connection
iRet = cmd.ExecuteNonQuery()
cmd = Nothing
myConnection.Close()

Posted 13-Oct-11 5:56am

mlco76

Updated 13-Oct-11 6:28am

Mehdi Gholam

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Simon Bang Terkildsen · Answer 1 · 2011-10-13T06:42:00

Solution 1

You must add a parameter for :DateTime like you've done for :Data, or remove :DateTime from your sql

Posted 13-Oct-11 6:42am

Simon Bang Terkildsen

Comments

mlco76 13-Oct-11 20:36pm

After putting in the DateTime values, I encounter a new error this time.
"ORA-00913: too many values"
Below are my coding.
Dim iRet As Integer = 0
sSql = "INSERT INTO RECIPEINFO VALUES('" & sRecipeName & _
"','" & sMcNo & "','" & sMcType & "','" & sLotNo & "','" & sDevice & _
"','" & sRecipeStatus & "','" & sEmpNo & "','" & sIndicator & "','" & sMode & "',:DateTime,:Data)"
cmd = New OracleCommand(sSql, myConnection)

cmd.Parameters.Add(New OracleParameter(":Data", OracleDbType.Blob))
cmd.Parameters.Item(":Data").Value = buffer

cmd.Parameters.Add(New OracleParameter(":DateTime", OracleDbType.Date))
cmd.Parameters(":DateTime").Value = Now

'executing the command and assigning it to connection
iRet = cmd.ExecuteNonQuery()
cmd = Nothing
myConnection.Close()

Btw, thank you Simon for your prompt reply.

Simon Bang Terkildsen 14-Oct-11 5:52am

That's likely because you don't have as many columns in your table as the number of values you're trying to insert into the new row.

mlco76 14-Oct-11 3:21am

Now I encounter this error: "ORA-00932: inconsistent datatypes: expected NUMBER got BLOB".

How to resolve this? Thank you.

Simon Bang Terkildsen 14-Oct-11 5:54am

That's because you're trying to insert a number (integer, double, ect.) into a column that has the type BLOB.
Remember when you write VALUES(...) in your sql the values must be specified in the exact same order as your columns is defined in the table, unless you state the columns in your sql, which you don't in the posted sql.

mlco76 18-Oct-11 22:12pm

Thanks Simon...I've resolved my problem already. Thank you for your hints.