VB.Net by API/System.Data.Odbc/OdbcCommand

OdbcCommand.CommandText

<source lang="vbnet"> 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

 </source>

OdbcCommand.ExecuteNonQuery()

<source lang="vbnet"> 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

 </source>

OdbcCommand.Parameters.Add

<source lang="vbnet"> 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

 </source>

OleDbCommand.CommandText

<source lang="vbnet"> Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Collections Imports System.Windows.Forms Imports System.Resources Public Class MainClass

   Shared Dim OleDbConnection As System.Data.OleDb.OleDbConnection
   Shared Dim AddressBookDataAdapter As System.Data.OleDb.OleDbDataAdapter
   Shared Sub Main()
      Connect()
      Display()
      
      Dim FirstName As String = "New Name"
      Dim ID As String = "1"

      Dim OleDbUpdateCommand As System.Data.OleDb.OleDbCommand
      OleDbUpdateCommand = new System.Data.OleDb.OleDbCommand
      
      OleDbUpdateCommand.rumandText = "UPDATE Employee SET firstName=" & _
              """ & FirstName & "" WHERE id=" & ID & " ;  "
      OleDbUpdateCommand.Connection = OleDbConnection
      AddressBookDataAdapter.UpdateCommand = OleDbUpdateCommand

      AddressBookDataAdapter.UpdateCommand.ExecuteNonQuery()

      Display()
      Disconnect()
   End Sub
   Shared Sub Display()
      Dim OleDbSelectCommand As System.Data.OleDb.OleDbCommand
      OleDbSelectCommand = New System.Data.OleDb.OleDbCommand()
      OleDbSelectCommand.rumandText = "SELECT ID, FirstName, LastName,Salary FROM Employee"
      OleDbSelectCommand.Connection = OleDbConnection
      AddressBookDataAdapter = New System.Data.OleDb.OleDbDataAdapter()
      AddressBookDataAdapter.SelectCommand = OleDbSelectCommand

      Dim AddressBookDataSet As System.Data.DataSet
      AddressBookDataSet = New System.Data.DataSet()
      CType(AddressBookDataSet, System.ruponentModel.ISupportInitialize).BeginInit()
      AddressBookDataAdapter.Fill(AddressBookDataSet)

        Dim dataTable As DataTable = AddressBookDataSet.Tables(0)
        " ensure dataTable not empty
        If dataTable.Rows.Count <> 0 Then
           Dim recordNumber As Integer = Convert.ToInt32(dataTable.Rows(0)(0))
           Console.Write( recordNumber.ToString & "  ")
           Console.Write( Convert.ToString(dataTable.Rows(0)(1)) & "  ")
           Console.Write( Convert.ToString(dataTable.Rows(0)(2)) & "  ")
           Console.Write( Convert.ToString(dataTable.Rows(0)(3)) & "  ")
           " display not-found message
        Else
           Console.WriteLine( "No record found" )
        End If
        Console.WriteLine(  )
   End Sub
   Shared Sub Connect()
     OleDbConnection = New System.Data.OleDb.OleDbConnection()
     OleDbConnection.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";" & _
        "User ID=Admin;Data Source=Employee.mdb;Mode=Sha" & _
        "re Deny None;Extended Properties="""";" & _
        "Jet OLEDB:System database="""";Jet OLEDB:Regis" & _
        "try Path="""";Jet OLEDB:Database Password="""";" & _
        "Jet OLEDB:Engine Type=5;Jet OLEDB:Dat" & _
        "abase Locking Mode=1;Jet OLEDB:Global Partial " & _
        "Bulk Ops=2;Jet OLEDB:Global Bulk T" & _
        "ransactions=1;Jet OLEDB:New Database " & _
        "Password="""";Jet OLEDB:Create System Databas" & _
        "e=False;Jet OLEDB:Encrypt Database=False;" & _
        "Jet OLEDB:Don"t Copy Locale on Compact=" & _
        "False;Jet OLEDB:Compact Without Replica " & _
        "Repair=False;Jet OLEDB:SFP=False"

     Try
        OleDbConnection.Open()
     Catch ex As Exception
        Console.WriteLine(ex.Message)
     End Try
   End Sub
   Shared Sub Disconnect()
     Try
        OleDbConnection.Close()
     Catch ex As Exception
        Console.WriteLine(ex.Message)
     End Try
   End Sub

End Class

 </source>

OleDbCommand.ExecuteReader()

<source lang="vbnet">

Imports System.Data Module Test

   Sub Main()
       Dim sConnectionString, sSQL As String
       
     sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\yourdatabase.mdb"
       sSQL = "SELECT Title FROM yourTable"
       Dim conn As New System.Data.OleDb.OleDbConnection(sConnectionString)
       Dim cmd As New System.Data.OleDb.OleDbCommand(sSQL, conn)
       Dim dr As System.Data.OleDb.OleDbDataReader
       conn.Open()
       dr = cmd.ExecuteReader()
       Do While dr.Read()
           System.Console.WriteLine(dr.Item("Title"))
       Loop
       dr.Close()
       conn.Close()
   End Sub

End Module

 </source>