Solved

OleDB Insert not working

Posted on 2004-10-18
5
199 Views
Last Modified: 2012-05-05
When I execute this block of could I get the following error:

Syntax error in INSERT INTO statement. Can not add record.

Can anybody see the problem?

My temp table contains many fields, but I want to insert into these specific ones for this record

Here is what I have:

string myInsertCmdStr = "INSERT INTO temp " +
                  "(herd_unit, study_no, year_est, date, belt, species_code, distance, recType) " +
                  "VALUES (@herd_unit, @study_no, @year_est, @date, @belt, @species_code, @distance, @recType)";
                                          
myConnection = new OleDbConnection(myConnectionString);
myConnection.Open();

foreach( RecordType7 curRow in allRec7)
{
     OleDbCommand myInsertCmd = new OleDbCommand(myInsertCmdStr,myConnection);

    myInsertCmd.Parameters.Add(new OleDbParameter("@herd_unit",OleDbType.VarChar, 3));
    myInsertCmd.Parameters["@herd_unit"].Value = curRow.herd_unit;
    myInsertCmd.Parameters.Add(new OleDbParameter("@study_no",OleDbType.VarChar, 2));
    myInsertCmd.Parameters["@study_no"].Value = curRow.study_no;
    myInsertCmd.Parameters.Add(new OleDbParameter("@year_est",OleDbType.VarChar, 2));
    myInsertCmd.Parameters["@year_est"].Value = curRow.year_established;
    myInsertCmd.Parameters.Add(new OleDbParameter("@date",OleDbType.VarChar, 10));
    myInsertCmd.Parameters["@date"].Value = curRow.date;
    myInsertCmd.Parameters.Add(new OleDbParameter("@belt",OleDbType.VarChar, 1));
    myInsertCmd.Parameters["@belt"].Value = curRow.belt;
    myInsertCmd.Parameters.Add(new OleDbParameter("@species_code",OleDbType.VarChar, 5));
    myInsertCmd.Parameters["@species_code"].Value = curRow.species;
    myInsertCmd.Parameters.Add(new OleDbParameter("@distance",OleDbType.VarChar, 3));
    myInsertCmd.Parameters["@distance"].Value = curRow.distance;
    myInsertCmd.Parameters.Add(new OleDbParameter("@recType",OleDbType.VarChar, 1));
    myInsertCmd.Parameters["@recType"].Value = 7;
                   
    try
    {
          myInsertCmd.ExecuteNonQuery();
    }
    catch(OleDbException e)
    {
            // Let the user know what went wrong.
            MessageBox.Show (e.Message + "Can not add record.", "Error",
            MessageBoxButtons.OK, MessageBoxIcon.Error);
              break;
    }
                                    
}

myConnection.Close();
0
Comment
Question by:Koderiter
  • 3
5 Comments
 
LVL 12

Accepted Solution

by:
vascov earned 500 total points
ID: 12344635
If this is executing against SQL Serv, date is a reserved word

Try it like this:
string myInsertCmdStr = "INSERT INTO temp " +
               "(herd_unit, study_no, year_est, [date], belt, species_code, distance, recType) " +
               "VALUES (@herd_unit, @study_no, @year_est, @date, @belt, @species_code, @distance, @recType)";

hth

Vasco
0
 
LVL 12

Expert Comment

by:vascov
ID: 12344648
btw, temp is also reserved.

try it like:
string myInsertCmdStr = "INSERT INTO [temp] " +
               "(herd_unit, study_no, year_est, [date], belt, species_code, distance, recType) " +
               "VALUES (@herd_unit, @study_no, @year_est, @date, @belt, @species_code, @distance, @recType)";

hth

Vasco

PS: without the table itself we have to run it virtually :)
0
 
LVL 12

Expert Comment

by:vascov
ID: 12344655
Hmm, actually i take it back :) temp is not reserved.

PS: i need some coffee
0
 
LVL 4

Expert Comment

by:Jigit
ID: 12345045
Koderiter, I can not point on error for sure, but can give you a few directions to check:
1. Check types, for example what is the type of "date" in the database? Is it varchar? What is the type of curRow.date? Is it string? BTW, maybe this should help:
myInsertCmd.Parameters["@recType"].Value = "7";

2. Use some Profiler tool to see what is the final query that is executed against your DB server. Maybe there are some quotes missing or invalid types.

HTH,
Jigit
0
 

Author Comment

by:Koderiter
ID: 12362170
Thanks for your input. That's exactly what the problem was. " date " was the little bugger causing all the problems.

Thanks, agian
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

746 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now