|
 |
aspx_beginners thread: Listbox SQL
Message #1 by jchristiana@k... on Tue, 7 Jan 2003 14:53:56
|
|
Hello. I am having some problems with my listbox. Here we go...
I have created a form that has a listbox that is hardcoded with 30
departments. When I do a CTRL and select 2 or more from the list and
submit it to my SQL database it only grabs the first on of what I had
Selected.
A
B
C
only A is sent to the database.
Here is the code of the first page.
>>>>
public string CSOOptionsgroups
{
get
{
return csooptionsgroups.SelectedItem.Text;
}
}
<asp:listbox id="csooptionsgroups"
rows="3"
selectionmode="multiple"
runat="server">
<asp:listitem>Admin and Support</asp:listitem>
<asp:listitem>Admitting</asp:listitem>
<asp:listitem>Admitting Nurses</asp:listitem>
<asp:listitem>AHS Outreach</asp:listitem>
</asp:listbox>
//I have shortend the list//
>>>>
************************************************************
Then when this is submited, it is sent to this page.
<%@ Page Language="C#" %>
<%@ Reference Page="ComputerSOForm.aspx" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="c#" runat="server">
CSOPageClass fp;
void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
fp = (CSOPageClass)Context.Handler;
}
string[] strArrayDetails = new string[27];
strArrayDetails[0] = fp.IdUni;
strArrayDetails[1] = fp.CSOEmployeenamefirst;
strArrayDetails[2] = fp.CSOEmployeenamelast;
strArrayDetails[3] = fp.CSOEmployeenamemiddle;
strArrayDetails[4] = fp.CSONickname;
strArrayDetails[5] = fp.CSOSsnumber;
strArrayDetails[6] = fp.CSODate;
strArrayDetails[7] = fp.CSOEmployeexpdate;
strArrayDetails[8] = fp.CSOPcdevice;
strArrayDetails[9] = fp.CSODepartment;
strArrayDetails[10] = fp.CSOPosition;
strArrayDetails[11] = fp.CSODateeffective;
strArrayDetails[12] = fp.CSOPersonreplacing;
strArrayDetails[13] = fp.CSOMirrorprofile;
strArrayDetails[14] = fp.CSOOptionswww;
strArrayDetails[15] = fp.CSOOptionsbh;
strArrayDetails[16] = fp.CSOOptionscsi;
strArrayDetails[17] = fp.CSOOptionsint;
strArrayDetails[18] = fp.CSOOptionsstm;
strArrayDetails[19] = fp.CSOOptionsstt;
strArrayDetails[20] = fp.CSOOptionsoutlook;
strArrayDetails[21] = fp.CSOOptionsics;
strArrayDetails[22] = fp.CSODepart;
strArrayDetails[23] = fp.CSOWorktimeuser;
strArrayDetails[24] = fp.CSOProvider;
strArrayDetails[25] = fp.CSOLocation;
strArrayDetails[26] = fp.CSOOptionsgroups;
SqlConnection thisConnection = new SqlConnection(
@"Data Source=KRMC-VPN;user id=HelpDesk;password=deleted;"
+
"Initial Catalog=CSO");
thisConnection.Open();
SqlDataAdapter thisAdapter = new SqlDataAdapter(
"SELECT * FROM CSOData", thisConnection);
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet, "CSOData");
DataRow thisRow = thisDataSet.Tables["CSOData"].NewRow();
thisRow["IdUni"] = (strArrayDetails[0]);
thisRow["CSOEmployeenamefirst"] = (strArrayDetails[1]);
thisRow["CSOEmployeenamelast"] = (strArrayDetails[2]);
thisRow["CSOEmployeenamemiddle"] = (strArrayDetails[3]);
thisRow["CSONickname"] = (strArrayDetails[4]);
thisRow["CSOSsnumber"] = (strArrayDetails[5]);
thisRow["CSODate"] = (strArrayDetails[6]);
thisRow["CSOEmployeexpdate"] = (strArrayDetails[7]);
thisRow["CSOPcdevice"] = (strArrayDetails[8]);
thisRow["CSODepartment"] = (strArrayDetails[9]);
thisRow["CSOPosition"] = (strArrayDetails[10]);
thisRow["CSODateeffective"] = (strArrayDetails[11]);
thisRow["CSOPersonreplacing"] = (strArrayDetails[12]);
thisRow["CSOMirrorprofile"] = (strArrayDetails[13]);
thisRow["CSOOptionswww"] = (strArrayDetails[14]);
thisRow["CSOOptionsbh"] = (strArrayDetails[15]);
thisRow["CSOOptionscsi"] = (strArrayDetails[16]);
thisRow["CSOOptionsint"] = (strArrayDetails[17]);
thisRow["CSOOptionsstm"] = (strArrayDetails[18]);
thisRow["CSOOptionsstt"] = (strArrayDetails[19]);
thisRow["CSOOptionsoutlook"] = (strArrayDetails[20]);
thisRow["CSOOptionsics"] = (strArrayDetails[21]);
thisRow["CSODepart"] = (strArrayDetails[22]);
thisRow["CSOWorktimeuser"] = (strArrayDetails[23]);
thisRow["CSOProvider"] = (strArrayDetails[24]);
thisRow["CSOLocation"] = (strArrayDetails[25]);
thisRow["CSOOptionsgroups"] = (strArrayDetails[26]);
thisDataSet.Tables["CSOData"].Rows.Add(thisRow);
thisAdapter.Update (thisDataSet, "CSOData");
thisConnection.Close();
}
</script>
<html>
<head>
<title>Form has been Submitted</title>
</head>
<body>
<%
Response.Redirect("http://10.6.3.2:68/FormSubmitsuccess.aspx");
%>
<br />
</body>
</html>
Can anyone help here.
Thanks.
Message #2 by jchristiana@k... on Wed, 8 Jan 2003 13:22:37
|
|
I found this example. How can I display all that is selected? I am working
on a program that requires all that is selected to be passed to my SQL
database. I have looked through the books and cannot find anything to help
me. I am passing only one value right now. And this sample asp listbox is
basically what it is doing. Is there somewhere I can see an example or is
it a simple simple fix to pass all selections to the screen/database?
<%@ Page Language="C#" %>
<html>
<head>
<script runat="server">
void SubmitBtn_Click(Object sender, EventArgs e)
{
if (ListBox1.SelectedIndex > -1)
Label1.Text="The first item you chose: " +
ListBox1.SelectedItem.Text;
}
</script>
</head>
<body>
<form runat="server">
<h3>ListBox Example</h3>
<asp:ListBox id="ListBox1"
Rows="4"
SelectionMode="Multiple"
Width="100px"
runat="server">
<asp:ListItem>Item 1</asp:ListItem>
<asp:ListItem>Item 2</asp:ListItem>
<asp:ListItem>Item 3</asp:ListItem>
<asp:ListItem>Item 4</asp:ListItem>
<asp:ListItem>Item 5</asp:ListItem>
<asp:ListItem>Item 6</asp:ListItem>
</asp:ListBox>
<asp:button id="Button1"
Text="Submit"
OnClick="SubmitBtn_Click"
runat="server" />
<p>
<asp:Label id="Label1"
Font-Name="Verdana"
Font-Size="10pt"
runat="server"/>
</form>
</body>
</html>
Message #3 by "Jack" <jdunstan7@h...> on Wed, 8 Jan 2003 12:21:29 -0500
|
|
Well, looking at your code, it would appear only one is sent because that's all you retrieve, i.e.
public string CSOOptionsgroups
{
get
{
return csooptionsgroups.SelectedItem.Text;
}
}
** above returns the selecteditem.text
** below gets it but only one
strArrayDetails[26] = fp.CSOOptionsgroups;
Your code adds a row based on the data you retrieve using the " fp" parameter, and then uses it to set values in
the"strArrayDetails" array. If you are going to update your database base on the fp parameter, and its possible that the last
parameter, e.g. fp.CSOOptionsgroups will have multiple values, then you need a method by which you can test/retrieve these multiple
values.
Two things to do:
1) provide a means to store and/or retrieve the multiple selections of the listbox
you could try making this a string array that is returned
2) when you do your update, you'll need to allow for a loop to update which each of the items selected
Other comments:
- Unless you need to display the dataset, why do bother grabbing all the records in the database with this sql "Select * from
CSOData" ? If you are only adding data couldn't you use a command object and a sql insert string?
- If its possible that there could be "many" "CSOOptionsgroups" to one "IdUni" (assuming that's your primary key) then it would
appear you CSOData table isn't normalized. If this is the case perhaps you should consider adding two tables - 1 for the " 30
departments " and 2) a reference table that ties the CSOData to the CSODepartments. You could then use the CSODepartment table to
fill (bind ) to your list box (instead of hardcoding it) and the insert update with regard to CSOOptionsgroups would be a simple
insert of the idUni and the CSODepartment table id (which is the id you could get from the list box).
Hope this helps
Jack
----- Original Message -----
From: jchristiana@k...
To: aspx_beginners
Sent: Tuesday, January 07, 2003 2:53 PM
Subject: [aspx_beginners] Listbox SQL
Hello. I am having some problems with my listbox. Here we go...
I have created a form that has a listbox that is hardcoded with 30
departments. When I do a CTRL and select 2 or more from the list and
submit it to my SQL database it only grabs the first on of what I had
Selected.
A
B
C
only A is sent to the database.
Here is the code of the first page.
>>>>
public string CSOOptionsgroups
{
get
{
return csooptionsgroups.SelectedItem.Text;
}
}
<asp:listbox id="csooptionsgroups"
rows="3"
selectionmode="multiple"
runat="server">
<asp:listitem>Admin and Support</asp:listitem>
<asp:listitem>Admitting</asp:listitem>
<asp:listitem>Admitting Nurses</asp:listitem>
<asp:listitem>AHS Outreach</asp:listitem>
</asp:listbox>
//I have shortend the list//
>>>>
************************************************************
Then when this is submited, it is sent to this page.
<%@ Page Language="C#" %>
<%@ Reference Page="ComputerSOForm.aspx" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="c#" runat="server">
CSOPageClass fp;
void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
fp = (CSOPageClass)Context.Handler;
}
string[] strArrayDetails = new string[27];
strArrayDetails[0] = fp.IdUni;
strArrayDetails[1] = fp.CSOEmployeenamefirst;
strArrayDetails[2] = fp.CSOEmployeenamelast;
strArrayDetails[3] = fp.CSOEmployeenamemiddle;
strArrayDetails[4] = fp.CSONickname;
strArrayDetails[5] = fp.CSOSsnumber;
strArrayDetails[6] = fp.CSODate;
strArrayDetails[7] = fp.CSOEmployeexpdate;
strArrayDetails[8] = fp.CSOPcdevice;
strArrayDetails[9] = fp.CSODepartment;
strArrayDetails[10] = fp.CSOPosition;
strArrayDetails[11] = fp.CSODateeffective;
strArrayDetails[12] = fp.CSOPersonreplacing;
strArrayDetails[13] = fp.CSOMirrorprofile;
strArrayDetails[14] = fp.CSOOptionswww;
strArrayDetails[15] = fp.CSOOptionsbh;
strArrayDetails[16] = fp.CSOOptionscsi;
strArrayDetails[17] = fp.CSOOptionsint;
strArrayDetails[18] = fp.CSOOptionsstm;
strArrayDetails[19] = fp.CSOOptionsstt;
strArrayDetails[20] = fp.CSOOptionsoutlook;
strArrayDetails[21] = fp.CSOOptionsics;
strArrayDetails[22] = fp.CSODepart;
strArrayDetails[23] = fp.CSOWorktimeuser;
strArrayDetails[24] = fp.CSOProvider;
strArrayDetails[25] = fp.CSOLocation;
strArrayDetails[26] = fp.CSOOptionsgroups;
SqlConnection thisConnection = new SqlConnection(
@"Data Source=KRMC-VPN;user id=HelpDesk;password=deleted;"
+
"Initial Catalog=CSO");
thisConnection.Open();
SqlDataAdapter thisAdapter = new SqlDataAdapter(
"SELECT * FROM CSOData", thisConnection);
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet, "CSOData");
DataRow thisRow = thisDataSet.Tables["CSOData"].NewRow();
thisRow["IdUni"] = (strArrayDetails[0]);
thisRow["CSOEmployeenamefirst"] = (strArrayDetails[1]);
thisRow["CSOEmployeenamelast"] = (strArrayDetails[2]);
thisRow["CSOEmployeenamemiddle"] = (strArrayDetails[3]);
thisRow["CSONickname"] = (strArrayDetails[4]);
thisRow["CSOSsnumber"] = (strArrayDetails[5]);
thisRow["CSODate"] = (strArrayDetails[6]);
thisRow["CSOEmployeexpdate"] = (strArrayDetails[7]);
thisRow["CSOPcdevice"] = (strArrayDetails[8]);
thisRow["CSODepartment"] = (strArrayDetails[9]);
thisRow["CSOPosition"] = (strArrayDetails[10]);
thisRow["CSODateeffective"] = (strArrayDetails[11]);
thisRow["CSOPersonreplacing"] = (strArrayDetails[12]);
thisRow["CSOMirrorprofile"] = (strArrayDetails[13]);
thisRow["CSOOptionswww"] = (strArrayDetails[14]);
thisRow["CSOOptionsbh"] = (strArrayDetails[15]);
thisRow["CSOOptionscsi"] = (strArrayDetails[16]);
thisRow["CSOOptionsint"] = (strArrayDetails[17]);
thisRow["CSOOptionsstm"] = (strArrayDetails[18]);
thisRow["CSOOptionsstt"] = (strArrayDetails[19]);
thisRow["CSOOptionsoutlook"] = (strArrayDetails[20]);
thisRow["CSOOptionsics"] = (strArrayDetails[21]);
thisRow["CSODepart"] = (strArrayDetails[22]);
thisRow["CSOWorktimeuser"] = (strArrayDetails[23]);
thisRow["CSOProvider"] = (strArrayDetails[24]);
thisRow["CSOLocation"] = (strArrayDetails[25]);
thisRow["CSOOptionsgroups"] = (strArrayDetails[26]);
thisDataSet.Tables["CSOData"].Rows.Add(thisRow);
thisAdapter.Update (thisDataSet, "CSOData");
thisConnection.Close();
}
</script>
<html>
<head>
<title>Form has been Submitted</title>
</head>
<body>
<%
Response.Redirect("http://10.6.3.2:68/FormSubmitsuccess.aspx");
%>
<br />
</body>
</html>
Can anyone help here.
Thanks.
Message #4 by jchristiana@k... on Fri, 10 Jan 2003 14:58:19
|
|
Thanks for your help. I thought that the value that was returned was
something like this:
If A, B, and C are selected shouldent the submited value be
A;B;C and then I would only have to grab one value? Because it would
combine all selected options into one value.
I am happy with just returning that value to the database.
I didnt realize how much effort this simple listbox would be.
Is there any good examples on the wrox boox on this? I will try to add a
loop as you suggested.
> Well, looking at your code, it would appear only one is sent because
that's all you retrieve, i.e.
public string CSOOptionsgroups
{
get
{
return csooptionsgroups.SelectedItem.Text;
}
}
** above returns the selecteditem.text
** below gets it but only one
strArrayDetails[26] = fp.CSOOptionsgroups;
Your code adds a row based on the data you retrieve using the " fp"
parameter, and then uses it to set values in the"strArrayDetails" array.
If you are going to update your database base on the fp parameter, and its
possible that the last parameter, e.g. fp.CSOOptionsgroups will have
multiple values, then you need a method by which you can test/retrieve
these multiple values.
Two things to do:
1) provide a means to store and/or retrieve the multiple selections of the
listbox
you could try making this a string array that is returned
2) when you do your update, you'll need to allow for a loop to update
which each of the items selected
Other comments:
- Unless you need to display the dataset, why do bother grabbing all the
records in the database with this sql "Select * from CSOData" ? If you are
only adding data couldn't you use a command object and a sql insert string?
- If its possible that there could be "many" "CSOOptionsgroups" to
one "IdUni" (assuming that's your primary key) then it would appear you
CSOData table isn't normalized. If this is the case perhaps you should
consider adding two tables - 1 for the " 30 departments " and 2) a
reference table that ties the CSOData to the CSODepartments. You could
then use the CSODepartment table to fill (bind ) to your list box (instead
of hardcoding it) and the insert update with regard to CSOOptionsgroups
would be a simple insert of the idUni and the CSODepartment table id
(which is the id you could get from the list box).
Hope this helps
Jack
|
|
|
View Cart
