Question How can I call Store Procedure

[neihmac]

This is procedure :

CREATE PROCEDURE prc_Check_Banding
   @User_Name nvarchar(50),
   @Time_Diff int OUTPUT
AS
BEGIN
   DECLARE @Start_Date smalldatetime
   DECLARE @End_Date smalldatetime
   SELECT @Start_Date=[Start_Banding], @End_Date=[End_Banding] FROM User_Banding WHERE [User_Name]=@User_Name
   
   return DATEDIFF(SECOND,@Start_Date,@End_Date)
END

In SQL console, it run very good :

DECLARE @second int, @Time int
exec @second=prc_Check_Banding 'admin', @Time
Select @second

Now, I want to call this procedure by VB.NET. Help me

 

[Tom]

I think it would be easier to do without the output parameter, simply select the single value you want returned but here is an example

        Using con As New SqlConnection(strConnectionStringHere)
            Dim intSeconds As Integer = 0
            Dim cmd As New SqlCommand

            cmd.Connection = con
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "prc_Check_Banding"
            cmd.Parameters.AddWithValue("@User_Name", "admin")
            cmd.Parameters.Add("@Time_Diff", SqlDbType.Int)
            cmd.Parameters("@Time_Diff").Direction = ParameterDirection.Output

            con.Open()
            cmd.ExecuteNonQuery()
            intSeconds = CInt(cmd.Parameters("@Time_Diff").Value)
            con.Close()
            cmd.Dispose()
        End Using

 

[neihmac]

Thanks Tom, I tried it but there have error : Conversion from type 'DBNull' to type 'String' is not valid.
Error here :

intSeconds = CInt(cmd.Parameters("@Time_Diff").Value)

 

[Tom]

I cant tell why you would be returning a null value, are you sure your not passing something different for your parameter value? Also not sure why it would be trying to convert to a string rather then an int. Can you post a full example

 

[neihmac]

Thank you very much, Tom
I'm Vietnamese (my English so bad ), so you try to understand the description this example.
I want to check the login-time. When somebody login failed, 3 time. That User Name will be band, in 6 hours. That User Name can't login.
If VB.Net provides a function as so as DATEDIFF in SQL, the all ok.
But i don't know it.
My vision is the time difference between StartTime and EndTime.

 

[Tom]

Yes that should work but you are being returned a null value... Are you perhaps checking under a name that had no failed logins, hence nothing being returned? If there is a possibility of returning a null value then you must compensate for this by either ensuring that your stored procedure returns something like a zero and/or first checking in your vb application whether or not the value is null.

 

[neihmac]

Hi Tom. The all ok. Thank you very much
My store procedure is not valid. However, it can run in SQL. I don't know why.
This is procedure valid

CREATE PROCEDURE prc_Check_Banding
	@User_Name nvarchar(50),
	@Time_Diff int OUTPUT
AS
BEGIN
	DECLARE @Start_Date smalldatetime
	DECLARE @End_Date smalldatetime
	SELECT @Start_Date=[Start_Banding], @End_Date=[End_Banding] FROM User_Banding WHERE [User_Name]=@User_Name
	Set @Time_Diff=DATEDIFF(SECOND,@Start_Date,@End_Date)
	return @Time_Diff
END

 

[cjard]

Write your procedure
Check it works
Make a new Dataset in Visual Studio
Add a new Query
Set the connection string
"Use an existing stored procedure"
Choose your stored procedure
Visual Studio will make it go for you