Adding Parameters to Stored Procedures

[stevegreen]

Hello
I'm a bit of a beginer with VB.net and am having some bother adding parameters to a stored procedure I need to execute. I want to execute a stored procedure that in SQL I can do using

exec sp_spaceused Customer

Not sure how to do this in VB.net. To get the stored proc running I'm doing the following

cStored.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
cStored.CommandText = "sp_spaceused"
cStored.ActiveConnection = cConnection

recRS = cStored.Execute

Can someone please tell me how I add the Customer parameter.

Cheers

Steve

[Mike Hildner]

Have not tried it, but MSDN has an example of how to do. Search for "Using Stored Procedures with a Command".

Here's a snippet, copied right from their example. Looks like you add a paramater to the command, then set the value:

Dim myParm As OleDbParameter = salesCMD.Parameters.Add("@CategoryName", OleDbType.VarChar, 15)
myParm.Value = "Beverages"

[stevegreen]

I saw similar code on the net when I was looking, but using this code is not working. Do I need to add some reference or something?

Steve

[Mike Hildner]

Can you give more details on what you mean by "not working"? Syntax error? Exception? This code works, although I'm using MSSQL:

Dim con As SqlConnection = New SqlConnection("Persist Security Info=False;Integrated Security=SSPI;database=Patriot;server=MIKELAP")
        Dim cmd As SqlCommand = New SqlCommand("sp_spaceused", con)
        cmd.CommandType = CommandType.StoredProcedure
        Dim myParam As SqlParameter = cmd.Parameters.Add("@objname", SqlDbType.NVarChar, 20)
        myParam.Value = "Individuals"
        con.Open()
        Dim myReader As SqlDataReader = cmd.ExecuteReader
        Do While myReader.Read
            MessageBox.Show("Rows: " & myReader.GetString(1))
        Loop
        myReader.Close()
        con.Close()

[brown monkey]

first post of mike you need imports system.data.oledb and the secod, you need imports system.data.sqlclient.