Tweet
This program and the series of sql commands work except for the Insert which has the dte1 field. This field is defined as a date field. If the field is changed to a character field, it works fine. How can the dte1$ field be defined as a date field?
Code:
#INCLUDE "TB_ODBC352.INC"
'========================================================================================
' Main
'========================================================================================
FUNCTION PBMAIN
DEFLNG a-z
LOCAL hEnv AS DWORD
LOCAL hDbc AS DWORD
LOCAL hStmt AS DWORD
LOCAL ConStr AS STRING
' Allocates the environment handle
hEnv = OdbcAllocEnv
IF ISFALSE hEnv THEN EXIT FUNCTION
' Allocates the connection handle
hDbc = OdbcAllocConnect(hEnv)
IF ISFALSE hDbc THEN GOTO Terminate
infofle$ = "c:\accounts\cnbdt\ck001.dss"
OPEN infofle$ FOR INPUT AS 1
INPUT #1, conam$, lb1$, flena1$, lb2$, flena2$, fleno%, dr$
CLOSE 1
' flena2$="c:\develop\pbcc\odbc\okla08.mdb"
' Connects with the ODBC driver
' ConStr = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
' "DBQ=c:\develop\pbcc\odbc\okla08.mdb;UID=;PWD=;"
ConStr = "Driver={Microsoft Visual FoxPro Driver};" & _
"SourceType=DBF;" & "SourceDB=" & "C:\develop\pbcc\testlib1\disc06.dbf" & ";" & _
"Exclusive=No"
' ConStr = "Driver={Microsoft Visual FoxPro Driver};" & _
' "SourceType=DBF;" & "SourceDB=" & flena2$ & ";" & _
' "Exclusive=No"
OdbcOpenConnection(hDbc, ConStr)
IF OdbcError THEN
PRINT OdbcGetConnectionErrorInfo(hDbc)
GOTO Terminate
END IF
' Allocates an statement handle
hStmt = OdbcAllocStmt(hDbc)
' Cursor type
OdbcSetKeysetDrivenCursor hStmt
' Optimistic concurrency
OdbcSetOptimisticConcurrency hStmt
' Generates a result set
' selec$="SELECT * FROM cheetah"
' selec$="SELECT * FROM trans"
' selec$="SELECT * FROM cnbdt08"
' selec$="select sum (deposit) from cnbdt08"
' selec$="select sum (wthdl) from cnbdt08 where chkno = '501'"
' selec$="select * from cnbdt08 where interest > 0"
' selec$="select * from cnbdt08 where chkmark = '*'"
' selec$="select * from cnbdt08 where deposit > 130000"
' selec$="select * from cnbdt08 group by acctno"
' selec$="select * from cnbdt08 order by acctno"
' selec$="select * from cnbdt08 where acctno in ('1001','1004')"
' selec$="select * from cnbdt08 where deposit between 300 and 9000"
' selec$="select * from cnbdt08 having deposit > 120000"
' selec$="select sum (deposit) from cnbdt08"
' selec$="select * from cnbdt08 where acctno = '1001' and deposit > 120000"
' selec$="select acctno,deposit into cursor tmpfile from cnbdt08 where deposit = 123100" 'may not work
' selec$="select acctno,deposit to file tmpfile.dbf from cnbdt08 where deposit = 123100" 'may not work
' selec$="select * from cnbdt08 where acctno = '1001'"
' selec$="select dte1,descr,acctno from cnbdt08 where acctno = '1001'order by dte1"
' selec$="select dte1,descr,wthdl from cnbdt08 where wthdl > 0 order by dte1"
' selec$="delete from cnbdt08 where primarykey = 2"
' selec$="update cnbdt08 set deposit = 3333 where primarykey = 3"
' selec$="SELECT * FROM cnbdt08 where acctno = '1001'"
' selec$="SELECT * FROM disc06 where primarykey = 4"
' custid$="1052"
' custname$="this is a test"
' salary$="111"
' selec$="insert into cheetah (custid,custname,salary) values ('" + custid$ + "','" + custname$ + "',"+salary$+")"
primarykey$="21"
dte1$="#2008-01-01#"
descr$="test1again"
deposit$="23456"
wthdl$="5678"
interest$="890"
chkno$="2222"
chkmark$="*"
acctno$="2002"
comment$="this is a test"
chkmark2$="*"
selec$="insert into disc06 (primarykey,dte1,descr,deposit,wthdl,interest,chkno,chkmark,acctno,comment,chkmark2) " + _
"values ("+primarykey$+",'"+dte1$+"','"+descr$+"',"+deposit$+","+wthdl$+"," + interest$+",'"+ chkno$+"','"+chkmark$+"','"+acctno$+"','"+comment$+"','"+chkmark2$+"')"
' LINE INPUT selec$
OdbcExecDirect hStmt,selec$
IF OdbcError THEN
PRINT OdbcGetStatementErrorInfo(hStmt)
GOTO Terminate
END IF
' Parse the result set
DO
OdbcFetch hStmt
IF ISTRUE OdbcError THEN EXIT DO
PRINT USING$ ("\ \",OdbcGetDataString(hStmt, 1));" "; 'primarykey
PRINT USING$ ("\ \",OdbcGetDataString(hStmt, 2))" "; 'dte1
PRINT USING$ ("\ \",OdbcGetDataString(hStmt, 3));" "; 'descr
PRINT USING$ ("\ \",OdbcGetDataString(hStmt, 4));" "; 'deposit
PRINT USING$ ("\ \",OdbcGetDataString(hStmt, 5));" "; 'wthdl
PRINT USING$ ("\ \",OdbcGetDataString(hStmt, 6));" "; 'interest
PRINT USING$ ("\ \",OdbcGetDataString(hStmt, 7));" "; 'chkno
PRINT USING$ ("\ \",OdbcGetDataString(hStmt, 8));" "; 'chkmark
PRINT USING$ ("\ \",OdbcGetDataString(hStmt, 9));" "; 'acctno
PRINT USING$ ("\ \",OdbcGetDataString(hStmt, 10));" ";'comment
PRINT USING$ ("\ \",OdbcGetDataString(hStmt, 11)) 'chkmark2
LOOP
Terminate:
' Closes the cursor
IF hStmt THEN OdbcCloseCursor hStmt
' Closes the statement handle
IF hStmt THEN OdbcCloseStmt hStmt
' Closes the connection
IF hDbc THEN OdbcCloseConnection hDbc
' Frees the environment handle
IF hEnv THEN OdbcFreeEnv hEnv
WAITKEY$
END FUNCTION
'======================================================================================
Comment