Procedure or Function 'SubscribeToNewsletters' expects parameter '@emailAddress', which was not supplied.

Hi i have been trying to insert some values selected in a checkbox list into the database, however it is giving me the following error "Procedure or Function 'SubscribeToNewsletters' expects parameter '@emailAddress', which was not supplied. "

public void Page_Load(object sender, EventArgs e) -- on page load the checkbox list is populated through stored procedure (stream_NewsletterTypes)

{

try

{

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);

SqlCommand command = new SqlCommand("stream_NewsletterTypes", conn);

command.CommandType = CommandType.StoredProcedure;

command.Connection.Open();

SqlDataReader datareader = command.ExecuteReader();

AlertList.DataSource = datareader;

AlertList.DataTextField =
"newsletterName";

AlertList.DataBind();

command.Connection.Close();

command.Connection.Dispose();

}

catch (Exception ex)

{

throw new Exception("Exception. " + ex.Message);

}

}

 

protected void Btn_Subscribe_Click(object sender, EventArgs e) when the button is clicked, the values should be sent to the database

{

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);

SqlCommand command = new SqlCommand("stream_NewsletterTypes", conn);

command.CommandType = CommandType.StoredProcedure;for (int i = 0; i < AlertList.Items.Count; i++)

{

if (AlertList.Items[i].Selected)

{

command.Parameters.AddWithValue("firstName", txtFirstName.Text);

command.Parameters.AddWithValue("surname", txtSurname.Text);

command.Parameters.AddWithValue("emailAddress", txtEmail.Text);

command.Parameters.AddWithValue("newsletterID", AlertList.Items[i].Value);

}

}

try

{

conn.Open();

command.ExecuteNonQuery();

}

catch (Exception ex)

{

throw new Exception("Exception adding account. " + ex.Message);

}

finally

{

conn.Close();

}

}

 

And my stored procedure;

ALTER PROCEDURE [dbo].[SubscribeToNewsletters]

@emailAddress VARCHAR(250),

@firstName VARCHAR(250),

@surname VARCHAR(250),

@newsletterID INT,

@userID INT OUTPUT

AS

INSERT INTO ThinkUsers (emailAddress, firstName, surname)

VALUES

(@emailAddress, @firstName, @surname)

Select @userID = @@Identity -- this is the ID created in the TheUserTable

-- when you make that first insert, the database

-- will generate the next ID value in that table

INSERT INTO SubscribedNewsletters (userID, newsletterID)

VALUES

(@userID, @newsletterID)

0
Pmillio
3/21/2008 3:37:05 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

26 Replies
626 Views

Similar Articles

[PageSpeed] 33

 Change this code:

command.Parameters.AddWithValue("@firstName", txtFirstName.Text);

command.Parameters.AddWithValue("@surname", txtSurname.Text);

command.Parameters.AddWithValue("@emailAddress", txtEmail.Text);

command.Parameters.AddWithValue("@newsletterID", AlertList.Items[i].Value);

 


Save our world, its all we have! A must watch video Pale Blue Dot

Please use the search feature of the forum before asking a question.
0
XPSCodes
3/21/2008 4:14:12 PM
Hi i tried that and that didnt have no effect either. It still gave me that error.
0
Pmillio
3/21/2008 4:16:40 PM

It looks like your ExecuteNonQuery might be firing even when the alert isn't selected, i.e., your paramaters aren't loaded.


Bruce


Please remember to click "Mark as Answer" on the posts that helped solve your issue.
0
BHendry
3/21/2008 4:21:52 PM

 Maybe this condition is never getting set? Try setting a breakpoint on that.

AlertList.Items[i].Selected


Save our world, its all we have! A must watch video Pale Blue Dot

Please use the search feature of the forum before asking a question.
0
XPSCodes
3/21/2008 4:23:03 PM

Hi thanks for the responses guys, i set a breakpoint on that line and it shows the following  " AlertList.Items[i].Selected false bool" and  " i 0 int", what does that mean, and how can i rectify it?

0
Pmillio
3/21/2008 4:31:20 PM

Yeah, I don't know if that was just a product of your cutting and pasting, but your bracketing didn't make sense for the flow of things, try this:

protected void Btn_Subscribe_Click(object sender, EventArgs e) when the button is clicked, the values should be sent to the database 
{

	SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString); 
	
	for (int i = 0; i < AlertList.Items.Count; i++)
	{
		if (AlertList.Items[i].Selected) 
		{
			SqlCommand command = new SqlCommand("SubscribeToNewsletters", conn);

			command.CommandType = CommandType.StoredProcedure;
	
			command.Parameters.AddWithValue("@firstName", txtFirstName.Text);

			command.Parameters.AddWithValue("@surname", txtSurname.Text); 
			command.Parameters.AddWithValue("@emailAddress", txtEmail.Text);

			command.Parameters.AddWithValue("@newsletterID", AlertList.Items[i].Value); 

			try

			{

				conn.Open();

				command.ExecuteNonQuery();

			}

			catch (Exception ex) 
			{

				throw new Exception("Exception adding account. " + ex.Message); 
			}

			finally

			{

				conn.Close();

			}
		}

	}
}

 

Sorry Pmillio, did that in a hurry in the window... just edited...


Bruce


Please remember to click "Mark as Answer" on the posts that helped solve your issue.
0
BHendry
3/21/2008 4:32:35 PM

Pmillio,

When you set your break point, step through and make sure that your code is not executing the try/catch block when AlertList.Items[i].Selected is FALSE. The try/catch block should be included in the if AlertList.Items[i].Selected grouping, like my example above.


Bruce


Please remember to click "Mark as Answer" on the posts that helped solve your issue.
0
BHendry
3/21/2008 4:37:08 PM

Thanks that gets rid of that error, however no values are being sent to the database; and when i debug it on this line it shows the command value is "null"

SqlCommand command = new SqlCommand("SubscribeToNewsletters", conn); ---- This is correct the stored procedure, i made a mistake in the original post.

 

0
Pmillio
3/21/2008 4:44:19 PM

 Hi,

I may be wrong cause i've never done like you did here. Can you please let me know what is your code doing here. Cause i see you are adding parameters in a loop now if you have selected two items in your AlterList and your procedure accepts only 4 parameters then you are adding 8 parameters to the command object.
Also parameter name should be added with @ sign so firstName will become @firstName. Also what about the output variable.

 

command.CommandType = CommandType.StoredProcedure;for (int i = 0; i < AlertList.Items.Count; i++)

{
if (AlertList.Items[i].Selected)

{

command.Parameters.AddWithValue("firstName", txtFirstName.Text);
command.Parameters.AddWithValue("surname", txtSurname.Text);

command.Parameters.AddWithValue("emailAddress", txtEmail.Text);
command.Parameters.AddWithValue("newsletterID", AlertList.Items[i].Value);

}

}


[Please mark the post as answer that helps you.]

Regards,
Farhan Uddin Khan
Enpointe Technologies
0
FarhanK
3/21/2008 4:45:25 PM

That code is meant to go through the checkboxlist and get the ones which have been selected, my knowledge in programming is still basic so i may have done it the wrong way. If you have any suggestions on the way i should be doing it please tell me, thanks.

0
Pmillio
3/21/2008 4:48:54 PM

Yeah, that's a good point. You should re-instantiate your command object at the beginning of each loop. So you should move the following lines so they're right above you command.Paramaters.AddWithValue lines:

 

SqlCommand command = new SqlCommand("SubscribeToNewsletters", conn);

command.CommandType = CommandType.StoredProcedure;
 
Bruce


Please remember to click "Mark as Answer" on the posts that helped solve your issue.
0
BHendry
3/21/2008 4:55:38 PM

Hi do you mean like this;

protected void Btn_Subscribe_Click(object sender, EventArgs e)

{

 

for (int i = 0; i < AlertList.Items.Count; i++)if (AlertList.Items[i].Selected)

{

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);

SqlCommand command = new SqlCommand("SubscribeToNewsletters", conn);

command.CommandType = CommandType.StoredProcedure;

command.Parameters.AddWithValue("@firstName", txtFirstName.Text);

command.Parameters.AddWithValue("@surname", txtSurname.Text);

command.Parameters.AddWithValue("@emailAddress", txtEmail.Text);

command.Parameters.AddWithValue("@newsletterID", AlertList.Items[i].Value);

 

I did the above still no difference.

0
Pmillio
3/21/2008 5:00:15 PM

Yeah, with the exception of the SqlConnection line, it should stay outside of the loop (I updated my example).

What happens when you step through it? No exception occurs on ExecuteNonQuery? You can se the values in txtFirstName.Text, etc. as they are appended to the parameters?


Bruce


Please remember to click "Mark as Answer" on the posts that helped solve your issue.
0
BHendry
3/21/2008 5:03:28 PM

I have updated the code, I debugged it and no errors are being thrown up. Have i gone about the method the wron way?

0
Pmillio
3/21/2008 5:28:55 PM

I'm just about done with a working example using your code. I should be able to help you pinpoint the issue. Will let you know when I'm done.


Bruce


Please remember to click "Mark as Answer" on the posts that helped solve your issue.
0
BHendry
3/21/2008 5:33:32 PM
I appreciate your help very much, thank you.
0
Pmillio
3/21/2008 5:43:48 PM

No problem. Okay, I have an exact replica working on my end.

I'm not sure why you weren't seeing that error. Are you familiar with how to read the exception data while in debug mode? Let me know if you can get this working and then I will help you understand how to solve it in the future if you want.


Bruce


Please remember to click "Mark as Answer" on the posts that helped solve your issue.
0
BHendry
3/21/2008 5:59:24 PM

Now that I can see what you're doing I can tell you have another issue, too. Your current implementation will insert the user in your ThinkUsers table multiple times, which you don't want. We'll fix that in a sec, but first at least get it working. When you debug, be sure to put a breakpoint on the throw new Exception line, that way you can examine the exception object (ex in this case) to see the details of any exception that occurs.


Bruce


Please remember to click "Mark as Answer" on the posts that helped solve your issue.
0
BHendry
3/21/2008 6:09:20 PM

Pmillio,

You will need to update your existing stored procedure and add another one. Then you will need to update your code in the Btn_Subscribe_Click event. I'm going to paste these 3 things below. Make sure you understand what's happening as it will help you next time. Let me know if you have questions or you have trouble implementing it.

Add this stored procedure:

CREATE PROCEDURE AddNewUser
	
@emailAddress VARCHAR(250),

@firstName VARCHAR(250),

@surname VARCHAR(250)

AS
SET NOCOUNT ON

INSERT INTO ThinkUsers (emailAddress, firstName, surname)

VALUES (@emailAddress, @firstName, @surname)

select CAST(@@IDENTITY AS int) 

-- this is the ID created in the TheUserTable

-- when you make that first insert, the database

-- will generate the next ID value in that table
 

Update this stored procedure:

 

ALTER PROCEDURE [dbo].[SubscribeToNewsletters]

@userID int,
@newsletterID int

AS

SET NOCOUNT ON
	
INSERT INTO SubscribedNewsletters (userID, newsletterID)
	
VALUES (@userID, @newsletterID)

RETURN @@ROWCOUNT

Update Btn_Subscribe_Click code:

 

protected void Btn_Subscribe_Click(object sender, EventArgs e)
    {
        

        //first determine if user has selected anything, don't add new user if nothing is selected, just exit
        if (AlertList.SelectedIndex != -1)
        {
            //at least one thing is selected, add user
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);
            SqlCommand command = new SqlCommand("AddNewUser", conn);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.AddWithValue("@firstName", txtFirstName.Text);
            command.Parameters.AddWithValue("@surname", txtSurname.Text);
            command.Parameters.AddWithValue("@emailAddress", txtEmail.Text);


            conn.Open();

            try
            {
                
                //insert new user and get userID back
                int newUserID;
                newUserID = (int)(command.ExecuteScalar());


                if (newUserID > 0) //user add was successful, add subscriptions
                {
                    for (int i = 0; i < AlertList.Items.Count; i++)
                    {
                        if (AlertList.Items[i].Selected)
                        {
                            command = new SqlCommand("SubscribeToNewsletters", conn);

                            command.CommandType = CommandType.StoredProcedure;

                            command.Parameters.AddWithValue("@userID", newUserID);
                            command.Parameters.AddWithValue("@newsletterID", AlertList.Items[i].Value);

                            command.ExecuteNonQuery();

                        }

                    }
                }

            }
            catch (Exception ex)
            {

                throw new Exception("Exception adding account. " + ex.Message);

            }
            finally
            {

                conn.Close();

            }
        }
        
    }
 
Bruce


Please remember to click "Mark as Answer" on the posts that helped solve your issue.
0
BHendry
3/21/2008 8:05:36 PM

Hi thanks for your help so far, i have implemented your code however its not working for some reason, if you look at the code below i had to comment out the first if statement, because with the if statement wrapped around it nothing happened, however when i comment it out, it executes the first stored procedure "AddNewUser" because the new user is added to the table, but it goes no further than that because the second stored procedure has no effect or is not reached. Have i omitted something?

protected void Btn_Subscribe_Click(object sender, EventArgs e)

{

//first determine if user has selected anything, don't add new user if nothing is selected, just exit

//if (AlertList.SelectedIndex != -1)

//{

//at least one thing is selected, add user

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);

SqlCommand command = new SqlCommand("AddNewUser", conn);

command.CommandType = CommandType.StoredProcedure;

command.Parameters.AddWithValue("@firstName", txtFirstName.Text);

command.Parameters.AddWithValue("@surname", txtSurname.Text);command.Parameters.AddWithValue("@emailAddress", txtEmail.Text);

conn.Open();

try

{

//insert new user and get userID back

int newUserID;newUserID = (int)(command.ExecuteScalar());

 

if (newUserID > 0) //user add was successful, add subscriptions

{

for (int i = 0; i < AlertList.Items.Count; i++)

{

if (AlertList.Items[i].Selected)

{

command =
new SqlCommand("SubscribeToNewsletters", conn);

command.CommandType = CommandType.StoredProcedure;

command.Parameters.AddWithValue("@userID", newUserID);command.Parameters.AddWithValue("@newsletterID", AlertList.Items[i].Value);

command.ExecuteNonQuery();

}

}

}

}

catch (Exception ex)

{

throw new Exception("Exception adding account. " + ex.Message);

}

finally

{

conn.Close();

}

//}

}

 

 

0
Pmillio
3/22/2008 3:01:13 PM

Pmillio,

The code definitely works. I would put it back the way it was and then we can go through it. The first IF (if (AlertList.SelectedIndex != -1)) statement that you commented out checks your AlertList CheckBoxList to see if any of the checkboxes are checked. If you had to comment it out that means that your CheckBoxList didn't think anything was checked. You might want to break on that If statement and evaluate your CheckBoxList in the Locals or Immediate window to see what it contains.

Looking at your Page_Load, if you're rebinding your CheckBoxList on postback that may be the problem. Try only initializing your CheckBoxList in Page_Load when it's not a postback.


Bruce


Please remember to click "Mark as Answer" on the posts that helped solve your issue.
0
BHendry
3/22/2008 5:47:27 PM

Hi BHendry, i have now done the following;

public void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)---ADDED THIS SECTION

{

try

{

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);

SqlCommand command = new SqlCommand("stream_NewsletterTypes", conn);

command.CommandType = CommandType.StoredProcedure;

command.Connection.Open();

SqlDataReader datareader = command.ExecuteReader();

AlertList.DataSource = datareader;

AlertList.DataTextField =
"newsletterName";

AlertList.DataBind();

command.Connection.Close();

command.Connection.Dispose();

}

catch (Exception ex)

{

throw new Exception("Exception. " + ex.Message);

}

}

}

 I now get this error "Exception adding account. Error converting data type nvarchar to int. " so i am guessing we are getting closer.

0
Pmillio
3/22/2008 6:09:47 PM

When you bind your AlertList, make sure your DataValueField is binding to the ID field of your newsletters, that is what is getting inserted into your subscription table. Break on the code that pulls the value out of your checkbox for the newletterID and see what it's pulling out.


Bruce


Please remember to click "Mark as Answer" on the posts that helped solve your issue.
0
BHendry
3/22/2008 6:17:42 PM
Hi BHendry, thank you for your perseverance, it is working now, i had to bind the ID field of the newsletters as you sugested. Thank you for all your help, you are a credit to this forum. Thanks
0
Pmillio
3/22/2008 6:26:00 PM

Glad you got it going. Hopefully you'll be able to use it as a reference getting your head around some of the concepts. Let me know if you have any questions about it in the future. Hasta!


Bruce


Please remember to click "Mark as Answer" on the posts that helped solve your issue.
0
BHendry
3/22/2008 6:33:24 PM

I will do thanks.

0
Pmillio
3/22/2008 6:34:07 PM
Reply: