passing parameters to stored procedures

  • Thread starter Thread starter Carter
  • Start date Start date
C

Carter

I know when I manually run a stored procedure, I can
exec MyStoredProcedure "ThisValue"

How do I do that in a vb.net program:

I have tried a gazillion different syntax; the first one, without parameter
information, works, with GetInfo the name of the stored procedure:
Dim cmd as New SqlCommand("GetInfo", cn)

But when I add a value to the optional parameter, I am doing something
wrong:
Dim cmd as New SqlCommand("GetInfo value", cn)

Dim cmd as New SqlCommand("GetInfo "value"", cn)

I'm sure there's something wrong with my syntax, as I don't know how to
"quote" the value when there's already quotes around the expression.

Thanks for your help.
 
Hi Carter,

It's not too difficult. Below I pass a string to an sp, using it as a faux
array:

Dim longstring As String

longstring = "06960 ,18778 ,08430 "

Dim addcmd As New SqlCommand

addcmd = New SqlCommand("getorderlist2", oconn)

addcmd.CommandType = CommandType.StoredProcedure

Dim param1 As SqlParameter

param1 = addcmd.Parameters.Add("@orderlist", SqlDbType.Char, 7000)

param1.Direction = ParameterDirection.Input

param1.Value = longstring

Try

addcmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.message)

End Try

HTH,

Bernie Yaeger
 
Since you're using SQL Server, you need to define a Command object and a
Parameter object for each input parameter. The Command text should contain
just the name of the SP. The CommandType should be set to the "stored
procedure" enum. Define the Parameter object datatype to correspond with the
type defined for the stored procedure arguments.
I wrote an article that discusses this in more detail. See
www.betav.com/articles (msdn or SQL
Server magazine).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top