VB.Net/Database ADO.net/ODBC

Odbc Connection Demo

Imports System
Imports System.Data
Imports System.Data.Odbc
Module OdbcProvider
   Sub Main()
      " Set up connection string
      Dim ConnString As String = "dsn=NorthwindOdbc"
      " Set up query string
      Dim CmdString As String = "SELECT * FROM employees"
      "Declare Connection and DataReader variables
      Dim Conn As OdbcConnection
      Dim Reader As OdbcDataReader
      Try
         "Open Connection
         Conn = New OdbcConnection(ConnString)
         Conn.Open()
         "Execute Query
         Dim Cmd As New OdbcCommand(CmdString, Conn)
         Reader = Cmd.ExecuteReader()
         "Display output header
         Console.WriteLine("Querying database {0} with query {1}" & _
            ControlChars.NewLine, Conn.Database, Cmd.rumandText)
         Console.WriteLine("FirstName" & ControlChars.Tab & "LastName")
         "Process The Result Set
         While (Reader.Read())
            Console.WriteLine(Reader("FirstName").PadLeft(9) & _
               ControlChars.Tab & Reader(1))
         End While
      Catch ex As Exception
         Console.WriteLine("Error: {0}", ex)
      Finally
         "Close Connection
         Reader.Close()
         Conn.Close()
      End Try
   End Sub
End Module

Use command parameter for Odbc connection

Imports System
Imports System.Data
Imports System.Data.Odbc
Module CommandOdbcExample
   Sub Main()
      Dim thisConnection As New OdbcConnection _
         ("dsn=MyOdbc")
      Dim nonqueryCommand As OdbcCommand = thisConnection.CreateCommand()
      Try
         thisConnection.Open()
         Console.WriteLine("Connection Opened")
         nonqueryCommand.rumandText = "CREATE TABLE MyTable " & _
            "(MyName VARCHAR (30), MyNumber integer)"
         Console.WriteLine("Executing {0}", _
            nonqueryCommand.rumandText)
         Console.WriteLine("Number of rows affected : {0}", _
            nonqueryCommand.ExecuteNonQuery())
         nonqueryCommand.rumandText = "INSERT INTO MyTable VALUES (?, ?)"
         nonqueryCommand.Parameters.Add("@MyName", OdbcType.VarChar, 30)
         nonqueryCommand.Parameters.Add("@MyNumber", OdbcType.Int)
         " Prepare command not supported in ODBC
         " nonqueryCommand.Prepare()
         " Data to be inserted
         Dim names() As String = {"Z", "S", "J", "D"}
         For i As Integer = 1 To 4
            nonqueryCommand.Parameters("@MyName").Value = names(i - 1)
            nonqueryCommand.Parameters("@MyNumber").Value = i
            Console.WriteLine("Executing {0}", _
               nonqueryCommand.rumandText)
            Console.WriteLine("Number of rows affected : {0}", _
               nonqueryCommand.ExecuteNonQuery())
         Next i
         nonqueryCommand.rumandText = _
            "SELECT MyName, MyNumber FROM MyTable"
         Dim thisReader As OdbcDataReader = nonqueryCommand.ExecuteReader()
         While (thisReader.Read())
            Console.WriteLine("Name and Number: {0} {1}", _
               thisReader.GetValue(0), thisReader.GetValue(1))
         End While
         thisReader.Close()
         nonqueryCommand.rumandText = "DROP TABLE MyTable"
         nonqueryCommand.ExecuteNonQuery()
      Catch ex As OdbcException
         " Display error
         Console.WriteLine("Error: " & ex.ToString())
      Finally
         " Close Connection
         thisConnection.Close()
         Console.WriteLine("Connection Closed")
      End Try
   End Sub
End Module