Insert INTO Query Error

[Ziggy713]

Hi Everyone,

I am currently Building a Stock Control System for a School Project and Using access 2007 with Visual Studio 2010 to build the VB.
I am trying to use an INSERT INTO SQL Command to insert new stock items into my Access DB.
I am getting an error thrown at me when attempting to execute the NonQuery().

This is my code

Dim con as new OleDBconnection (Connection String)
        Dim cmd As New OleDbCommand
        Query = "INSERT INTO PROD_DB_Complete_Board (ID, [Board Size], Laminate, [Stock Level]) Values('" & TextBox4.Text & "'" & "AND" & "'" & TextBox2.Text & "'" & "And" & "'" & TextBox3.Text & "'" & "And" & "'" & TextBox1.Text & "')"
        cmd = New OleDbCommand(Query, con)
        MsgBox(Query)
        cmd.ExecuteNonQuery()
        cmd.Dispose()
        con.Close()

I keep getting thrown the error, Query Values and Destination fields are not the Same.
What does this mean and how do I solve these Issues?

Many Thanks

Ziggy

[Pgmer]

Whay using AND?

[Jx_Man]

Don't Use AND

Query = "INSERT INTO PROD_DB_Complete_Board (ID, [Board Size], Laminate, [Stock Level]) Values('" & TextBox4.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox1.Text & "')"

[Ziggy713]

Ah, Had missed those, Just looked up the Syntax of the Insert INTO Command again, hadn't realised that they weren't needed.....

Silly Mistake from me!

Many Thanks

Ziggy

[Pgmer]

Syntax for insert into is as below

INSERT INTO TABLENAME(
					COLUMN1,
					COLUMN2,
					COLUMN3,
					)
			 VALUES(VALUE1,
					VALUE12,
					VALUE13,)

[Ziggy713]

After Removing the excess "AND"'s I am still getting the Same error as before,

here is the Modified Code;

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim cmd As New OleDbCommand
        Query = "INSERT INTO PROD_DB_Complete_Board (ID, [Board Size], Laminate, [Stock Level]) Values('" & TextBox4.Text & ",'" & "'" & TextBox2.Text & ",'" & "'" & TextBox3.Text & ",'" & "'" & TextBox1.Text & "')"
        cmd = New OleDbCommand(Query, con)
        MsgBox(Query)
        cmd.ExecuteNonQuery()
        cmd.Dispose()
        con.Close()

Is There any other Changes that I need to Make?

Many Thanks

Ziggy

[Pgmer]

What is the error message? Why cant u take the Query before running cmd.ExecuteNonQuery() and try to run on SQL server?

[Jx_Man]

You wrong when put commas n single quotes. did u see my post?

[Reverend Jim]

Use

Query = "INSERT INTO PROD_DB_Complete_Board _
    (ID, [Board Size], Laminate, [Stock Level]) _
    Values('" & TextBox4.Text & "','" _
        & "'" & TextBox2.Text & "','" _
        & "'" & TextBox3.Text & "','" _
        & "'" & TextBox1.Text & "')"

It's easier to see the problem when the fields line up. I don't knkow if you have the option, but I strongly suggest you modify the database so the field names do not contain spaces. You should remove them or replace them with underscores.