Real Newbie - Calling stored procedures

[Singularity.co.uk]

Hi

I want to call a stored procedure from my page and the code I am using is:

SqlConnection sqlConn = new SqlConnection();
sqlConn.ConnectionString = DBConnection;
SqlCommand cmd = new SqlCommand("spGetProjects", sqlConn);
//Set up the command
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@UserID", SqlDbType.BigInt, 5);
// Test Stored Procedure with hard coded value
cmd.Parameters["@UserID"].Value = 12;

try
{
sqlConn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while(dr.Read())
{
this.cboProjects.DataValueField = dr["ProjectId"].ToString();
this.cboProjects.DataTextField = dr["ProjectCode"].ToString();
}
//Close the DataReader
dr.Close();
}
catch(SqlException ex)
{
}
finally
{
sqlConn.Close();
}

However nothing is returned, even though when I run the stored procedure
through the SQL Query Analyzer 4 records are returned. If I use the code
below, then the drop down is populated, but I want to use a stored procedure
instead of coding the SQL in the page:

SqlConnection sqlConn = new SqlConnection();
sqlConn.ConnectionString = DBConnection;
SqlDataAdapter dsc = new SqlDataAdapter("SELECT Projects.ProjectId,
Projects.ProjectCode FROM Projects ORDER BY ProjectCode", sqlConn);
DataSet ds = new DataSet();
dsc.Fill(ds, "Projects");
cboProjects.DataSource = ds.Tables["Projects"].DefaultView;
cboProjects.DataValueField = "ProjectId";
cboProjects.DataTextField = "ProjectCode";
cboProjects.DataBind();

Any and all help is greatly appreciated.

Thanks

Brendan

 

[Matt]

Hi Brendan. I dont know if it will make much difference, but have you
specified the direction of the parameter?
i.e.

SqlParameter myParm = selectCMD.Parameters.Add("@UserID",
SqlDbType.BigInt,5);

myParm.Direction = ParameterDirection.Input;

Secondly, have you put a breakpoint on the line while(dr.Read()) and stepped
through the code, looking into the data reader to see what is in there.

Thirdly, you could add in an output parameter

myParm = selectCMD.Parameters.Add("@recordCount", SqlDbType.Int,4);

myParm.Direction = ParameterDirection.Output;

and in your SQL code have Set @recordCount = @@RowCount near the bottom.

then you should be able to look in the parameters collection for Output and
see hopw many records you are returning. (not much help, but if you can see
a 4 in there, then you know that the SQL and .Net are talking properly, and
the SQL is exectuting as expected.)

Matt

 

[Richard Blewett [DevelopMentor]]

The problem is you are trying to databind to a data reader. The code that you showed with the inline SQL used a DataSet.

A data reader is a readonly *forward only" server side cursor. You've looped through the reader setting and resetting the databindings but there is no data added to the combobox. Either add the data into the combobox in your Read loop or use a DataSet, setting the SqlCommand that you create to the SelectCommand property of the data adapter.

Regards

RIchard Blewett - DevelopMentor
http://www.dotnetconsult.co.uk/weblog
http://www.dotnetconsult.co.uk

Hi

I want to call a stored procedure from my page and the code I am using is:

SqlConnection sqlConn = new SqlConnection();
sqlConn.ConnectionString = DBConnection;
SqlCommand cmd = new SqlCommand("spGetProjects", sqlConn);
//Set up the command
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@UserID", SqlDbType.BigInt, 5);
// Test Stored Procedure with hard coded value
cmd.Parameters["@UserID"].Value = 12;

try
{
sqlConn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while(dr.Read())
{
this.cboProjects.DataValueField = dr["ProjectId"].ToString();
this.cboProjects.DataTextField = dr["ProjectCode"].ToString();
}
//Close the DataReader
dr.Close();
}
catch(SqlException ex)
{
}
finally
{
sqlConn.Close();
}

However nothing is returned, even though when I run the stored procedure
through the SQL Query Analyzer 4 records are returned. If I use the code
below, then the drop down is populated, but I want to use a stored procedure
instead of coding the SQL in the page:

SqlConnection sqlConn = new SqlConnection();
sqlConn.ConnectionString = DBConnection;
SqlDataAdapter dsc = new SqlDataAdapter("SELECT Projects.ProjectId,
Projects.ProjectCode FROM Projects ORDER BY ProjectCode", sqlConn);
DataSet ds = new DataSet();
dsc.Fill(ds, "Projects");
cboProjects.DataSource = ds.Tables["Projects"].DefaultView;
cboProjects.DataValueField = "ProjectId";
cboProjects.DataTextField = "ProjectCode";
cboProjects.DataBind();

Any and all help is greatly appreciated.

Thanks

Brendan



[microsoft.public.dotnet.languages.csharp]

 

[Singularity.co.uk]

Matt

Thanks for the reply. Am after debugging through the code and the correct
data is coming from SQL Server in the while(dr.Read()).

I reckon I am probably trying the wrong way to populate the drop down list
with the datareader.

Brendan

Hi Brendan. I dont know if it will make much difference, but have you
specified the direction of the parameter?
i.e.

SqlParameter myParm = selectCMD.Parameters.Add("@UserID",
SqlDbType.BigInt,5);

myParm.Direction = ParameterDirection.Input;

Secondly, have you put a breakpoint on the line while(dr.Read()) and stepped
through the code, looking into the data reader to see what is in there.

Thirdly, you could add in an output parameter

myParm = selectCMD.Parameters.Add("@recordCount", SqlDbType.Int,4);

myParm.Direction = ParameterDirection.Output;

and in your SQL code have Set @recordCount = @@RowCount near the bottom.

then you should be able to look in the parameters collection for Output and
see hopw many records you are returning. (not much help, but if you can see
a 4 in there, then you know that the SQL and .Net are talking properly, and
the SQL is exectuting as expected.)

Matt

Hi

I want to call a stored procedure from my page and the code I am using is:

SqlConnection sqlConn = new SqlConnection();
sqlConn.ConnectionString = DBConnection;
SqlCommand cmd = new SqlCommand("spGetProjects", sqlConn);
//Set up the command
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@UserID", SqlDbType.BigInt, 5);
// Test Stored Procedure with hard coded value
cmd.Parameters["@UserID"].Value = 12;

try
{
sqlConn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while(dr.Read())
{
this.cboProjects.DataValueField = dr["ProjectId"].ToString();
this.cboProjects.DataTextField = dr["ProjectCode"].ToString();
}
//Close the DataReader
dr.Close();
}
catch(SqlException ex)
{
}
finally
{
sqlConn.Close();
}

However nothing is returned, even though when I run the stored procedure
through the SQL Query Analyzer 4 records are returned. If I use the code
below, then the drop down is populated, but I want to use a stored procedure
instead of coding the SQL in the page:

SqlConnection sqlConn = new SqlConnection();
sqlConn.ConnectionString = DBConnection;
SqlDataAdapter dsc = new SqlDataAdapter("SELECT Projects.ProjectId,
Projects.ProjectCode FROM Projects ORDER BY ProjectCode", sqlConn);
DataSet ds = new DataSet();
dsc.Fill(ds, "Projects");
cboProjects.DataSource = ds.Tables["Projects"].DefaultView;
cboProjects.DataValueField = "ProjectId";
cboProjects.DataTextField = "ProjectCode";
cboProjects.DataBind();

Any and all help is greatly appreciated.

Thanks

Brendan

 

[Guest]

Do you get an exception? If so, what is it?

Do you (User in the connection string) have execution rights on the stored
procedure?

Marius Holkema

 

[Singularity.co.uk]

Marinus

Thanks for your reply.

I don't get any errors returned. The page displays correctly except the
cboProjects drop down list is empty, when it should contain 4 records.

Brendan

Do you get an exception? If so, what is it?

Do you (User in the connection string) have execution rights on the stored
procedure?

Marius Holkema

Hi

I want to call a stored procedure from my page and the code I am using is:

SqlConnection sqlConn = new SqlConnection();
sqlConn.ConnectionString = DBConnection;
SqlCommand cmd = new SqlCommand("spGetProjects", sqlConn);
//Set up the command
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@UserID", SqlDbType.BigInt, 5);
// Test Stored Procedure with hard coded value
cmd.Parameters["@UserID"].Value = 12;

try
{
sqlConn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while(dr.Read())
{
this.cboProjects.DataValueField = dr["ProjectId"].ToString();
this.cboProjects.DataTextField = dr["ProjectCode"].ToString();
}
//Close the DataReader
dr.Close();
}
catch(SqlException ex)
{
}
finally
{
sqlConn.Close();
}

However nothing is returned, even though when I run the stored procedure
through the SQL Query Analyzer 4 records are returned. If I use the code
below, then the drop down is populated, but I want to use a stored procedure
instead of coding the SQL in the page:

SqlConnection sqlConn = new SqlConnection();
sqlConn.ConnectionString = DBConnection;
SqlDataAdapter dsc = new SqlDataAdapter("SELECT Projects.ProjectId,
Projects.ProjectCode FROM Projects ORDER BY ProjectCode", sqlConn);
DataSet ds = new DataSet();
dsc.Fill(ds, "Projects");
cboProjects.DataSource = ds.Tables["Projects"].DefaultView;
cboProjects.DataValueField = "ProjectId";
cboProjects.DataTextField = "ProjectCode";
cboProjects.DataBind();

Any and all help is greatly appreciated.

Thanks

Brendan

 

[SP]

A data reader is a readonly *forward only" server side cursor. You've
looped through the reader setting and resetting the databindings but there
is no data added to the combobox. Either add the data into the combobox in
your Read loop or use a DataSet, setting the SqlCommand that you create to
the SelectCommand property of the data adapter.

To clarify Richard's answer the code below does not add data to the
combobox.

while(dr.Read())
{
this.cboProjects.DataValueField = dr["ProjectId"].ToString();
this.cboProjects.DataTextField = dr["ProjectCode"].ToString();
}

You are using the DataValueField and DataTextField properties as if that is
how you add data to the combobox which they do not. These properties are
used to set the fields that are to be used for display and value if you were
to bind it to a datatable (show this field but use the value from this
field). If you were binding to a dataset then you would do this once:

this.cboProjects.DataValueField = "ProjectId";
this.cboProjects.DataTextField = "ProjectCode";

Instead, in your Read loop you should be adding to what I believe is the
Items property of the combobox which represents the Items to display in the
combobox.

SP

 

[Keith M]

Based on the other replies I think you might want something like this:

try
{
sqlConn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if(dr.HasRows)
{
this.cboProjects.DataValueField = dr["ProjectId"].ToString();
this.cboProjects.DataTextField = dr["ProjectCode"].ToString();
this.cboProjects.DataSource = dr;
this.cboProjects.DataBind();
}
//Close the DataReader
dr.Close();
}

HTH

 

[Keith M]

Ooops,

more like this:

try
{
sqlConn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if(dr.HasRows)
{
this.cboProjects.DataValueField = "ProjectId";
this.cboProjects.DataTextField = "ProjectCode";
this.cboProjects.DataSource = dr;
this.cboProjects.DataBind();
}
//Close the DataReader
dr.Close();
}

 

[Singularity.co.uk]

Thanks to everyone who offered advice. I have now got the information to
display.

This seems a great newsgroup and I will no doubt be needing help again in
the future, so hope to hear from you all then.

Thanks again

Brendan