Oracle Stored Procedures

[CanMike]

Help:

This code works. It will call the Oracle SP 'Library_updates.add_books', but only when I hard code the values. Nice, but useless.

As you can see, when attempted to use variables, I get a Oracle error telling my that Oracle cannot insert null values. It's not substituting the variable values with the '?'.

Any ideas?

I'm not using the full version of VB.Net, I'm using the Academic Version. It allows me to connect to a Oracle 9i Database via ODBC.

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
   
   	Dim cmd As OleDbCommand
   	Dim intPosition As Integer
   	Dim detail_inserted As Boolean
   	Dim author_inserted As Boolean
   	Dim owner As String = "mjp"
   	Dim tech As String = "n"
   
   	con.Open()
   
     // this will not work
   	'cmd = New OleDbCommand("library_updates.add_books(?,?,?,?,?,?,?,?,'mjp','n')", con)  
   
   	'cmd.Parameters.Add("@a_isbn", OleDbType.VarChar, 13).Value = txtISBN.Text
   	'cmd.Parameters.Add("@d_title", OleDbType.VarChar, 60).Value = txtBookTitle.Text
   	'cmd.Parameters.Add("@a_lastname", OleDbType.VarChar, 35).Value = txtLastName.Text
   	'cmd.Parameters.Add("@a_firstname", OleDbType.VarChar, 35).Value = txtFirstName.Text
   	'cmd.Parameters.Add("@d_year", OleDbType.Integer, 4).Value = CInt(txtYearPublished.Text)
   	'cmd.Parameters.Add("@d_cost", OleDbType.Double, 6).Value = CDbl(txtPrice.Text)
   	'cmd.Parameters.Add("@d_type", OleDbType.VarChar, 12).Value = txtType.Text
   	'cmd.Parameters.Add("@d_cover", OleDbType.VarChar, 10).Value = txtCover.Text
   	'cmd.Parameters.Add("@a_owner", OleDbType.VarChar, 3).Value = owner
   	'cmd.Parameters.Add("@a_tech", OleDbType.VarChar, 1).Value = tech
   // this will work!
 cmd = New OleDbCommand("library_updates.add_books('1-123-55555-1', 'AA','AAAA', 'Mike', 2005, 15, 'Fiction', 'Hard Cover','mjp','n')", con)
   
   	cmd.CommandType = CommandType.StoredProcedure
   
 	'Dim n_isbn As OleDbParameter = cmd.Parameters.Add("@in_isbn", OleDbType.VarChar, 13) // commented out
   	'n_isbn.Value = txtISBN.Text  // commented out
   
   	Try
   	  cmd.ExecuteNonQuery()
   	  detail_inserted = True
   	Catch SQLExceptionErr As Exception
   	  MessageBox.Show(SQLExceptionErr.Message)
   	End Try
   
   	cmd = New OleDbCommand("insert into authors values (?, ?, ?)", con)
   
   	cmd.Parameters.Add("@a_isbn", OleDbType.VarChar, 13).Value = txtISBN.Text
   	cmd.Parameters.Add("@a_lastname", OleDbType.VarChar, 35).Value = txtLastName.Text
   	cmd.Parameters.Add("@a_firstname", OleDbType.VarChar, 35).Value = txtFirstName.Text
   
   	Try
   	  cmd.ExecuteNonQuery()
   	  author_inserted = True
   	Catch SQLExceptionErr As Exception
   	  MessageBox.Show(SQLExceptionErr.Message)
   	End Try
   
   	con.Close()

Funny how the insert statment will accepted the variables, but not the SP.

 

[levyuk]

Not sure about this but try it anyway

Try replacing the ? with the variable name, e.g. replace the first ? with @a_isbn and so on.

It might work it might not, but at least you tried

 

[ser]

Try this

Don't include the parameters in the command text, just the procedure name, eg:

// this will not work
cmd = New OleDbCommand("library_updates.add_books", con)

cmd.Parameters.Add("@a_isbn", OleDbType.VarChar, 13).Value = txtISBN.Text
cmd.Parameters.Add("@d_title", OleDbType.VarChar, 60).Value = txtBookTitle.Text
cmd.Parameters.Add("@a_lastname", OleDbType.VarChar, 35).Value = txtLastName.Text
cmd.Parameters.Add("@a_firstname", OleDbType.VarChar, 35).Value = txtFirstName.Text
cmd.Parameters.Add("@d_year", OleDbType.Integer, 4).Value = CInt(txtYearPublished.Text)
cmd.Parameters.Add("@d_cost", OleDbType.Double, 6).Value = CDbl(txtPrice.Text)
cmd.Parameters.Add("@d_type", OleDbType.VarChar, 12).Value = txtType.Text
cmd.Parameters.Add("@d_cover", OleDbType.VarChar, 10).Value = txtCover.Text
cmd.Parameters.Add("@a_owner", OleDbType.VarChar, 3).Value = owner
cmd.Parameters.Add("@a_tech", OleDbType.VarChar, 1).Value = tech


Then make sure the command type is Stored Procedure, not text. Also the paramater names need to match those defined in the stroed procedure.

Cheers
Simon

 

[CanMike]

Thank-you Simon. I works beautifully!

You have know idea what I have tried to make it work and I never thought of this solution.

I'm learning

Sorry for the delay in replying, been out on a small holiday.