By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,872 Members | 2,333 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,872 IT Pros & Developers. It's quick & easy.

Syntax Error in INSERT INTO statement

P: 33
Expand|Select|Wrap|Line Numbers
  1.  private void button4_Click(object sender, EventArgs e)
  2.         {
  3.  
  4.             ConnectionStringSettings met = ConfigurationManager.ConnectionStrings["Met"];
  5.             OleDbConnection connection = new OleDbConnection(met.ConnectionString);
  6.  
  7.             OleDbCommand cmd = connection.CreateCommand();
  8.             cmd.CommandType = CommandType.Text;
  9.             cmd.CommandText = "Select * from DataTable";
  10.             OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd.CommandText, connection.ConnectionString);
  11.  
  12.  
  13.             OleDbCommandBuilder kk = new OleDbCommandBuilder(dAdapter);
  14.             kk.GetInsertCommand();
  15.             OleDbCommandBuilder JJ = new OleDbCommandBuilder(dAdapter);
  16.             JJ.GetUpdateCommand();
  17.  
  18.             DataSet datSet = new DataSet();
  19.             dAdapter.Fill(datSet,"DataTable");
  20.  
  21.             textBox3.Text = datSet.Tables[0].TableName.ToString();
  22.             textBox2.Text=datSet.Tables[0].Rows.Count.ToString();
  23.  
  24.             DataRow dr;
  25.             dr = datSet.Tables["DataTable"].NewRow();
  26.             dr["No"] = 6;
  27.             datSet.Tables["DataTable"].Rows.Add(dr);
  28.             dAdapter.Update(datSet, "DataTable");
  29.  
  30.         }
[IMG]file:///C:/DOCUME%7E1/ONGSUK%7E1/LOCALS%7E1/Temp/moz-screenshot.jpg[/IMG]

Is the Commandbuilder getting me this Error?

Thanks
Jan 23 '09 #1
Share this Question
Share on Google+
19 Replies


Plater
Expert 5K+
P: 7,872
I see you running these lines:
kk.GetInsertCommand();
JJ.GetUpdateCommand();

But you never do anything with the values. Those functions return an OleDBCommand that you then assign to the UpdateCommand and InsertCommand properties. You also shouldn't need to create 2 command builders, just one should suffice
Jan 23 '09 #2

P: 33
@Plater
What do you meant by "But you never do anything with the values. Those functions return an OleDBCommand that you then assign to the UpdateCommand and InsertCommand properties."

I'm new to this, I google around the web, I could not get any satisfactory answer to my headache...

Can you provide me some code or example on what you said?

Thanks
Jan 24 '09 #3

Plater
Expert 5K+
P: 7,872
kk.GetInsertCommand(); has a return object, it doesn't modify your objects directly, you have to use the return object yourself.

For example:
Expand|Select|Wrap|Line Numbers
  1. OleDbCommandBuilder kk = new OleDbCommandBuilder(dAdapter); 
  2. OleDbCommand updatecommand = kk.GetUpdateCommand(); 
  3. OleDbCommand insertcommand =  kk.GetInsertCommand();
  4.  
  5. dAdapter.UpdateCommand = updatecommand;
  6. dAdapter.InsertCommand = insertcommand;
  7.  
Jan 26 '09 #4

P: 33
Hi Platter:
I "found out" where is my error came from.
If I changed "ConnectionStringSettings met = ConfigurationManager.ConnectionStrings["Met"];"

to this: string connectionString= path of the database, instead of the above. The Syntax error just disappeared

Expand|Select|Wrap|Line Numbers
  1.              OleDbCommandBuilder kk = new OleDbCommandBuilder(dAdapter);
  2.           //   kk.GetInsertCommand();
  3.            // OleDbCommandBuilder JJ = new OleDbCommandBuilder(dAdapter);
  4.            //  JJ.GetUpdateCommand();
  •  
  • by omitting line 2,3 and 4. I'm OK.

    But I just don't Quite Understand what is wrong with this:
    Expand|Select|Wrap|Line Numbers
    1.            ConnectionStringSettings met = ConfigurationManager.ConnectionStrings["Met"];
    2.  
    I found this at app.configuration

    Expand|Select|Wrap|Line Numbers
    1.  <connectionStrings>
    2.         <add name="Met" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\MET.mdb"
    3.             providerName="System.Data.OleDb" />
    4.  
    I'm able to use this with Dataview but why I'm not able to insert and updates the database?
    Thanks Again
    Jan 28 '09 #5

    Plater
    Expert 5K+
    P: 7,872
    That solved your connection issue yes.
    But where are you providing the update and insert SQL commands?
    Jan 28 '09 #6

    P: 33
    Expand|Select|Wrap|Line Numbers
    1.  
    2.  
    3.             OleDbDataAdapter da = new OleDbDataAdapter("Select * From DataTable", connection);
    4.  
    5.  
    6.             OleDbCommandBuilder bldr = new OleDbCommandBuilder(da);
    7.  
    8.             da.Fill(mDataset, "DataTable");
    9.  
    10.  
    11.             dr = mDataset.Tables["DataTable"].NewRow();
    12.             dr["No_1"] = "55";
    13.             Console.WriteLine(dr.RowState.ToString());
    14.             mDataset.Tables["DataTable"].Rows.Add(dr);
    15.  
    16.             Console.WriteLine(dr.RowState.ToString());
    17.  
    18.             da.Update(mDataset, "DataTable");
    19.  
    20.  
    21.             connection.Close();
    22.  
    23.  

    The above code.

    But I want to know why the ConnectionSettingStrings problem, what wrong with this?

    Thanks
    Jan 28 '09 #7

    Plater
    Expert 5K+
    P: 7,872
    Hmm, I thought you had your connection string issue taken care of.

    What are the ACTUAL errors your are getting here?
    Jan 28 '09 #8

    P: 33
    ACTUAL errors: As the Title

    Syntax Error In Insert into Statement
    .


    Funny?

    The app.Configuration was generated when I created it. Am I missing something?

    I try using this

    Properties.Settings.Default.MetConnectionString;

    Well It work also.

    So, what is my problem?

    Funny....?
    Jan 28 '09 #9

    P: 33
    Ok. ConnectionStringSettings met = ConfigurationManager.ConnectionStrings["Met"];
    I'm not able to insert and update the Database. Thinking if the OLEDBCommandBuilder is having problem. I rewrite my connectionString, Well I had my problem solved, which raised another question mark for me, that is Why is ConnectionStringSettings not working?
    Jan 28 '09 #10

    Plater
    Expert 5K+
    P: 7,872
    If you are retreiving data from the database, then your connection string is fine.

    So you have no errors/exceptions occuring?

    I still think you need to do more with the OleDBCommandBuilder, but MSDN's examples don't show it.

    Every other time this question has come up, its been because people forgot to assign anything to the UpdateCommand and InsertCommand properties.
    Jan 28 '09 #11

    Curtis Rutland
    Expert 2.5K+
    P: 3,256
    The reason you are having syntax errors is because the statement is blank...it doesn't exist.

    The CommandBuilder object doesn't make any modifications at all by itself. It's just a tool to help you generate the command. Look at the lines I added to your code:
    Expand|Select|Wrap|Line Numbers
    1.  
    2.  
    3.             OleDbDataAdapter da = new OleDbDataAdapter("Select * From DataTable", connection);
    4.  
    5.  
    6.             OleDbCommandBuilder bldr = new OleDbCommandBuilder(da);
    7.  
    8.             /////////////what I added
    9.             da.InsertCommand = bldr.GetInsertCommand();
    10.             da.UpdateCommand = bldr.GetUpdateCommand();
    11.             da.DeleteCommand = bldr.GetDeleteCommand();
    12.             /////////////
    13.             da.Fill(mDataset, "DataTable");
    14.  
    15.  
    16.             dr = mDataset.Tables["DataTable"].NewRow();
    17.             dr["No_1"] = "55";
    18.             Console.WriteLine(dr.RowState.ToString());
    19.             mDataset.Tables["DataTable"].Rows.Add(dr);
    20.  
    21.             Console.WriteLine(dr.RowState.ToString());
    22.  
    23.             da.Update(mDataset, "DataTable");
    24.  
    25.  
    26.             connection.Close();
    27.  
    That should clear things up.

    If you are having a connection string problem, you wouldn't be able to retrieve any data from your database, you'd never be able to connect to it. The syntax error comes after the connection is already successful.
    Jan 28 '09 #12

    P: 33
    Hi InsertAlias:
    In fact I had these 3 line of Code

    1. da.InsertCommand = bldr.GetInsertCommand();
    2. da.UpdateCommand = bldr.GetUpdateCommand();
    3. da.DeleteCommand = bldr.GetDeleteCommand();
    I google the ConnectionStringSettings,

    [COLOR=#000000]To set the DataDirectory property, call the AppDomain.SetData method. If you do not set the DataDirectory property, the following default rules will be applied to access the database folder: [/COLOR] • For applications that are put in a folder on the user's computer, the database folder uses the application folder. • For applications that are running under ClickOnce, the database folder uses the specific data folder that is created.
    I'm still try to understand this
    Jan 29 '09 #13

    P: 3
    Hi, I have the same problem. And my application is a quite simple log in box. I'm using Microsoft Visual Basic 2008 express edition.
    The 'syntax error in INSERT INTO statement' apears at da.Update(ds, "table_schedule") line. Please help me in this issue. Thank you in advance.

    Expand|Select|Wrap|Line Numbers
    1. Private Sub btnCrtUsrCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCrtUsrCreate.Click
    2.         Dim con As New OleDb.OleDbConnection
    3.         Dim ds As New DataSet
    4.         Dim da As OleDb.OleDbDataAdapter
    5.         Dim sql As String
    6.  
    7.         If txtCrtUsrName.Text = "" Then
    8.             MsgBox("Please Enter a User Name")
    9.         ElseIf txtCrtUsrPassword.Text = "" Then
    10.             MsgBox("Please Enter a Password")
    11.         ElseIf txtCrtUsrPassword.Text <> txtCrtUsrRetype.Text Then
    12.             MsgBox("The Passwords do not match")
    13.         Else
    14.             con.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = tvscheduler.mdb"
    15.             con.Open()
    16.  
    17.             sql = "SELECT * FROM tblSchedule"
    18.             da = New OleDb.OleDbDataAdapter(Sql, con)
    19.  
    20.             da.Fill(ds, "table_schedule")
    21.             con.Close()
    22.  
    23.             txtTest.Text = ds.Tables("table_schedule").Columns.Count
    24.             Dim cb As New OleDb.OleDbCommandBuilder(da)
    25.             Dim dsNewRow As DataRow
    26.  
    27.             dsNewRow = ds.Tables("table_schedule").NewRow()
    28.             dsNewRow.Item(1) = txtCrtUsrName.Text
    29.             dsNewRow.Item(2) = txtCrtUsrPassword.Text
    30.  
    31.             ds.Tables("table_schedule").Rows.Add(dsNewRow)
    32.             da.Update(ds, "table_schedule")
    33.  
    34.             MsgBox("The User Account " + txtCrtUsrName.Text + " is Successfully Created")
    35.         End If
    36.     End Sub
    Feb 19 '09 #14

    Plater
    Expert 5K+
    P: 7,872
    Pretty sure you can't use Item, and that you need to use the column names, otherwise the data never seemed to make it into the correct locations(columns)
    Feb 19 '09 #15

    P: 3
    Hi Plater,
    I'm happy about the quick response. I changed the item(1) into item("username") and item(2) into item("password"). which are exactly correct as the database.
    still no chance.
    Feb 19 '09 #16

    Plater
    Expert 5K+
    P: 7,872
    Well like the previous person before you, I don't see where you assign the Update and Insert commands to your DataAdapter.
    Feb 19 '09 #17

    P: 33
    @Plater
    Actually You don't need to assign the update or insert commnad. I read it some where in MSDN. I can't recall where.

    My code work (C#), my problem occured with the connection string and the actual database (debug or release).

    Try this: Create your DataAdapter and Command Builder earlier in your code before you fill the Dataset. Go and Check where is the Database located. You might look at the wrong database (depending on the connection string ).
    Feb 22 '09 #18

    P: 3
    Guys, I found a solution at last. Thanks for all the replies. and specially Plater.

    Amazingly, the fault in my program was to include a column name 'password'. When I changed it to 'pass' the whole program is working. I think the 'password' clashes with another attribute in the INSERT INTO command.

    thanks to crow_w in http://www.daniweb.com/forums/thread173074.html
    Feb 24 '09 #19

    P: 1
    Hi guys !

    Like shamlxp said, I had a same problem before, then I google, I found (but I forgot where is it) that they said may be datatype is not compatible.
    In my issue any other (except field password) values were OK, just field password was not OK. then I tried to another name, and ... surprised, it was work good !

    That's my experienced I want to share with you guys !
    Thanks for reading !

    Update : this is link I found : http://stackoverflow.com/questions/1...o-an-access-db (you can check Answered mark post)
    Apr 30 '13 #20

    Post your reply

    Sign in to post your reply or Sign up for a free account.