Populating webform using query

Hi Folks,

I’m trying to populate a webform using a SQL Server 2005 database.

I can popuylate the text areas and checkboxes easily enough. The problem occurs when i try and populate the drop down menu with a list of category names from a categorys table. I need to tell if the category name is the same that is selected so I can set the defaut value of the select list when the page is loaded.

Is there a best way to do this, or do I just use nested while loops?

Here is my code to populate the webform:

      getComm = New SqlCommand( _
            "SELECT * FROM documents WHERE documentid = @docid", conn)
        getComm.Parameters.Add("@docid", System.Data.SqlDbType.Int)
        getComm.Parameters("@docid").Value = Request.QueryString("documentid")
  
        ' Enclose database code in Try-Catch-Finally
        Try
            ' Open the connection
            conn.Open()
           
            ' Execute the subjects command
            reader = getComm.ExecuteReader()
	
    While reader.Read()

                If (reader.Item("documentpub") = 1) Then
                    addpub.checked = True
                End If
    
                description.value = reader.Item("documentdescription")
                
	End While

Here is my code to populate the drop down list, but it doesnt work I get the error: Invalid attempt to call MetaData when reader is closed.

 'populate categories
        getComm = New SqlCommand("SELECT * FROM documentCategory WHERE documentCategorysiteid = 4 ORDER BY documentCategoryID", conn)

        conn.Open()

        catreader = getComm.ExecuteReader()

        ' Populate the Control 
        While catreader.Read()
            Dim newListItem As New ListItem()
            newListItem.Text = catreader.Item("documentCategoryName")
            newListItem.Value = catreader.Item("documentCategoryId")
            If (catreader.Item("documentCategoryId") = reader.Item("documentcat")) Then
                newListItem.Selected = True
            End If
            docCat.Items.Add(newListItem)
            
        End While

        ' Close the reader
        catreader.Close()

        ' Close the connection
        conn.Close()

Any advice veru much appreciated.

Thankyou

You can bind your list of items to the dropdown like normal. Then when loading the form values, you can set the SelectedItem like this:

ddl.SelectedIndex = ddl.Items.IndexOf(ddl.Items.FindByValue(dr[“dbBalue”].ToString()));

I think that’s where your problem is, you’re trying to read from the previous reader, remember, you used a while loop and at this stage u’ve stop reading and have exited the while loop.

You can create a variable to hold the value of reader.Item(“documentcat”) while u’re reading reader and then use that variable for the listitem value while u’re reading catreader.

If (catreader.Item(“documentCategoryId”) = yourvariable Then

Hope it helps