We're a community of 1.1M IT Pros here for help, advice, solutions, professional growth and fun. Join us!
1,080,571 Members — Technology Publication meets Social Media
Username:
Password:
Lost login information?
Start New Discussion Reply to this Discussion

Insert INTO Query Error

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

4
Contributors
8
Replies
5 Hours
Discussion Span
1 Year Ago
Last Updated
9
Views
Question
Answered
Ziggy713
Newbie Poster
9 posts since Jan 2012
Reputation Points: 10
Solved Threads: 0
Skill Endorsements: 0

Whay using AND?

Pgmer
Practically a Posting Shark
881 posts since Apr 2008
Reputation Points: 60
Solved Threads: 158
Skill Endorsements: 1

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 & "')"
Jx_Man
Senior Poster
3,553 posts since Nov 2007
Reputation Points: 1,500
Solved Threads: 531
Skill Endorsements: 65

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

Ziggy713
Newbie Poster
9 posts since Jan 2012
Reputation Points: 10
Solved Threads: 0
Skill Endorsements: 0

Syntax for insert into is as below

INSERT INTO TABLENAME(
					COLUMN1,
					COLUMN2,
					COLUMN3,
					)
			 VALUES(VALUE1,
					VALUE12,
					VALUE13,)
Pgmer
Practically a Posting Shark
881 posts since Apr 2008
Reputation Points: 60
Solved Threads: 158
Skill Endorsements: 1

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

Ziggy713
Newbie Poster
9 posts since Jan 2012
Reputation Points: 10
Solved Threads: 0
Skill Endorsements: 0

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

Pgmer
Practically a Posting Shark
881 posts since Apr 2008
Reputation Points: 60
Solved Threads: 158
Skill Endorsements: 1

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

Jx_Man
Senior Poster
3,553 posts since Nov 2007
Reputation Points: 1,500
Solved Threads: 531
Skill Endorsements: 65

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.

Reverend Jim
Illigitimae non carborundum
Moderator
3,743 posts since Aug 2010
Reputation Points: 585
Solved Threads: 470
Skill Endorsements: 33
Question Answered as of 1 Year Ago by Pgmer, Jx_Man and Reverend Jim

This question has already been solved: Start a new discussion instead

Post: Markdown Syntax: Formatting Help
 
You
View similar articles that have also been tagged:
 
© 2013 DaniWeb® LLC
Page generated in 0.0837 seconds using 2.74MB