-->

				got a virus?

Pc Technician - visual studio code examples

alt="" />

Image from the antique store inventory program

In this code example using Visual Studio basic - I wanted to read an MDB database and extract some values and save them to a text file. This example illustrates the following:

1. Access an MDB database using a connect string. If you have ever tried this - it can be painful as the varying syntax levels may drive you to drink - see link HERE
2. Replace characters in a string.
3. Replace null values.
4. Write to a text file.

Feel free to copy the code below

' Code to read mdb  database -  using connection string

' Define connection string and the SQL  query string to query the database

Dim sConnectionString,    sSQL As String

'Set the value of the SQL Connection String:

sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                          " Data Source=" &  (  location of the mdb database , eg  "c:\db\mydatabase.mdb")

' Set the value of the SQL query string:

sSQL = "SELECT * FROM  (table - this is your table name in the database) "

' Setup variables to read the database  with the following code.
' In this example to read the database records and display the
' first, second, and sixth columns in the database table. Also to 
' count the records.  In this database - there are text fields with 
' single quotes and the these quotes are replaced with escape 
' double quote. The null values in the numerical field is also 
' replaced with a numerical  0 . This is the sixth column in my 
' database.
' I did this to clean up the data which is then saved to a txt file to
' be later read and inserted into a temporary mdb database.
' This cleanup was necessary as the code to  SQL insert the data
' into the temporary database cannot have single quotes contained
' in the text field and the expected interger values must be intergers
' and not nulls.

' Set up the variable  to point to a txt file

Dim writer As StreamWriter = _
                    New StreamWriter("c:\TEXTFILE\saved.txt")

' One note of caution - for the above - do not try to save
' a txt file in the root directory (windows 7) as it will complain
' and Bill Gates will not like you.

Dim conn As New OleDbConnection(sConnectionString)
        Dim cmd As New OleDbCommand(sSQL, conn)
        Dim dr As OleDbDataReader
        Dim dblquote As String
        Dim numval As Single
        Dim  count As Integer

Using conn
            conn.Open()
            cmd.CommandText = sSQL
            cmd.CommandType = CommandType.Text
            cmd.Connection = conn
            dr = cmd.ExecuteReader()

'------------------------------------------------
            '

If dr.HasRows Then
                Do While dr.Read()
                    ' count records
                    count = count + 1

' MessageBox.Show(dr.GetValue(0) & "," & dr.GetValue(1) & "," & dr.GetValue(5) )
                    ' replace single quote with escape double quote

dblquote = Replace(dr.GetValue(1), "'", "''")
                    '  MessageBox.Show(dblquote)

If IsDBNull(dr.GetValue(5)) Then
                        numval = 0

'  write data to the txt file:

writer.WriteLine(dr.GetValue(0) & ",'" & dblquote & "'," & numval)
                    Else
                        writer.WriteLine(dr.GetValue(0) & ",'" & dblquote & "'," & dr.GetValue(5))
                    End If

Loop

MessageBox.Show("number rows in table is " & count)

Else

MessageBox.Show("No data found")
                conn.Close()
                Close()

End If
            '------------------------------------------------
            '

End Using

Cool Counters @ pctechnician.com

VISUAL STUDIO BASIC CODE EXAMPLES

Your Ad Here