Executing a Stored Procedure in VB.net

[system243trd]

Hi,
I have written a piece of code to execute a stored procedure, however it isn't working, it is coming up with no errors and appears to be connecting to the database fine

Imports System.Data.SqlClient
Public Class Form1

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
Dim sqlConnection1 As New SqlConnection("Data Source=XXXX;InitialCatalog=xxx;IntegratedSecurity = True")
Dim cmd As New SqlCommand
Dim reader As SqlDataReader
cmd.CommandText = "name"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlConnection1
sqlConnection1.Open()
reader = cmd.ExecuteReader()
sqlConnection1.Close()

End Sub
End Class

[Serge]

Have you tested the stored proc itself (in sql server management studio)? Does it return data?

[system243trd]

Yes I have tested the stored procedure and it works fine, it just updates a field within a table.

[techgnome]

if it's jsut an update, you shouldn't be using a reader... you should be using the ExecuteNonQuery method instead. If it DOES return data, then you can't close the connection until you're done with it.
And how do you know it's not working? (allegedly)

-tg

[TysonLPrice]

There's a myriad of ways to debug stored procedures. One method that is very nice but I find difficult to set up is from Visual Studio you can step right into the SP code and debug it like a project. Here is a link that can get you started.

http://www.codeproject.com/Articles/...-Visual-Studio

One of the issues with using Visual Studio is you need SA access rights and DBAs is most business environments are reluctant to give that out. Another method I use is to create a temporary global table and capture debugging statements in it. Here is a general idea of what I do:

Create a global temporary in the stored procedure and capture debugging statements in it. Since it is a global table it will be available until the session ends or you delete it. So if you add this to the top of the stored procedure:

If object_id('tempdb..##MyDebugger') is not null drop table ##MyDebugger
create table ##MyDebugger(MyComments varchar(1000))

And at strategic points in the stored procedure add debugging statements:

Insert into ##MyDebugger(MyComments) values('started procedure')

Insert into ##MyDebugger(MyComments) values('starting update')

-- update statement
-- capture results

Insert into ##MyDebugger(MyComments) values('update results:' + Convert(varchar,@@Error))

you can then do a select and see what occured during execution:

select * from ##MyDebugger

The key point to remember is to keep the SQL session open on the server and database the VB program is using.

I usually only do that when something is fairly complex. By far stepping through the code via Visual Studio is the best way. As I mentioned the set up may have restrictions depending on your environment.

One of those methods might help you figure out what is going wrong.

[MMock]

if it's jsut an update, you shouldn't be using a reader... you should be using the ExecuteNonQuery method instead. If it DOES return data, then you can't close the connection until you're done with it.
And how do you know it's not working? (allegedly)

-tg

Right, I thought I was missing something. What do you expect to see or have happen that isn't and that's making you think there's an error?

[system243trd]

The form opens without any errors so I assume it is connecting to the database.
The stored procedure does not return any data.
I'm not sure how to execute the non query method. Can anyone help?

[techgnome]

Windows 7... 64-bit by chance? -- this is important...

As for using ExecuteNonQuery... a simple search would have revealed this http://msdn.microsoft.com/en-us/libr...enonquery.aspx

-tg

[system243trd]

Yes I am using Windows 7 - I did look at this page but I'm not sure where to reference the stored procedure name

Public Sub CreateCommand(ByVal queryString As String, _
ByVal connectionString As String)
Using connection As New SqlConnection(connectionString)
Dim command As New SqlCommand(queryString, connection)
command.Connection.Open()
command.ExecuteNonQuery()
End Us

[MMock]

That looks like the caller is passing the query as a string to parameter "queryString". So that's your sproc name. Also remember to set command.CommandType = CommandType.StoredProcedure as you were before. Make sure you have an exception handler so you can easily catch an exception.

[system243trd]

I have used the following code and it works fine. Thanks guys

Dim sqlConnection1 As New SqlConnection("Data Source=x;Initial Catalog=x;Integrated Security = True")
Dim cmd As New SqlCommand
Dim rowsAffected As Integer

cmd.CommandText = "dbo.x"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlConnection1

sqlConnection1.Open()

rowsAffected = cmd.ExecuteNonQuery()

sqlConnection1.Close()
End Sub

[MMock]

Good call throwing a rowsAffected in there! I was going to suggest you get a return value. Glad it's working.