Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
ASP.NET 2.0 Basics If you are new to ASP or ASP.NET programming with version 2.0, this is the forum to begin asking questions. Please also see the Visual Web Developer 2005 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 2.0 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 15th, 2008, 07:51 PM
Authorized User
 
Join Date: Dec 2008
Posts: 15
Thanks: 4
Thanked 0 Times in 0 Posts
Default Calling a stored procedure

I having a problem to understand this : I have a procedure called update_assignreceivers which I already executed and works fine. This Procedure takes three inputs from my data entryform: @AccessNumber,@AssignedDate and @TechID.and then update the table like this:

@AccessNumber nchar(15),
@AssignedTo
nchar(40),
@DateAssigned
smalldatetime
AS
BEGIN
UPDATE table1
SET AssignedTo = @AssignedTo,
DateAssigned = @DateAssigned
WHERE
AccessNumber = @AccessNumber

END.

The control ID are DropDownList1, DateAssigned, and AccessNumber01. Now how I call and pass this values to my store procedure.

What I did is create a script like this:

Dim connect AsNew SqlConnection("<%$ ConnectionStrings:LocalSqlServer %>")
connect.Open()
Dim cmd AsNew SqlCommand("SearchbyAccessNumber", connect)
cmd.CommandType = CommandType.StoredProcedure
cmd.ExecuteNonQuery()
connect.Close()


But I don't know what is missing. Of course the parameter statement is one, but where I put it? So far went I run this I got message:

Type 'SqlConnection is not defined.
Type 'SqlCommand' is not defined

Thank you for your help.
Roberto
 
Old December 15th, 2008, 09:12 PM
Friend of Wrox
 
Join Date: Nov 2007
Location: Central Florida, USA.
Posts: 207
Thanks: 2
Thanked 15 Times in 15 Posts
Default In short...

You need to pass the parameters to the procedure and separate AsNew (As New)

here's what you can do

Code:
            Dim sqlCon As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("LocalSQLServer").ConnectionString)
            Dim sqlCmd As New System.Data.SqlClient.SqlCommand("update_assignreceivers")
            sqlCon.Open()
            sqlCmd.CommandType = CommandType.StoredProcedure
            sqlCmd.Parameters.AddWithValue("@AccessNumber", "Access Number Here")
            sqlCmd.Parameters.AddWithValue("@AssignedTo", "Assigned To Here")
            sqlCmd.Parameters.AddWithValue("@DateAssigned", "Date Assigned Here")
            sqlCmd.Connection = sqlCon
            sqlCmd.ExecuteNonQuery
            sqlCmd.Dispose()
            sqlCmd = Nothing
            sqlCon.Close()
            sqlCon.Dispose()
            sqlCon = Nothing
I would really put that into a try catch block though

Code:
           Dim sqlCon As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("LocalSQLServer").ConnectionString)
           Dim sqlCmd As New System.Data.SqlClient.SqlCommand("update_assignreceivers")

       Try
            sqlCon.Open()
            sqlCmd.CommandType = CommandType.StoredProcedure
            sqlCmd.Parameters.AddWithValue("@AccessNumber", "Access Number Here")
            sqlCmd.Parameters.AddWithValue("@AssignedTo", "Assigned To Here")
            sqlCmd.Parameters.AddWithValue("@DateAssigned", "Date Assigned Here")
            sqlCmd.Connection = sqlCon
            sqlCmd.ExecuteNonQuery

       Catch ex as Exception
           'Do what you want with the exception
       Finally
            sqlCmd.Dispose()
            sqlCmd = Nothing
            sqlCon.Close()
            sqlCon.Dispose()
            sqlCon = Nothing
       End Try
__________________
Jason Hall

Follow me on Twitter @jhall2013
 
Old December 15th, 2008, 10:24 PM
Authorized User
 
Join Date: Dec 2008
Posts: 15
Thanks: 4
Thanked 0 Times in 0 Posts
Default Calling a Stored Procedure

Jason, thank for your response.

Ok, I tryed it, but still not working. The good news is that I do not getting any error message. The bad news is that I do not what is the error. After I click the SAVE botton nothing is updated to the database.This is what I have:

<scriptlanguage="VB"runat="server">
Sub Page_Load(ByVal Src AsObject, ByVal E As EventArgs)
Dim sqlCon AsNew System.Data.SqlClient.SqlConnection(ConfigurationM anager.ConnectionStrings("LocalSqlServer").ConnectionString)
Dim sqlCmd AsNew System.Data.SqlClient.SqlCommand("update_assignreceivers")
Try
sqlCon.Open()
sqlCmd.CommandType = CommandType.StoredProcedure
sqlCmd.Parameters.AddWithValue(
"@AccessNumber", "RegularReceiver01")
sqlCmd.Parameters.AddWithValue(
"@AssignedTo", "DropDownList1")
sqlCmd.Parameters.AddWithValue(
"@DateAssigned", "DateAssigned")
sqlCmd.Connection = sqlCon
sqlCmd.ExecuteNonQuery()
Catch ex As Exception
'Do what you want with the exception
Finally
sqlCmd.Dispose()
sqlCmd =
Nothing
sqlCon.Close()
sqlCon.Dispose()
sqlCon =
Nothing
EndTry
EndSub
</script>

Please note that : RegularReceiver01 is a label. date assigned is a label also but, from a AJAX control (calendar) and assignedto is a Dropdownlist from a column of another table.

Also, I was trying to see where is the error by deleting, for example, a caracter of the connection string and still no errors messages. It looks like that after I click the save botton nothing is executing. something is missing.

Please help, thank you
Roberto
 
Old December 16th, 2008, 10:30 AM
Friend of Wrox
 
Join Date: Nov 2007
Location: Central Florida, USA.
Posts: 207
Thanks: 2
Thanked 15 Times in 15 Posts
Default You need to replace...

...the values I gave you with your values.


sqlCmd.Parameters.AddWithValue("@AccessNumber", "RegularReceiver01")
sqlCmd.Parameters.AddWithValue(
"@AssignedTo", "DropDownList1")
sqlCmd.Parameters.AddWithValue(
"@DateAssigned", "DateAssigned")

should be something LIKE:

sqlCmd.Parameters.AddWithValue("@AccessNumber", lblRegularReceiver01.Text)
sqlCmd.Parameters.AddWithValue(
"@AssignedTo", DropDownList1.SelectedValue)
sqlCmd.Parameters.AddWithValue(
"@DateAssigned", CalendarExtender1.SelectedDate)


and do this with the catch

Catch ex as Exception
Response.Write(ex.message)

you were catching the error but not doing anything with it which is why you saw now error. Now you will catch the error then write it to the page.
__________________
Jason Hall

Follow me on Twitter @jhall2013
 
Old December 16th, 2008, 03:21 PM
Authorized User
 
Join Date: Dec 2008
Posts: 15
Thanks: 4
Thanked 0 Times in 0 Posts
Default Calling a Stored Procedure

Ok, I did that. And it still is not working, And not error at all. When you say :

" Now you will catch the error then write it to the page" what page ?. I do not what else I can do. Remember something: I am using masterpages. That is may the problem?.

Thank you for your help.

Roberto
 
Old December 16th, 2008, 05:46 PM
Authorized User
 
Join Date: Dec 2008
Posts: 15
Thanks: 4
Thanked 0 Times in 0 Posts
Default Calling a Stored Procedure

OK, Look I did :

1. I add one line in my ASPX. file , Just inside the <body> part. I added
Button1_Click() and I saw I for first time a error, the error say that @assignedDate was null. So I start to suspect that the problem is about that.

2. Eliminated the parameter assigndate, I erased that AJAJ calendar, I erased , everythin related to date. And in the Procedure I did this; AssignedDate= getdate().

3. Now IT IS WORKING!!!!!

But now my problem is, since in my dataform I will have several @AccessNumber as a input with the same AssignedTo parameter. In another words:

For the first input:

sqlCmd.Parameters.AddWithValue(
"@AccessNumber", RegularReceiver01.Text)
sqlCmd.Parameters.AddWithValue(
"@AssignedTo", DropDownList1.SelectedValue)

So can I do that?

sqlCmd.Parameters.AddWithValue("@AccessNumber", RegularReceiver01.Text)
sqlCmd.Parameters.AddWithValue(
"@AssignedTo", DropDownList1.SelectedValue)
sqlCmd.Parameters.AddWithValue("@AccessNumber", RegularReceiver02.Text)
sqlCmd.Parameters.AddWithValue(
"@AssignedTo", DropDownList1.SelectedValue)
sqlCmd.Parameters.AddWithValue("@AccessNumber", RegularReceiver03.Text)
sqlCmd.Parameters.AddWithValue(
"@AssignedTo", DropDownList1.SelectedValue)
.................................................. .........
sqlCmd.Parameters.AddWithValue("@AccessNumber", RegularReceiver40.Text)
sqlCmd.Parameters.AddWithValue(
"@AssignedTo", DropDownList1.SelectedValue)

and what happen if one of these parameters is(are) empty??

Thank you.

Roberto


 
Old December 16th, 2008, 06:09 PM
Authorized User
 
Join Date: Dec 2008
Posts: 15
Thanks: 4
Thanked 0 Times in 0 Posts
Default

OK when I do this for the other AccessNumber I got this message:

Procedure or function update_assignreceivers has too many arguments specified.

I am trying to do this;

sqlCmd.Parameters.AddWithValue("@AccessNumber", RegularReceiver01.Text)
sqlCmd.Parameters.AddWithValue(
"@AssignedTo", DropDownList1.SelectedValue)
sqlCmd.Parameters.AddWithValue(
"@AccessNumber", RegularReceiver02.Text)
sqlCmd.Parameters.AddWithValue(
"@AssignedTo", DropDownList1.SelectedValue)
.................................................. .........
.......................................
sqlCmd.Parameters.AddWithValue("@AccessNumber", RegularReceiver19.Text)
sqlCmd.Parameters.AddWithValue(
"@AssignedTo", DropDownList1.SelectedValue)
sqlCmd.Parameters.AddWithValue(
"@AccessNumber", RegularReceiver20.Text)
sqlCmd.Parameters.AddWithValue(
"@AssignedTo", DropDownList1.SelectedValue)
 
Old December 17th, 2008, 08:17 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

Hello..

If your parameters are empty, you have 2 choices: Test in the code, and don't send them to the SP, or test them in the SP so you don't proccess them (IMHO the first option will be the best).

For the other problem, since your SP is only Adding a row at a time, you have to call it for every data you want to insert...
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
 
Old December 17th, 2008, 08:30 AM
Authorized User
 
Join Date: Mar 2007
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by taboadar View Post
OK when I do this for the other AccessNumber I got this message:

Procedure or function update_assignreceivers has too many arguments specified.

I am trying to do this;

sqlCmd.Parameters.AddWithValue("@AccessNumber", RegularReceiver01.Text)
sqlCmd.Parameters.AddWithValue(
"@AssignedTo", DropDownList1.SelectedValue)
sqlCmd.Parameters.AddWithValue(
"@AccessNumber", RegularReceiver02.Text)
sqlCmd.Parameters.AddWithValue(
"@AssignedTo", DropDownList1.SelectedValue)
.................................................. .........
.......................................
sqlCmd.Parameters.AddWithValue("@AccessNumber", RegularReceiver19.Text)
sqlCmd.Parameters.AddWithValue(
"@AssignedTo", DropDownList1.SelectedValue)
sqlCmd.Parameters.AddWithValue(
"@AccessNumber", RegularReceiver20.Text)
sqlCmd.Parameters.AddWithValue(
"@AssignedTo", DropDownList1.SelectedValue)
The problem is caused by the parameters incrementing - after the each call to the SP, you need to clear the parameters down by using

sqlCmd.Parameters.Clear()

Each AddWithValue is adding a parameter object, along with its value. On all calls after the first you're passing too many params to the SP.
 
Old December 17th, 2008, 09:10 AM
Authorized User
 
Join Date: Dec 2008
Posts: 15
Thanks: 4
Thanked 0 Times in 0 Posts
Default Calling a Stored Procedure more than once

Hello Gonzalo,

Yes but how you called when is more than once????

I am trying to do this;

sqlCmd.Parameters.AddWithValue("@AccessNumber", RegularReceiver01.Text)
sqlCmd.Parameters.AddWithValue(
"@AssignedTo", DropDownList1.SelectedValue)
sqlCmd.Parameters.AddWithValue(
"@AccessNumber", RegularReceiver02.Text)
sqlCmd.Parameters.AddWithValue(
"@AssignedTo", DropDownList1.SelectedValue)
.................................................. .........
.......................................
sqlCmd.Parameters.AddWithValue("@AccessNumber", RegularReceiver19.Text)
sqlCmd.Parameters.AddWithValue(
"@AssignedTo", DropDownList1.SelectedValue)
sqlCmd.Parameters.AddWithValue(
"@AccessNumber", RegularReceiver20.Text)
sqlCmd.Parameters.AddWithValue(
"@AssignedTo", DropDownList1.SelectedValue)

but it is not working, any ideas?

Thank you

Roberto




Similar Threads
Thread Thread Starter Forum Replies Last Post
calling stored procedure jomet JSP Basics 0 November 23rd, 2007 08:06 AM
Calling on a database within a stored procedure Hadware SQL Language 1 January 8th, 2007 05:11 PM
Calling Stored Procedure Using vc++ senthil_mano Visual C++ 0 August 30th, 2006 12:20 AM
Calling Stored Procedure with parameters zarina_24 Classic ASP Professional 4 March 2nd, 2006 11:57 AM
Calling an Oracle Stored Procedure booksnore2 BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 2 October 1st, 2004 09:35 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.