Help... Not sure what's wrong...

[AznCutie]

I'm not sure what's erroring out, and I don't know what's wrong. Basically, I've got a sub that loads pictures into a datalist, and they link to a specific category. link. I don't know why it's erroring out at this area, but I have a feeling it's got something to do with the data adapter.

 

Here is the sub for the selection of the categories

Private Sub Page_Load(sender as object, e as eventargs)
	   Dim objCN  as OleDbConnection		' Database Connection object
	   Dim objDA  as OleDbDataAdapter   ' Database Adaptor object
           Dim objDS  as DataSet            ' Dataset object
	   Dim objCM  as OleDbCommand
	   Dim strSQL as String
	   Dim strCN  as String
               
           Dim intSelection As Integer = CInt(Request.QueryString("selection"))
	   
	   If Not IsPostBack Then
		   strCN = "PROVIDER = Microsoft.Jet.OLEDB.4.0;" & _	
		           "DATA SOURCE=" & Server.MapPath("./db/datastore.mdb" ) 		   
  					    
	      objCN = New OleDbConnection( strCN )	
	      objCN.Open()	          
  		      If (intSelection=0) Then      	
                       strSQL = "SELECT * FROM (tblProduct LEFT JOIN tblCategory on tblProduct.ProdCat=tblCategory.CategoryID) WHERE Category='Stainless' "
  		      Else if (intSelection=1) Then
                       strSQL = "SELECT * FROM (tblProduct LEFT JOIN tblCategory on tblProduct.ProdCat=tblCategory.CategoryID) WHERE Category='Specialty' "
                     else if (intSelection=2) Then
                       strSQL = "SELECT * FROM (tblProduct LEFT JOIN tblCategory on tblProduct.ProdCat=tblCategory.CategoryID) WHERE Category='Lefty'"
                     else if (intSelection=3) Then
                       strSQL = "SELECT * FROM (tblProduct LEFT JOIN tblCategory on tblProduct.ProdCat=tblCategory.CategoryID) WHERE Category='Clipper' "
                     else if (intSelection=4) Then
                       strSQL = "SELECT * FROM (tblProduct LEFT JOIN tblCategory on tblProduct.ProdCat=tblCategory.CategoryID) WHERE Category='Iron' "
                     else if (intSelection=5) Then
                       strSQL = "SELECT * FROM (tblProduct LEFT JOIN tblCategory on tblProduct.ProdCat=tblCategory.CategoryID) WHERE Category='Custom' "
                     else if (intSelection=6) Then
                       strSQL = "SELECT * FROM (tblProduct LEFT JOIN tblCategory on tblProduct.ProdCat=tblCategory.CategoryID) WHERE Category='Comb' "
                     else if (intSelection=7) Then
                       strSQL = "SELECT * FROM (tblProduct LEFT JOIN tblCategory on tblProduct.ProdCat=tblCategory.CategoryID) WHERE Category='Cobalt' "
                     else if (intSelection=8) Then
                       strSQL = "SELECT * FROM (tblProduct LEFT JOIN tblCategory on tblProduct.ProdCat=tblCategory.CategoryID) WHERE Category='Dryer' "
                     else 
                       strSQL="SELECT * from tblProduct"
                     End IF

	      objDA = New OleDbDataAdapter( strSQL, objCN )
	      objDS = New DataSet
  			         
	      objDA.Fill (objDS, "tblProduct")
	      objCN.Close()
	     ProductList.DataSource=objDS.Tables("tblProduct").DefaultView 
	     ProductList.DataBind() 

           End If
     End Sub

 

And here's the code for the datalist control:

<asp:datalist id="ProductList" repeatcolumns="3" repeatdirection="horizontal" runat="server">
<Itemtemplate> <IMG height="80" width="220" SRC='./images/<%# Container.DataItem("ImageAddress") %>' ><br><%# Container.DataItem("ProdName") %>
</itemtemplate>
<selecteditemtemplate> </selecteditemtemplate>
</asp:datalist> 

 

Any ideas would be greatly appreciated.

[Robby]

My code is not a solution, merely a suggestion on a leaner layout. To take it a step further I would place ALL the code within the IsPostBack into its' own routine.

 

Private Sub Page_Load(sender As Object, e As eventargs)
   Dim objCN  As OleDbConnection        ' Database Connection object
   Dim objDA  As OleDbDataAdapter   ' Database Adaptor object
   Dim objDS  As DataSet            ' Dataset object
   Dim objCM  As OleDbCommand
   Dim strSQL As String
   Dim strCN  As String
   
   Dim intSelection As Integer = CInt(Request.QueryString("selection"))
   
   If Not IsPostBack Then
       strCN = "PROVIDER = Microsoft.Jet.OLEDB.4.0;" & _
       "DATA SOURCE=" & Server.MapPath("./db/datastore.mdb")
       
       objCN = New OleDbConnection( strCN )
       objCN.Open()
       
       strSQL = GetSql(intSelection)

       
       objDA = New OleDbDataAdapter( strSQL, objCN )
       objDS = New DataSet
       
       objDA.Fill (objDS, "tblProduct")
       objCN.Close()
       ProductList.DataSource = objDS.Tables("tblProduct").DefaultView
       ProductList.DataBind()
   End If
End Sub

Private Function GetSql(ByVal intSelection As Integer) As String
Dim s As String = "SELECT * FROM (tblProduct LEFT JOIN tblCategory on tblProduct.ProdCat=tblCategory.CategoryID) WHERE Category="

       Select Case intSelection
           Case 0
               Return s & "'Stainless' "
           Case 1
               return s & "'Specialty' "
           Case 2
               return s & "'Lefty'"
           Case 3
               return s & "'Clipper' "
           Case 4
               return s & "'Iron' "
           Case 5
               return s & "'Custom' "
           Case 6
               return s & "'Comb' "
           Case 7
               return s & "'Cobalt' "
           Case 8
               return s & "'Dryer' "
           Case Else
               return "SELECT * from tblProduct"
       End Select
End Function

[AznCutie]

thanks for formatting it to be easier to read for others, but can anyone figure out what is actually wrong with it?

[AznCutie]

Got it to work,

The problem was that both tblCategory and tblProduct both had an ImageAddress field

so I changed tblCategory's field to CategoryImage instead

and alls working fine now