Help with stored procedure in VB

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

SQL Server 2000 Forums

SQL Server Development (2000)

Help with stored procedure in VB

Author

Topic

nhaas
Yak Posting Veteran

90 Posts

Posted - 2007-03-06 : 01:11:55

I am having some issues with trying to run a stored procedure in Visual Basic. I have gotten the procedure that inserts a new row but I dont get the data inserted. Hopefully someone can point me to my errors in my ways.

''''
Dim cmd As New SqlClient.SqlCommand()
Dim MySqlConnection ......
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "TUNITS"
cmd.Connection = MySqlConnection
Dim p As New SqlClient.SqlParameter

p.ParameterName = "@UNITS1"

p.Value = "1"

cmd.Parameters.Add(p)

Dim p1 As New SqlClient.SqlParameter

p1.ParameterName = "@UNITS2"

p1.Value = "Blah2"

cmd.Parameters.Add(p1)

Try

cmd.Connection.Open()

cmd.ExecuteNonQuery()

cmd.Connection.Close()

MsgBox("maybe")

Catch

MsgBox("nope")

End Try

test Insert Procedure.

CREATE PROCEDURE [TUNITS] AS
DECLARE @UNITS1 numeric(8)
DECLARE @UNITS2 numeric(8)

begin
INSERT INTO test([test1], [test2])values(@UNITS1, @UNITS2)
end
GO

SQL2000 and VB
thank you for the help

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-06 : 01:23:23

Your SP is having two numeric parameters, but you are passing varchar value for the second parameter...Why?

Also, have you tried running this SP from Query Analyzer and check whether data gets inserted?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

nhaas
Yak Posting Veteran

90 Posts

Posted - 2007-03-06 : 11:17:34

Silly question, how do I check the Stored procedure through Query analyzer? (newbe question for this I guess...)

you are correct I did some updating to the Insert but i get nothing inserted, except for a row.

.....
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "TUNITS"
cmd.Connection = MySqlConnection

Dim IFirst As Integer
IFirst = 1
Dim Isecond As Integer
Isecond = 1
Dim p1 As New SqlClient.SqlParameter("@UNITS1", IFirst)
Dim p2 As New SqlClient.SqlParameter("@UNITS2", Isecond)

Try
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
MsgBox("maybe")
Catch
MsgBox("nope")
End Try

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-06 : 11:26:22

To run SP through Query Analyzer, type this in QA:

exec TUNITS @UNITS1=1, @UNITS2=1

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

nhaas
Yak Posting Veteran

90 Posts

Posted - 2007-03-06 : 12:37:07

My bad, stored procedure was bad.....

CREATE PROCEDURE TUNITS
(
@UNITS1 numeric(8),
@UNITS2 numeric(8)
)
AS

INSERT INTO test ([test1], [test2])values(@UNITS1, @UNITS2)

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources