access database select query through c#

[tekkerguy]

I'm trying to pass the value of a text box to my dataaccess layer. This is the code that I'm using to create the query.

 OleDbConnection thisConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\dir\accessdb.mdb");
             OleDbCommand cmd = new OleDbCommand();


             string sql = "select pd.*, cust.companyname as custcmpname, cust.customerid as cucustid from [Project Description] pd inner join customers " +
                    "cust on pd.customerid = cust.customerid";

             if (srchCriteria.Count > 0)
             {
                 sql += " where ";
                 for (int i = 0; i < srchCriteria.Count; i++)
                 {
                     TextBox tb = (TextBox)srchCriteria[i];
                     sql += String.Format("{0} = ? ", tb.ID.ToString().Replace("_","."));
                 }


                 foreach (TextBox tb in srchCriteria)
                 {
                     cmd.Parameters.Add(new OleDbParameter(tb.ID.ToString().Replace("_","."), tb.Text));
                 }
             }


            cmd.CommandText = sql;
            thisConnection.Open();

            OleDbDataAdapter mainTableAdapter = new OleDbDataAdapter(sql, thisConnection);


            DataSet thisDataSet = new DataSet();
            try
            {
                mainTableAdapter.Fill(thisDataSet, "[Project Description]");
            }
            catch (Exception e)
            {
                string error = e.ToString();
            }


            
            _tableDataSet = thisDataSet;
            thisConnection.Close();
        }

But I'm getting "no value given for one or more parameters"

The query comes out to:

select pd.*, cust.companyname as custcmpname, cust.customerid as cucustid from [Project Description] pd inner join customers cust on pd.customerid = cust.customerid where cust.CompanyName = ?

And the parameter is being passed properly (I can see the text when I debug and examine the parameter create)

I my access query statement wrong?

 

[tekkerguy]

I figured this one out, but one last question, if I use = "cmpname", it returns results, but if I use LIKE ? and "\"*"cmpname"*\"" doesn't return any records, but it does in access.

Any reasons for this?

 

[jmeckley]

it's probably a wildcard syntax issue. try using % instead of *.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[chiph]

MS-Access is not ANSI-SQL compliant, and uses the * character as it's wildcard. Like jmeckley says, use the standard % character and it should work.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first