locked
Incorrect syntax near '1'. RRS feed

  • Question

  • User-1510961914 posted

    Im trying to make a webpage where they user is able to register their own account and also at the same time another table is being updated. Ignore all the // :P

    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["LoginConn"].ConnectionString);
    SqlCommand cmd = new SqlCommand();
    string sql = "INSERT INTO userInfo(userTitle,userFirstName,userLastname";
    sql += ",userNric,userEmail,userAddress1";
    sql += ",userAddress2,userZipcode,userMobile";
    sql += ",userPhone,userName,userPassword";
    sql += ",userRole)";
    sql += "VALUES(@userTitle,@userFirstName,@userLastName,@userNric,@userEmail,@userAddress1,@userAddress2,@userZipcode,@userMobile,@userPhone,@userName,@userPassword,@userRole)";
    cmd.CommandText = sql;
    cmd.Connection = con;
    con.Open();
    cmd.Parameters.AddWithValue("@userTitle", rbMr.Text);
    cmd.Parameters.AddWithValue("@userFirstName", tbFirstName.Text);
    cmd.Parameters.AddWithValue("@userLastName", tbLastName.Text);
    cmd.Parameters.AddWithValue("@userNric", tbNric.Text);
    cmd.Parameters.AddWithValue("@userEmail", tbEmail.Text);
    cmd.Parameters.AddWithValue("@userAddress1", tbAddress1.Text);
    cmd.Parameters.AddWithValue("@userAddress2", tbPassword.Text.Trim());
    cmd.Parameters.AddWithValue("@userZipcode", tbAddress2.Text);
    cmd.Parameters.AddWithValue("@userMobile", int.Parse(tbMobileNum.Text));
    cmd.Parameters.AddWithValue("@userPhone", int.Parse(tbPhoneNumber.Text));
    cmd.Parameters.AddWithValue("@userName", tbUsername.Text.Trim());
    cmd.Parameters.AddWithValue("@userPassword", tbPassword.Text.Trim());
    cmd.Parameters.AddWithValue("@userRole", "Customer");


    cmd.ExecuteNonQuery();


    // SqlCommand id = new SqlCommand("SELECT max(UserID) FROM UserInfo");

    // TODO 1. select max(UserID) query to read the max id from the UserInfo table
    // 2. use a data reader to "loop" the result from the above query
    //string maxID = "";
    // 3. store the result of the reader into maxID;
    // 2. use a data reader to "loop" the result from the above query
    //string maxID = "";
    // 3. store the result of the reader into maxID;
    // id.Connection = con;

    DataTable dt = new DataTable();
    SqlCommand cd = new SqlCommand();
    cd.Connection = con;
    string sql1 = "SELECT max(UserID) From UserInfo";
    cd.CommandText = sql1;
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = cd;
    da.Fill(dt);
    cd.ExecuteNonQuery();

    Session.Add("max(UserID)", dt.Rows[0][0].ToString());
    string id = Session["max(UserID)"].ToString();

    // string strCommandText = "SELECT max(UserID) From UserInfo";
    //SqlCommand cc = new SqlCommand(strCommandText, con);
    //cc.Connection = con;
    //cc.ExecuteScalar();


    SqlCommand myConn = new SqlCommand();
    string oo = "INSERT INTO UserD(UserID,ABalance,LBalance,HDay,1day,2day) VALUES (@ID,@Ab,@Lb,@Hd,@1d,@2d)";
    myConn.CommandText = oo;
    myConn.Connection = con;
    myConn.Parameters.AddWithValue("@ID", id);
    myConn.Parameters.AddWithValue("@Ab", 500);
    myConn.Parameters.AddWithValue("@Lb", null);
    myConn.Parameters.AddWithValue("@Hd", null);
    myConn.Parameters.AddWithValue("@1d", null);
    myConn.Parameters.AddWithValue("@2d", null);

    myConn.ExecuteNonQuery();

    try
    {
    lbMessage.Text = "Register Completed";
    btnContinue.Visible = true;

    }
    catch (Exception ex)
    {
    lbMessage.Text = ex.Message;
    //throw new Exception(ex.Message);
    }
    con.Close();

    Tuesday, July 14, 2015 9:15 AM

Answers

  • User61956409 posted

    Hi Gslick94,

    string oo = "INSERT INTO UserD(UserID,ABalance,LBalance,HDay,1day,2day) VALUES (@ID,@Ab,@Lb,@Hd,@1d,@2d)";

    The error may be caused by the above code, you could try to modify your code as below.

    string oo = "INSERT INTO UserD(UserID,ABalance,LBalance,HDay,[1day],[2day]) VALUES (@ID,@Ab,@Lb,@Hd,@1d,@2d)";

    Best Regards,

    Fei Han

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 15, 2015 5:08 AM

All replies

  • User197322208 posted

    replace

    @1d

    with 

    @d1

    and retry

    Tuesday, July 14, 2015 10:35 AM
  • User61956409 posted

    Hi Gslick94,

    string oo = "INSERT INTO UserD(UserID,ABalance,LBalance,HDay,1day,2day) VALUES (@ID,@Ab,@Lb,@Hd,@1d,@2d)";

    The error may be caused by the above code, you could try to modify your code as below.

    string oo = "INSERT INTO UserD(UserID,ABalance,LBalance,HDay,[1day],[2day]) VALUES (@ID,@Ab,@Lb,@Hd,@1d,@2d)";

    Best Regards,

    Fei Han

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 15, 2015 5:08 AM