Solved

Stored Procedure when called from VB Subroutine is not working

Posted on 2012-04-06
8
348 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
[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
  • 4
  • 3
8 Comments
 
LVL 60

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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 60

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
 

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 60

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

617 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