Calling a paramaterized stored procedure?

I am looking for a few lines of VB.NET code to call a stored procedure, passing it two variables which get set in the sp. I thought I would find it in the huge help but I get lost there w/out finding.
LVL 1
Jess31Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Joel CoehoornDirector of Information TechnologyCommented:

'Assuming you have "Imports System.data.SqlClient" at the top of the file:'
'Change "String" to match the actual type of your parameters'
Sub ExecSP(ByVal Var1 As String, ByVal Var2 As String)
    Dim conn As New SqlConnection("your connection string here")
    Dim cmd As New SqlCommand("StoredProcedureName", conn)
    cmd.CommandType = CommandType.StoredProcedure
 
    'Change "SqlDbType.VarChar" and "10" to match the type and size of your parameter.'
    'Omit the third argument for data types with a standard size (like datetime)'
    cmd.Parameters.Add("@Param1Name", SqlDbType.VarChar, 10).Value = Var1
    cmd.Parameters.Add("@Param2Name", SqlDbType.VarChar, 10).Value = Var2
 
    Try
        conn.Open()
 
        'assumes you are not exepecting any results'
        'if you are expecting results, using something like .ExecuteReader() or .ExecuteScalar() instead.'
        cmd.ExecuteNonQuery() 
    Catch
        'Handle errors here like normal'
    Finally
        conn.Close()  
    End Try
End Sub

Open in new window

0
Jess31Author Commented:
thanks.
Now since I have some Datasets allready defined and they are using a connection string, how can I find that connection string so that I don't have to createa a new one?
0
Jess31Author Commented:
I will only be sending Parameters to received a value, I will not be passing any value. Will that change how I configure the Parameters?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

mdouganCommented:
Well, if you don't want to actually pass a value in the parameter object, then you should do this:

    cmd.Parameters.Add("@Param1Name", SqlDbType.VarChar, 10).Value =  System.DBNull.Value
    cmd.Parameters.Add("@Param2Name", SqlDbType.VarChar, 10).Value =  System.DBNull.Value

0
mdouganCommented:
Opps, you probably need to define them as Output parameters too or at least InputOutput.  Look in the syntax for the Parameters.Add function to see where to add that.
0
Joel CoehoornDirector of Information TechnologyCommented:
You can't do it at the time of the add.  Change the two lines in question to this:

    cmd.Parameters.Add("@Param1Name", SqlDbType.VarChar, 10).Direction = ParameterDirection.Output
    cmd.Parameters.Add("@Param2Name", SqlDbType.VarChar, 10).Direction = ParameterDirection.Output
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jess31Author Commented:
I changed it to this:
cmd.Parameters.Add("@StartDate", SqlDbType.SmallDateTime).Direction = ParameterDirection.Output
cmd.Parameters.Add("S2", SqlDbType.SmallDateTime).Direction = ParameterDirection.Output
       Try
          conn.Open()
          cmd.ExecuteNonQuery()

but I'm not getthing anything back.
I checked cmd.parameter(0).value and I get null

Do the parameters that I send (S1, S2) have to be declared in the vb code? is of what type would I declare then as (in the sp they are SmallDateTime)?
Or should they match the names of the vars as they are in the sp?
0
Joel CoehoornDirector of Information TechnologyCommented:
Both the type and and name need to match the stored procedure.
0
mdouganCommented:
Are you sure that you are setting the parameters in the Stored Procedure?  How have you defined the parameters in the stored proc?  They need to include the Output keyword.

You should be able to use  the value from   cmd.parameter(0).value   or you can use the name too cmd.parameter("S2").value
0
Jess31Author Commented:
I know that my sp works cause I tested it in Query Analyzer and it returns the values just fine. Yes, I have declared them OUTPUT in the sp.
0
Jess31Author Commented:
Here is the actutal sp:

(
@MinDate SmallDateTime OUTPUT,
@MaxDate SmallDateTime OUTPUT
)
AS

SELECT @MinDate = MIN(DateTime),  @MaxDate = MAX(DateTime)
FROM tblItemManufacturedHistory;

RETURN
GO
0
Jess31Author Commented:
and here is the vb code:

cmd.Parameters.Add("@MinDate", SqlDbType.SmallDateTime).Direction = ParameterDirection.Output
cmd.Parameters.Add("@MaxDate", SqlDbType.SmallDateTime).Direction = ParameterDirection.Output
        Try
            conn.Open()
            cmd.ExecuteNonQuery()
0
mdouganCommented:
Well, dumb question, but does tblItemManufacturedHistory actually have any rows in it?  If not, then your results will be null.  To be sure that I've received some results from the query, I will usually set my variables to some pre-determined value.  So, I might try:

CREATE PROCEDURE MyProc
(
@MinDate SmallDateTime OUTPUT,
@MaxDate SmallDateTime OUTPUT
)
AS
BEGIN

SET @MinDate = '1/1/1950'
SET @MaxDate = '1/1/1950'

SELECT @MinDate = MIN(DateTime),  @MaxDate = MAX(DateTime)
FROM tblItemManufacturedHistory;

RETURN 0
END

GO

Then, you could always expect a value to be returned to your VB code... you'd just need to check to see if the date came back as 1/1/1950
0
mdouganCommented:
also, what do you mean by     @MaxDate = MAX(DateTime) ?

DateTime is a SQL data type, and you probably won't have a column name in your table with that name, since it is a reserved word... and if you do, that is the problem.  

DateTime needs to be a column name in the tblItemManufacturedHistory table, and it needs to be a unique name that is not a SQL reserved word... call it MyDateTime if you need to.
0
Jess31Author Commented:
mdougan,
Yes, the table has values, in fact I had tested the sp (and of course using the table) with Query Analyzer and it returned values!

@MaxDate = MAX(DateTime)
Won't this return the last date in the file in the column named DateTime (probably not the best choice of name).
0
Jess31Author Commented:
mdougan,
jcoehoorn

It's working!
I think I had a typo in the sp name.

thank you both so much.
0
mdouganCommented:
@MaxDate = MAX(DateTime)
Won't this return the last date in the file in the column named DateTime (probably not the best choice of name).

Yes, if you have a column in your table called DateTime, however, because DateTime is a SQL Server datatype, I was suspicious that this is what was causing some of your trouble... so yea, not the best choice for a name!  You might still consider changing it, as it could cause trouble down the road.

Glad you got it working!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.