CODEWITHSUNDEEP
c#sqlclassoledb
Noam650
Noam650

Reputation: 113

using oledb inside class

I have the code bellow and i get this error:

Troubleshooting Exceptions: System.Data.OleDb.OleDbException "syntax error"

and i don't know what i did wrong. it suppose to pull infomation from table. I used the same method on the whole project, and just in this, it makes me troubles... 

    class Codons
{
    private bool start, end;
    private string codon1, codon3, triplet1, triplet2, triplet3;
    private string triplet4, triplet5, triplet6, fullName;
    private OleDbConnection dataconnection;


    public Codons(string letter)
    {
        this.start = false;
        this.end = false;
        this.dataconnection = new OleDbConnection();
            this.dataconnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Projects_2012\\Project_Noam\\Access\\myProject.accdb";
            this.dataconnection.Open();


            string sql = "SELECT  tblCodons.codonsCodon3, " +
            "tblCodons.codonsTriplet1, tblCodons.codonsTriplet2, tblCodons.codonsTriplet3, " +
            "tblCodons.codonsTriplet4, tblCodons.codonsTriplet5, tblCodons.codonsTriplet6, " +
            "tblCodons.codonsFullName, tblCodons.codonsStart, tblCodons.codonsEnd"
            + " FROM tblCodons"
            + " WHERE tblCodons.codonsCodon1="+letter;

            OleDbCommand mycomm = new OleDbCommand(sql, dataconnection);
            OleDbDataReader dataReader = mycomm.ExecuteReader();
            dataReader.Read();
            this.codon1 = letter;
            this.codon3 = dataReader.GetString(0);
            this.triplet1 = dataReader.GetString(1);

            if (dataReader.IsDBNull(2))
                this.triplet2 = "     ";
            else
                this.triplet2 = dataReader.GetString(2);

            if (dataReader.IsDBNull(3))
                this.triplet3 = "     ";
            else
                this.triplet3 = dataReader.GetString(3);

            if (dataReader.IsDBNull(4))
                this.triplet4 = "     ";
            else
                this.triplet4 = dataReader.GetString(4);

            if (dataReader.IsDBNull(5))
                this.triplet5 = "     ";
            else
                this.triplet5 = dataReader.GetString(5);

            if (dataReader.IsDBNull(6))
                this.triplet6 = "     ";
            else
                this.triplet6 = dataReader.GetString(6);

            this.fullName = dataReader.GetString(7);
            this.start = dataReader.GetBoolean(8);
            this.end = dataReader.GetBoolean(9);
            dataReader.Close();

    }

Upvotes: 0

Views: 276

Answers (3)

Heinzi
Heinzi

Reputation: 172220

Here:

+ " WHERE tblCodons.codonsCodon1="+letter

You are creating an SQL clause that looks like this.

...WHERE tblCodons.codonsCodon1=A

which is invalid SQL. Strings literals in SQL must be quoted propery, i.e.

...WHERE tblCodons.codonsCodon1='A'

To do this, you can either manually add the single quotes and make sure to properly escape all single quotes in the string letter (unless you want to have to deal with SQL injection)...

... or (much better) you use parameterized queries:

+ " WHERE tblCodons.codonsCodon1 = ?"; 

OleDbCommand mycomm = new OleDbCommand(sql, dataconnection); 
mycomm.Parameters.AddWithValue("codonsCodon1", letter);

In addition, you should check the return value of dataReader.Read() instead of just calling it:

if (!dataReader.Read()) {
     // replace this with more useful error reporting, maybe throwing an exception
     MessageBox.Show("No codon for this letter found.");
     return;
}

Upvotes: 3

Brian
Brian

Reputation: 1823

It is probably that you enter the value of letter without quotes. It then treats the content of letter as a function. Since it can't find the function, it throws a syntax error exception.

Try closing letter in quotes ("letter")

Upvotes: 0

Justin Niessner
Justin Niessner

Reputation: 245399

Since you're building your SQL Query as a string, I'm guessing you have some invalid SQL syntax in letter. Once you append that value to the query, it causes the syntax error you're seeing.

I would take a look at this post about Parameterized Queries (OleDb). That is the correct way to pass parameters to a query that will avoid this (and other obvious SQL Injection attacks) in your application.

Upvotes: 1

Related Questions