I have a VB.net programming question

i am loading values into a list box from the database in a form being used to edit a database record.

the list populates fine using the following code.

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()
'opening the connection
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

the problem I have is marking the fields selecting the items in the listbox that are associated with the record I am editing. I have tried the following, but it only selects the last item that runs through the reader.

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()
'opening the connection
With myCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "MCC_GetRecipeCat"
.Parameters.Add("@recipeID", SqlDbType.Int).Value = MyID
.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

While myReader.Read
reccatID.SelectedValue = myReader("catID")
End While

myReader.Close()
myCommand.Dispose()
myConnection.Close()
End Sub

Hey wesley.groves

Can you please explain in detail what the problem is ? Also the code you have written always return the last selecte item.

While myReader.Read
reccatID.SelectedValue = myReader("catID")
End While

Because you are iterating through all the values from data reader. So when it reaches to the last record you will see last item selected.

I have a VB.net programming question

i am loading values into a list box from the database in a form being used to edit a database record.

the list populates fine using the following code.

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()
'opening the connection
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

the problem I have is marking the fields selecting the items in the listbox that are associated with the record I am editing. I have tried the following, but it only selects the last item that runs through the reader.

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()
'opening the connection
With myCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "MCC_GetRecipeCat"
.Parameters.Add("@recipeID", SqlDbType.Int).Value = MyID
.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

While myReader.Read
reccatID.SelectedValue = myReader("catID")
End While

myReader.Close()
myCommand.Dispose()
myConnection.Close()
End Sub

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.