MS Access & ADO.NET

[readraj]

I have made one program just to record timings of workers where i have given them user id and password.

when they sign in teh system the system time gets recorded in to database and same when they sign out.

This program is running on LAN and working properly with VB 6.

When i made use of VB.Net and ADO.NET the performance degraded as application takes lot of time to retrive the data.

I am using Dataset and data adaptor object as i have to check whether user exits if yes his password.

if both tallies then record current system time and show report.

But program gets halted for nearly 6-9 seconds at the time of retriving data.

Is it normal with VB.Net or is there any better way to do this?
Can LAN Connection differ it's behaviour for .Net?

 

[TechGnome]

It isn't .NET as so much as it is Access over a network. Access is extremely innefficient when used over the network. I'd suggest you look at SQL Server or MSDE even.

Tg

 

[kulrom]

as i said couple times before we should care when to consider using a DataReader or DataSet. Oh yeah i'm pretty sure that for sucha small data DataSet is not an appropriate solution http://www.vbdotnetforums.com/showthread.php?t=3051

Let me str8 something ... TechGnome you are right that MSSQL is better solution but i assure that Access is also powerful and it is not innefficient like you said if you know the limitations. In this case Access is more than enough. Namely, not because of my taste but, rather becuase of MSSQL $$$ price i have worked on many (really many) projects that deals with access file as backend and i do never had a problem like readraj does. Moreover i can approve my claiming. It seems like i forgot the primar goal here Ok this is the deal:
as you say when they sign in the system the system time gets recorded in to database and same when they sign out.

On load event i'm doing next:

Dim myDate As Date = Date.Now
strSQL = "INSERT INTO Loggin (Name, Started) VALUES ('" & LoggedUserFullName & "', '" & myDate & "')"
Inserting(strSQL)

in closing event i'm doing this:

Dim myDate As Date = Date.Now
strSQL = "UPDATE Loggin SET Ended = '" & myDate & "' WHERE ID=" & myNumber & ""
Updating(strSQL)

and to check user's ID and PWD i'm doing this:

maybe you don't like this manner (as there are many other to check user id and pwd) but i found this one for good too

this is the entire code from button enter of loggin form ... if user types the wrong pwd for more than three times app is getting exit.

		If userID.Text = String.Empty Then
			 MessageBox.Show("Please, enter your id !")
				userID.Focus()
				Exit Sub
			End If
			If PassID.Text = String.Empty Then
			 MessageBox.Show("Please, enter your password !")
				PassID.Focus()
				Exit Sub
		End If
 
		Try
		 strSQL = "SELECT UserID, PassID FROM Login WHERE UserID='" & userID.Text & "' AND PassID='" & PassID.Text & "'"
 
			cmd = New OleDbCommand(strSQL, oledbcon)
			oledbcon.Open()
			Dim dr As OleDbDataReader = cmd.ExecuteReader
			Dim bol As Boolean
			bol = False
 
		 If dr.Read = False Then
				bol = False
			Else
				bol = True
			End If
 
			If bol = True Then
				Dim frm2 As New MainForm
				frm2.Show()
				Me.Close()
			Else
				i += 1
				If i >= 3 Then
				 MessageBox.Show("Sorry, but app was canceled") 
				 Application.Exit()
				Else
				 MessageBox.Show("Wrong password, please try again !")
				 userID.Text = String.Empty
				 PassID.Text = String.Empty
				 userID.Focus()
				End If
			End If
 
 
		Catch ex As Exception
			porakaCatch(ex)
		Finally
			oledbcon.Close()
		End Try

All you need to change is location of the Access file in coonection string
just change datasource against server machine IP or name i.e. \\192.168.0.107\myDir\Setup\bin\myDB.mdb


that's it ... happy coding

Cheers


edit: oh i nearly forgot to say that this code takes less than 1 second to check the user's ID and PWD and to get log in. for less than one second (really, really less) you are in.

 

[TechGnome]

I've seen Acces run well and poorly over a network... and over the same network too! I've also seen it degrade very fast when multiple users are involved. As for the prise of SQL SErver, that's why I suggested MSDE.

I see that in the OP he said that from VB6 it works fine... I missed that the first time through. mea cupla.

That said, a data read would most certanly be faster. A few minor items I'd change... I notice that you build the SQL, execvute the reader, and THEN check the text boxes to see if they are filled in.... wouldn't it be better to do that first, then proceed if they are filled in?

Tg

 

[kulrom]

I notice that you build the SQL, execvute the reader, and THEN check the text boxes to see if they are filled in.

That's good notice and i made it occasionally durring copy/paste

Ah about MSDE
Well, MSDE has many limitations that i cannot accept (btw, remeber that M$ doesn't offer anything for free be sure in that maybe it only looks like that but it is not)
Some of the MSDE limitations: can be no larger than 2 GB, performance throttling occurs when there are more than five concurrent users, MSDE does not ship with GUI administrative tools such as Enterprise Manager or Query Analyzer ... There are also some other limitations in MSDE that you should be aware of.

while this is the limitations of MS Access:
http://www.vbdotnetforums.com/showthread.php?t=4069

Cheers

 

[readraj]

I Can Not accept the sugession of TechGnome as i can not install altogether new database or ask company to install it so i have to work with MS Access itself.

I feel the slow retriving of data may result because of diconnected architecture of ADO.NET. Where as it is not so with ADODB in VB6

So i just want to ask question to Kulrom, If i Started using ADODB Recordset and then filling it to Dataset will the problem get solved?

OR is there any better way?
member.php?u=2987

 

[kulrom]

I don't advocate TG but i really think he had a best wishes to you as it is really true that SQL is much much better backend solution than MS access but also as you said it's not easy to buy it.

Ok, as many times before we discused about datareader or dataset i think that datareader in your case will be more appropriate coice as it is much faster than dataset.
Btw, don't suspect ADO.NET architecture as it is the most powerful architecture for handling data known in the Universe. its "evolutionary" step in data access technologies that promises the world and infinitely more

sorry if i sound too Rough but Don't Even Think About Trying To Use DataSet in this case as you are trying to fetch only small protion of data. Let me know if you need help with any particulary issue i'd be glad to help you.

Cheers

 

[TechGnome]

Well, I never...screw you guys, I'm taking my MSDE and going home,...... ah, jsut kidding. Ok, so you're stuck with Access, no biggie... can't blame a gnome for suggesting an alternate.

But I do agree with kulrom, that a reader would be better than a dataset, it's fast, simple and if all you need is a quickecheck, and even better since all you need is one row (if the user exists in the DB or not - and if the password matches).

So what's the problem wit hthe code that kulrom posted? (other than the minor issues of B being before A, but that's easily cleaned up)

And I echo his sediments about not using ADO Recordset.... performance could be worse as it will have to go through yet one more layer (the interop layer thaty lets .NET objects talk to COM objects).

Tg

 

[readraj]

Thank you to both of you for providing much useful help and support, i know this is very late i am saying this but in between i was too busy as i had to meet 30/9 deadline for 2 projects.

I have worked out the problem as suggested by kulrom

Now i am using datareader for all my dropdown combos and list boxes and otherwise dataset, also i have created a class for all the connections which i require as i obseved that class works faster than normal coding also it save much more coding. Thanks to OOPs

But Still there is one thing which i have to handle through ADO Recordsets and that is getting data from excel table. As it is not possible to get connected with Excel using ADO.NET. Better way is using ADO Recordset and fill the dataset for working on It.
member.php?u=2880

 

[TechGnome]

It is too possible.... you have to do two things:
1) Use the right connection string, and
2) Use OLEDBClient, not SQLClient.

-tg

 

[readraj]

I am not using Sql Client as i want to connect to MS Excel

Are you serious as even on MSDN Site they clearly said that it is not possible to connect to MS Excel using ADO.NET

Presently I am using Connection string that i used to connect with my MS Access files that is through JET 4.0 and adding Extended Properties to It. Can you specify the connection string.

 

[TechGnome]

http://www.connectionstrings.com/
Click Excel, then OLE DB ..... ( or use ODBC if you like).

Do you have a link to the page that says it can't be done?

-tg

This page seems to indicate that it can be done:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/odatanet2.asp

 

[readraj]

Thanks

I must have made some mistake in reading as i could not locate the page again but they have suggested some way to handle it through linking excel table in ms-access but forget it this is the simplest way.

And also will increase by class utility, Thanks A LOT