dropdownlist fomr SQL data...

Stelly · 1 Feb 2007 at 11:05

Hi all...

Sorry about this... got another question for ya.. whats wrong with this..

string connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
System.Data.SqlClient.SqlConnection sqlconnection = new System.Data.SqlClient.SqlConnection(connectionString);

string queryString = "SELECT MAX ([book].[PriKey]) FROM [book]";
System.Data.SqlClient.SqlCommand Mycommand = new System.Data.SqlClient.SqlCommand(queryString, sqlconnection);

System.Data.SqlClient.SqlDataReader dr;
dr = Mycommand.ExecuteReader(CommandBehavior.CloseConnection);

while (dr.Read())
{
DropDownList ModelNumber = (DropDownList)Form.FindControl("ModelNumber");
ModelNumber.Items.Add(new ListItem((String)dr["FirstName"] + dr["LastName"], ""));
}
dr.Close();

the error I'm getting is this...

ExecuteReader requires an open and available Connection. The connection's current state is closed.

Thanks again...

Stelly

JonB · 1 Feb 2007 at 11:16

Second line, remove the space between the 'i' and the 'n' in the word string

Stelly · 1 Feb 2007 at 11:27

JonB said:
Second line, remove the space between the 'i' and the 'n' in the word string

How did I not spot that... I need to get to bed earlier!

Stelly

Stelly · 1 Feb 2007 at 11:29

not thats not it

Stelly

JonB · 1 Feb 2007 at 12:18

Sorry, can't help any further. I'm only really clued up on SQL syntax.

The error implies that you have not connected to the database properly though! (But then you already probably knew that!)

Hope this helps!

Haircut · 1 Feb 2007 at 13:28

I think the error message pretty much says it all.

You have created a new connection, but you never open it.
Try calling the .Open() method on your connection once it is created.

Stelly · 1 Feb 2007 at 13:50

Haircut said:
I think the error message pretty much says it all.

You have created a new connection, but you never open it.
Try calling the .Open() method on your connection once it is created.

Yer done that... still getting an error though...

Stelly

Haircut · 1 Feb 2007 at 13:55

What's the error message you are getting now then?

Stelly · 1 Feb 2007 at 14:03

ok have this...

if (!Page.IsPostBack)
{
BookingList.DataSource = SQLQueries.GetNonApp();
BookingList.DataBind();
//SQLQueries.PopulateList();

}

string connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
System.Data.SqlClient.SqlConnection sqlconnection = new System.Data.SqlClient.SqlConnection(connectionString);

string queryString = "SELECT ([Resource].[ModelNumber]) FROM ([Resource])";
System.Data.SqlClient.SqlCommand Mycommand = new System.Data.SqlClient.SqlCommand(queryString, sqlconnection);

System.Data.SqlClient.SqlDataReader dr;
Mycommand.Connection.Open();
dr = Mycommand.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
DropDownList ModelNumber = (DropDownList)form1.FindControl("ModelNumber");
ModelNumber.Items.Add((new ListItem((String)dr["FirstName"] + dr["LastName"], "")));
}
dr.Close();

getting this...

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near ')'.

Source Error:

Line 24: System.Data.SqlClient.SqlDataReader dr;
Line 25: Mycommand.Connection.Open();
Line 26: dr = Mycommand.ExecuteReader(CommandBehavior.CloseConnection);
Line 27: while (dr.Read())
Line 28: {

Thanks a lot for the help mate

Stelly

Haircut · 1 Feb 2007 at 14:19

The Incorrect syntax near ')' seems like a SQL Server error.
Are you sure that the table name should be enclosed in parentheses?

Stelly · 1 Feb 2007 at 14:29

yer worked it out.... missed an 's' off a table name...

now there is a problem with

DropDownList ModelNumber = (DropDownList)form1.FindControl("ModelNumber");
ModelNumber.Items.Add((new ListItem((String)dr["ModelNumber"])));

I was thinking with the dropdownlist is that its a child control of the FormView, it is not accessible directly. You have to locate the control by using FindControl() method.

but its now coming up with...

Object reference not set to an instance of an object.

ARGH!

Stelly

Dr_Evil · 1 Feb 2007 at 14:36

How about doing this in the front-end like this:

<asp:SqlDataSource ID="myDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:myCS %>" SelectCommand="SELECT ID, Name FROM vw_my_View"></asp:SqlDataSource>

<asp:DropDownList runat="server" ID="myDropDown" DataTextField="Name" DataValueField="ID" DataSourceID="myDataSource"></asp:DropDownList>

isn't this just much easier?

Stelly · 1 Feb 2007 at 14:39

Dr_Evil said:
How about doing this in the front-end like this:

<asp:SqlDataSource ID="myDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:myCS %>" SelectCommand="SELECT ID, Name FROM vw_my_View"></asp:SqlDataSource>

<asp:DropDownList runat="server" ID="myDropDown" DataTextField="Name" DataValueField="ID" DataSourceID="myDataSource"></asp:DropDownList>

isn't this just much easier?

Would that work in a datagrid??

Stelly

Dr_Evil · 1 Feb 2007 at 14:41

Stelly said:
Would that work in a datagrid??

it works fine in my gridview. You'll have to define TemplateFields, then an ItemTemplate and an EditItemTemplate. I place normal labels in the ItemTemplate, and have the dropdown in the EditItemTemplate.

are you using .Net 2.0 ?

Stelly · 1 Feb 2007 at 14:49

Dr_Evil said:
it works fine in my gridview. You'll have to define TemplateFields, then an ItemTemplate and an EditItemTemplate. I place normal labels in the ItemTemplate, and have the dropdown in the EditItemTemplate.

are you using .Net 2.0 ?

Yer all I want to do is to populate a dropdownlist from an SQL table... is that too much to ask lol... any ideas?

Stelly

Dr_Evil · 1 Feb 2007 at 15:11

Stelly said:
Yer all I want to do is to populate a dropdownlist from an SQL table... is that too much to ask lol...

You asked if it works in a DataGrid, I answered. Ofcourse you can also use this in your page.

L33 · 1 Feb 2007 at 15:12

Your current method of defining the connectionstring in the web.config rather than directly in the code-behind file is the correct way of doing things, so keep with that

private string connectionString = WebConfigurationManager.ConnectionStrings["whatever"].ConnectionString

protected void Page_Load()
{
if (!this.IsPostBack)
fillListBox();
}

private void fillListBox()
{
lstModelNumber.Items.Clear();

string selectSQL = "SELECT MAX ([book].[PriKey]) FROM [book]";

SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(selectSQL, con);
SqlDataReader reader;

try
{
con.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
ListItem newItem = new ListItem();
newItem.Text = reader["lastname"] + ", " + ["firstname"];
lstModelNumber.Items.Add(newItem);
}
reader.Close()
catch (Exception ex)
{
//do whatever you want........
}
}

Stelly · 1 Feb 2007 at 15:14

L33 said:
Your current method of defining the connectionstring in the web.config rather than directly in the code-behind file is the correct way of doing things, so keep with that

private string connectionString = WebConfigurationManager.ConnectionStrings["whatever"].ConnectionString

protected void Page_Load()
{
if (!this.IsPostBack)
fillListBox();
}

private void fillListBox()
{
lstModelNumber.Items.Clear();

string selectSQL = "SELECT MAX ([book].[PriKey]) FROM [book]";

SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(selectSQL, con);
SqlDataReader reader;

try
{
con.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
ListItem newItem = new ListItem();
newItem.Text = reader["lastname"] + ", " + ["firstname"];
lstModelNumber.Items.Add(newItem);
}
reader.Close()
catch (Exception ex)
{
//do whatever you want........
}
}

but that doesnt solve the problem of the dropdownlist... right?

Stelly

Dr_Evil · 1 Feb 2007 at 16:20

Stelly,

trust me - I tried doing things this way first, then I found out you could create those type of controls much easier in using <asp: tags. You can still access them in your code-behind, as they are server-controls. What you are trying to do is the long way around....

1. Create an ASP:SQLDatasource, use your connectionstring stored in your web.config, as you are doing now

2. Create an ASP

ropdown and set the DataSourceID to your SQLDatasource

that's all needed to get a drop-down list populated with values from a database.

Stelly · 1 Feb 2007 at 16:44

Dr_Evil said:
Stelly,

trust me - I tried doing things this way first, then I found out you could create those type of controls much easier in using <asp: tags. You can still access them in your code-behind, as they are server-controls. What you are trying to do is the long way around....

1. Create an ASP:SQLDatasource, use your connectionstring stored in your web.config, as you are doing now

2. Create an ASPropdown and set the DataSourceID to your SQLDatasource

that's all needed to get a drop-down list populated with values from a database.

Cheer mate... have already started

Stelly