1,105,242 Community Members

Insert INTO Query Error

Member Avatar
Ziggy713
Newbie Poster
9 posts since Jan 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
Pgmer
Practically a Posting Shark
881 posts since Apr 2008
Reputation Points: 50 [?]
Q&As Helped to Solve: 158 [?]
Skill Endorsements: 3 [?]
Featured
 
0
 

Whay using AND?

Member Avatar
Jx_Man
Senior Poster
3,543 posts since Nov 2007
Reputation Points: 987 [?]
Q&As Helped to Solve: 542 [?]
Skill Endorsements: 74 [?]
Featured
 
0
 

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 & "')"
Member Avatar
Ziggy713
Newbie Poster
9 posts since Jan 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
Pgmer
Practically a Posting Shark
881 posts since Apr 2008
Reputation Points: 50 [?]
Q&As Helped to Solve: 158 [?]
Skill Endorsements: 3 [?]
Featured
 
0
 

Syntax for insert into is as below

INSERT INTO TABLENAME(
					COLUMN1,
					COLUMN2,
					COLUMN3,
					)
			 VALUES(VALUE1,
					VALUE12,
					VALUE13,)
Member Avatar
Ziggy713
Newbie Poster
9 posts since Jan 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
Pgmer
Practically a Posting Shark
881 posts since Apr 2008
Reputation Points: 50 [?]
Q&As Helped to Solve: 158 [?]
Skill Endorsements: 3 [?]
Featured
 
0
 

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

Member Avatar
Jx_Man
Senior Poster
3,543 posts since Nov 2007
Reputation Points: 987 [?]
Q&As Helped to Solve: 542 [?]
Skill Endorsements: 74 [?]
Featured
 
0
 

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

Member Avatar
Reverend Jim
Noli mentula
5,409 posts since Aug 2010
Reputation Points: 746 [?]
Q&As Helped to Solve: 648 [?]
Skill Endorsements: 51 [?]
Moderator
Featured
 
0
 

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.

Question Answered as of 2 Years Ago by Pgmer, Jx_Man and Reverend Jim
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: