An unhandled exception of the type 'System.Data.SqlClient.SqlException' occurred in system.Data.dll Additional information: Must declare the Scalar variable "@8".

Question

0.00/5 (No votes)

See more:

Could some one help me to resolve this error please, I have been trying without luck. I am trying to register user details in database using C# Professional 2013. I ma getting this error: An unhandled exception of the type 'System.Data.SqlClient.SqlException' occurred in system.Data.dll Additional information: Must declare the Scalar variable "@8".
Here is my code: I also seem not to save the image at all.

C#

private void btnRegister_Click(object sender, EventArgs e)
       {
           if (txtUserName.Text == "")
           {
               MessageBox.Show("Please enter username", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
               txtUserName.Focus();
               return;
           }
           if (cmbUserType.Text == "")
           {
               MessageBox.Show("Please select user type", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
               txtUserName.Focus();
               return;
           }
           if (txtPassword.Text == "")
           {
               MessageBox.Show("Please enter password", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
               txtPassword.Focus();
               return;
           }
           if (txtConfirmPassword.Text != txtPassword.Text)
           {
               MessageBox.Show("The password does not match", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
               txtConfirmPassword.Focus();
               return;
           }
           if (txtFirstName.Text == "")
           {
               MessageBox.Show("Please enter name", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
               txtFirstName.Focus();
               return;
           }
           if (txtPhoneNo.Text == "")
           {
               MessageBox.Show("Please enter contact no.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
               txtPhoneNo.Focus();
               return;
           }
           if (txtEmail.Text == "")
           {
               MessageBox.Show("Please enter email", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
               txtEmail.Focus();
               return;
           }
          // try
         //  {
               con = new SqlConnection(cs.DBConn);
               con.Open();
               string ct = "select username from Registration where Username=@find";

               cmd = new SqlCommand(ct);
               cmd.Connection = con;
               cmd.Parameters.Add(new SqlParameter("@find", System.Data.SqlDbType.VarChar, 30, "Username"));
               cmd.Parameters["@find"].Value = txtUserName.Text;
               rdr = cmd.ExecuteReader();

               if (rdr.Read())
               {
                   MessageBox.Show("Username Already Exists", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                   txtUserName.Text = "";
                   txtUserName.Focus();


                   if ((rdr != null))
                   {
                       rdr.Close();
                   }
                   return;
               }

               con = new SqlConnection(cs.DBConn);
               con.Open();

               string cb = "insert into Registration(Username,Userpassword,Name,Surname,PhoneNumber,Email,DateOfBirth,UserType,Picture) VALUES (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@8,@9)";

               cmd = new SqlCommand(cb);

               cmd.Connection = con;

               cmd.Parameters.Add("@d1", System.Data.SqlDbType.VarChar);
               cmd.Parameters.Add("@d2", System.Data.SqlDbType.VarChar);
               cmd.Parameters.Add("@d3", System.Data.SqlDbType.VarChar);
               cmd.Parameters.Add("@d4", System.Data.SqlDbType.VarChar);
               cmd.Parameters.Add("@d5", System.Data.SqlDbType.VarChar);
               cmd.Parameters.Add("@d6", System.Data.SqlDbType.VarChar);
               cmd.Parameters.Add("@d7", System.Data.SqlDbType.VarChar);
               cmd.Parameters.Add("@d8", System.Data.SqlDbType.VarChar);
               //cmd.Parameters.Add(new SqlParameter("@d9", System.Data.SqlDbType.Image,32, "picture"));

               MemoryStream MemStream = new MemoryStream();
               Byte[] DataPic_Update = null;

               this.pictureImage.Image.Save(MemStream, ImageFormat.Jpeg);
               DataPic_Update = MemStream.GetBuffer();
               MemStream.Read(DataPic_Update, 0, DataPic_Update.Length);




               cmd.Parameters["@d1"].Value = txtUserName.Text.Trim();
               cmd.Parameters["@d2"].Value = txtPassword.Text;
               cmd.Parameters["@d3"].Value = txtFirstName.Text;
               cmd.Parameters["@d4"].Value = txtLastName.Text;
               cmd.Parameters["@d5"].Value = txtPhoneNo.Text;
               cmd.Parameters["@d6"].Value = txtEmail.Text;
               cmd.Parameters["@d7"].Value = dateDOB.Value;
               cmd.Parameters["@d8"].Value = cmbUserType.Text;
               // image content
               SqlParameter photo = new SqlParameter("@d9", SqlDbType.Image);
               photo.Value = DataPic_Update;
               cmd.Parameters.Add(photo);

               cmd.ExecuteReader();
              // cmd.ExecuteNonQuery();

               con.Close();
               con = new SqlConnection(cs.DBConn);
               con.Open();

               string cb1 = "insert into users(Username,Userpassword,UserType) VALUES (@d1,@d2,@d3)";

               cmd = new SqlCommand(cb1);

               cmd.Connection = con;

               cmd.Parameters.Add(new SqlParameter("@d1", System.Data.SqlDbType.VarChar, 30, "Username"));
               cmd.Parameters.Add(new SqlParameter("@d2", System.Data.SqlDbType.VarChar, 30, "Userpassword"));
               cmd.Parameters.Add(new SqlParameter("@d3", System.Data.SqlDbType.VarChar, 15, "UserType"));

               cmd.Parameters["@d1"].Value = txtUserName.Text.Trim();
               cmd.Parameters["@d2"].Value = txtPassword.Text;

               cmd.Parameters["@d3"].Value = cmbUserType.Text;
               cmd.ExecuteReader();


               con.Close();
              MessageBox.Show("Successfully Registered", "User", MessageBoxButtons.OK, MessageBoxIcon.Information);
               Autocomplete();
               btnRegister.Enabled = false;

           //}
           //catch (Exception ex)
           //{
           //    MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
          // }
       }

Thank you very much

Posted 10-Jun-14 20:51pm

katela

Updated 10-Jun-14 21:04pm

Thanks7872

v2

Add a Solution

Comments

Thanks7872 11-Jun-14 3:09am

First of all,remove unnecessary code. Error has nothing to do with your validation messages. Try to post smallest possible snippet.

Nelek 11-Jun-14 4:05am

For well explained description of what Rohan said, please read: best way to post code snippets[^]

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Naz_Firdouse · Accepted Answer · 2014-06-10T20:54:00

Solution 1

your parameter names are mismatch...

replace @8 and @9 in youe insert query with @d8 and @d9...

change your code like this

C#

string cb = "insert into Registration(Username,Userpassword,Name,Surname,PhoneNumber,Email,DateOfBirth,UserType,Picture) VALUES (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8,@d9)";

Posted 10-Jun-14 20:54pm

Naz_Firdouse

Updated 10-Jun-14 21:53pm

v2

Comments

Thanks7872 11-Jun-14 3:07am

And what is the difference between yours and OP's Code? How it will help resolve the issue?

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 11-Jun-14 3:18am

See my solution Rohan. :)

Naz_Firdouse 11-Jun-14 3:19am

parameter name mismatch....
OP mentioned the names as @8 and @8 in the query while the names referred in cmd.Parameters.Add are *d8 and @d9...

Thanks7872 11-Jun-14 3:22am

Thats what i wanted you to explain in the answer itself. Nyways +5..!

Naz_Firdouse 11-Jun-14 3:50am

Thanks...
Actually I updated the answer with explanation as well(before you commented)...but there was some network issue at my end and hence my updated answer didn't reflected...

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Accepted Answer · 2014-06-10T21:14:00

The problem is, in the query you have declared the parameter name different from that you have added to the command.

C#

string cb = "insert into Registration(Username,Userpassword,Name,Surname,PhoneNumber,Email,DateOfBirth,UserType,Picture) VALUES (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@8,@9)";

// ...................
cmd.Parameters.Add("@d8", System.Data.SqlDbType.VarChar);

//................
SqlParameter photo = new SqlParameter("@d9", SqlDbType.Image);

Mark that the Parameter names are different.

So, you need to change the query to reflect the same name as the parameters added.

C#

string cb = "insert into Registration(Username,Userpassword,Name,Surname,PhoneNumber,Email,DateOfBirth,UserType,Picture) VALUES (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8,@d9)";