Connecting to an Access database and retrieving values

When I first started programming one thing that I needed to do was to be able to connect to a Access database. Yes sure there are other free databases but I was experienced on that so it was a very easy decision to make.

BUT, for the life of me all the programming books that we were given at school, college etc (substitute with your tertiary institute of choice) were plain to say the list. The Internet wasn’t helping a lot to some point that I needed clarification. Some times it was taking me weeks to find something.

Other times the explanation and the ways to do it where, although the “proper ones” too difficult for me as a newbie to understand. (Yes thick as a brick, I know!)

And at the end I was able to put all the pieces together and I had a working model easy to implement. And very proudly I can presented it here for everyone wanting to copy it. I would like to thank all the people that posted messages in all the technical forums. This is my way to giving back. Thank you.

Reading some values from the database

Include needed:
Imports System
Imports System.Data
Imports System.Data.OleDb

Controls needed:
OleDbConnection1
OleDbCommand1

ConnectionString for OleBdConnection1:

Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Data Source="c:\databaseName.mdb";Mode=Share Deny None;Jet OLEDB:Engine Type=5;Provider="Microsoft.Jet.OLEDB.4.0";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1

C:\databaseName.mdb The name of the file containing the database.

The connection DOESN’T use any password protection or other security measures and everybody can write or delete from it.

Legend:
TblInfo: The name of the table in the database.
SQLString: The string that will contain the SQL command.

The following code takes data from a database with 6 columns (0 to 5) and reads ONLY the values 0 (column 1) and 5 (column 5), being the name of a user (the former) and the date of employment (the later).

Code

Dim dateOfEmpl As String
Dim userName, SQLString As String
Dim dateOfEmpl As Date

‘set up the connection data
Dim strConnection As String = OleDbConnection1.ConnectionString
Dim connect As New OleDbConnection(strConnection)
‘ Select ALL VALUES from the Database in the ConnectionString, from the table TblInfo and sort them out by Name in asceenting orderSQLString = "SELECT * FROM TblInfo ORDER BY Name ASC"
Dim cmd As New OleDbCommand(SQLString, connect)

‘open the connection
connect.Open()
Try
Dim reader As OleDbDataReader = cmd.ExecuteReader
‘read the data
Do While reader.Read()
userName = reader(0)
dateOfEmpl = reader(5)
Loop
'close the connection when there is nothing else to read reader.Close()
connect.Close()
Catch MyError As Exception
MsgBox(MyError.Message, MsgBoxStyle.Critical, "Error")
End Try

With this code it’s easy after to expand to further usage. For example you can add the data into a list box as follows:

While reader.Read()
ListBox1.Items.Add(reader.GetString(0))
ListBox1.Items.Add(reader.GetString(1))
ListBox1.Items.Add(reader.GetString(2))
ListBox1.Items.Add(reader.GetString(3))
ListBox1.Items.Add(reader.GetString(4))
ListBox1.Items.Add(reader.GetString(5))
ListBox1.Items.Add(reader.GetString(6))
ListBox1.Items.Add(reader.GetString(7))
End While

And that’s it. Hope you find it helpful.

Run an application from a VB.NET program

Ahhhhh, today I'm a bit bored so I thought to my self why not post a small module for VB.NET that will run an application from within a Visual Basic program.

Ok, you may say "bigggggg deeeallll!!", but hey I'm bored. So here it is:

'======================================

Module modRunMe
Dim myProcess As System.Diagnostics.Process = New System.Diagnostics.Process
Public Sub RunMe(ByVal fileName As String)
Try
myProcess.StartInfo.FileName = fileName
myProcess.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Normal
myProcess.Start()
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
End Try
End Sub
End Module

'======================================

You can call it like this:

RunMe("c:\temp\TheTestPdfFile.PDF"). And it will run the PDF file or any file that you want (just as long as there is a program associated with it).

If you find it useful let me know, and you never know I might upload more code. AHAHHAHAHAHAH (sound of crackling thunder on the background)

Comments

WOW! I got 4 comments for my win stack blog. That's amazing. Thank you for your kind words (and yes you too Mr.Window-fanatic). I'll keep trying to update my blog with solutions to exotic errors as soon as I encounter them (and then shoot them and hang their heads above my mantlepiece.)

AutoCAD 2008 LT slow pull down menus

I had to see this to believe it. AutoCAD 2008 LT had a weird problem when a user on a particular computer was clicking any of the pull down menus. It was taking like 1 minute to expand it.

“For crying out loud”, I thought, “what next? What else will make my life difficult?”. It seemed that Autodesk brings out every year a bit more crappier and buggy programs. I had to solve this one too. And it wasn’t easy let me tell you. I had my user nagging me to death over it! I couldn’t take it any longer!

But suddenly……. a post in one of the Autodesk forums (not for the LT version) helped me out. I needed to do the following:

1) go to the directory that the executable was (eg acadlt.exe)

2) Open notepad and create an empty file called acadlt.dll (the same name as the executable but with the .dll at the end.) For those who do not know how to create an empty file with different extension with notepad, don’t type anything and enclose your file name with brackets.eg: “acadlt.dll”

Apparently sometimes AutoCAD needs to read that file FIRST before revert to the normal file that contains some-short-of-information-about-the-pull-down-menus (AKA I can't remember the technical specs).

3) Save it.

And voila! After a restart of AutoCAD, the annoying error went away. God damn it! A great thank you to all the people that take time to contribute in the Autodesk forums. Thank you for saving my mental health. (I saw you there, stop saying “which mental health” YOU ARE NOT HELPING!)

Unable to initialize windows sockets interface, error code 10107

I was called the other day to have a look at a computer that couldn’t go online. I thought “probably something small”. Yeah! RIGHT! On Vista? HA! So I go there. I have a look. User log on was taking minutes, welcome screen more. No IP address. No DNS server. What the hell? I had never seen something like this.

Kaspersky wasn’t working. It was complaining that some components couldn’t run. “Ok”, I thought, “let’s disable some Vista settings not needed. Maybe that will live things up a bit because the computer is ‘flowing’ like honey on sand; not at all!”.

I open up Services and disable the worst ‘evil doer of delays’ the Windows Indexing. As I was moving up and down on the list of services, trying to figure what else I can kill, I saw DHCP service stuck on ‘Starting’. “Wait a second”, I said to myself (yes I talk to me. We have the greatest conversations this way. Full of zest and intelligence. Anyyywayyyys), “that’s not right. Why is this taking so long to start? Isn’t DHCP a service that assigns an IP address from the server? Maybe that’s the reason why. Ok let’s hit Google for answers”.

Unfortunately there was NONE to be found for Vista. It was like Micro$oft didn’t want to give MORE evidence of their crap OS. I was getting angry and frustrated. I had the user asking me every 5 seconds if I can fix it? Why the damn thing is not working? I tried ipconfig /all. Media disconnected. Ipconfig /renew. Media disconnected. Nothing. I was getting even more angry in the point to just format the damn thing and put Linux in it.

All my tries with the Windows repair suggestions were getting to the same result. Nothing. But as M$ is putting it: “It seems that there is still an error with your connection. Please contact your System Administrator”. In other words “Piss off and leave us alone. Find your solution yourselves.”

But I would not get defeated. I will get to the bottom of this. As I was searching the internet, I stumble across in an article about repairing the winsock. I found some helpful commands that I run straight away:

Sfc /scannow

The command found some errors and repaired them. But at the end the connection wasn’t up still. So I tried the next command:

Netsh winsock reset

After a short run the TCPIP stack was repaired and I could see a connection again. I was overjoyed. My user was happy too. I can’t understand why M$ couldn’t add that simple command into their Automatic repairing crap that they have??? I will never find out (NOR I CARE!)

I would like to thank all those people that contributed to the technical forums that helped me fix this error and moved my knowledge, on the mystic arts of fixing Vista problems, on step further. I bow to you. Thank you.