code problem throws error

matt001 · #1 (**permalink**) 10-23-05, 04:30 AM

Hi Guys

When I run this page I coded, code is below:

Code:

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

<html>
<head>
<title>View Products</title>
<link rel="stylesheet" href="netdemos.css">

<script language="C#" runat="server" src="fetchData_oledb.cs" />

<script language="C#" runat="server">
int rowCount;
string Category, query;

void Page_Load ( Object src, EventArgs e ) {
   if ( !IsPostBack ) {
      query = "select distinct Category from Products";
      typesList.DataSource = fetchReader ( query, "productdb" );
      typesList.DataBind ( );

      // initialize selection
      getSubTypes ( null,null );
   }
   else Category = subtypesList.SelectedItem.Value;
}

void getSubTypes ( Object src, EventArgs e ) {
   query = "select distinct SubCategory from Products where Category='" + 
      typesList.SelectedItem.Value + "'";
   subtypesList.DataSource = fetchReader ( query, "productdb" );
   subtypesList.DataBind ( );

   // initialize selection
   subtypesList.SelectedIndex = 0;
   Category = subtypesList.SelectedItem.Value;
   getFirstPage ( null,null );
}

void getFirstPage ( Object src, EventArgs e ) {
   // get total records count of query
   myGrid.VirtualItemCount = countRows ( );

   // reset paging variables
   myGrid.CurrentPageIndex = 0;
   ViewState [ "topID" ] = "";
   ViewState [ "endID" ] = "";

   // get first set of records
   query = "select top " + myGrid.PageSize + 
      " * from Products where SubCategory='" + Category + "' order by ProductID";
   bindGrid ( );
}

void setPage ( Object src, DataGridPageChangedEventArgs e ) {
   if ( e.NewPageIndex == myGrid.CurrentPageIndex + 1 )
      // get next set of records
      query = "select top " + myGrid.PageSize + 
         " * from Products where SubCategory='" + Category + "' and ProductID > '" + 
         ViewState [ "endID" ] + "' order by ProductID";
   else // get previous set of records
      query = "select top " + myGrid.PageSize + 
         " * from Products where SubCategory='" + Category + "' and ProductID < '" + 
         ViewState [ "topID" ] + "' order by ProductID desc";

   myGrid.CurrentPageIndex = e.NewPageIndex;
   bindGrid ( );
}

void bindGrid ( ) {
   myGrid.DataSource = fetchView ( );
   myGrid.DataBind ( );
   lblTracker.Text = "Page " + ( myGrid.CurrentPageIndex+1 ) + " of " + 
      myGrid.PageCount;
}

DataView fetchView ( ) {
   // fetch data segment
   DataTable dataSegment = fetchData ( query, "productdb" ).Tables [ 0 ];

   // store top and end key values
   DataRow [  ] currentRows = dataSegment.Select ( "","ProductID" );
   ViewState [ "topID" ] = currentRows [ 0 ]  [ "ProductID" ];
   ViewState [ "endID" ] = currentRows [ currentRows.Length-1 ]  [ "ProductID" ];

   // return sorted DataView
   DataView gridView = dataSegment.DefaultView;
   gridView.Sort = "ProductID";
   return gridView;
}

int countRows ( ) {
   // specify the data source
  OleDbConnection myConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + Server.MapPath("productdb.mdb" ));

   query = "select Count ( * ) from Products where SubCategory='" + Category + "'";
   OleDbCommand getCount = new OleDbCommand ( query, myConn );
   myConn.Open ( );
   rowCount = ( int ) getCount.ExecuteScalar ( );
   myConn.Close ( );
   return rowCount;
}
</script>
</head>

<body>

<div class="header"><h3>View Products</h3></div>

<hr size=1 width=90%>

<div align="center">
<form runat="server">

<table width="92%">
<tr>
   <td>Select Gear: <asp:dropdownlist id="typesList" 
      datatextfield="Category" onSelectedIndexChanged="getSubTypes" 
      autopostback runat="server" /></td>
   <td>Category: <asp:dropdownlist id="subtypesList" 
      datatextfield="SubCategory" onSelectedIndexChanged="getFirstPage" 
      autopostback runat="server" /></td>
   <td align="right" width=30%><b><asp:label id="lblTracker" runat="server" /></td></tr>
</table>

   <asp:datagrid id="myGrid" runat="server"
      width="90%" cellpadding=4
      font-size="8pt"
      gridlines="horizontal"
      showheader=true
      itemstyle-verticalalign="top"
      autogeneratecolumns=false
      allowpaging
      allowcustompaging
      pagesize=20
      onPageIndexChanged="setPage">

      <pagerstyle
         position="topandbottom"
         nextpagetext="Next" prevpagetext="Prev"
         backcolor="lightsteelblue"
         font-bold
         horizontalalign="right" />
  
     <columns>
	 	  <asp:boundcolumn headertext="Product Code"
            datafield="ProductID" 
            itemstyle-forecolor="darkslategray" 
            itemstyle-font-size="8pt" />
           <asp:boundcolumn headertext="Brand"
            datafield="Brand" 
            itemstyle-forecolor="darkslategray" 
            itemstyle-font-size="8pt" />
         <asp:HyperlinkColumn
             HeaderText="Product" 
             DataTextField="Model" 
             SortExpression="ProductID" 
             DataNavigateUrlField="ProductID"
             DataNavigateUrlFormatString=
             "product_details.aspx?id={0}"
             />
         <asp:boundcolumn headertext="Price"
            datafield="Price"
            dataformatstring="{0:c}"
            itemstyle-horizontalalign="left" />
      </columns>

   </asp:datagrid>

</form>
</div>

<hr size=1 width=90%>


</body>
</html>

I get this error message:

Data type mismatch in criteria expression.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.

Source Error:

Line 14:
Line 15: // return datareader
Line 16: return myCmd.ExecuteReader ( CommandBehavior.CloseConnection );
Line 17: }
Line 18:

Source File: C:\sites\add\fetchData_oledb.cs Line: 16

Here is the code for fetchData_oledb.cs

Code:

// generic method to fetch data from OleDb source into a reader

OleDbDataReader fetchReader ( string query, string db ) {
   // connect to data source
   OleDbConnection myConn = new OleDbConnection ( 
      "Provider=Microsoft.Jet.OleDb.4.0; Data Source=" + 
      Server.MapPath ( "/sites/add/" + db + ".mdb" ) );

   // initialize command object with query
   OleDbCommand myCmd = new OleDbCommand ( query, myConn );

   // open connection
   myConn.Open ( );

   // return datareader
   return myCmd.ExecuteReader ( CommandBehavior.CloseConnection );
}


// generic method to fetch data from OleDb source into a dataset

DataSet fetchData ( string query, string db ) {
   // connect to data source
   OleDbConnection myConn = new OleDbConnection ( 
      "Provider=Microsoft.Jet.OleDb.4.0; Data Source=" + 
      Server.MapPath ( "/sites/add/" + db + ".mdb" ) );

   // initialize dataadapter with query
   OleDbDataAdapter myAdapter = new OleDbDataAdapter ( query, myConn );

   // initalize and fill dataset with query results
   DataSet myData = new DataSet ( );
   myAdapter.Fill ( myData );

   // return dataset
   return myData;
}


// generic method to fetch scalar value from SQL Server

object fetchScalar ( string query, string db ) {
   // connect to data source
   OleDbConnection myConn = new OleDbConnection ( 
      "Provider=Microsoft.Jet.OleDb.4.0; Data Source=" + 
      Server.MapPath ( "matthew/test/" + db + ".mdb" ) );

   // initialize command object with query
   OleDbCommand myCmd = new OleDbCommand ( query, myConn );

   // open connection
   myConn.Open ( );

   // get scalar
   object scalar = myCmd.ExecuteScalar ( );

   // close connection
   myConn.Close ( );

   // return scalar
   return scalar;
}

I think it may be the that I am not returning a reader object.

So I have changed the code in the fetchData_oledb.cs

to

// return datareader
reader = myCmd.ExecuteReader ( CommandBehavior.CloseConnection );
return reader;

But I get this error when I run the file

Compiler Error Message: CS0103: The name 'reader' does not exist in the class or namespace 'ASP.Default_aspx'

Source Error:

Line 14:
Line 15: // return datareader
Line 16: reader = myCmd.ExecuteReader ( CommandBehavior.CloseConnection );
Line 17: return reader;
Line 18: }

Source File: C:\sites\add\fetchData_oledb.cs Line: 16

And I don't know how to delcare reader, or how to fix the problem, can someone please help me out

Thanks

Matthew