How to excute a MS Access Stored procedure?

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

Hi, there

I have create a MS Access Stored procedure using the code below and no error
occured.


'---------------------------------------------------------------------------
---
Dim cmd As OleDbCommand
cmd = Conn.CreateCommand
cmd.CommandType = CommandType.Text

Try
cmd.CommandText = _
"CREATE PROC udpGetSampleIDByName" & vbCrLf & _
"(@zSampleName VarChar(64))" & vbCrLf & _
"AS" & vbCrLf & _
"Select zSampleID FROM TSamples " & _
"Where zSampleName = @zSampleName "
cmd.ExecuteNonQuery()

Catch e As Exception
MessageBox.Show(e.ToString, "Error Creating Stored Procedure")
Finally
cmd.Dispose()
End Try
'---------------------------------------------------------------------------
------

And now I want to call the Stored procedure with OleDBCommand . After I
excute the codes below I got an error message: "The syntax of the input
string is invalid!"

'---------------------------------------------------------------------------
------
'connNoTran is an opened oledbconnection
Dim oCmd As New OleDbCommand

With oCmd
.Connection = connNoTran
.CommandType = CommandType.StoredProcedure
.CommandText = "udpGetSampleIDByName"

.Parameters.Add("", OleDbType.SmallInt)
.Parameters(0).Value = sName
End With

Try

oCmd.ExecuteNonQuery()

Catch e As Exception
MessageBox.Show(e.ToString)
End Try

'---------------------------------------------------------------------------
 
Back
Top