ODBC Library

[Pachuquin]

I have used the SQLDesktop.cs library (with the permission of Erel) to create a desktop library to access databases via Odbc connection.

The objects, methods and properties are almost identical, except:

BeginTransaction
AddParameter

It requires Microsoft.Data.Odbc.dll that can be downloaded from here.

I would like to write a help file, but nobody would understand it due to my horrible english. Any volunteer?

v0.5 Added two methods:

FieldNames - Return a string array filled with the name of the colums.

FieldType(int) - Return a string with the type of the field

Here is a sample:

con.New1

con.Open("DSN=Sales;")
   
[COLOR="seagreen"]'con.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\pacientes.mdb;" )
             'This would be for an ODBC DSN-Less connection[/COLOR]
   
cmd.New1("",con.Value)
reader.New1
   
con.BeginTransaction(cmd.Value) 
             [COLOR="seagreen"]'Must specify the Command object used[/COLOR]
   
cmd.AddParameter("id1",cmd.typeDouble ) 
            [COLOR="seagreen"] 'Must specify the paramater data type[/COLOR]

cmd.SetParameter("id1","9500")
   
cmd.AddParameter("id2",cmd.typeDouble ) 
             [COLOR="seagreen"]'Must specify the paramater data type[/COLOR]

cmd.SetParameter("id2","9520")

cmd.CommandText="SELECT * FROM final WHERE id> ? AND id< ?"
[COLOR="SeaGreen"]             'Parameter's names don't work in ODBC with CommandText, 
   'so you have to write question marks that will be replaced in the order that you added the parameters.[/COLOR]
   
reader.Value=cmd.ExecuteReader 
Do While reader.ReadNextRow 
   Msgbox(reader.GetValue(1))
Loop
reader.Close 
con.EndTransaction

You can find various connections strings here.

 

[Pachuquin]

Updated library

v0.5 Added two methods:

FieldNames - Return a string array filled with the name of the colums.

FieldType(int) - Return a string with the type of the field

 

[KeithTX]

This is just what I have been looking for, great work. Thanks!

I would be happy to assist with the help file, just let me know what I can do.

-Keith

 

[lancaster]

Excellent library. Thank you.

For anyone interested in reading Sage Line 50 Accounts package data, the following works for me to populate B4PPC tables with customer or product details.

Sub Globals
'Declare the global variables here.

End Sub

Sub App_Start
frmMain.Show
DbConn.New1
Dbconn.Open("DSN=SageLine50v14;UID=Manager;Pwd=;Mode=Read")
cmd.New1("",DbConn.Value)
reader.New1
End Sub


Sub btnAccounts_Click
Dbconn.BeginTransaction(cmd.Value)
cmd.CommandText = "SELECT * FROM SALES_LEDGER"
cmd.ExecuteTable("tblResults",0)
Dbconn.EndTransaction
End Sub

Sub btnProducts_Click
Dbconn.BeginTransaction(cmd.Value)
cmd.CommandText = "SELECT * FROM STOCK"
cmd.ExecuteTable("tblResults",0)
Dbconn.EndTransaction
End Sub


The ODBC DSN is part of the Sage installation. This one for V14 of the package.