Syntax error in INSERT INTO statement

mega · December 3rd, 2004, 09:35 PM

Hi all.
My code is generated via a method in a class called DBTool. In this code snippet I'll use an object of that class called myTool:

Code:

string []fieldNames = {"CustomerType", "Name", "Password", "Address", "ZipCode", "City",
 "Phone", "EMail", "PaymentRecord", "CreditAmount", "Balance"};
object [] values = {0 , "Hans", "goggle", "Baldersgade 123", "2200", "Copenhagen",
 "298120843", "007-is-his-iq@ofir.dk", 0, 5000.00m, 0.00m};
    //string []fieldNames = {"BookID", "Comment"};
    //object [] values = {3, "Fantastisk hvis det virker!"};
//IsInDB = myTool.Insert("Comments", fieldNames, values);
IsInDB = myTool.Insert("Customers", fieldNames, values);

The commented code works fine..
The Insert method creates an INSERT INTO SQL string:

Code:

public bool Insert(string table, string[] schema, params object[] fields)
{
    if (schema.Length != fields.Length)
    throw new ArgumentException("The number of fields does not match the size of the schema.");

    string query = "INSERT INTO " + table + " (";
    for (int i = 0; i < schema.Length; i++)
        {
            query += schema[i];
            if (i != schema.Length - 1)
                query += ", ";
        }
        query += ") VALUES (";
        for (int i = 0; i < fields.Length; i++)
        {
            query += Formatted(fields[i]);
            if (i != fields.Length -1)
                query += ", ";
        }
        query += ")";
            return this.ExplicitNonQuery(query);
    }

The values a define as fields and formatted via the method Formatted:

Code:

protected override string Formatted(object obj)
    {
        switch(Convert.ToString(obj.GetType()))
        {
            case "System.Int32":
                return ((int)obj).ToString();
            case "System.String":
                return "'" + (string)obj + "'";
            case "System.Byte":
                return ((byte)obj).ToString();
            case "System.Boolean":
                return (bool)obj ? "1" : "0";
            case "System.Decimal":
                return Convert.ToString(obj);
            case "System.Double":
                return Convert.ToString(obj);
            case "System.DBNull":
                return "NULL";            
            default:
                // There is an unhandled conversion
                throw new MyException("Total crab: " + obj.ToString() + "
 er af typen " + obj.GetType());
                return obj.ToString();
        }

The last line of code (default:) courses an Exeption, I know that so I throw an error. Just to see if I use some data types I don't convert. The last thing I do is executing the SQL string against the db:

Code:

public bool ExplicitNonQuery(string query)
    {
        if (query.Length > 0)
        {
            int RowsAffected;
            this.command.CommandText = query;
    /*        INSERT INTO Customers (CustomerType, Name, Password, Address, 
ZipCode, City, Phone, EMail, PaymentRecord, CreditAmount, Balance)
VALUES (0, 'Hans','fasd', 'engade 2', '2123', 'HornbÃ¦k', '29800843',
 'jon_ronnenberg@hotmail.dk',0, 100, 0);
       */
    OleDbTransaction trans = onnection.BeginTransaction(IsolationLevel.ReadCommitted);
    this.command.Transaction = trans;
    try
        {                
        RowsAffected = this.command.ExecuteNonQuery();
        trans.Commit();
        return RowsAffected>0;
        }
        catch (Exception ex)
            {    
                try
                {
                    trans.Rollback();
                    throw new MyException(ex.Message, ex.Source);
                //    return false;
                }
                catch(OleDbException OleDbex)
                {
                throw new MyException(OleDbex.Message, OleDbex.InnerException);
                }
            }
        } 
        else 
        {
            throw new MyException("query is empty");
        }
    }

Yet again the commented code works, but only if I run it inside Access. I tried unescaping the query field (like this @), right before I ship it to ExplicitNonQuery but in vain. If anyone can pick up on this code flaw I would greatly appreciate it. This code is due on monday, so I got little time. Thanks in advance.
- mega

- mega
Moving to C# .NET

Imar · December 4th, 2004, 05:56 AM

What error do you get? And how does the final SQL statement look like before you pass it to the database?

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

mega · December 4th, 2004, 10:35 AM

Hi Imar.

I get an OleDbExeption with the message: Syntax error in INSERT INTO statement. and the source says: Microsoft JET Database Engine
My SQL string look like this: INSERT INTO Customers (CustomerType, Name, Password, Address, ZipCode, City, Phone, EMail, PaymentRecord, CreditAmount, Balance) VALUES (0, 'Hans', 'goggle', 'Baldersgade 123', '2200', 'KÃ¸benhavn', '298120843', '007-is-his-iq@ofir.dk', 0, 5000, 0) (printed out into a txt file).

I can't spot any error in the SQL but then again maybe it's just me.

- mega
Moving to C# .NET

mega · January 12th, 2005, 04:30 PM

JET got some reserved keywords like Name and Password. When I renamed all the fields it worked out..
Thanks anyway.

- mega
Moving to C# .NET