Values chosen from dropdown list in form do not save to database

Posted on 2005-04-21
Last Modified: 2010-04-07
Hi there,

Ok, I'm going over the work that I've done on a rather large project that I've been working on over the last few months.

I've stuck lots of test data into it and there appears to be a serious problem. The dropdown boxes (which let the user choose between new and used items, and another which sets the item as a chair,table,cabinet or item of childrens furniture) actually show the data when adding items.

The problem is this:

When I actually go and look at the items I've just added, they aren't where they appear to be. When I downloaded the database, all the items where set to 'new' and 'chair' even if I actually chose something like 'used' and 'table'. Can anyone help me at all?

Thanks in advance.

I cannot stress how urgent and important this is!

Code for stock addition follows:

    <script runat="server">
      Private Sub Page_Load

        dim dbconn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("../admin.mdb"))

          dim NewSQLQuery As String = "SELECT * FROM tblProductType"
          dim Cmd = New OleDbCommand(NewSQLQuery,dbconn)
          DropDownList1.DataSource = Cmd.ExecuteReader()
          DropDownList1.DataTextField = "ProductType"
          DropDownList1.DataValueField = "ProductType"


        dim dbconn2 As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("../admin.mdb"))

          dim NewItemSQL As String = "SELECT * FROM tblNewOrUsed"
          dim Cmd2 = New OleDbCommand(NewItemSQL,dbconn2)
          DropDownList2.DataSource = Cmd2.ExecuteReader()
          DropDownList2.DataTextField = "ItemNew"
          DropDownList2.DataValueField = "ItemNew"


        dim dbconn3 As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("../admin.mdb"))

          dim SpecialOfferSQL As String = "SELECT * FROM tblSpecialOffer"
          dim Cmd3 = New OleDbCommand(SpecialOfferSQL,dbconn3)
          DropDownList3.DataSource = Cmd3.ExecuteReader()
          DropDownList3.DataTextField = "ItemSpecialOffer"
          DropDownList3.DataValueField = "ItemSpecialOffer"


        dbconn = Nothing
        dbconn2 = Nothing
        dbconn3 = Nothing
        Cmd = Nothing
        Cmd2 = Nothing
        Cmd3 = Nothing
        NewSQLQuery = Nothing
        NewItemSQL = Nothing
        SpecialOfferSQL = Nothing

      End Sub

      Sub submit(sender As Object, e As System.Web.UI.WebControls.CommandEventArgs)


          if NOT ((itemsName.Text = nothing) or (itemsDescription.Text = nothing) or (itemsPrice.Text = nothing) or (itemsPictureAddress.Text = nothing) or (itemsPictureWidth.Text = nothing) or (itemsPictureHeight.Text = nothing))  then

            dim dbconn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("../admin.mdb"))

              dim DropDownValue as string = dropdownlist1.selectedItem.Text
              dim ItemNew as string = dropdownlist2.selectedItem.Text
              dim ItemSpecial as string = dropdownlist3.selectedItem.Text

              dim ItemNameValue as string = itemsName.Text
              dim ItemDescriptionValue as string = itemsDescription.Text
              dim ItemPriceValue as string = itemsPrice.Text
              dim PictureValue as string = itemsPictureAddress.Text
              dim PictureWidth as string = itemsPictureWidth.Text
              dim PictureHeight as string = itemsPictureHeight.Text

              dim sql As String = "insert INTO tblProducts (itemName, ProductType, itemNew, itemSpecialOffer, itemDescription, itemPrice, itemPictureAddress, itemPictureWidth, itemPictureHeight) values ('" & ItemNameValue & "', '" & DropDownValue & "', '" & ItemNew & "', '" & ItemSpecial & "', '" & ItemDescriptionValue & "', '" & ItemPriceValue & "', '" & PictureValue & "', '" & PictureWidth & "', '" & PictureHeight & "')"

              dim Cmd As OleDbCommand = New OleDbCommand(sql,dbconn)


            response.write("<b>Data inserted:</b><br>")

            dbconn = Nothing
            sql = Nothing
            Cmd = Nothing
            itemsName.Text = Nothing
            itemsDescription.Text = Nothing
            itemsPrice.Text = Nothing
            itemsPictureAddress.Text = Nothing
            itemsPictureWidth.Text = Nothing
            itemsPictureHeight.Text = Nothing

            response.write("<b>Data cannot be inserted because one or more fields are empty. Please fill all fields and try again.</b>")
          end if

        Catch ex As Exception
          Response.Write (ex.Message)
        End Try

      end Sub

Code for displaying items

<script  runat="server">
  Sub Page_Load
    Dim strProduct as String
    Dim strType as String
    Dim sql as String

    If ((Request.QueryString("product") Is Nothing) Or (Request.Querystring("product") = String.Empty)) Then
      strProduct = "new"
     strProduct = Request.Querystring("product")
    End If

    If ((Request.QueryString("type") Is Nothing) Or (Request.Querystring("type") = String.Empty)) Then
      sql = "SELECT ROUND([ItemPrice] * 0.175,2) AS ItemPriceVat, ROUND([ItemPrice] + [itemPriceVat],2) AS ItemPriceIncVat, * FROM tblProducts WHERE [ItemNew] = '" & strProduct & "'"
      sql = "SELECT ROUND([ItemPrice] * 0.175,2) AS ItemPriceVat, ROUND([ItemPrice] + [itemPriceVat],2) AS ItemPriceIncVat, * FROM tblProducts WHERE [ItemNew] = '" & strProduct & "' AND [ProductType] = '" & strType & "'"
    End If

    dim dbconn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("admin.mdb"))

      dim Cmd As OleDbCommand = New OleDbCommand(sql,dbconn)
      dim reader As OleDbDataReader
      reader = Cmd.ExecuteReader()
        admin.DataSource = reader

  End Sub

Question by:DanBAtkinson
    LVL 15

    Assisted Solution

    how come you dont have your code in

    if Page.IsPostBack check loop...isn't that necessary

    Author Comment

    how and where would one put that in?

    I've never seen this used and nobody has ever mentioned it.
    LVL 15

    Expert Comment

    Sub Page_Load
    if Not Page.IsPostBack then
      put the current code here
    end if
    LVL 7

    Expert Comment

    What are you doing after the post of the form? Are you doing a Response.Redirect to another page?

    If so, youre not going to see any of your Response.Writes, cuz that's gonna go to the next page. So you won't see the error, it'll look like everything's cool, but if it bombs out anywhere in that block, you're toast.

    Try removing your Try/Catch block, executing it, and see where it bombs. Having all of that in a Try/Catch without doing anything with it in the Catch block is asking for trouble.



    Author Comment

    @praneetha: I have added this in but could you please explain its function!


    The same page is reloaded (in case the user wishes to add more stock) with the responses. I'd rather have a textbox telling the user that it was sucessful, but there's an error with modal dialogs in the form so I left it.

    I removed the try/catcher and no errors occur.
        <table width="500">
        <form runat="server">
            <tr><b>Name of item:</b><br>
              <asp:TextBox id="itemsName" runat="server" /></td><br><br>

            <tr><b>Product Type:</b><br>
              <asp:DropDownList id="DropDownList1" runat="server" /></td><br>

            <tr><b>New Or Used?:</b><br>
              <asp:DropDownList id="DropDownList2" runat="server" /></td><br>

            <tr><b>Is item a special offer?:</b><br>
              <asp:DropDownList id="DropDownList3" runat="server" /></td><br>

            <tr><b>Description: </b><a href="HTMLTutorial.html" target="_blank">Click here for HTML help</a><br>
              <asp:TextBox id="itemsDescription" TextMode="multiline" width="300" height="100" runat="server" /></td><br><br>
            <tr><b>Price (without £ symbol (10.00)):</b><br>
              <asp:TextBox id="itemsPrice" runat="server" /></td><br><br>
            <tr><b>Picture Address: (You must upload the picture first)</b><br>
              <asp:TextBox id="itemsPictureAddress" runat="server" /></td><br><br>
            <tr><b>Picture Width: (must be an integer)</b><br>
              <asp:TextBox id="itemsPictureWidth" runat="server" /></td><br><br>
            <tr><b>Picture Height: (must be an integer)</b><br>
              <asp:TextBox id="itemsPictureHeight" runat="server" /></td><br>

            <tr><asp:Button OnCommand="submit" Text="Submit" runat="server" /></td>
    LVL 7

    Accepted Solution

    Oh, *slaps head* ... here's what I think might be happening, which is what Pran is talking about.

    When you have your Bindings in the PageLoad, every time the page gets reloaded, it's going to REBIND your data. So after the user submits the data, it REBINDS (or, resets) your dropdown values. THEN it adds to the DB.

    Do this: Put your bindings in a function called BindData() or something like that, and then in your PAGELOAD, put

    If Not Page.IsPostBack Then
    End If

    This way, the data binds once, since Viewstate is going to keep all of those values.

    I think this should do it.
    LVL 15

    Expert Comment

    yep he is adding it helped?

    Author Comment

    ok. Thankyou NauticalNonsense. That appears to have worked.

    I will split the points between both you and Praneetha since your solution used his comment.

    There is a further problem which I will be writing another question which involves a querytsting problem I'm having in a bit, but for now, thankyou both for the quick answers.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now