I have a form for a web application that selects categories using a listbox control. I have no problem populating the values in the listbox with data from a table and extracting that to save. The problem is when I go to edit, I cannot find a way to mark the associated records as selected.
The first sub below is the sub that populates the listbox in the form. The second sub attempts to load values from a table that associates records from a primary table to records in the categories table.
The second sub only marks the last record in the reader as selected. I have searched extensively for a solution to what should be a simple task and have found nothing. I could do this blindfolded with inline code; this is frustrating.
Protected Sub LoadCat()
Dim myCommand As New SqlCommand
Dim myReader As SqlDataReader = Nothing
Dim strConnection As String
strConnection = ConfigurationManager.AppSettings("ConnectionString")
Dim sPath = System.Web.HttpContext.Current.Request.Url.AbsolutePath
Dim oInfo = New System.IO.FileInfo(sPath)
Dim sRet = oInfo.Name
Try
myConnection = New SqlConnection(strConnection)
myConnection.Open()
With myCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "MCC_LoadRecCat"
.Connection = myConnection
myReader = .ExecuteReader()
End With
Catch ex As Exception
Dim EmailClass As New EmailClass
EmailClass.EmailError(ex.Message.ToString, sRet, Session("ErrorEmail"), Session("EmailUser"), Session("EmailPassword"), Session("MailServer"))
Response.Redirect("Error.aspx")
Exit Sub
End Try
reccatID.DataSource = myReader
reccatID.DataTextField = "reccatName"
reccatID.DataValueField = "reccatID"
reccatID.DataBind()
myReader.Close()
myCommand.Dispose()
myConnection.Close()
End Sub
Protected Sub PopulateCat()
Dim myCommand As New SqlCommand
Dim myReader As SqlDataReader = Nothing
Dim strConnection As String
strConnection = ConfigurationManager.AppSettings("ConnectionString")
Dim sPath = System.Web.HttpContext.Current.Request.Url.AbsolutePath
Dim oInfo = New System.IO.FileInfo(sPath)
Dim sRet = oInfo.Name
Dim MyID = Request("ID")
ViewState("recipeID") = Request("ID")
Try
myConnection = New SqlConnection(strConnection)
myConnection.Open()
With myCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "MCC_GetRecipeCat2"
.Parameters.Add("@recipeID", SqlDbType.Int).Value = MyID
.Connection = myConnection
myReader = .ExecuteReader()
End With
Catch
End Try
While myReader.Read
reccatID.SelectedValue = myReader("catID")
End While
myReader.Close()
myCommand.Dispose()
myConnection.Close()
End Sub