Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

INSERT INTO tablename gives error

Posted on 2011-03-05
7
Medium Priority
?
375 Views
Last Modified: 2012-05-11
I have two out of five of my tables in a MS Access database not allowing an INSERT INTO statement.  It gives me the error:

 The Insert Into statement contains the following unknown field name:  CurrMonthName.

My SQL statement goes something like this:
"Insert Into DataTable (CurrMonthName,CurrYear) Values (@CurrMonthName,@CurrYear);

I've tried deleting the table and re-creating it.  I removed the CurrMonthName field in the statement so it's just asking for CurrYear, and I still get the same error, except of course it says the unknown field name = CurrYear.  I've also tried inserting the data directly from an Append Query in MS Access, and that won't work either.

Any ideas of what I'm doing wrong?
0
Comment
Question by:Agent909
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 75
ID: 35044540
Here is the general syntax:

INSERT INTO Table2 ( FIELD1, FIELD2 )
SELECT Table1.FIELD1, Table1.FIELD2
FROM Table1;

Not sure about you @ symbols ...

mx
0
 
LVL 1

Author Comment

by:Agent909
ID: 35044616
This is my code, and it works for my other forms:
I will try the code you posted, and get back to you.  Thank you!


str = "INSERT INTO DataTraffic " & _
                    "(CurrMonthName,CurrYear,StoreName,Traffic) " & _
                    "VALUES (" & _
                    "@CurrMonthName,@CurrYear,@StoreName,@Traffic)"
                    myCommand = New OleDbCommand(str, myConnection)
                    myCommand.Parameters.AddWithValue("@CurrMonthName", obj3.CurrMonthName)
                    myCommand.Parameters.AddWithValue("@CurrYear", obj3.CurrYear)
                    myCommand.Parameters.AddWithValue("@StoreName", obj3.StoreName)
                    myCommand.Parameters.AddWithValue("@Traffic", obj3.Traffic)

Open in new window

0
 
LVL 75
ID: 35044622
What is this:

myCommand.Parameters.AddWithValue

?

Are you sure this in Access SQL ?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:Agent909
ID: 35044665
yes, I'm using it in Access SQL. The OleDbCommand has .Parameters and .AddWithValue.
0
 
LVL 75
ID: 35044675
Sorry ... not really familiar with OleDb ...

Someone will be along ...

mx
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 35044924
The syntax you're working with is more SQL Server or Oracle based (think Stored Procedures). Access doesn't work that way.

To run your Insert statement, just issue it directly:

sSQL = "Insert Into DataTable (CurrMonthName,CurrYear) Values ('" & obj3.CurrMonthName & "','" & obj3.CurrYear & "')"
YourConnection.Execute sSQL

If CurrYear is Numeric, you don't need the single quotes around those values.

0
 
LVL 1

Author Closing Comment

by:Agent909
ID: 35044941
Thank you!
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question