[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

OleDB Insert not working

Posted on 2004-10-18
5
Medium Priority
?
227 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
[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
5 Comments
 
LVL 12

Accepted Solution

by:
vascov earned 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
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.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

656 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