Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Almighty Expert Needed!!!!!!!

Posted on 2004-08-25
13
Medium Priority
?
533 Views
Last Modified: 2008-01-09
I am at my wits end with attempting to retrieve the values selected for a dropdown list control.  I am having no problems retrieving the text results of those values selected however; I cannot seem to get all of the values selected to show up.  Only the first value of the first item selected in the dropdownlist is retrieved.

Can someone please help me discover the woes of my code?

Here is the script for the control:
<asp:listbox id="Creator" runat="server" SelectionMode="Multiple" Rows="1"></asp:listbox>

Here is the script to gather the data selected from the control:
SqlConnection conn = new SqlConnection("server=C099450d01;uid=sa;pwd=;database=tools");
SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand("Select UID, LastName +','+ FirstName From Employee Where UID = @UID ORDER BY LastName", conn);
conn.Open();
foreach (ListItem myItem in Creator.Items)
{
if (myItem.Selected == true)
{
if (lblCreator.Text.Length > 0)
{
cmd.Parameters.Add("@UID", SqlDbType.Real).Value = Creator.SelectedValue;
lblCreator.Text += " - ";
}
lblCreator.Text += myItem.Text;

What is happening is that if I put a break point in the build located at the end of this script I can see that the "Creator.Items" shows me all of those selected from the ddl.  However if I mouseover the "Creator.SelectedValue" I am only seeing one value where I should be seeing a value for all selected.

Any help would be greatly appreciated.

Sincerely,
Tim
0
Comment
Question by:TMF123
  • 7
  • 4
13 Comments
 
LVL 1

Expert Comment

by:chmohan
ID: 11892681
did u try SelectedItem.Text instead of Value?
0
 

Author Comment

by:TMF123
ID: 11892873
The problem is that I do not want the text to be submitted to the db but instead the values that the text represent.  In other words, for each text item in the ddl there is an integer value because the ddl is populated by a table of employees.

Farrell, Timothy = 1
Sachs, Joel = 2

and so on...

My problem is I can't seem to extract the values of those items selected in the ddl.  Only the first value of the first item selected shows up.

I need to have the int value in the db so I can perform searches and queries against the column.

Tim
0
 
LVL 1

Expert Comment

by:chmohan
ID: 11894145
okay got u,use  a   datareader:)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:TMF123
ID: 11894777
Interesting...

Oddly enough it would appear that the code submitted is in vb not c#(where the user posted his question).

 I also need to make reference to c# since that is what I am using.  I will have to locate other resources for understanding how to implement a datareader for this purpose.
0
 
LVL 1

Expert Comment

by:chmohan
ID: 11895085
SqlDataReader myReader;
myReader = myCommand.ExecuteReader();
    // Always call Read before accessing data.
    while (myReader.Read()) {
     Combobox3.Items.Add(myReader.GetInt32(0) + ", " + myReader.GetString(1));
    }
    // always call Close when done reading.
    myReader.Close();


0
 

Author Comment

by:TMF123
ID: 11896471
This appears to be collecting the text values from the ddl:

Combobox3.Items.Add(myReader.GetInt32(0) + ", " + myReader.GetString(1));

lastname, firstname

Is there a way to get this to collect the values or the integers for all selected?

Currently I get an error stating:

System.Data.SqlClient.SqlException: Must declare the variable '@UID'.

Thank you for hanging in there.
0
 
LVL 1

Expert Comment

by:chmohan
ID: 11896565
oh okay got u

see this link and see function public void ReadData()

http://www.thecodeproject.com/useritems/SmartReader.asp

0
 
LVL 1

Expert Comment

by:chmohan
ID: 11896637
so i think u got to use  

Convert.ToInt32(Reader[“ UID”])    right?
0
 

Author Comment

by:TMF123
ID: 11896650
I get an error from the server attempting to access this page.
0
 
LVL 1

Accepted Solution

by:
chmohan earned 2000 total points
ID: 11896869
Introduction
SQLDataReader provides a way to access the records in forward direction from the database. A typed SQLDataReader would provide an interface to read the column data using friendly column names

 

SQLDataReader
.Net had provided a new way of different ways to access data based on various scenarios. SQLDataReader is one of the fastest ways of access SQL database as it reads data in one direction- forward read only.

 

Limitation
To read column value from SQLDataReader object, one needs to call the function with column ordinal as the input parameter. This is tedious as it requires changes in the column parameter again on any changes in the SQL column sequence

One could also read column values based on column names provided user explicitly cast the property to proper data type.

Solution
To counter these issues, I created a new class that would take input as column names and returns the value with appropriate data type same like using column ordinals. This class would also provide functionality to handle null values.

 

The Class SmartDataReader have the same function names but different signatures to access the column values as that of SQLDataReader. SmartDataReader class uses the SQLDataReader object that is initialized in the constructor of this class. The rest of call handling is done by calling the appropriate method of this object inside the class.

 

 

Example:

 

Different ways to access Int column(“EmpID”)

 

Using SQLDataReader(Column Ordinal)
 Reader.GetInt32(1)
 
Using SQLDataReader(Column Name)
 Convert.ToInt32(Reader[“EmpID”])
 
Using SmartSQLDataReader
 SmartReader.GetInt32(“EmpID”)
 


 

As you could see, to use the SQLDataReader with column names, we need to explicitly perform the proper casting. This is not the case with SmartDataReader which works same way as using column ordinals.

 

 

Sample:

 

public sealed class SmartDataReader

{

private DateTime defaultDate;

public SmartDataReader(SqlDataReader reader)

{

          this.defaultDate = DateTime.MinValue;

          this.reader = reader;

}

 

public int GetInt32(String column)

{

          int data = (reader.IsDBNull(reader.GetOrdinal(column))) ? (int)0 : (int)reader[column];

          return data;

}

 

public short GetInt16(String column)

{

          short data = (reader.IsDBNull(reader.GetOrdinal(column))) ? (short)0 : (short)reader[column];

          return data;

}

 

public float GetFloat(String column)

{

          float data = (reader.IsDBNull(reader.GetOrdinal(column))) ? 0 : float.Parse(reader[column].ToString());

          return data;

}

 

public bool GetBoolean(String column)

{

          bool data = (reader.IsDBNull(reader.GetOrdinal(column))) ? false : (bool)reader[column];

          return data;

}

 

public String GetString(String column)

{

          String data = (reader.IsDBNull(reader.GetOrdinal(column))) ? null : reader[column].ToString();

          return data;

}

 

public DateTime GetDateTime(String column)

{

          DateTime data = (reader.IsDBNull(reader.GetOrdinal(column))) ? defaultDate : (DateTime)reader[column];

          return data;

}

 

public bool Read()

{

          return this.reader.Read();

}

private SqlDataReader reader;

}

/// <summary>

/// Read from database using SmartDataReader

/// </summary>

public void ReadData()

{

          SqlConnection connection = new SqlConnection("Initial Catalog=Northwind;Data Source=Test;Integrated Security=SSPI;");

          SqlDataReader reader = null;

          try

          {

                   connection.Open();

                   string sql = "SELECT EmployeeID, FirstName, BirthDate FROM Employees";

                   SqlCommand command = new SqlCommand();

                   command.CommandText = sql;

                   command.Connection = connection;

                   reader = command.ExecuteReader();

                   SmartDataReader smartReader = new SmartDataReader(reader);

                   while(smartReader.Read())

                   {

         

                             int idSmart = smartReader.GetInt32("EmployeeID");

                             string nameSmart = smartReader.GetString("FirstName");

                             DateTime dateSmart = smartReader.GetDateTime("BirthDate");

 

                   }

          }

          catch(SqlException e)

          {

                   

                   Console.WriteLine(String.Format("An exception occurred : {0}. Please contact your system administrator.", e.Message));

          }

          finally

          {

                   if(reader != null) reader.Close();

                   if(connection != null) connection.Close();

 

          }

}

 

 

This sample opens a database connection to the SQL database. A SQLDataReader object is created based on the SQL query and this object is passed to the constructor of SmartDataReader class object. Once, SmartDataReader object is set, all table values are accessed by using SmartDataReader object

 


 


 

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month10 days, 13 hours left to enroll

885 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