0
 public partial class SignUp : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btSignup_Click(object sender, EventArgs e)
    {
        if (tbUname.Text != "" & tbPass.Text != "" && tbName.Text != "" && tbEmail.Text != "" && tbCPass.Text != "")
        {
            if (tbPass.Text == tbCPass.Text)
            {
                String CS = ConfigurationManager.ConnectionStrings["Database_AvaliacaoConnectionString1"].ConnectionString;
                using (SqlConnection con = new SqlConnection(CS))
                {
                    SqlCommand cmd = new SqlCommand("insert into Users Values('" + tbUname.Text + "','" + tbPass.Text + "','" + tbEmail.Text + "','" + tbName.Text + "','')", con);
                    con.Open();

                    cmd.ExecuteNonQuery();
                    lblMsg.Text = "Registration Successfull";
                    lblMsg.ForeColor = Color.Green;
                   // Response.Redirect("~/Signin.aspx");
                }
            }
            else
            {
                lblMsg.ForeColor = Color.Red;
                lblMsg.Text = "Passwords do not match";
            }
        }
        else
        {
            lblMsg.ForeColor = Color.Red;
            lblMsg.Text = "All Fields Are Mandatory";

        }
    }
}

In the Users table I got the following values 

 [Uid] int IDENTITY (1,1) PRIMARY KEY, 
[Username] NVARCHAR(MAX) NULL, 
[Password] NVARCHAR(MAX) NULL, 
[Email] NVARCHAR(MAX) NULL, 
[Name] NVARCHAR(MAX) NULL

It gives me the following error when I try to sign up:

System.Data.SqlClient.SqlException: 'An explicit value for the identity column in table 'Users' can only be specified when a column list is used and IDENTITY_INSERT is ON.'

For some reason it doesn't let me add to the values.

tadman
  • 208,517
  • 23
  • 234
  • 262
iHugo
  • 1
  • 3
  • 1
    `uid` gets its value from the DB itself. Don't you provide it! So name the columns you insert into like this (and leave the `uid` column): `insert into t (col2, col3) values ('v1', 'v2')` – juergen d Jan 10 '18 at 20:45
  • 2
    And don't store passwords in plain text. Store the hash value only!! – juergen d Jan 10 '18 at 20:47
  • 3
    And use Prepared Statements instead of patching your queries together like this! – juergen d Jan 10 '18 at 20:48
  • 2
    **WARNING**: Don't forget to [properly escape your inputs](http://bobby-tables.com). As Jurgen says, **use prepared statements with placeholder values**. – tadman Jan 10 '18 at 20:49
  • I did think of that solution to insert only the values to the columns I pretend but it gave the following error -> There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement. – iHugo Jan 10 '18 at 20:53
  • Those data types don't look like MySQL. I suspect you're actually using SQL-Server. – Barmar Jan 10 '18 at 21:12

1 Answers1

2

You are inserting into the columns, in order as they appear in the table:

"insert into Users Values('" + tbUname.Text + "','" + tbPass.Text + "','" + tbEmail.Text + "','" + tbName.Text + "','')"

So you are saying here that the first field UID should be set to whatever is in tbUname.Text. That's nonsense.

Instead, specify which fields you are inserting to and then list them in that order:

"insert into Users (Username, Password, Email, [Name]) Values('" + tbUname.Text + "','" + tbPass.Text + "','" + tbEmail.Text + "','" + tbName.Text + "')"

Unrelated but troubling things in your code:

  1. Don't concatenate your SQL string together. This leaves you wide open for a SQL injection attack. Instead parameterize your SQL

  2. Don't store passwords in plain text in your database. They should be hashed and salted.

JNevill
  • 46,980
  • 4
  • 38
  • 63
  • But I've done that and it gives me this -> There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement. – iHugo Jan 10 '18 at 21:02
  • Are you certain that you have removed that final blank `''` string you are trying to insert in your original statement. The end of yours: `+ tbName.Text + "','')"` The end of mine: `+ tbName.Text + "')"` – JNevill Jan 10 '18 at 21:12
  • 1
    It looks like he's actually using SQL-Server, not MySQL, So the backticks around `Name` should be square brackets. – Barmar Jan 10 '18 at 21:13
  • @JNevill Yeah, that was really obvious and I didn't even see it. Thank you for your help mate! Could really improve my coding from you guys here, as I really need to haha. – iHugo Jan 10 '18 at 21:17