Incorrect syntax near '1'. [Answered] RSS

• • Reply
  Gslick94

  None

  0 Points

  1 Post

  Incorrect syntax near '1'.

  Jul 14, 2015 09:15 AM|Gslick94|LINK

  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();

• • Reply
  ignatandrei

  All-Star

  120145 Points

  27982 Posts

  Moderator

  MVP

  Re: Incorrect syntax near '1'.

  Jul 14, 2015 10:35 AM|ignatandrei|LINK

  replace

  @1d

  with 

  @d1

  and retry

  .NET Teacher | .NET Videos 5 minutes
• • Reply
  Fei Han - MS...

  All-Star

  40515 Points

  6233 Posts

  Microsoft

  Re: Incorrect syntax near '1'.

  Jul 15, 2015 05:08 AM|Fei Han - MSFT|LINK

  Hi Gslick94,

  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

  
  MSDN Community Support
  Please remember to "Mark as Answer" the responses that resolved your issue.