Listbox HELP please

Discussion in '.NET' started by jlateehs, Mar 17, 2011.

  1. jlateehs

    jlateehs

    Joined:
    Mar 17, 2011
    Messages:
    3
    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, Mar 17, 2011
    #1
    1. Advertising

  2. jlateehs

    jlateehs

    Joined:
    Mar 17, 2011
    Messages:
    3
    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>
     
    jlateehs, Mar 21, 2011
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. blenderdude
    Replies:
    0
    Views:
    1,148
    blenderdude
    Aug 3, 2003
  2. DC Gringo
    Replies:
    0
    Views:
    421
    DC Gringo
    Apr 6, 2004
  3. kent
    Replies:
    1
    Views:
    909
    Andrew de la Harpe
    May 3, 2004
  4. Simon Prince
    Replies:
    2
    Views:
    3,383
    =?Utf-8?B?TWljaGFlbCBUYXlsb3I=?=
    Oct 19, 2004
  5. KK
    Replies:
    2
    Views:
    707
    Big Brian
    Oct 14, 2003
Loading...

Share This Page