Solved

how to use cmd.ExecuteReader

Posted on 2011-03-08
12
801 Views
Last Modified: 2012-05-11
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();
                        }
0
Comment
Question by:daz1234
12 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 35068510
0
 

Author Comment

by:daz1234
ID: 35068556
so how would i rewrite the syntax above i am no programmer

0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 35068586
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 53

Expert Comment

by:Dhaest
ID: 35068587
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
 
LVL 15

Expert Comment

by:angus_young_acdc
ID: 35068604
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
 

Author Comment

by:daz1234
ID: 35068661
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
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 35068694
                  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
 

Author Comment

by:daz1234
ID: 35068736
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
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 500 total points
ID: 35068768
Change that line to:
System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();

Open in new window

0
 

Author Comment

by:daz1234
ID: 35068817
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
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 500 total points
ID: 35068840
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
 

Author Closing Comment

by:daz1234
ID: 35068887
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

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
The viewer will learn how to dynamically set the form action using jQuery.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now