SqlCommand.ExecuteReader vs SqlDataAdapter.Fill

[ralphtrent]

Hi
I have an SqlCommand Object. When I bind that object to an SqlDataAdapter and execute SqlDataAdapter.Fill, I get an SQL convert error. When I just SqlCommand.ExecuteReader(), that same sql executes fine.

ANY IDEA'S WHY? THIS IS KILLING ME.

Thanks,
RalphTrent

 

[AlexCuse]

can you post the code you're using?

[small]----signature below----[/small]
The author of the monograph, a native of Schenectady, New York, was said by some to have had the highest I.Q. of all the war criminals who were made to face a death by hanging. So it goes.

My Crummy Web Page

 

[jmeckley]

SqlDataAdapter is an abstraction of the raw ADO.Net objects.
SqlCommand is the raw ADO.Net.

When I bind that object to an SqlDataAdapter and execute SqlDataAdapter.Fill, I get an SQL convert error.

what do you mean by "that object"?

I think Fill returns void while ExecuteReader returns an IDataReader. it could be you are attempting to bind Void to an object.

As alex said. Posting the code will give us a context for the problem.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[ralphtrent]

Thanks guys, here is the code, good and bad. The error that I am getting is "failed to covert nvarchar to type decimal". I know that is not a specific dot net error so it is something that is being return in my data results set. I am fine with using SqlCommand.ExecuteReader, but i need a way to put that data into a datatable. The easiest way seems to be SqlDatAdapter.Fill(datatable). If you know of an easier way using SqlCommand, im all ears.

Thanks,
RalphTrent

Non Working

System.Data.SqlClient.SqlCommand objComm = new System.Data.SqlClient.SqlCommand();
objComm.CommandText = "MY_SQL";
objComm.Parameters.Add("@UserName", "user_name");
objComm.Parameters.Add("@StartDate",  Convert.ToDateTime("6/23/2009 12:00:00 AM"));
objComm.Parameters.Add("@EndDate", Convert.ToDateTime("6/24/2009 11:59:59 PM"));
System.Data.SqlClient.SqlDataAdapter objSDA = new System.Data.SqlClient.SqlDataAdapter(objComm);
System.Data.SqlClient.SqlConnection objConn = new System.Data.SqlClient.SqlConnection("MY_CONNECTION");
objComm.Connection = objConn;
objConn.Open();
System.Data.DataTable dt = new System.Data.DataTable();

try
{
    objSDA.Fill(dt); //here is where I fail
}
catch (Exception exception) { System.Diagnostics.Debugger.Break(); }
finally
{
    objConn.Close();
    objComm.Cancel();
    objSDA = null;
    objComm = null;
    objConn = null;
}

Working

System.Data.SqlClient.SqlCommand objComm = new System.Data.SqlClient.SqlCommand();
objComm.CommandText = "MY_SQL";
objComm.Parameters.Add("@UserName", "user_name");
objComm.Parameters.Add("@StartDate",  Convert.ToDateTime("6/23/2009 12:00:00 AM"));
objComm.Parameters.Add("@EndDate", Convert.ToDateTime("6/24/2009 11:59:59 PM"));
System.Data.SqlClient.SqlDataAdapter objSDA = new System.Data.SqlClient.SqlDataAdapter(objComm);
System.Data.SqlClient.SqlConnection objConn = new System.Data.SqlClient.SqlConnection("MY_CONNECTION");
objComm.Connection = objConn;
objConn.Open();
System.Data.DataTable dt = new System.Data.DataTable();

try
{
    objComm.ExecuteReader();
}
catch (Exception exception) { System.Diagnostics.Debugger.Break(); }
finally
{
    objConn.Close();
    objComm.Cancel();
    objSDA = null;
    objComm = null;
    objConn = null;
}

 

[jmeckley]

in your second block of code you are not doing anything with the reader. make the following changes and see what happens

dt.Load(objComm.ExecuteReader());

you can also remove the catch statement altogether. it will break in debug mode anyway and your swallowing the exception.

you could even reduce it to

var results = new DataTable();
using(var connection = new SqlConnection(...))
using(var command = connection.CreateCommand())
{
   command.Text = "sql statement";
   var parameter = command.CreateParameter();
   parameter.Name = "name";
   parameter.Value = aValue;
   command.Parameters.Add(parameter);

   connection.Open();
   results.Load(command.ExecuteReader());
}
return results;

all closing and disposing is done for you with the using blocks.

as any FYI you can also create dates like this

var january2nd2009At1005PM = new DateTime(2009, 1, 2, 22, 5, 0);

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[ralphtrent]

UPDATE.

I tried the following code mode to the working code

dt.Load(objComm.ExecuteReader());

BAM it BLOWS. So its when ever I load this data into a datatable. I am not defining the dt upfront, I am letting SQL server do that. If I take that same sql and run it against sql query analyzer, no errors and I never find the data the error is complaining about.

 

[ralphtrent]

J,
Thanks for the post. This is just the shortened sample code. I do not have the debugger.break code in play in prod. The prod dot-net verison I am using is still 1.1. What dot-net release is using in as you have shown above?

 

[jmeckley]

.net 3.5. exchange the var keywork for an explicit type an it should work.

since the last reported error message was "failed to covert nvarchar to type decimal" it is an conversion issue. post the sql as this may have some clues.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[ralphtrent]

PROBLEM Fixed. It was a sub query i was running that was returning the error. But the question still stands, why does Query Analyzer not have a problem with ADO.net does?

 

[jmeckley]

are you positive the query in QA and the query in .net were the same? I know it sounds simple, but sometimes the simple mistakes are the ones we overlook.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[ralphtrent]

I copied the same sql from my dot-net code, into a QA window. The only thing i had to do was declare my variables in QA. I wonder if it is a connection driver issue. Do they both use the same connection code?

 

[jmeckley]

same connection code? qa and .net? i highly doubt it. the .net framework wasn't even around when QA (for sql2000) was introduced.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190