Solved

how to use cmd.ExecuteReader

Posted on 2011-03-08
12
802 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Batch, VBS, and scripts in general are incredibly useful for repetitive tasks.  Some tasks can take a while to complete and it can be annoying to check back only to discover that your script finished 5 minutes ago.  Some scripts may complete nearly …
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

809 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