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_UpdateTemptbl_Stops", con)
        If con.State = ConnectionState.Closed Then con.Open()
        cmd6.CommandText = "stp_UpdateTemptbl_Stops"
        cmd6.CommandType = CommandType.StoredProcedure
        cmd6.ExecuteNonQuery()
    End Sub

SQL Procedure.....

USE [OEE]
GO
/****** Object:  StoredProcedure [dbo].[stp_UpdateTemptbl_Stops]    Script Date: 03/01/2014 21:07:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[stp_UpdateTemptbl_Stops]
      -- 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
SweetingAAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Easwaran ParamasivamCommented:
Check whether the connection string is correct or not?

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.
0
SweetingAAuthor Commented:
Connection string is ok
Stored procedure is in the correct DB
13 rows should be affected

No errors but no update
0
SweetingAAuthor Commented:
I put a try in to trap any error and i get "object reference not set to instance of object"
0
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Fernando SotoRetiredCommented:
On what line do you get the exception.
0
CodeCruiserCommented:
Have you initialized the con object?
0
Easwaran ParamasivamCommented:
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.
0
SweetingAAuthor Commented:
Dim cmd6 = New SqlCommand("stp_UpdateTemptbl_Stops", con)

        Try
            If con.State = ConnectionState.Closed Then con.Open()
            cmd6.CommandType = CommandType.StoredProcedure
            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.
0
Éric MoreauSenior .Net ConsultantCommented:
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.
0
SweetingAAuthor Commented:
My connection string is read in the form class

Dim con As SqlConnection = New SqlConnection("Server=.\SQLEXPRESS;Database=OEE;USER Id=Me;Trusted_Connection=True")
0
CodeCruiserCommented:
Are you disposing the connection object anywhere?
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
SweetingAAuthor Commented:
simple but true, i missed all the connection close lines
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
Visual Basic.NET

From novice to tech pro — start learning today.