Application Note for QuNect ODBC for QuickBase
VB.Net Console Application for Copying Rows from a SQL Server Table to a Quickbase Table
This console application takes five command line arguments.
The SQL Server table should have as the last word in its name the DBID of the corresponding Quickbase table. You can find the DBID of your Quickbase table by reading the How to Find the DBID of a Quickbase Table application note. All columns in the SQL Server table will be copied to the columns of the same name in the Quickbase table. This piece of code relies on a properly configured ODBC Data Source called QuickBase via QuNect.
Imports System.Data.Odbc Imports System.Data.SqlClient Module SQLQuNectQuickBase Dim quNectConn As OdbcConnection Dim quNectCmd As OdbcCommand Dim quNectCacheWritesCmd as OdbcCommand Dim quNectFlushCmd As OdbcCommand Dim quNectParam As OdbcParameter Dim sqlConn As SqlConnection Dim sqlComm As SqlCommand Dim sqlDr As SqlDataReader Sub Main() If My.Application.CommandLineArgs.Count <> 5 Then Console.WriteLine("Please supply: SQLserver SQLuser SQLpassword SQLDatabase SQLTable") Console.WriteLine("The SQL table should have the Quickbase DBID as the last or only word in the table name.") Exit Sub End If Dim argvServer As String = My.Application.CommandLineArgs.Item(0) Dim argvUID As String = My.Application.CommandLineArgs.Item(1) Dim argvPassword As String = My.Application.CommandLineArgs.Item(2) Dim argvDatabase As String = My.Application.CommandLineArgs.Item(3) Dim argvTable As String = My.Application.CommandLineArgs.Item(4) quNectConn = New OdbcConnection("DSN=QuickBase via QuNect;") Try quNectConn.Open() Catch excpt As Exception Console.WriteLine(excpt.Message) Exit Sub End Try 'First let's open up a connection to SQL Server Dim sqlConnectString As String = "server=" & argvServer & ";uid=" & argvUID & ";pwd=" & argvPassword & ";database=" & argvDatabase sqlConn = New SqlConnection(sqlConnectString) sqlConn.Open() sqlComm = New SqlCommand("Select * from """ & argvTable & """", sqlConn) Try sqlDr = sqlComm.ExecuteReader() Catch excpt As Exception Console.WriteLine("SQL Server complained: " & excpt.Message) Exit Sub End Try Dim fieldCount As Integer = sqlDr.FieldCount Dim fieldCounter As Integer Dim quickBaseSQLFieldList As String = "(" Dim quickBaseSQLParameterList As String = "(" Dim quickBaseSQL As String For fieldCounter = 0 To fieldCount - 1 quickBaseSQLFieldList &= """" & sqlDr.GetName(fieldCounter).ToString() & """," quickBaseSQLParameterList &= "?," Next quickBaseSQLFieldList = quickBaseSQLFieldList.Substring(0, quickBaseSQLFieldList.Length - 1) & ")" quickBaseSQLParameterList = quickBaseSQLParameterList.Substring(0, quickBaseSQLParameterList.Length - 1) & ")" quickBaseSQL = "INSERT INTO """ & argvTable & """ " & quickBaseSQLFieldList & " VALUES " & quickBaseSQLParameterList quNectCmd = New OdbcCommand(quickBaseSQL, quNectConn) For fieldCounter = 0 To fieldCount - 1 Dim thisODBCType As Odbc.OdbcType If sqlDr.GetFieldType(fieldCounter).Name = "String" Then thisODBCType = OdbcType.NVarChar ElseIf sqlDr.GetFieldType(fieldCounter).Name = "Double" Then thisODBCType = OdbcType.Double ElseIf sqlDr.GetFieldType(fieldCounter).Name = "DateTime" Then thisODBCType = OdbcType.DateTime ElseIf sqlDr.GetFieldType(fieldCounter).Name = "Date" Then thisODBCType = OdbcType.Date ElseIf sqlDr.GetFieldType(fieldCounter).Name = "Money" Then thisODBCType = OdbcType.Numeric ElseIf sqlDr.GetFieldType(fieldCounter).Name = "Bit" Then thisODBCType = OdbcType.Bit ElseIf sqlDr.GetFieldType(fieldCounter).Name.Contains("Char") Then thisODBCType = OdbcType.VarChar ElseIf sqlDr.GetFieldType(fieldCounter).Name = "Decimal" Then thisODBCType = OdbcType.Decimal End If quNectParam = New OdbcParameter("@qdb" & fieldCounter, thisODBCType) quNectCmd.Parameters.Add(quNectParam) Next While sqlDr.Read() For fieldCounter = 0 To fieldCount - 1 quNectCmd.Parameters(fieldCounter).Value = sqlDr(fieldCounter) Next fieldCounter Try quNectCmd.ExecuteNonQuery() Catch excpt As Exception Console.WriteLine(excpt.Message) End Try End While sqlDr.Close() sqlConn.Close() quNectConn.Close() End Sub End Module