Problems calling a stored procedures depending on parameters

Hi guys, hoping one of you may be able to help me out. I am using VS 2005, and VB.net for a Windows application.

I have a table in SQL that has a list of Storedprocedures: Sprocs Table: SPID - PK (int), ID (int), NAME (string), TYPE (string)
The ID is a Foreign key (corresponding to a Company ID), the name is the stored procedure name, and Type (is the type of SP).

On my application I need to a certain SP depending on the company selected and what page you are on. I have a seperate SP that passes in parameters for both Company, and Type and should output the Name value:

ALTERPROCEDURE [dbo].[S_SPROC]
(@.IDint,@.TYPECHAR(10),@.NAMECHAR(20) OUTPUT)
AS

SELECT @.NAME= NAME
FROM SPROCS
WHERE [ID]= @.ID
AND [TYPE]= @.TYPE

Unfortunately I dont seem to be able to get the output in .Net, or then be able to fill my dataset with the Stored Procedure.
Has anyone done something similar before, or could point me in the right direction to solving this problem.

Thanks
Phil

Since @.NAME is an output parameter, you need to indicate that in your Command object (ParameterDirection.InputOutput or ParameterDirection.Output). That allows the parameter's value to be retrieved after the command has been executed.

Alternatively, you could select the data like you would in a normal data retrieval, and not worry about using an output parameter.

|||

Thanks for your reply Mark, I will try adding the ParameterDirection part.

If I use normal data retrieval how can I select the appropriate stored procedure when I try filling my table adapter from the dataset?

Thanks

|||

I assumed you would be performing an operation to select the stored proc name, then another operation to execute that stored proc.

|||

Yes that is what I am trying to do, but not so sure on how to go about it. Do you have any code examples?

Thanks

|||

hi mate,

Here is a sample

Dim cmd_ObjectpathAsNew SqlCommand("Select * from [" & tabelName &"]", sqlCon)

Dim adapterAsNew SqlDataAdapter(cmd_Objectpath)

Dim resultAsNew DataTable

adapter.Fill(result)

ForEach rowAs DataRowIn result.Rows

////do the process u want

next

|||

The code I have so far is:

Dim IDAs Int32
Dim TypeAsString

PrivateSub SimpleButton1_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles SimpleButton1.Click

ID =Me.TextBox1.Text
Type =Me.TextBox2.Text

Me.SPROCSTableAdapter.Fill(Me.DataSet1.SPROCS, ID, Type)

GetSprocName("AUMS_VALID")

Try

'Logic is a seperate VB file containing further code
Logic.run_SQL_fill_dataset(Me.sqlDataAdapter1, DataSet1.GEN_VALID)
Catch exAs Exception

EndTry

EndSub

PrivateFunction GetSprocName(ByVal st1)AsString
' Gets the names for the sprocs so each table can be filled with differant data. Using value 1 for param 1 just to test
Me.SQLCommand_GetSprocName.Parameters(1).Value = 1
Me.SQLCommand_GetSprocName.Parameters(2).Value = st1.ToString()
Logic.run_SQL_command(Me.sqlConnection1,Me.SQLCommand_GetSprocName)
' This is is where the app seems to fail
ReturnMe.SQLCommand_GetSprocName.Parameters(3).Value.ToString()

EndFunction

**** Code in Logic File: *****

'Sub to run SQLcommand, checks the connection and haddles errors

PublicSharedSub run_SQL_command(ByVal sqlcon1As SqlClient.SqlConnection,ByVal sqlcom1As SqlClient.SqlCommand)

Try
If sqlcon1.State <> ConnectionState.ClosedThen' connection check
sqlcon1.Close()
EndIf

If sqlcon1.State = ConnectionState.ClosedThen' connection check

sqlcon1.Open()

EndIf

sqlcom1.ExecuteNonQuery()

If sqlcon1.State = ConnectionState.OpenThen

sqlcon1.Close()

EndIf

Catch exAs Exception

If sqlcon1.State = ConnectionState.OpenThen

sqlcon1.Close()

EndIf

Error_box(ex,"Error on Running SQL Command")'Can place more better code here later

MsgBox(sqlcom1.CommandText.ToString)

EndTry

EndSub

PublicSharedSub run_SQL_fill_dataset(ByVal sqladapterAs SqlClient.SqlDataAdapter,ByVal datatableAs Data.DataTable)

Try

datatable.Clear()

sqladapter.Fill(datatable)

Catch exAs Exception

Error_box(ex,"Error on fill on dataset.")

EndTry

|||

Hi,

I'm afraid that there's something wrong in your code. What we can provide is a general process of communicating with a stored procedure from a .NET application.

Let's take the stored procedure you provided as the sample.

ALTER PROCEDURE [dbo].[S_SPROC]
( @.ID int, @.TYPE CHAR(10), @.NAME CHAR(20) OUTPUT )
AS

SELECT @.NAME = NAME
FROM SPROCS
WHERE [ID] = @.ID
AND [TYPE] = @.TYPE

In your procs, there are 2 input parameters and an output parameter. Then in your application, you should following the steps below:

1. Create the connection which links to the database.
a) Dim myconn As New SqlConnection(ConnectionString)

2. Create the SqlCommand object which execute the procs.
Dim sc As New SqlCommand()
sc.CommandType = CommandType.StoredProcedure
sc.CommandText = "YourProcsName"
sc.Connection = myconn

3. Setting your parameters and add them to SqlCommand object.

Dim sp1 As New SqlParameter()
sp1.ParameterName = "Parameter1"
sp1.Value = ""

Dim sp2 As New SqlParameter()
sp2.ParameterName = "Parameter2"
sp2.Value = ""

Dim sp3 As New SqlParameter()
sp3.ParameterName = "Parameter3"
sp3.Size = 10
sp3.Direction = ParameterDirection.Output

sc.Parameters.Add(sp1)
sc.Parameters.Add(sp2)
sc.Parameters.Add(sp3)

4. Open the connection, execute the process, and get the output parameter.

myconn.Open()
sc.ExecuteNonQuery()
myconn.Close()
Dim c As String = sp.Value.ToString()

After all, you can get the output parameter from the variable C.

Besides, this is a WebForm support forum, if you are developing WindowForm application, it would be better for you to go to MSDN forum where you can get more help.

Thanks.

|||

Thanks for your reply - it has been a big help.

Phil