SQL Debugging

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I'm very new to C# and am working on using a stored procedure to MSSQL with
two date parameters. The first problem I'm having is that SQL appears to be
throwing an exception (or my parameters are not being passed thru as I think
they are).
1. How can I see exactly what C# (ADO) is sending to the server?
2. How can I see the actual results including messages and errors returned?

I am able to run a stored procedure with no parameters, so I think I have
that part working, but I am unable to figure out what I'm doing wrong with
the parameters. I'd also appreciate seeing a code snippet that actually
passes a date as a parameter to a stored procedure.

Thanks,
CD
 
Sahil,
Thanks for the info, but I could not figure out how, using Sql Profiler, to
get the actual command that is sent to the server.


Also, could you supply a snippet of c# code that executes a stored proc with
a date as a parameter?

Thanks again for the help

CD
 
Right so suppose you have a stored procedure called MyProc that has one
argument, a DateTime, which we'll call DateArg. You'd need to do
something like...

try
{
DateTime arg = DateTime.Parse("1-Jan-2004"); //whatever you like here

SqlConnection conn = new SqlConnection(Server.dbpath);
SqlCommand comm = new SqlCommand("MyProc", conn);
comm.CommandType = CommandType.StoredProcedure;

comm.Parameters.Add("@DateArg", SqlDbType.DateTime);
comm.Parameters["@DateArg"].Value = arg;

//run it now
conn.Open();
SqlDataReader dr = comm.ExecuteReader();
}
catch(SQLException ex)
{
for (int i=0; i < ex.Errors.Count; i++)
{
errorMessages += " Error #" + i
+ " Message: " + ex.Errors.Message
+ " LineNumber: " + ex.Errors.LineNumber
+ " Procedure: " + ex.Errors.Procedure + "\r";
}
}
catch(Exception)
{
Console.WriteLine("Other exceptions here");
}


Right so the important thing is to surround whatever you do with a try
catch, and catch the SQLException which will give you the line number of
any errors in the stored procedure (this is really handy!).

Let me know how you go

Wal
 
Wal,
ok, so I got that part going and I am not getting a SqlException, but I'm
struggling to get this into a DataGrid. From the docs I read, I am supposed
to do something like dataGrid1.DataBind(), but dataGrid doesn't understand
DataBind. I am trying with a DataAdapter. Here is my latest cut at the this.
I've removed the Stored procedure at this point in order to try and isolate
my current issue. I appreciate the help since I'm new with C# and trying to
ramp up

try
{
SqlConnection myConnection = new SqlConnection(
"User ID=CD;Data Source=MyServerName;Initial Catalog=PLHistory;Workstation
ID=CD;Packet Size=4096");

SqlCommand myCommand = new SqlCommand("Select dailySeniorDebitInterest FROM
ProfitAndLossDetails2004Q4 WHERE TradeDate='12/12/2004'", myConnection);
SqlDataAdapter myDataAdapter = new SqlDataAdapter();
myDataAdapter.SelectCommand = myCommand;
myConnection.Open();


// SqlDataAdapter myCommand = new SqlDataAdapter("spSumPLDetails2004New",
myConnection);
// myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
// myCommand.SelectCommand.Parameters.Add(new SqlParameter("@startDate",
startDate.Text));
// myCommand.SelectCommand.Parameters.Add(new SqlParameter("@endDate",
startDate.Text));

DataSet ds = new DataSet();
myDataAdapter.Fill(ds, "ProfitAndLossDetails2004Q4");

//dataGrid1.DataSource=ds.Tables["spSumPLDetails2004New"].DefaultView;
//dataGrid1.SetDataBinding(ds, "spSumPLDetails2004New");
//ds.FillDataSet(dataGrid1);

myConnection.Close();

}
















vooose said:
Right so suppose you have a stored procedure called MyProc that has one
argument, a DateTime, which we'll call DateArg. You'd need to do
something like...

try
{
DateTime arg = DateTime.Parse("1-Jan-2004"); //whatever you like here

SqlConnection conn = new SqlConnection(Server.dbpath);
SqlCommand comm = new SqlCommand("MyProc", conn);
comm.CommandType = CommandType.StoredProcedure;

comm.Parameters.Add("@DateArg", SqlDbType.DateTime);
comm.Parameters["@DateArg"].Value = arg;

//run it now
conn.Open();
SqlDataReader dr = comm.ExecuteReader();
}
catch(SQLException ex)
{
for (int i=0; i < ex.Errors.Count; i++)
{
errorMessages += " Error #" + i
+ " Message: " + ex.Errors.Message
+ " LineNumber: " + ex.Errors.LineNumber
+ " Procedure: " + ex.Errors.Procedure + "\r";
}
}
catch(Exception)
{
Console.WriteLine("Other exceptions here");
}


Right so the important thing is to surround whatever you do with a try
catch, and catch the SQLException which will give you the line number of
any errors in the stored procedure (this is really handy!).

Let me know how you go

Wal
Click to expand...
 
DataGrid is a class I know nothing about. I havent used it once, so I
won't be able to help you with that part. I thought your main issue was
getting the data into the dataset.

Wal
 
It was, which I can now do, but I quickly ran into this. I'll have to
backtrack and find some examples suited for this



Thanks for your help

CD
 
CD,

Sorry I hadn't noticed this thread earlier --- here are the answers ----
Thanks for the info, but I could not figure out how, using Sql Profiler,
to get the actual command that is sent to the server.
Click to expand...

Sql Profiler is a tool that gets installed alongwith Sql Server - not a part
of .NET/.NET framework. You'll have to run a "trace", it's real easy start
the tool and play with it. You'll figure it out I'm sure.
Also, could you supply a snippet of c# code that executes a stored proc
with a date as a parameter?
Click to expand...

Okay .. I'd recommend a book (maybe mine), but here's some sample code not
written in Vstudio

SqlConnection conn = new SqlConnection("...") ;
SqlCommand cmd = conn.CreateCommand() ;
SqlParameter sqparam = new SqlParameter() ;
sqparam.datatype = // set to date time
sqparam.parametername = // make sure this matches
cmd.CommandText = "sprocname" ;
cmd.CommandType = CommandType.StoredProcedure ;

// then it depends on what u wanna do with the stored proc, to fill a
dataset, you'll have to use data adapter, or in it's simplest form you could
do Command.Execute*.* functions

HTH :)

Any more questions - please shoot this way I'll make sure I'm not as laggard
in noticing your messages as I've been :)

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
 
Is your dataset properly filled? do a dataset.GetXML to make sure you have
data in it.

If that is all fine and dandy --- all u gotta do is

DataGrid.DataSource = dataset <--- winforms

DataGrid.DataSource = dataset ;
DataGrid.DataBind() ; <.---- ASP.NET


- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik


CD said:
Wal,
ok, so I got that part going and I am not getting a SqlException, but I'm
struggling to get this into a DataGrid. From the docs I read, I am
supposed to do something like dataGrid1.DataBind(), but dataGrid doesn't
understand DataBind. I am trying with a DataAdapter. Here is my latest cut
at the this. I've removed the Stored procedure at this point in order to
try and isolate my current issue. I appreciate the help since I'm new with
C# and trying to ramp up

try
{
SqlConnection myConnection = new SqlConnection(
"User ID=CD;Data Source=MyServerName;Initial Catalog=PLHistory;Workstation
ID=CD;Packet Size=4096");

SqlCommand myCommand = new SqlCommand("Select dailySeniorDebitInterest
FROM ProfitAndLossDetails2004Q4 WHERE TradeDate='12/12/2004'",
myConnection);
SqlDataAdapter myDataAdapter = new SqlDataAdapter();
myDataAdapter.SelectCommand = myCommand;
myConnection.Open();


// SqlDataAdapter myCommand = new SqlDataAdapter("spSumPLDetails2004New",
myConnection);
// myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
// myCommand.SelectCommand.Parameters.Add(new SqlParameter("@startDate",
startDate.Text));
// myCommand.SelectCommand.Parameters.Add(new SqlParameter("@endDate",
startDate.Text));

DataSet ds = new DataSet();
myDataAdapter.Fill(ds, "ProfitAndLossDetails2004Q4");

//dataGrid1.DataSource=ds.Tables["spSumPLDetails2004New"].DefaultView;
//dataGrid1.SetDataBinding(ds, "spSumPLDetails2004New");
//ds.FillDataSet(dataGrid1);

myConnection.Close();

}
















vooose said:
Right so suppose you have a stored procedure called MyProc that has one
argument, a DateTime, which we'll call DateArg. You'd need to do
something like...

try
{
DateTime arg = DateTime.Parse("1-Jan-2004"); //whatever you like here

SqlConnection conn = new SqlConnection(Server.dbpath);
SqlCommand comm = new SqlCommand("MyProc", conn);
comm.CommandType = CommandType.StoredProcedure;

comm.Parameters.Add("@DateArg", SqlDbType.DateTime);
comm.Parameters["@DateArg"].Value = arg;

//run it now
conn.Open();
SqlDataReader dr = comm.ExecuteReader();
}
catch(SQLException ex)
{
for (int i=0; i < ex.Errors.Count; i++)
{
errorMessages += " Error #" + i
+ " Message: " + ex.Errors.Message
+ " LineNumber: " + ex.Errors.LineNumber
+ " Procedure: " + ex.Errors.Procedure + "\r";
}
}
catch(Exception)
{
Console.WriteLine("Other exceptions here");
}


Right so the important thing is to surround whatever you do with a try
catch, and catch the SQLException which will give you the line number of
any errors in the stored procedure (this is really handy!).

Let me know how you go

Wal
Click to expand...
Click to expand...
 
Back
Top