|
can some tell me what is the best way to count the records in MS access database based on WHERE statement?
Jassim Rahma
|
|
|
|
|
If you're using ADO, this may help:
RecordCount Property
Indicates the number of records in a Recordset object.
Return Value
Returns a Long value that indicates the number of records in the Recordset.
Remarks
Use the RecordCount property to find out how many records are in a Recordset object. The property returns -1 when ADO cannot determine the number of records or if the provider or cursor type does not support RecordCount. Reading the RecordCount property on a closed Recordset causes an error.
[From the SDK Documentation]
As I read it, you should be able to select your recordset using the WHERE clause, then use rstMyRecordSet.RecordCount() to return the number of records it contains.
"How many times do I have to flush before you go away?" - Megan Forbes, on Management (12/5/2002)
|
|
|
|
|
Well from your question I read two things you might be wanting to do:
SELECT Count([column]) AS [NumRows] FROM [Table] [[WHERE [Condition]]
The count is returned in NumRows. If you need to specify additional rows to return then you'll need to look into grouping which should be in the MS Access documentation.
Or are you wanting to include a row count within another condition? E.g.:
SELECT * FROM [Table] WHERE "some table's row count is equal to 4"
Skippy, the rain won't come! [+]
|
|
|
|
|
Can some tell me about his experience using mySQL with .NET? what are the advantages and disadvantages? any limitations or restrications?
I want to build a corporate application which will have a network access as well as remote access and I'm planning to use mySQL because of the limitation inb MS Access and SQL server with MSDE.
Jassim Rahma
|
|
|
|
|
Before you go developing a "corporate level" application using MySQL, you should spend some quality time reading the Reference manual that's available both on-line, from the MySQL website, and as part of the MySQL installation. I just checked their website before writting this reply, and found that as of their 4.0 Gamma release (not production), MySQL is still lacking support for both Stored Procedures and Triggers. They "expect to have it in version 5.0", but they do indicate that they are "looking into it", and not that "they will do it". Additionally, their 32-bit ODBC driver is still considered a Gamma release and conforms to the ODBC version 3.51 spec, and not the newer 3.52 spec. These are fairly signficant components to a mission critical application, if you want a true Client/Server application.
I have to assume that you are not considering SQL Server an option because of price, so I'll suggest that if you want a true Client/Server database consider Sybase SQL Anywhere (also called iAnywhere). I've experience with both MS SQL Server 7.0 and 2000, as well as Sybase 6.0 and 7.0 and I have to admit that Sybase is stable, fast and powerful, handles transaction processing, can be used with MTS, has a small foot-print and doesn't hurt the ol' pocket book.
Just my two cents, I'll sign off now, because I'm beginning to sound like a commerical for Sybase ...
Good-luck.
D.
|
|
|
|
|
what about SAP DB?
Jassim
|
|
|
|
|
You make a valid point about the lack of certain powerful features, but if you want power and scalability in a small, inexpensive, easy-to-use database, you can't beat MySQL. I have been frustrated, from time to time, with not having transactions and stored procedures, but I've built complete apps that work well and serve their purpose with handling those kinds of things in my application code. It's probably not the perfect solution for everything, but talk to the guys at slashdot.org about power and scalability and they'll show you 50 million pages served per month!! You can read more about the slashdot setup here Just my 2 cents too.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
What limitations of SQL server(Msde) are stopping you from using it? 2GB database size maximum?,no Replication? In a "Corporate" app, I wouldn't think the cost of SQL would be that big an obstacle (and would probably ony be there if you expected more than the 5 concurrent user that MSDE is tuned for).
Some ideas are so stupid that only an intellectual could have thought of them - George Orwell
|
|
|
|
|
yes, limitation in number of users is the major problem for me. I have 8 users now and expected to 18 in few months. what is your suggesstion?
Jassim
|
|
|
|
|
I think it depends on how database centric your application will be.
MySQL appears to be very fast and works fine with .NET (just like SQL Server does), but its lack of views, stored procedures and triggers may be the factors that stop you using it.
Dave.
|
|
|
|
|
Hi all,
I've been working on a conversion app, that is a one off application... basically reading data from one table and inserting it into a new table. I'm using AppendChunk to insert the new record into the destination table... now on to the problem.
When I append the data to the field it seems that this is being done asynchronously and when I try to free the memory I’m getting an exception that says the memory is locked. Here's the snippet of code: This code is inside a try/catch block
<br />
...<br />
_variant_t varChunk;<br />
varChunk.vt = VT_ARRAY|VT_UI1;<br />
varChunk.parray = ChunkBlock;<br />
<br />
pRs->Fields->Item["Data"]->AppendChunk(varChunk); <br />
<br />
hr = pRs->Update();<br />
pRs->Close();<br />
SafeArrayDestroy(varChunk.parray);<br />
...<br />
My questions are; is AppendChunk an asynchronous operation? And if so, is there a way to force it to be synchronous?
Thanks for any help or pointers...
Dave "Dak Lozar" Loeser
When access is allowed to a member, it said to be accessible. Otherwise, it is inaccessible. - MSDN:C# Programmer's Reference
|
|
|
|
|
guys..can someone explain or lead me to some good documentation
that shows that manipulating the DB from a COM component is more scalable
than accessing the DB directly from an ASP page using ADODB ?
Ofcourse, it is understood that using a component has advantages like
object/connection pooling, re-usability, cleaner design etc etc..but my concern is whether
by putting the database logic in a component, it would be able to manage a
larger user base i.e does using ADODB.dll directly from ASP have a limitation to the no of users
that it can support ??
There are no failures; there are only extended learning opportunities.
|
|
|
|
|
Friends, we are getting strange errors whenever our applications access database from SQL server. The errors we are getting are:
1)
Microsoft OLE DB Provider for SQL Server error '80004005' <br />
<br />
[DBMSSOCN]General network error. Check your network documentation.
2)
[MICROSOFT][ODBC][SQL SERVER DRIVER] [TCP/IP SOCKETS]ConnectionRead(recv()).
Can anyone help me getting rid of these errors. Our applications that access database are developed in VB and ASP.
|
|
|
|
|
Sounds like you are having (generic) network problems. Possibly a flaky network card or switch somewhere.
Some ideas are so stupid that only an intellectual could have thought of them - George Orwell
|
|
|
|
|
I'm writing program that I use Access database and C#.And I use it in disconnected(DataSet) mode.When I run this code my dataset will be have 0 rows,and it seems it return nothing but if I only I change my connection string to SQL connection and use SQLServer instead of Access,I'll get correct result.This is my code:
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:database
Password='';User ID=Admin ; Persist Security Info =False;Data Source=F:\\Visual
Studio Projects1\\Visual C#\\Reminder Manager\\bin\\Debug\\RemindManagerDB.mdb");
oleDbConnection1.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter("select * from my table", conn);
DataSet firstjobds = new DataSet();
adapter.Fill(firstjobds);
oleDbConnection1.Close();
foreach (DataRow r in firstjobds.Tables[0].Rows)
{
userjobslv.Items.Add(r["JobTitle"].ToString());
}
Any idea?
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
Don't know if it was just the formatting in your code sample, but:
1.
Jet OLEDB:database Password=''
seems to have some garbage whitespace...
This probably should be just
password =;
anyway, rather than the jet specific one.
2.
"select * from my table"
has white space in the table name...
use:
"select * from [my table]"
Some ideas are so stupid that only an intellectual could have thought of them - George Orwell
|
|
|
|
|
Thanks Rob,But problem in connection string caused unhandled error,but I don't recieve any error,and also about name of table.Those are problems when I copy paste codes here. 
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
|
I have an application which stores data in a text field (sql server 2000) like the following:
1040970851Demotest me five1040970856Demothis is another thing1040970955Demothis is the n. no quotes now. that was scary.
I need to parse the data for crystal reports 8.0 so that it displays as it does in the application...
I don't have a problem parsing the data, but I do have a problem in figuring out how to implement a solution. It turns out that using Crystal 8.0 UFL dlls are limited to 255 characters or less.
Can I get the data to crystal properly using external stored procedures, or am I going to be relegated to writing an odbc driver?
|
|
|
|
|
Do we have something like GetDate() in Access?
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
Mazdak wrote:
Do we have something like GetDate() in Access?
Oh, in VB you need to use Now(). You can also format it such as:
d = Format(Now, "mm/dd/yyyy")
Nick Parker
You see the Standards change. - Fellow co-worker
|
|
|
|
|
Hi Nick:
I need to used GetDate() in a query that Access does not support it.I don't use VB.I use C#.
Anyway thanks.
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
The previous CPian gave you the correct answer. The equivalent function for getDate() in MsAccess is Now(). It will work in both an isert query and as the default value for a date/time field: "INSERT INTO MyTable( Mydate) SELECT Now() AS Expr1"
or
"INSERT INTO MyTable( Mydate) Values( Now())"
Some ideas are so stupid that only an intellectual could have thought of them - George Orwell
|
|
|
|
|
Oh,I got it. Thanks.
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
When binding a datagrid, one of the columns calls a function to process the "Status" field. However, sometimes that field is not there - sometimes I get the exception "Status is neither a DataColumn nor a DataRelation for table ABC".
So I used an IF statement to check if that field is Nothing, but that too caused the exception to be thrown.
How do I determine if a datarow contains a particular column?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
