how to use cmd.ExecuteReader

i have a cript that excute a single row using excutescalar and i want to change to excutereader to have more rows

how do i apply it

// Create command
                                    cmd.CommandText = sql;
                                    cmd.CommandType = System.Data.CommandType.Text;
                                    cmd.Connection = conn;

                                    object resultObj = cmd.ExecuteScalar();

                                    if (resultObj != null)
                                    {
                                          credit = resultObj.ToString();
                                          balance.Text=string.Format("{0}",credit);
                                    }
                                    else{
                                          balance.Text="Name: N/A";
                                          }
                              }

                              conn.Close();
                        }
daz1234Asked:
Who is Participating?
 
Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented:
                  void  Page_Load()
                  {
                        string dialplan = Request.QueryString["dialplan"];
                        string credit = "";
                        string sql = string.Format("select * from zones('{0}')", dialplan);
                        
                        
                        using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(Web.Configuration.DatabaseConnectionString))
                        {
                              conn.Open();

                              using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
                              {
                                    // Create command
                                    cmd.CommandText = sql;
                                    cmd.CommandType = System.Data.CommandType.Text;
                                    cmd.Connection = conn;

                              
                                    
                                    SqlDataReader reaer = cmd.ExecuteReader();

                                    if (reader.Read())
                                    {
                                         do {
                                          credit = reader.GetDouble(0).ToString();
                                          balance.Text += string.Format("{0}<br />",credit);
                                        } while (reader.Read());
                                    }
                                    else{
                                          balance.Text="Name: N/A";
                                          }

                                    
                              }

                              conn.Close();
                        }

                  }

Open in new window

0
 
Carl TawnSystems and Integration DeveloperCommented:
0
 
daz1234Author Commented:
so how would i rewrite the syntax above i am no programmer

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Carl TawnSystems and Integration DeveloperCommented:
Well, there isn't much in your code to state what database type you are connecting to or what your result set is or what datatypes you are using. But, making some assumptions it would be something like:
// Create command
                                    cmd.CommandText = sql;
                                    cmd.CommandType = System.Data.CommandType.Text;
                                    cmd.Connection = conn;

                                    SqlDataReader reader = cmd.ExecuteReader();

                                    while (reader.Read())
                                    {
                                          credit = reader.GetDouble(0);
                                          balance.Text=string.Format("{0}",credit);
                                    }
                                    else{
                                          balance.Text="Name: N/A";
                                          }
                              }

                              conn.Close();
                        }

Open in new window

0
 
DhaestCommented:
What do you want to do with the multiple results ? Append them in one textbox, show in a datagrid, ... ?

// Create command
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = conn;

SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

while(myReader.Read()) 
{
    Console.WriteLine(myReader.GetString(0));
}
myReader.Close();

Open in new window

0
 
angus_young_acdcCommented:
Try the following
cmd.CommandText = sql;
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Connection = conn;
            SqlDataReader reader = default(SqlDataReader);
            reader = cmd.ExecuteReader();
            balance.Text="Name: N/A";
            while(reader.Read())
            {
                 credit = resultObj.ToString();
                 balance.Text=string.Format("{0}",credit);

            }
            conn.Close();

Open in new window

0
 
daz1234Author Commented:
want to show the result as text on a webpage as rows

full script below

<%@ Page Language="C#" Title="rate" %>


<script runat="server" type="text/C#">

                  void  Page_Load()
                  {
                        string dialplan = Request.QueryString["dialplan"];
                        string credit = "";
                        string sql = string.Format("select * from zones('{0}')", dialplan);
                        
                        
                        using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(Web.Configuration.DatabaseConnectionString))
                        {
                              conn.Open();

                              using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
                              {
                                    // Create command
                                    cmd.CommandText = sql;
                                    cmd.CommandType = System.Data.CommandType.Text;
                                    cmd.Connection = conn;

                              
                                    
                                    object resultObj = cmd.ExecuteScalar();

                                    if (resultObj != null)
                                    {
                                          credit = resultObj.ToString();
                                          balance.Text=string.Format("{0}",credit);
                                    }
                                    else{
                                          balance.Text="Name: N/A";
                                          }

                                    
                              }

                              conn.Close();
                        }

                  }
</script>
            
<asp:label id="balance" runat="server"/>
</asp:label>
0
 
daz1234Author Commented:
carl_tawn

i get an error message

 
Compiler Error Message: CS0246: The type or namespace name 'SqlDataReader' could not be found (are you missing a using directive or an assembly reference?)

Line 24:                              
Line 25:                                    
Line 26:                                     SqlDataReader reaer = cmd.ExecuteReader();
Line 27:
Line 28:                                     if (reader.Read())
 
0
 
Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented:
Change that line to:
System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();

Open in new window

0
 
daz1234Author Commented:
now i get

Exception Details: System.InvalidCastException: Specified cast is not valid.

Source Error:


Line 27:                                     {
Line 28:                                          do {
Line 29:                                           credit = reader.GetDouble(0).ToString();
Line 30:                                           balance.Text += string.Format("{0}<br />",credit);
Line 31:                                         } while (reader.Read());
 
0
 
Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented:
Well since we can only guess at the data type of your underlying data, just use:
credit = reader.GetValue(0).ToString();

Open in new window

0
 
daz1234Author Commented:
fantastic

worked like a treat

how would you excute script from a webpage

where the user enter the dial plan digits and then the submit button would excute the script
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.