count the element in a database table

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?

Re: count the element in a database table

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();

Re: count the element in a database table

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

Re: count the element in a database table

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

Re: count the element in a database table

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

Re: count the element in a database table

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