Listbox HELP please

[jlateehs]

Here's my code...there's just something that doesn't work. Right now I'm getting an error in the data bind about an open connection! Thanks in advance for the help!

<%@ Page Title="" Language="VB" MasterPageFile="admin.master" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

<script runat="server">
Sub Page_Load(sender as Object, e as EventArgs)
BindData()

End Sub

Sub BindData()
Const sConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\sheetalk\Documents\Database3.accdb"
Dim objConn As New OleDbConnection(sConnStr)

objConn.Open()

Const strSQL As String = "SELECT Show_ID, Show_Name FROM Shows Order By Show_Name"
Dim objCmd As New OleDbCommand(strSQL, objConn)

'Create/Populate the DataReader
'Dim objDR As OleDbDataReader
objDR = objCmd.ExecuteReader()
Showslistbox.DataSource = objCmd.ExecuteReader(CommandBehavior.CloseConnection)
Showslistbox.DataBind()
End Sub
Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim conCoaxis As OleDbConnection
Dim result As Integer = 0
Dim pickvalue As String = Showslistbox.SelectedItem.Value
Dim i As Integer = Convert.ToInt16(pickvalue)
Dim strSelect As String
Dim strInsert As String
Dim cmdSelect As OleDbCommand
Dim cmdInsert As OleDbCommand
conCoaxis = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\sheetalk\Documents\Database3.accdb")
strSelect = "Select ID from Users WHERE Username = '" & Session("User") & "'"
cmdSelect = New OleDbCommand(strSelect, conCoaxis)

strInsert = "INSERT into Affidavits (show_id, affidavit_air_date, affidavit_air_time, user_id) Values (@showid, @affDate, @affTime," & result & ")"
cmdInsert = New OleDbCommand(strInsert, conCoaxis)

cmdInsert.Parameters.AddWithValue("@affDate", affDate.Text)
cmdInsert.Parameters.AddWithValue("@affTime", affTime.Text)
cmdInsert.Parameters.AddWithValue("@showid", i)


conCoaxis.Open()
result = DirectCast(cmdSelect.ExecuteScalar(), Integer)

cmdInsert.ExecuteNonQuery()

conCoaxis.Close()
End Sub

</script>

<asp:Content ID="Content1" ContentPlaceHolderID="cphhead" Runat="Server">

</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="cphMain" Runat="Server">
<h1>Affidavit</h1>
<table>
<tr>
<td>Shows: </td>
<td><asp:ListBox ID="Showslistbox" runat="server" Rows="1" DataTextField="Show_Name" Datavaluefield="Show_ID" /></td>
</tr>
<tr>
<td>Time: </td>
<td><asp:TextBox ID="affTime" runat="server"/>
<asp:RequiredFieldValidator ID="affTimereq" ControlToValidate="affTime" ErrorMessage="Required" Display="Dynamic" runat="server" /></td>
</tr>
<tr>
<td>Date: </td>
<td><asp:TextBox ID="affDate" runat="server" />
<asp:RequiredFieldValidator ID="affDatereq" ControlToValidate="affDate" ErrorMessage="Required" Display="Dynamic" runat="server" /></td>
</tr>
<tr><td><asp:Button ID="Submit" runat="server" text="Submit" OnClick="Submit_click" /></td></tr>
</table>
</asp:Content>

 

[jlateehs]

After some tweeking I'm getting a new error. In Ln 35 (where I dim pickvalue) I get object reference not set to an instance of an object.
Here's my new code...thanks to anyone that can help!

<%@ Page Title="" Language="VB" MasterPageFile="admin.master" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

<script runat="server">
Sub Page_Load(sender as Object, e as EventArgs)
BindData()

End Sub

Sub BindData()
'Create a connection
Const sConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\sheetalk\Documents\Database3.accdb"
Dim objConn As New OleDbConnection(sConnStr)

'You must open the connection before populating the DataReader
objConn.Open()


'Create a command object for the query
Const strSQL As String = "SELECT Show_ID, Show_Name FROM Shows Order By Show_Name"
Dim objCmd As New OleDbCommand(strSQL, objConn)

'Create/Populate the DataReader
Dim objDR As OleDbDataReader
objDR = objCmd.ExecuteReader(CommandBehavior.CloseConnection)
Showslistbox.DataSource = objDR
Showslistbox.DataBind()
'objConn.Close
End Sub
Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim conCoaxis As OleDbConnection
'Dim username As String = Session("User")
Dim result As Integer = 0
Dim pickvalue As String = Showslistbox.SelectedItem.Value
Dim i As Integer = Convert.ToInt16(pickvalue)
Dim strSelect As String
Dim strInsert As String
Dim cmdSelect As OleDbCommand
Dim cmdInsert As OleDbCommand
conCoaxis = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\sheetalk\Documents\Database3.accdb")
'Literal3.Text = Showslistbox.SelectedValue
strSelect = "Select ID from Users WHERE Username = '" & Session("User") & "'"
cmdSelect = New OleDbCommand(strSelect, conCoaxis)
'cmdSelect.Parameters.AddWithValue("@username", username)
'cmdSelect.SelectCommand.Parameters.Add(New OleDbParameter("@ID", user))

strInsert = "INSERT into Affidavits (show_id, affidavit_air_date, affidavit_air_time, user_id) Values (@showid, @affDate, @affTime," & result & ")"
cmdInsert = New OleDbCommand(strInsert, conCoaxis)

cmdInsert.Parameters.AddWithValue("@affDate", affDate.Text)
cmdInsert.Parameters.AddWithValue("@affTime", affTime.Text)
cmdInsert.Parameters.AddWithValue("@showid", i)
'cmdInsert.Parameters.AddWithValue("@result", result)

conCoaxis.Open()
result = DirectCast(cmdSelect.ExecuteScalar(), Integer)

cmdInsert.ExecuteNonQuery()

conCoaxis.Close()
'Response.Write("Updated Successfully!<p>&nbsp;</p><p>&nbsp;</p><p>&nbsp;</p>")




End Sub



</script>

<asp:Content ID="Content1" ContentPlaceHolderID="cphhead" Runat="Server">

</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="cphMain" Runat="Server">
<asp:Literal ID="Literal3" runat="server" /><br />
<h1>Affidavit</h1>
<table>
<tr>
<td>Shows: </td>
<td><asp:ListBox ID="Showslistbox" runat="server" Rows="1" DataTextField="Show_Name" Datavaluefield="Show_ID" /></td>
</tr>
<tr>
<td>Time: </td>
<td><asp:TextBox ID="affTime" runat="server"/>
<asp:RequiredFieldValidator ID="affTimereq" ControlToValidate="affTime" ErrorMessage="Required" Display="Dynamic" runat="server" /></td>
</tr>
<tr>
<td>Date: </td>
<td><asp:TextBox ID="affDate" runat="server" />
<asp:RequiredFieldValidator ID="affDatereq" ControlToValidate="affDate" ErrorMessage="Required" Display="Dynamic" runat="server" /></td>
</tr>
<tr><td><asp:Button ID="Submit" runat="server" text="Submit" OnClick="Submit_click" /></td></tr>
</table>
</asp:Content>