how to insert values to database if the values not in the database using c# asp.net. I got an error violation of primarykey username when enter username already in database

Question

0.00/5 (No votes)

See more:

C#

protected void Register_btn_Click(object sender, EventArgs e)
     {

 con = new SqlConnection("Data Source=USER-PC;Initial Catalog=myproject;User ID=sa;Password=Password1");
          cmd = new SqlCommand("Select  * from Registration_table", con);
          adt = new SqlDataAdapter();
          adt.SelectCommand = cmd;
          ds = new DataSet();
          adt.Fill(ds, "table");
          if (ds.Tables[0].Rows.Count > 0)
          {
              if (txt_Uname.Text == ds.Tables[0].Rows[0][0].ToString())
              {
                  Label2.Text = "Username already exists";
                  Panel1.Visible = false;
                  return;
              }
              else
              {
                              Register();
                          }
                      }

                  }

                  public void Register()
                  {
                  con = new SqlConnection("Data Source=USER-PC;Initial Catalog=myproject;User ID=sa;Password=Password1");
                  cmd = new SqlCommand("Select * from Registration_table ", con);
                  adt = new SqlDataAdapter();
                  adt.SelectCommand = cmd;
                  ds = new DataSet();
                  adt.Fill(ds, "temp");
                  SqlCommandBuilder cb = new SqlCommandBuilder(adt);
                  dr = ds.Tables["temp"].NewRow();
                  dr[0] = txt_Uname.Text;
                  dr[1] = CreateRandomPassword(7);
                  dr[2] = drop_Question.Text;
                  dr[3] = txt_SecurityAnswer.Text;
                  dr[4] = txt_email.Text;
                  dr[5] = drop_College.Text;
                  dr[6] = drop_Day.SelectedValue + drop_Month.SelectedValue + drop_Year.SelectedValue;
                  dr[7] = txt_Authority.Text;
                  dr[8] = txt_Subauthority.Text;
                  dr[9] = RandomString(6);
                  dr[10] = RandomPassword(6);
                  ds.Tables["temp"].Rows.Add(dr);
                  adt.Update(ds, "temp");
                  MailMessage msg = new MailMessage();
                  string ActivationUrl = string.Empty;
                  string emailid = string.Empty;
                  SmtpClient smtp = new SmtpClient();
                  string emailId = txt_email.Text.Trim();
                  msg.From = new MailAddress("m.sukanya44@gmail.com");
                  msg.To.Add(emailId);
                  msg.Subject = "Conformation mail";
                  msg.Body = "hello" + " " + "" + txt_Uname.Text.Trim() + "" + "<br />" + "Your Username:" + "" + txt_Uname.Text.Trim() + "" + "<br />" + " Password:" + "" + dr[1] + "" + "<br />" + "Authority Password:" + "" + dr[9] + "" + "<br />" + "SubAuthority Password:" + "" + dr[10] + "" + "<br />";
                  msg.IsBodyHtml = true;
                  smtp.Host = "smtp.gmail.com";
                  smtp.EnableSsl = true;
                  NetworkCredential NetworkCred = new NetworkCredential("m.sukanya44@gmail.com", "KAIRALIMANIKANDAN");
                  smtp.UseDefaultCredentials = true;
                  smtp.Credentials = NetworkCred;
                  smtp.Port = 587;
                  smtp.Send(msg);
                  Response.Write("<script language='javascript'>window.alert('Registration Completed Successfully');</script>");

              }

Posted 26-Aug-14 20:45pm

Member 11026295

Updated 26-Aug-14 21:14pm

Gihan Liyanage

v2

Add a Solution

Comments

Gihan Liyanage 27-Aug-14 3:13am

Where is your insert query ?

Member 11026295 27-Aug-14 3:19am

within the register function.please check it sir

3 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)

Gihan Liyanage · Answer 1 · 2014-08-26T21:41:00

Why are you using this much of code for check existing users ? If you like you can use this code for check existing users

C#

using (SqlConnection con = new SqlConnection("Data Source=Data Source=USER-PC;Initial Catalog=myproject;User ID=sa;Password=Password1")) 
        {
            con.Open();

            bool exists = false;

            // create a command to check if the username exists
            using (SqlCommand cmd = new SqlCommand("select count(*) from [User] where UserName = @UserName", con))
            {
                cmd.Parameters.AddWithValue("UserName", txtUsername.Text);
                exists = (int)cmd.ExecuteScalar() > 0;
            }

            // if exists, show a message error
            if (exists)
                errorPassword.SetError(txtUsername, "This username has been using by another user.");
            else 
            {
                            // does not exists, so, persist the user 
            }

            con.Close();
        }

Dilan Shaminda · Answer 2 · 2014-08-26T21:49:00

The easy way is to use a Stored Procedure to check whether the username is exists or not.If not exists you can insert the username else you can return some value and catch it using C# code.

example :

SQL

CREATE PROCEDURE [dbo].[checkuserExists]
      @Username VARCHAR(20)

AS
BEGIN
      SET NOCOUNT ON;
      IF EXISTS(SELECT userID FROM TableName WHERE username=@Username)
      BEGIN
            SELECT -1 -- Username exists.
      END
      ELSE
      BEGIN
            INSERT INTO [_userDetailsCustomer]
                    --columns
            VALUES
                    --values
     END
END

in your C# insert code

C#

int user_Id = 0; 
using (SqlConnection con = new SqlConnection(connect.GetConnectionString()))
        {
            using (SqlCommand cmd = new SqlCommand("checkuserExists"))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Username", Username);
                    
                    cmd.Connection = con;
                    con.Open();
                    user_Id = Convert.ToInt32(cmd.ExecuteScalar());
                    con.Close();
                }
             }
        }

By checking the user_Id you can prompt user to re-enter the username.

Pikoh · Answer 3 · 2014-08-26T21:19:00

You are loading your dataset with all the records in your database table,and then checking the name with the first row. Try something like this:

C#

protected void Register_btn_Click(object sender, EventArgs e)
{
 
con = new SqlConnection("Data Source=USER-PC;Initial Catalog=myproject;User ID=sa;Password=Password1");
cmd = new SqlCommand("Select * from Registration_table", con);
adt = new SqlDataAdapter();
adt.SelectCommand = cmd;
ds = new DataSet();
adt.Fill(ds, "table");
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
if (txt_Uname.Text == dr[0].ToString())
{
Label2.Text = "Username already exists";
Panel1.Visible = false;
return;
}
}

else
{
Register();
}
}
 
}

This is just one approach. You could use linq, or in your query select just the records with name= txt_Uname and count to check if username is already there.

how to insert values to database if the values not in the database using c# asp.net. I got an error violation of primarykey username when enter username already in database

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0