Database Error when retrieving specific records from a table

[Eagle32]

Hi, I am trying to retrieve specific records from a table by specifying the parameter value at run time but the following error keeps occuring when i pass in a value of the correct data type. System.Data.OleDb.OleDbException: No value given for one or more required parameters. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) at Service.RetrieveComments(Int32 ThreadID) As you can tell from the error message I am using Microsoft Access database provider. The data type for ThreadID is 'Number' and the field size is set to 'long integer'. The field does exist in the table. In my code I even tried changing datatype from 'int' to 'uint64' but that didnt solve the problem. Here is the code I had developed. [WebMethod] public DataSet RetrieveComments(int ThreadID) { String queryString = "SELECT * FROM [Comments] WHERE" + "([ThreadID]=@ThreadID)"; DataSet dataSet = new DataSet(); String connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|/MyDatabase.mdb;Persist Security Info=True"; OleDbConnection dbConn = new OleDbConnection(connString); OleDbDataAdapter dbAdapter = new OleDbDataAdapter(queryString, dbConn); dbConn.Open() dbAdapter.Fill(dataSet, "Comments"); dbConn.Close(); return dataSet; } Is there anything wrong with the above code? I have tried the above SQL query in MS Access and it runs fine providing I remove the

[Tarakeshwar Reddy]

The problem is because you are not setting the parameter value. Also, you need to use the ? and not a named parameter @ThreadID in the query.

public DataSet RetrieveComments(int threadID)
{
String connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|/MyDatabase.mdb;Persist Security Info=True";
OleDbConnection dbConn = new OleDbConnection(connString);

String queryString = "SELECT * FROM [Comments] WHERE [ThreadID] = ?";
OleDbCommand oleDbCommand = new OleDbCommand(queryString, dbConn);
oleDbCommand.Parameters.Add("@threadID", OleDbType.Integer, 10).Value = threadID;

OleDbDataAdapter dbAdapter = new OleDbDataAdapter();
dbAdapter.SelectCommand = oleDbCommand;
dbConn.Open();

DataSet dataSet = new DataSet();
dbAdapter.Fill(dataSet, "Comments");

dbConn.Close();
return dataSet;
}

Make sure the parameters and syntax are okay. Also use the using statement to make sure your objects get disposed properly.

[hyjiacan]

String queryString = "SELECT * FROM [Comments] WHERE [ThreadID]=" + ThreadID; Can this work?

[Tarakeshwar Reddy]

hyjiacan wrote:

Can this work?

It will work, but that will make the query prone to SQL Injection Attacks[^].