I have a problem with a numeric column , using
CASE cType = "N"
cQuery += aFlds[ i, DBS_NAME ] + " NUMERIC ( " + NTRIM( aFlds[ i, DBS_LEN ] + 1 ) + ", " + NTRIM( aFlds[ i, DBS_DEC ] ) + " ), "
to build the table.
I tryed to reduce the code as much as possible (see also viewtopic.php?f=3&t=26694).
A table is build with one numeric field (N9.2).
After the table is build , the table is populated with one value : 7500.00
A controle (line 141) :
? oRs:Fields("SALARY"):Value , x , Procline()
shows 7500.00 7500.00 141
After closing the table is the table is opened again , line 166 :
? oRs:Fields("Salary"):Value
Shows 750000.00
Why it is multplyed with 100 ?????????????????????????????
Frank
- Code: Select all Expand view
# include "fivewin.ch"
# include "dbstruct.ch"
# include "ado.ch"
# include "xbrowse.ch"
#xcommand DBG <vars,...> => ;
XBrowse( ArrTranspose( \{ \{ <"vars"> \}, Eval( \{ || \{ <vars> \} \} ) \} ), ;
ProcName(0) + " : Line : " + LTrim( Str( ProcLine(0) ) ),, ;
{ |o| o:cHeaders := { "Variable", "Value" } } )
#define STRIM( cStr, nChr ) Left( cStr, Len( cStr ) - nChr )
#define NTRIM( nNumber ) LTrim( Str( nNumber ) )
PROC MAIN(...)
// DbfFile , MdbFile , TableName
LOCAL aFlds[0]
SET DELETED on
SET CENTURY on
SET 3DLOOK on
IF File("Test.mdb")
fErase("Test.mdb")
END
IF ! File("Test.mdb")
FW_CreateMDB( "Test.mdb") // FWH1305
END
aFlds := {{"Salary","N",9,2}}
AddTable("JET","Customer",aFlds,"Test.mdb")
Populate("Test.mdb","Customer",7500.00)
AdoBrowse("Test.mdb","Customer")
RETURN
********************************************************************************************************
FUNC AddTable(cMot, cTab, aFld , cMdbFile)
******************************************
LOCAL oCn , xConnect , cSQL , oErr
Try
oCn := CREATEOBJECT( "ADODB.Connection" )
Catch
MsgInfo( "Could not create the ADO object for connection")
End Try
TRY
xCONNECT := "Provider='Microsoft.Jet.OLEDB.4.0'; Data Source=" + cMdbFile //Test.mdb"
oCn:Open( xCONNECT )
CATCH oErr
MsgInfo( "Could not open a Connection to Database "+cMdbFile )
RETURN(.F.)
END TRY
cSQL := Enrico(aFld,"JET",cTab)
Try
oCn:Execute( cSQL )
Catch
MsgInfo( "Create Table Customer Failed" )
oCn:Close()
Return(.f.)
End try
oCn:Close()
oCn := nil
RETURN(.T.)
*************************************************************************
FUNC enrico(aFld,cMot,cTab)
*****************
LOCAL cQuery := "CREATE TABLE " + cTab + " ( "
LOCAL cType
LOCAL i , el
LOCAL aFlds := aClone(aFld)
IF cMot == "JET"
cQuery += "Id COUNTER PRIMARY KEY, "
ELSEIF cMot == "MSSQL"
cQuery += "Id INT IDENTITY PRIMARY KEY, "
ELSEIF cMot == "MYSQL"
cQuery += "Id SERIAL, "
ENDIF
FOR i = 1 TO LEN( aFlds )
cType = aFlds[ i, DBS_TYPE ]
IF cType <> "M" .OR. cMot <> "MYSQL"
aFlds[i,1] := ALLTRIM(aFlds[i,1])
IF LEFT(aFlds[i,1],1)<>"["
aFlds[i,1] := "[" + aFlds[i,1]
END
IF RIGHT(aFlds[i,1],1)<>"]"
aFlds[i,1] += "]"
END
END
DO CASE
CASE cType = "C"
cQuery += aFlds[ i, DBS_NAME ] + " VARCHAR ( " + NTRIM( aFlds[ i, DBS_LEN ] ) + " ), "
CASE cType = "N"
cQuery += aFlds[ i, DBS_NAME ] + " NUMERIC ( " + NTRIM( aFlds[ i, DBS_LEN ] + 1 ) + ", " + NTRIM( aFlds[ i, DBS_DEC ] ) + " ), "
CASE cType = "D"
cQuery += aFlds[ i, DBS_NAME ] + " DATETIME, "
CASE cType = "L"
//"[Married] Yesno NULL, "
IF cMot == "JET"
cQuery += aFlds[ i, DBS_NAME ] + " yesno NULL, "
ELSE
cQuery += aFlds[ i, DBS_NAME ] + " INT, "
ENDIF
CASE cType = "M"
IF cMot == "JET"
cQuery += "[" + aFlds[ i, DBS_NAME ] + "]" + " MEMO, "
ELSEIF cMot == "MSSQL"
cQuery += "[" + aFlds[ i, DBS_NAME ] + "]" + " TEXT, "
ELSEIF cMot == "MYSQL"
cQuery += aFlds[ i, DBS_NAME ] + " TEXT, "
ENDIF
ENDCASE
NEXT
cQuery = STRIM( cQuery, 2 ) + " )"
RETURN cQuery
FUNC Populate(cMdbFile,cTableName,x)
LOCAL xConnect := "Provider='Microsoft.Jet.OLEDB.4.0'; Data Source=" + cMdbFile
LOCAL oRs , cSQL , oErr , saying , i , j , n
LOCAL fName
oRs := TOleAuto():New( "ADODB.Recordset" )
oRs:CursorType := 1 // opendkeyset
oRs:CursorLocation := 3 // local cache
oRs:LockType := 3 // lockoportunistic
cSQL := "SELECT * FROM " + cTableName //Customer"
TRY
oRs:Open( cSQL, xCONNECT )
CATCH oErr
? oErr:Description , Procname() , procline()
Return(.f.)
End Try
oRs:AddNew()
oRs:Fields("Salary"):Value := x
oRs:Update()
? oRs:Fields("SALARY"):Value , x , Procline()
oRs:CLose()
oRs := nil
