Click here to Skip to main content
15,921,841 members
Please Sign up or sign in to vote.
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
Updated 10-Jun-14 21:04pm
v2
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[^]

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)";
 
Share this answer
 
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?
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...
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)";
 
Share this answer
 
Comments
Thanks7872 11-Jun-14 3:21am    
Clear enough. That is what i want Firdose to explain. :-)
Thanks7872 11-Jun-14 3:35am    
I think votes are not getting displayed. Bug is still there. I am not sure if i am the only one.
Its displaying now. But initially, it was not.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900