count the element in a database table RSS

• • Reply
  

  christene

  Member

  75 Points

  15 Posts

  count the element in a database table

  Aug 03, 2003 12:20 PM|LINK

  i have a problem here, where i would like to count how many elements on the table. i am using ms access as my database and OLEDB. Let's say, the purchase_table, i would like to know how many item that a particular customer purchase. can anyone pls help me on this?
• • Reply
  

  McMurdoStati...

  Contributor

  3015 Points

  601 Posts

  Re: count the element in a database table

  Aug 03, 2003 04:50 PM|LINK

  Assuming that you have a field called CustomerID that identifies the purchaser then something like this should work:

  int customerIDValue = theCustomerIDYouWantToUse;
  OleDbCommand oledb = new OleDbCommand();
  OleDbConnection connection = new OleDbConnection( "YourConnectionString" );
  oledb.CommandType = CommandType.TableDirect;
  oledb.CommandText = "SELECT Count(*) AS CustomerPurchaseCount FROM purchase_table Where CustomerID=@CustomerID";
  OleDbParameter sp1 = oledb.Parameters.Add( new OleDbParameter("@CustomerID",OleDbType.Integer,4   ));
  sp1.Value = customerIDValue;
  connection.Open();
  int count = (int)oledb.ExecuteScalar();
  connection.Close();

• • Reply
  

  christene

  Member

  75 Points

  15 Posts

  Re: count the element in a database table

  Aug 04, 2003 02:44 AM|LINK

  thanks a lot for your help. This is the code that i have tried, but it doesn't work....when i tried to use commandtype.Tabledirect, the error message say "Object reference not set to an instance of an object." Public Function GetItemCount(ByVal cartID As String) As Integer ' Create Instance of Connection and Command Object Dim myConnection2 As OleDbConnection Dim myCommand As OleDbCommand Dim qry As String Dim NumRec As Integer myConnection2 = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source= E:\webpage\Database\test.mdb") qry = "SELECT COUNT(*) FROM shoppingCart WHERE CartID = @CartID" myCommand = New OleDbCommand(qry, myConnection2) 'i have tried this code as well 'myCommand.CommandType = CommandType.TableDirect 'myCommand.CommandText = "SELECT COUNT(*) As NumRec FROM shoppingCart WHERE CartID = @CartID" 'Dim parameterCartID As OleDbParameter = New OleDbParameter("@CartID", OleDbType.VarChar, 10) 'parameterCartID.Value = cartID 'myCommand.Parameters.Add(parameterCartID) ' Open the connection and execute the Command myConnection2.Open() myCommand.ExecuteScalar() myConnection2.Close() Return CInt(NumRec) End Function //end of code what's wrong with the code???? thanks christene
• • Reply
  

  McMurdoStati...

  Contributor

  3015 Points

  601 Posts

  Re: count the element in a database table

  Aug 04, 2003 05:05 AM|LINK

  I'm not sure but I think because you used the constructor for OleDbCommand that takes the query as an arguement that you then can't set CommandType. Maybe try this:

  qry = "SELECT COUNT(*) FROM shoppingCart WHERE CartID = @CartID"
  myCommand = New OleDbCommand()
  myCommand.Connection = myConnection2
  'myCommand.CommandType = CommandType.TableDirect
  myCommand.CommandText = qry

• • Reply
  

  christene

  Member

  75 Points

  15 Posts

  Re: count the element in a database table

  Aug 04, 2003 07:52 AM|LINK

  well,i have tried the code....and this is the code that i run, Public Function GetItemCount(ByVal cartID As String) As Integer ' Create Instance of Connection and Command Object Dim myConnection2 As OleDbConnection Dim myCommand As OleDbCommand Dim qry As String Dim NumRec As Integer myConnection2 = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source= E:\webpage\Database\test.mdb") qry = "SELECT COUNT(*) As NumRec FROM shoppingCart WHERE CartID = @CartID" myCommand = New OleDbCommand myCommand.Connection = myConnection2 myCommand.CommandType = CommandType.TableDirect myCommand.CommandText = qry Dim parameterCartID As OleDbParameter = New OleDbParameter("@CartID", OleDbType.VarChar, 10) parameterCartID.Value = cartID myCommand.Parameters.Add(parameterCartID) ' Open the connection and execute the Command myConnection2.Open() myCommand.ExecuteScalar() myConnection2.Close() Return CInt(NumRec) End Function //end of code However, it says "too few parameter.Expected 1." for the myCommand.ExecuteScalar(). what is that mean?how shold i solve this? thanks. christene
• • Reply
  

  christene

  Member

  75 Points

  15 Posts

  Re: count the element in a database table

  Aug 04, 2003 09:01 AM|LINK

  well well...i have done the COUNT() with this code.. Public Function GetItemCount(ByVal cartID As String) As Integer ' Create Instance of Connection and Command Object Dim myConnection2 As OleDbConnection Dim myCommand As OleDbCommand Dim qry As String Dim NumRec As Integer myConnection2 = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source= E:\webpage\Database\test.mdb") qry = "SELECT COUNT(*) FROM shoppingCart Where CartID= '" & cartID & "'" myCommand = New OleDbCommand(qry, myConnection2) Dim parameterCartID As OleDbParameter = New OleDbParameter("@CartID", OleDbType.VarChar, 10) parameterCartID.Value = cartID myCommand.Parameters.Add(parameterCartID) ' Open the connection and execute the Command myCommand.Connection.Open() NumRec = myCommand.ExecuteScalar() myConnection2.Close() Return CInt(NumRec) End Function Thanks a lot for guiding me!!!.... christene