Syntax Error in INSERT INTO statement

[Jetean]

 private void button4_Click(object sender, EventArgs e)
        {
          
            ConnectionStringSettings met = ConfigurationManager.ConnectionStrings["Met"];
            OleDbConnection connection = new OleDbConnection(met.ConnectionString);

            OleDbCommand cmd = connection.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Select * from DataTable";
            OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd.CommandText, connection.ConnectionString);
            
           
            OleDbCommandBuilder kk = new OleDbCommandBuilder(dAdapter);
            kk.GetInsertCommand();
            OleDbCommandBuilder JJ = new OleDbCommandBuilder(dAdapter);
            JJ.GetUpdateCommand();
           
            DataSet datSet = new DataSet();
            dAdapter.Fill(datSet,"DataTable");
            
            textBox3.Text = datSet.Tables[0].TableName.ToString();
            textBox2.Text=datSet.Tables[0].Rows.Count.ToString();
           
            DataRow dr;
            dr = datSet.Tables["DataTable"].NewRow();
            dr["No"] = 6;
            datSet.Tables["DataTable"].Rows.Add(dr);
            dAdapter.Update(datSet, "DataTable");
            
        }

[IMG]file:///C:/DOCUME%7E1/ONGSUK%7E1/LOCALS%7E1/Temp/moz-screenshot.jpg[/IMG]

Is the Commandbuilder getting me this Error?

Thanks

[Plater]

I see you running these lines:
kk.GetInsertCom mand();
JJ.GetUpdateCom mand();

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

[Jetean]

I see you running these lines:
kk.GetInsertCom mand();
JJ.GetUpdateCom mand();

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

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

[Plater]

kk.GetInsertCom mand(); has a return object, it doesn't modify your objects directly, you have to use the return object yourself.

For example:
[code=c#]
OleDbCommandBui lder kk = new OleDbCommandBui lder(dAdapter);
OleDbCommand updatecommand = kk.GetUpdateCom mand();
OleDbCommand insertcommand = kk.GetInsertCom mand();

dAdapter.Update Command = updatecommand;
dAdapter.Insert Command = insertcommand;
[/code]

[Jetean]

Hi Platter:
I "found out" where is my error came from.
If I changed "ConnectionStri ngSettings met = ConfigurationMa nager.Connectio nStrings["Met"];"

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

[LIST=1][*]             OleDbCommandBuilder kk = new OleDbCommandBuilder(dAdapter);[*]          //   kk.GetInsertCommand();[*]           // OleDbCommandBuilder JJ = new OleDbCommandBuilder(dAdapter);[*]           //  JJ.GetUpdateCommand();[/LIST]

by omitting line 2,3 and 4. I'm OK.

But I just don't Quite Understand what is wrong with this:

           ConnectionStringSettings met = ConfigurationManager.ConnectionStrings["Met"];

I found this at app.configurati on

 <connectionStrings>
        <add name="Met" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\MET.mdb"
            providerName="System.Data.OleDb" />

I'm able to use this with Dataview but why I'm not able to insert and updates the database?
Thanks Again

[Plater]

That solved your connection issue yes.
But where are you providing the update and insert SQL commands?

[Jetean]

            OleDbDataAdapter da = new OleDbDataAdapter("Select * From DataTable", connection);

            
            OleDbCommandBuilder bldr = new OleDbCommandBuilder(da);

            da.Fill(mDataset, "DataTable");
           
            
            dr = mDataset.Tables["DataTable"].NewRow();
            dr["No_1"] = "55";
            Console.WriteLine(dr.RowState.ToString());
            mDataset.Tables["DataTable"].Rows.Add(dr);

            Console.WriteLine(dr.RowState.ToString());
            
            da.Update(mDataset, "DataTable");


            connection.Close();

The above code.

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

Thanks

[Plater]

Hmm, I thought you had your connection string issue taken care of.

What are the ACTUAL errors your are getting here?

[Jetean]

ACTUAL errors: As the Title

Syntax Error In Insert into Statement.


Funny?

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

I try using this

Properties.Sett ings.Default.Me tConnectionStri ng;

Well It work also.

So, what is my problem?

Funny....?

[Jetean]

Ok. ConnectionStrin gSettings met = ConfigurationMa nager.Connectio nStrings["Met"];
I'm not able to insert and update the Database. Thinking if the OLEDBCommandBui lder is having problem. I rewrite my connectionStrin g, Well I had my problem solved, which raised another question mark for me, that is Why is ConnectionStrin gSettings not working?

[Plater]

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 OleDBCommandBui lder, 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.

[Curtis Rutland]

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:

 
 
            OleDbDataAdapter da = new OleDbDataAdapter("Select * From DataTable", connection);
 
 
            OleDbCommandBuilder bldr = new OleDbCommandBuilder(da);

            /////////////what I added
            da.InsertCommand = bldr.GetInsertCommand();
            da.UpdateCommand = bldr.GetUpdateCommand();
            da.DeleteCommand = bldr.GetDeleteCommand();
            /////////////
            da.Fill(mDataset, "DataTable");
 
 
            dr = mDataset.Tables["DataTable"].NewRow();
            dr["No_1"] = "55";
            Console.WriteLine(dr.RowState.ToString());
            mDataset.Tables["DataTable"].Rows.Add(dr);
 
            Console.WriteLine(dr.RowState.ToString());
 
            da.Update(mDataset, "DataTable");
 
 
            connection.Close();

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.

[Jetean]

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

1. da.InsertComman d = bldr.GetInsertC ommand();
2. da.UpdateComman d = bldr.GetUpdateC ommand();
3. da.DeleteComman d = bldr.GetDeleteC ommand();

I google the ConnectionStrin gSettings,

[COLOR=#000000]To set the DataDirectory property, call the AppDomain.SetDa ta 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

[shamalxp]

Same Problem with me, Simple Scenario

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.

Private Sub btnCrtUsrCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCrtUsrCreate.Click
        Dim con As New OleDb.OleDbConnection
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String

        If txtCrtUsrName.Text = "" Then
            MsgBox("Please Enter a User Name")
        ElseIf txtCrtUsrPassword.Text = "" Then
            MsgBox("Please Enter a Password")
        ElseIf txtCrtUsrPassword.Text <> txtCrtUsrRetype.Text Then
            MsgBox("The Passwords do not match")
        Else
            con.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = tvscheduler.mdb"
            con.Open()

            sql = "SELECT * FROM tblSchedule"
            da = New OleDb.OleDbDataAdapter(Sql, con)

            da.Fill(ds, "table_schedule")
            con.Close()

            txtTest.Text = ds.Tables("table_schedule").Columns.Count
            Dim cb As New OleDb.OleDbCommandBuilder(da)
            Dim dsNewRow As DataRow

            dsNewRow = ds.Tables("table_schedule").NewRow()
            dsNewRow.Item(1) = txtCrtUsrName.Text
            dsNewRow.Item(2) = txtCrtUsrPassword.Text

            ds.Tables("table_schedule").Rows.Add(dsNewRow)
            da.Update(ds, "table_schedule")

            MsgBox("The User Account " + txtCrtUsrName.Text + " is Successfully Created")
        End If
    End Sub

[Plater]

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(colum ns)