SweetingA
asked on
Executing SQL stored procedure from vb.net
I am trying to execute an SQL stored procedure from vb express, i have done this several times and normally its works - with exactly the same code. This time there is no error but the update does not run.
If i execute the procedure in SQL the update works fine.
VB code....
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cmd6 = New SqlCommand("stp_UpdateTemp tbl_Stops" , con)
If con.State = ConnectionState.Closed Then con.Open()
cmd6.CommandText = "stp_UpdateTemptbl_Stops"
cmd6.CommandType = CommandType.StoredProcedur e
cmd6.ExecuteNonQuery()
End Sub
SQL Procedure.....
USE [OEE]
GO
/****** Object: StoredProcedure [dbo].[stp_UpdateTemptbl_S tops] Script Date: 03/01/2014 21:07:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[stp_UpdateTemptbl_S tops]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
UPDATE d
SET DowntimeCode = s.DowntimeCode
FROM dbo.tbl_MachineData d
INNER JOIN dbo.temptbl_Stops s
ON d.StartDate = s.StartDate
END
Thanks
If i execute the procedure in SQL the update works fine.
VB code....
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cmd6 = New SqlCommand("stp_UpdateTemp
If con.State = ConnectionState.Closed Then con.Open()
cmd6.CommandText = "stp_UpdateTemptbl_Stops"
cmd6.CommandType = CommandType.StoredProcedur
cmd6.ExecuteNonQuery()
End Sub
SQL Procedure.....
USE [OEE]
GO
/****** Object: StoredProcedure [dbo].[stp_UpdateTemptbl_S
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[stp_UpdateTemptbl_S
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
UPDATE d
SET DowntimeCode = s.DowntimeCode
FROM dbo.tbl_MachineData d
INNER JOIN dbo.temptbl_Stops s
ON d.StartDate = s.StartDate
END
Thanks
ASKER
Connection string is ok
Stored procedure is in the correct DB
13 rows should be affected
No errors but no update
Stored procedure is in the correct DB
13 rows should be affected
No errors but no update
ASKER
I put a try in to trap any error and i get "object reference not set to instance of object"
On what line do you get the exception.
Have you initialized the con object?
Debug the application and put breakpoint inside the method. See what happens? If any of the issue, solve it first. Make sure that the sp is being hit using SQL profiler. It should be working.
ASKER
Dim cmd6 = New SqlCommand("stp_UpdateTemp tbl_Stops" , con)
Try
If con.State = ConnectionState.Closed Then con.Open()
cmd6.CommandType = CommandType.StoredProcedur e
cmd6.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message , "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
I get the error on the line connection line. I can get round the problem by opening second connection but i am unsure why i am having to do that.
Any advice would be most welcome as i don't really want lots of connection channels.
Try
If con.State = ConnectionState.Closed Then con.Open()
cmd6.CommandType = CommandType.StoredProcedur
cmd6.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message
End Try
I get the error on the line connection line. I can get round the problem by opening second connection but i am unsure why i am having to do that.
Any advice would be most welcome as i don't really want lots of connection channels.
where is your connection supposed to be opened?
ADO.Net was built to do not hold connection to save resources. It relies on connection pool to save time reopening connections.
ADO.Net was built to do not hold connection to save resources. It relies on connection pool to save time reopening connections.
ASKER
My connection string is read in the form class
Dim con As SqlConnection = New SqlConnection("Server=.\SQ LEXPRESS;D atabase=OE E;USER Id=Me;Trusted_Connection=T rue")
Dim con As SqlConnection = New SqlConnection("Server=.\SQ
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
simple but true, i missed all the connection close lines
Make sure that your SP is in the correct db and u have correct sp.
cmd6.ExecuteNonQuery() will return integer value..Howmany rows are affected? Ensure that it is not zero.