Solved

Executing SQL stored procedure from vb.net

Posted on 2014-03-01
11
286 Views
Last Modified: 2014-03-04
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
0
Comment
Question by:SweetingA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +2
11 Comments
 
LVL 16

Expert Comment

by:Easwaran Paramasivam
ID: 39897780
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
 

Author Comment

by:SweetingA
ID: 39897796
Connection string is ok
Stored procedure is in the correct DB
13 rows should be affected

No errors but no update
0
 

Author Comment

by:SweetingA
ID: 39897820
I put a try in to trap any error and i get "object reference not set to instance of object"
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39898007
On what line do you get the exception.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39898079
Have you initialized the con object?
0
 
LVL 16

Expert Comment

by:Easwaran Paramasivam
ID: 39898145
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
 

Author Comment

by:SweetingA
ID: 39898259
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39898593
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
 

Author Comment

by:SweetingA
ID: 39898883
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
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 39900419
Are you disposing the connection object anywhere?
0
 

Author Closing Comment

by:SweetingA
ID: 39904046
simple but true, i missed all the connection close lines
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how to make search between pages 17 59
VB.Net Data Class 1 45
Format Transaction Number 19 49
How to call new form in VB2013? 4 34
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question