Simple Query Not Executing

  • Thread starter Thread starter Water Cooler v2
  • Start date Start date
W

Water Cooler v2

Dang! A *simple* query doesn't run on my machine. I wrote a Data Access
Layer (DAL) in a dll that I am testing with the help of a console
application.

Here's the code. I am damn sure that my code is fine. I think my
problem has something to do with MSDAC or some service pack or some
stupid Windows patch missing on my machine.


public static void GetJetScalar()
{
//string SQL = "select [Category Name], [Description] FROM
Categories WHERE [Category ID] = 2";
string SQL = "select [Order ID], [Product] FROM [Order Details]
WHERE [Order ID] = 10248";
DataProvider provider = null;
CategoryShort cat = null;

try
{
provider = new DataProvider();
cat = (CategoryShort)provider.GetScalar(SQL);
if (cat != null)
Console.WriteLine("Name: " + cat.Name + "\n\nDescription: " +
cat.Description);
}
catch(System.Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
cat = null;
provider = null;
}
}





Here's the code for the GetScalar() implemented by the DataProvider
class:

public System.Object GetScalar(string SQL)
{
System.Object ret = null;

if (this._connection.UnderlyingConnection == null)
{
System.Exception ex = new DatabaseTypeNotSupportedException();
_log.Write(new LogEntry(ex), true);
throw ex;
}

if (this._connection.Database == Databases.MSAccess)
ret = (new OleDbCommand(SQL,
(OleDbConnection)this._connection.UnderlyingConnection)).ExecuteScalar();
else if (this._connection.Database == Databases.SQLServer)
ret = (new SqlCommand(SQL,
(SqlConnection)this._connection.UnderlyingConnection)).ExecuteScalar();

return ret;
}


I get an exception that says: "No value provided for one or more
required parameters." I am trying it on the Northwind database only to
test it. I tried a query two or three queries, all syntactically
correct, but they all yeild the same exception as their result.
 
chanmm said:
ExecuteScalar() only returna single value but your SQL statement "select
[Order ID], [Product] FROM [Order Details]..."
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar(d=ide).aspx

Use ExecuteNonQuery or others instead.

chanmm
Click to expand...



You're missing the point. This is a *known* exception, which, in my
vast experience, I have encountered many times and resolved by
installing some update for the data access components. Only this time,
I do not know which one.

The DataReader works just fine. The only problem is with the
disconnected objects (DataSet/Scalars). The same code, however, works
perfectly on my laptop.

About your specific advise, I am sorry to say that you must read your
own advise carefully before you give it out misleading people. The MSDN
says:

"Executes the query, and returns the first column of the first row in
the result set returned by the query. Additional columns or rows are
ignored."


Correct Interpretation: You may execute any select query but it will
fetch the DataTable[0][0] result for the results of that query.

And ExecuteNonQuery() is not for "select" statements. Kindly read the
documentation thoroughly. It is for INSERT, UPDATE, DELETE (non-query
SQL statements, thereby meaning, statements that do *not* query data
but rather modify it).
 
Walter,

Can you give the next time a complete problem description. This as you do
now is in my opinion very very much misleading.

I did not give an answer, I was expecting a reply from you as you have now
given to Chanm. We are not here to be demotivated by you.

I agree completely whit Chanm, your statements are strange. The
executescalar is meant to return *one* (the first) value of your select.
Therefore that is at least misleading as well.

As thirth misleading part in your question you have outcommented code,
simply removing that in the message would help others better to help you.

Just to motivate Chanm again and let him/here not be demotivated by you

Cor

Water Cooler v2 said:
ExecuteScalar() only returna single value but your SQL statement "select
[Order ID], [Product] FROM [Order Details]..."
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar(d=ide).aspx

Use ExecuteNonQuery or others instead.

chanmm
Click to expand...



You're missing the point. This is a *known* exception, which, in my
vast experience, I have encountered many times and resolved by
installing some update for the data access components. Only this time,
I do not know which one.

The DataReader works just fine. The only problem is with the
disconnected objects (DataSet/Scalars). The same code, however, works
perfectly on my laptop.

About your specific advise, I am sorry to say that you must read your
own advise carefully before you give it out misleading people. The MSDN
says:

"Executes the query, and returns the first column of the first row in
the result set returned by the query. Additional columns or rows are
ignored."


Correct Interpretation: You may execute any select query but it will
fetch the DataTable[0][0] result for the results of that query.

And ExecuteNonQuery() is not for "select" statements. Kindly read the
documentation thoroughly. It is for INSERT, UPDATE, DELETE (non-query
SQL statements, thereby meaning, statements that do *not* query data
but rather modify it).
Click to expand...
 
Back
Top