How to read all data in a database column

Question

0.00/5 (No votes)

See more:

I have looked around the net trying to find the best way to loop through the columns in my access database tables. I would like to know if there is a more flexible method than the one I am trying to use.

Code:

VB

Dim conn = New OleDbConnection ("Connectionstring")
Conn.Open()
Dim CMD as new oleDb.oleDbCommand("Select * from Sig Database", Conn, Conn.BeginTransaction)
Dim DR = CMD.ExecuteReader
Do While DR.Read
Dim V1 = DR.item("Sig")   
Dim V2 = DR.item("threat")
Dim V3 = DR.item("Virus Name")
DataGridview1.Rows.Add(V1, V2, V3)
Loop
DR.Close
Conn.Close()

In short I am looking to compare my strings from each file on my computer to see if it matches a string in my access database. if true add the matching string found to a listbox.

Thank you very much in advance

Just one other small question out of curiosity:

I would like to know if there is an equivalent to stream reader but for access database?. Maybe File stream? or anything to get better performance from the string search?

Posted 31-Jan-12 14:58pm

Dale 2012

Updated 31-Jan-12 15:08pm

Chris Maunder773.6K

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Accepted Answer · 2012-01-31T16:34:00

Solution 1

The correct way to do it is, first, only read the data you need and second, let the DB do it's job. So, you should run SQL that asks if the column contains the value you want, not grab all the data and do string comparisons in code.

Posted 31-Jan-12 16:34pm

Christian Graus

Comments

Dale 2012 1-Feb-12 14:00pm

I can run SQL on access database?...... Im a bit confused now as to what you mean. Can you give an example of what your talking about please?

Christian Graus 1-Feb-12 14:03pm

Access is a database. In fact, Access by default nowadays uses SQL Server for it's data, if it is installed. Yes, you should be able to run SQL on an Access DB, unless something has changed since I last looked at it. If you can't, then ditch Access and use SQL Server Compact Edition or SQLite.

_Maxxx_ · Accepted Answer · 2012-02-01T01:50:00

Assuming you have to have the info in your db, the. Looping as you are is not a bad option.
Adding to some collection rather than a datagridview would be better - as I'm guessing you don't really want to see the data?
I'm guessing you're going to stream in a file and the look through your datagridview looking for matches? That's going to be pretty slow if you have many sigs or many files, so you want to be as efficient as should can.

So - store the sig threat and name in some collection like a List(of VirusDefinition)
Then you can iterate that list...

VB

For each vid as VirusDefinition in myVirusDefinitions
If myTextFileInAString.Contains(vid.Sig) Then
    Do whatever you want to do
End if
Next