Calling Stored Procedure VB.NET

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Calling Stored Procedure VB.NET

Author

Topic

hutty
Starting Member

37 Posts

Posted - 2006-01-24 : 14:02:01

I'm trying to call a stored procedure from vb.net that's on the sql server. I would like to have the output on another form or a listbox where I can print results. I'm not getting any errors, but i'm not getting any output either. Any help appreciated. Thanks.

'Stored Procedure = COMPARE, Output = comp_out. COL001 is returned 'field from SP.

Dim myCommand As New SqlCommand
Dim myParameter As SqlParameter

Dim myConnection = New SqlConnection("Integrated Security=SSPI;Packet Size=4096;Data Source=TEST\MYSQL;Initial Catalog=hutty;Persist Security Info=False;Workstation ID=TEST")

myCommand.Connection = myConnection
myCommand.CommandText = "COMPARE"
myCommand.CommandType = CommandType.StoredProcedure
myParameter = myCommand.CreateParameter()
myParameter.ParameterName = "@comp_out"
myParameter.Direction = ParameterDirection.Output
myParameter.SqlDbType = SqlDbType.VarChar
myParameter.Size = 50
myCommand.Parameters.Add(myParameter)

myCommand.Connection.Open()
myCommand.ExecuteNonQuery()

Span1.Text = Convert.ToString((myCommand.Parameters("@comp_out").Value.ToString()))

ListBox1.Items.Add(myCommand.Parameters("@comp_out").Value.ToString)
End Sub

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2006-01-28 : 03:50:58

Hmm, in your Stored procedure do you set the parameter?
SET @comp_out = 'SomeValue'

Making parameters can be a lot easier. ASP.NET 2.0 has myCommand.Parameters.AddWithValue ("@comp_out",SomeValue) or 1.1 has myCommand.Parameters.Add("@comp_out",SomeValue)

But I can give you antoher direction. Why not use a scalar? and in the stored procedure do a
SELECT @comp_out or SELECT SomeValue

In the code you do SomeVariable = myCommand.ExecuteScalar()

Or better get a table back since you want a Value and a TextField representing that value.

Henri
~~~~
The envious praises me unknowingly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources