• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

OleDB Insert not working

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
Koderiter
Asked:
Koderiter
  • 3
1 Solution
 
vascovCommented:
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
 
vascovCommented:
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
 
vascovCommented:
Hmm, actually i take it back :) temp is not reserved.

PS: i need some coffee
0
 
JigitCommented:
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
 
KoderiterAuthor Commented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now