Solved

Stored Procedure when called from VB Subroutine is not working

Posted on 2012-04-06
8
344 Views
Last Modified: 2012-04-06
I have a stored procedure that deleted records from a table based on billdate field.  When I execute the stored procedure, it works fine.  However if I call it from a VB Subroutine, no rows are deleted.  I can't see anything wrong.  Can someone help?  (See attached files)

ALTER-PROCEDURE-dbo.doc

Sub-DelMonthlyHospPricingArchive.doc
0
Comment
Question by:kshumway
  • 4
  • 3
8 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 37817248
Try it as:
Sub DelMonthlyHospPricingArchive(ByVal dBillDate As Date)
        Dim cmdHP As New SqlCommand
        Try
            conn.Open()
            cmdHP.Connection = conn
            cmdHP.CommandType = CommandType.StoredProcedure
            cmdHP.Parameters.AddWithValue("@BillDate", dBilldate.ToString("yyyy-MM-dd"))
            cmdHP.CommandText = "DeleteHospPricingSQLArchive"
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            conn.Close()
        End Try
    End Sub

Open in new window


MSDN: http://msdn.microsoft.com/en-us/library/zdtaw1bw.aspx
0
 
LVL 18

Accepted Solution

by:
lludden earned 500 total points
ID: 37817293
You never call an execute method.

Sub DelMonthlyHospPricingArchive(ByVal dBillDate As Date)
        Dim cmdHP As New SqlCommand
        Try
            conn.Open()
            cmdHP.Connection = conn
            cmdHP.CommandType = CommandType.StoredProcedure
            cmdHP.Parameters.AddWithValue("@BillDate", dBilldate.ToString("yyyy-MM-dd"))
            cmdHP.CommandText = "DeleteHospPricingSQLArchive"
            cmdHP.ExecuteNonQuery
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            conn.Close()
        End Try
    End Sub
0
 

Author Comment

by:kshumway
ID: 37817314
I execute the stored procedure in SQL Server.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 37817326
Good catch, @lludden! You have to execute the SqlCommand at some point, so if your code truly has that missing then try adding that first. If it doesn't work from there, my thought was that the date conversion was causing an issue (i.e., converting incorrectly and so not finding any records to delete). If that is the case, then the fix is to use ToString() to format the date as 'yyyy-MM-dd' or '2012-04-06', which SQL Server should understand as a date properly.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:kshumway
ID: 37817333
OK - I just tried it and it still did not delete the records.  There are 4300 records - would that make a difference?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 37817347
How are you calling the sub within VB?
0
 

Author Comment

by:kshumway
ID: 37817374
Oh - now I know what you mean - I knew something was missing but just couldn't see it:

 cmdHP.ExecuteNonQuery

Yes that was it.  However I could not do the date conversion without an error.  It worked the way I originally had it - with the exeute line, of course.

THANK YOU SO MUCH!  I have been looking at this forever and could not see what was wrong
0
 

Author Closing Comment

by:kshumway
ID: 37817377
Thank you again...
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How would you add MULTITHREADING to the attached C# code? 4 51
SQL Server Deadlocks 12 49
C# Single Form 8 27
Help Parsing a String with SQL Syntax 23 26
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now