SQL Statement Problem

Discussion in 'OT Technology' started by Spiral Out, Mar 2, 2004.

  1. Spiral Out

    Spiral Out Active Member

    Joined:
    Mar 30, 2002
    Messages:
    12,287
    Likes Received:
    0
    Location:
    Florida
    This is the SQL statement:

    INSERT INTO Permissions (RatingCode, CustomerID, ContactID, YesNo) VALUES ('G' , 1 , '1Z' , 'True')

    This is my code:
    Code:
    sSQLG = "INSERT INTO Permissions (RatingCode, CustomerID, ContactID, YesNo) " +
     "VALUES ('" + chkG.Text + "' , " + lCustNumber +
    " , '" + txtAdditionalID.Text + "' , '" + chkG.Checked + "')";
     
     OleDbConnection dbConnection = null;
    OleDbCommand objCmd = null;
     
    dbConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; User Id=; Password=; Data Source=mrsystem.mdb");
     dbConnection.Open();
     
    objCmd = new OleDbCommand(sSQLG, dbConnection);
    objCmd.ExecuteNonQuery();
     
    dbConnection.Close();
    I keep an error that says something is wrong with the INSERT INTO syntax but I can't find anything...
     
  2. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    make sure all your values that you're inserting in that string there, have values in them. What i like to do is output the sql statement once it has been put together, and then copy/paste it into the database and see if it works by itself. In this case, you would want to put it in a query in Access
     
  3. Spiral Out

    Spiral Out Active Member

    Joined:
    Mar 30, 2002
    Messages:
    12,287
    Likes Received:
    0
    Location:
    Florida
    Everything is there in that first SQL statement so no values are empty. This is the first time I have run into this error...
     
  4. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    what are your field types? might try not putting the "true" value in quotes, or i think access likes to use the value (-1) as true and 0 as false
     
  5. choler

    choler New Member

    Joined:
    May 21, 2002
    Messages:
    183
    Likes Received:
    0
    1. if the YesNo column is defined as a "Yes/No" ... then change the name of the columns
    2. insert should be
    Code:
    INSERT INTO Permissions (RatingCode, CustomerID, ContactID, YesNo) VALUES ('G' , 1 , '1Z' , true) 
    and not... 'True')
    
    so get rid of " ' "'s
    hope that helps
     
  6. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Yeah, or assign -1, not 'True'
     
  7. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Or I could do exactly what I tell every other asshole who does that and read all of the replies before responding.
     
  8. Spiral Out

    Spiral Out Active Member

    Joined:
    Mar 30, 2002
    Messages:
    12,287
    Likes Received:
    0
    Location:
    Florida
    chkG.Checked

    This is what will calculate to true or false. Should I have an if statement to save true or -1 in a value then pass that in?
     
  9. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    What type of variabe is Checked? Boolean or string? I would change it to boolean (yes/no) and get rid of the quotes in your sql string - that should do it. You can't assign a string value ('true') to a boolean type field - that's your problem.
     
  10. Spiral Out

    Spiral Out Active Member

    Joined:
    Mar 30, 2002
    Messages:
    12,287
    Likes Received:
    0
    Location:
    Florida
    chkG.Checked comes back either True or False. It is being inserted into an Access Database with a data type of yes/no. I tried puttin true, 'true', and -1 and I still get the syntax error.

    This is what comes up:

    System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
    at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
    at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
    at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
    at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
    at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
    at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
    at MRSystem.frmCustomer.addAddNewTest() in g:\school files\systems analysis and solutions architectures\project files\mrsystem\mrsystem\frmcustomer.cs:line 991
    at MRSystem.frmCustomer.cmdAddAddNew_Click(Object sender, EventArgs e) in g:\school files\systems analysis and solutions architectures\project files\mrsystem\mrsystem\frmcustomer.cs:line 912
    at System.Windows.Forms.Control.OnClick(EventArgs e)
    at System.Windows.Forms.Button.OnClick(EventArgs e)
    at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
    at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
    at System.Windows.Forms.Control.WndProc(Message& m)
    at System.Windows.Forms.ButtonBase.WndProc(Message& m)
    at System.Windows.Forms.Button.WndProc(Message& m)
    at System.Windows.Forms.ControlNativeWindow.OnMessage(Message& m)
    at System.Windows.Forms.ControlNativeWindow.WndProc(Message& m)
    at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
     
  11. Spiral Out

    Spiral Out Active Member

    Joined:
    Mar 30, 2002
    Messages:
    12,287
    Likes Received:
    0
    Location:
    Florida
    :bowdown::bowdown:Thank You! I changed the name to Granted and it works now...
     
  12. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Definately - stay away from reserved words
     
  13. Spiral Out

    Spiral Out Active Member

    Joined:
    Mar 30, 2002
    Messages:
    12,287
    Likes Received:
    0
    Location:
    Florida
    I didn't know Access had reserved words...:hs:
     
  14. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Oi does it ever. Because it used JetSQL, there's also VB words that it can get confused with. I can't tell you the number of issues I've had because I got lazy and left a field name date, or even month
     

Share This Page