Problem using DataSet in C# and ASP.NET

.NET
1

I am trying to fill a dataSet with rows from a table in my SQL Server Express 2008 and I keep getting an error.

Error: Must declare the scalar variable “@city”.


using System;
using System.Data;
using System.Data.SqlClient;

class ParamDemo
{
     static void Main()
     {
          // conn and reader declared outside try
          // block for visibility in finally block
          SqlConnection conn = null;
          SqlDataReader reader = null;

          string inputCity = "Redmond";
          
          try
          {
               //SqlConnection conn = null;

               // instantiate and open connection
               conn = new
                    SqlConnection(@"Server=Server\\SQLEXPRESS;Database=HotelEnterprise;Trusted_Connection=True");
               conn.Open();


            
               

               SqlCommand cmd = new SqlCommand("select * from Customers where city = @city", conn);

               cmd.CommandType = CommandType.Text;
               //cmd.CommandText = SqlDbType.Text; 

               cmd.Parameters.AddWithValue("@city", inputCity);
               
               SqlDataAdapter adCust = new SqlDataAdapter(cmd.CommandText, conn);

              
               string id = (string)cmd.ExecuteScalar();

               Console.WriteLine("{0}", id);


               DataSet ds = new DataSet();
               
               [COLOR="Red"]adCust.Fill(ds, "Customers");[/COLOR]

               //DataTable RoomsTable = ds.Tables[0];

               //Console.WriteLine("Rows: {0}", RoomsTable.Rows.Count);


               //foreach (DataRow row in RoomsTable.Rows)
               //{
               //     Console.WriteLine("{0}", row["city"]);
               //}

          }
          finally
          {
               // close reader
               if (reader != null)
               {
                    reader.Close();
               }

               // close connection
               if (conn != null)
               {
                    conn.Close();
               }
          }

          Console.ReadKey();
     }
}

The error happens at the adCust.Fill command and I am unsure why. If the variable @city was not declared than why did the WriteLine command work. I would appreciate any help on this.

:confused:

2

its a T-SQL error

3

OKay, I changed the code to


             [COLOR="Red"]SqlCommand cmd = new SqlCommand("declare @city varchar(50); select * from Customers where city = @city;", conn);
[/COLOR]
               cmd.CommandType = CommandType.Text;
               //cmd.CommandText = SqlDbType.Text; 

               cmd.Parameters.AddWithValue("@city", inputCity);
               
               SqlDataAdapter adCust = new SqlDataAdapter(cmd.CommandText, conn);

              
               //string id = (string)cmd.ExecuteScalar();

               //Console.WriteLine("{0}", id);

               

               DataSet ds = new DataSet();
               
               adCust.Fill(ds, "Customers");

               DataTable RoomsTable = ds.Tables[0];

               Console.WriteLine("Rows: {0}", RoomsTable.Rows.Count);


               foreach (DataRow row in RoomsTable.Rows)
               {
                    Console.WriteLine("{0}", row["city"]);
               }

It passes the adCust.Fill now, but it does not return any rows like it should.

In TSQL I would have used a

set @city = inputCity;

but is I thought

cmd.Parameters.AddWithValue("@city", inputCity);

would do the same thing. What am I missing?

:confused:

4

Okay, I figured it out and the code I used is below.

:slight_smile:


static void Main()
     {
          // conn and reader declared outside try
          // block for visibility in finally block
          SqlConnection conn = null;
          
          string inputCity = "Redmond";
          //string @City = inputCity;

          try
          {

               // instantiate and open connection
               conn = new
                    SqlConnection(@"Server=Server\\SQLEXPRESS;Database=HotelEnterprise;Trusted_Connection=True");
        
               SqlCommand cmd = new SqlCommand("select * from Customers where city = @city;", conn);
               cmd.Parameters.AddWithValue("@city", inputCity);

               SqlDataAdapter adCust = new SqlDataAdapter();
               adCust.SelectCommand = cmd;
               DataSet ds = new DataSet();
               adCust.Fill(ds, "Customers");

               DataTable RoomsTable = ds.Tables[0];

               foreach (DataRow row in RoomsTable.Rows)
               {
                    Console.WriteLine("{0}", row["city"]);
               }


          }
          catch
          {

          }
          finally
          {
               
               // close connection
               if (conn != null)
               {
                    conn.Close();
               }
          }

          Console.ReadKey();
     }