Calling a stored procedure

taboadar · December 15th, 2008, 07:51 PM

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

alliancejhall · December 15th, 2008, 09:12 PM

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

taboadar · December 15th, 2008, 10:24 PM

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

alliancejhall · December 16th, 2008, 10:30 AM

...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.

taboadar · December 16th, 2008, 03:21 PM

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

taboadar · December 16th, 2008, 05:46 PM

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

taboadar · December 16th, 2008, 06:09 PM

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)

gbianchi · December 17th, 2008, 08:17 AM

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...

startfarm · December 17th, 2008, 08:30 AM

Quote:

Originally Posted by taboadar

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.

taboadar · December 17th, 2008, 09:10 AM

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