Stored Procedure when called from VB Subroutine is not working

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
kshumwayAsked:
Who is Participating?
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.

Kevin CrossChief Technology OfficerCommented:
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
lluddenCommented:
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

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
kshumwayAuthor Commented:
I execute the stored procedure in SQL Server.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Kevin CrossChief Technology OfficerCommented:
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
kshumwayAuthor Commented:
OK - I just tried it and it still did not delete the records.  There are 4300 records - would that make a difference?
0
Kevin CrossChief Technology OfficerCommented:
How are you calling the sub within VB?
0
kshumwayAuthor Commented:
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
kshumwayAuthor Commented:
Thank you again...
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
Microsoft SQL Server

From novice to tech pro — start learning today.