				got a virus?

Pc Technician - visual studio code examples

Image from an antique store inventory program written with VB Studio

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 ConnectionString, SQL As String 'Set the value of the SQL Connection String: ConnectionString = "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: SQL = "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 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