Building a Dynamically Dropdown From SQL Table

Hello Everyone,

I need some assistance in getting a dynamically dropdown build from a sql table. I'm using a store procedure to add the selected value to my sql database table.

I got the code that builds the dropdown list but when I try to select an item from the drop down I get an error. "Input string was not in a correct format."

My guess is that when I select an item from the list is not passing the value id to store procedure?

I know I have something wrong, But I could not figure out what is wrong.

Please help!

Here is my Code:


@ Import Namespace = "System.Data" %>


<%@ Import Namespace = "System.Data.SqlClient" %>


<%@ Page Language="vb" Debug="true" %>


<Script runat="server">


Sub Page_Load(Sender As Object, E As EventArgs)


'1. Create a connection


Dim myConnection = New SqlConnection("Data Source=local;User Id=user;Password=password;Initial Catalog=Calendar")



'2. Create the command object, passing in the SQL string


Const strSQL as String = "SELECT TypeID, TypeDescription AS Description " & _


"FROM EventTypes ORDER BY Description"


Dim myCommand as New SqlCommand(strSQL, myConnection)





'3. Create the DataReader


myConnection.Open()





Dim objDR as SqlDataReader


objDR = myCommand.ExecuteReader(CommandBehavior.CloseConne ction)





'Databind the DataReader to the listbox Web control


EventTypeID.DataSource = objDR


EventTypeID.DataBind()





'Add a new listitem to the beginning of the listitemcollection


EventTypeID.Items.Insert(0, new ListItem("-- Choose a FAQ --"))








'Set the calendar to today's date


If Not(Page.IsPostBack) Then


EventDate.SelectedDate = EventDate.TodaysDate


End if


End Sub





Sub Do_Insert(Sender As Object, e As EventArgs)


'Only update the database if the user entered valid inputs





If Not Page.IsValid then Exit Sub





'//Add the event to the database





Dim myConnection As New SqlConnection("Data Source=local;User Id=user;Password=password;Initial Catalog=Calendar")


Dim myCommand As New SqlCommand("Events_sp_Item_Insert", myConnection)





myCommand.CommandType = CommandType.StoredProcedure





Dim parameterEventDate As New SqlParameter("@EventDate", SqlDbType.DateTime, 8)


'I used the simplest version of the calendar control to allow the user to pick the date.


'So, to retrieve the value from the control, I need to get the value of the SelectedDate property


parameterEventDate.Value = EventDate.SelectedDate


myCommand.Parameters.Add(parameterEventDate)





Dim parameterEventDescription As New SqlParameter("@EventDescription", SqlDbType.NvarChar, 255)


parameterEventDescription.Value = EventDescription.Text


myCommand.Parameters.Add(parameterEventDescription )





'//This is where I think my code is wrong


Dim parameterEventTypeID As New SqlParameter("@EventTypeID", SqlDbType.Int, 4)


parameterEventTypeID.Value = EventTypeID.SelectedItem.Value


'//Response.Write(EventTypeID.Selected.Value)


'//parameterEventTypeID.Value = EventTypeID.SelectedList.Value


myCommand.Parameters.Add(parameterEventTypeID)





Dim parameterEventStartTime As New SqlParameter("@EventStartTime", SqlDbType.VarChar, 50)


parameterEventStartTime.Value = EventStartTime.Text


myCommand.Parameters.Add(parameterEventStartTime)





Dim parameterEventEndTime As New SqlParameter("@EventEndTime", SqlDbType.VarChar, 50)


parameterEventEndTime.Value = EventEndTime.Text


myCommand.Parameters.Add(parameterEventEndTime)





Dim parameterEventPhone As New SqlParameter("@EventPhone", SqlDbType.VarChar, 50)


parameterEventPhone.Value = EventPhone.Text


myCommand.Parameters.Add(parameterEventPhone)








myConnection.Open()


myCommand.ExecuteNonQuery()


myConnection.Close()


response.redirect("calendar.aspx")


End Sub


</script>




<table width="60%" border="1" cellpadding="0" cellspacing="0" bordercolor="#003366" bgcolor="#d0dae3">


<tr>


<td>&nbsp;


<form runat="server">


<table border="0" cellpadding="0" cellspacing="0">


<tr>


<td colspan="2" valign="top">


<a href="calendar.aspx" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('Image70','','../calendar/images/smtadmincalendar.gif',1)">


<img src="../calendar/images/smtadmincalendar-over.gif" alt="Calendar" name="Image70" width="108" height="33" border="0">


</a>


<a href="add.aspx" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('Image71','','../calendar/images/smtadminadd.gif',1)">


<img src="../calendar/images/smtadminadd.gif" alt="Add" name="Image71" width="108" height="33" border="0">


</a>


<a href="delete.aspx" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('Image72','','../calendar/images/smtadmindelete.gif',1)">


<img src="../calendar/images/smtadmindelete-over.gif" alt="Delete" name="Image72" width="108" height="33" border="0">


</a>


</td>


</tr>


<tr bgcolor="#7b88a9">


<td colspan="2" valign="top">&nbsp;</td>


</tr>


<tr>


<td valign="top">&nbsp;</td>


<td>&nbsp;</td>


</tr>


<tr>


<td width="157" valign="top" nowrap>


<div align="right"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Description:&nbsp;</font></div>


</td>


<td width="535">


<asp:TextBox width="300" Font-Name="Verdana" id="EventDescription" runat="server"/>


<asp:RequiredFieldValidator id="rfvEventDescription" ControlToValidate="EventDescription" runat="server">* This is a required field</asp:RequiredFieldValidator>


<br> <br>


</td>


</tr>


<tr>


<td valign="top" nowrap>


<div align="right">


<font size="2" face="Verdana, Arial, Helvetica, sans-serif">Date:&nbsp;</font>


</div>


</td>


<td valign="top">


<asp:calendar id="EventDate"


BorderStyle="Dashed"


BorderColor="#7b88a9"


TitleStyle-Font-Name="Verdana"


TitleStyle-Font-Size="10"


DayStyle-Font-Name="Verdana"


DayStyle-Font-Size="10"


NextPrevStyle-Font-Name="Verdana"


NextPrevStyle-Font-Size="10"


DayHeaderStyle-Font-Name="Verdana"


DayHeaderStyle-Font-Size="10"


DayHeaderStyle-ForeColor="#CC0000"


runat="server"/>


<br> <br>


</td>


</tr>


<tr>


<td valign="top" nowrap><div align="right">


<font size="2" face="Verdana, Arial, Helvetica, sans-serif">Type:&nbsp;</font></div>


</td>


<td valign="top">


<asp:dropdownlist id="EventTypeID"


DataTextField ="Description"


DataValueField="TypeID"


Font-Name="Verdana"


runat="server" />


<asp:RequiredFieldValidator id="rfvEventTypeID" ControlToValidate="EventTypeID" runat="server">* This is a required field</asp:RequiredFieldValidator>


<br><%Response.Write(EventTypeID)%>><br></td>


</tr>


<tr>


<td valign="top" nowrap><div align="right"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Start


Time:&nbsp;</font></div></td>


<td valign="top">


<asp:TextBox id="EventStartTime" Font-Name="Verdana" runat="server"/>


<asp:RequiredFieldValidator id="rfvEventStartTime" ControlToValidate="EventStartTime" runat="server">* This is a required field</asp:RequiredFieldValidator>


<br> <br>


</td>


</tr>


<tr>


<td valign="top" nowrap>


<div align="right"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">End


Time:&nbsp;</font>


</div>


</td>


<td valign="top">


<asp:TextBox id="EventEndTime" Font-Name="Verdana" runat="server"/>


<asp:RequiredFieldValidator id="rfvEventEndTime" ControlToValidate="EventEndTime" runat="server">* This is a required field</asp:RequiredFieldValidator>


<br> <br>


</td>


</tr>


<tr>


<td valign="top" nowrap><div align="right"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">Phone:&nbsp;</font></div></td>


<td valign="top">


<asp:TextBox id="EventPhone" Font-Name="Verdana" runat="server"/>


<asp:RequiredFieldValidator id="rfvEventPhone" ControlToValidate="EventPhone" runat="server">* This is a required field</asp:RequiredFieldValidator>


<br> <br>


</td>


</tr>


<tr bgcolor="#7b88a9">


<td colspan="2" valign="top">&nbsp;</td>


</tr>


<tr>


<td valign="top">&nbsp;</td>


<td align="center"><asp:Button id="Submit" text="Submit" OnClick="Do_Insert" runat="server"/></td>


</tr>


</table>


</form>


</td>


</tr>


</table>


Thank you