Click here to Skip to main content
16,020,666 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi im tring to insert values in my database but the thing is, am having problems when im inserting password value. I get this error "Syntax error in INSERT INTO statement."

C#
protected void Page_Load(object sender, EventArgs e)
  {
      // specifies database connection. Change this if needed
      connection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
      connection += Server.MapPath("~/App_Data/dbStudent.mdb") + ";";
  }
  protected void btnTest_Click(object sender, EventArgs e)
  {
      // set up and open connection
      dbConn = new OleDbConnection(connection);
      dbConn.Open();

      string sqlInsert = "INSERT INTO StudentmemberTable(Fname,Lname,Telephone,Email,Password)"
                        + "Values(@Fname,@Lname,@telephone,@email,@password);";
      //txtName.Text
      try
      {
          dbCmd = new OleDbCommand(sqlInsert, dbConn);
          dbCmd.Parameters.AddWithValue("@Fname", OleDbType.VarChar).Value = txtName.Text;
          dbCmd.Parameters.AddWithValue("@Lname", OleDbType.VarChar).Value = txtSurName.Text;
          dbCmd.Parameters.AddWithValue("@telephone", OleDbType.Single).Value = txtTelephone.Text;
          dbCmd.Parameters.AddWithValue("@email", OleDbType.VarChar).Value = txtEmail.Text;
          dbCmd.Parameters.AddWithValue("@password", OleDbType.VarChar).Value = txtpassword.Text;
          dbCmd.ExecuteNonQuery();
      }
      catch(Exception ex)
      {
          builder = new StringBuilder();
          builder.Append("<script type='etxt/javascript'>");
          builder.Append(ex);
          builder.Append("</script>");
      }

      txtEmail.Text = String.Empty;
      txtName.Text = string.Empty;
      txtSurName.Text = string.Empty;
      txtTelephone.Text = String.Empty;
      dbConn.Close();
  }
Posted

hi, just putting an space after 'Password)' will do or just put an space before 'Value'. This will surely solve you problem. Currently your query is going in SQL as :

INSERT INTO StudentmemberTable(Fname,Lname,Telephone,Email,Password)Values(@Fname,@Lname,@telephone,@email,@password)


which is wrong, it should be :


INSERT INTO StudentmemberTable(Fname,Lname,Telephone,Email,Password) Values(@Fname,@Lname,@telephone,@email,@password).


This will surely do, no need to replace parameters with '?' at all.

Anurag
@cheers@
 
Share this answer
 
Add a space after the first ')'.
so:
MIDL
string sqlInsert = "INSERT INTO StudentmemberTable(Fname,Lname,Telephone,Email,Password) "
                       + "Values(@Fname,@Lname,@telephone,@email,@password);";


Also make sure your table /column names are correct.
And if that doesn't work try replacing the '@' names with '?', not sure but I seem to remember that access doesn't like those '@'
so:
MIDL
string sqlInsert = "INSERT INTO StudentmemberTable(Fname,Lname,Telephone,Email,Password) "
                       + "Values(?,?,?,?,?);"

;

If you use the '?' you have to make sure you add the parameters to the connection in the right order since that is how they will be inserted.
 
Share this answer
 
Comments
Anele Ngqandu 16-Sep-10 7:49am    
Hi Tom, well I tried both your methods but nothing seems to change. And my data types seems to be fine in my database. Now am lost.

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