wfinn
asked on
Trailing zeroes in numeric fields being truncated in ADO recordset
Hello Experts,
I have a visual basic dll that is executing a query on Sybase System 11 using ADO. If I execute the query in Sybase, or look directly at the table the results are 100% correct. When the data is retrieved into the ADO recordset, any numeric fields have trailing zeroes removed. For example the number 1850 will be returned as 185, 1500 will be returned as 15.
Now just for interest, I tried the same query, and swithed to microsoft RDO drivers. With RDO everything worked correctly. However, switching to RDO is not an option for me at this point.
I have checked the web and found a hit on the microsoft web site that talked about this exact problem. However they indicated it should have been fixed with mdac version 2.5. I am running mdac version 2.7.
I am at a loss as to what else could be causing the problem. This is a very urgent problem as it is affecting a production application. It has been shutdown until this is resolved. Does anyone have a solution for this problem?
Thanks,
wfinn
I have a visual basic dll that is executing a query on Sybase System 11 using ADO. If I execute the query in Sybase, or look directly at the table the results are 100% correct. When the data is retrieved into the ADO recordset, any numeric fields have trailing zeroes removed. For example the number 1850 will be returned as 185, 1500 will be returned as 15.
Now just for interest, I tried the same query, and swithed to microsoft RDO drivers. With RDO everything worked correctly. However, switching to RDO is not an option for me at this point.
I have checked the web and found a hit on the microsoft web site that talked about this exact problem. However they indicated it should have been fixed with mdac version 2.5. I am running mdac version 2.7.
I am at a loss as to what else could be causing the problem. This is a very urgent problem as it is affecting a production application. It has been shutdown until this is resolved. Does anyone have a solution for this problem?
Thanks,
wfinn
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sethi
Install MDAC 2.7 Refresh and change the OLE DB drivers for Try using default MDASQL drivers. If the result is correct then there is some problem with OLEDB drivers you are using.
wat query u using?
ASKER
In answer to your comments:
supunr: The database is on a different computer. It is running sybase on a unix machine. I am not using cursors.
Sethi: I tried installing MDAC 2.7 Refresh. No luck. I still get the same result. I am not sure what you mean by using the default MDASQL drivers. How do I do that?
Asif_Saadat: Here is the code I am using. The query really doesn't matter. I will just show you a very simple example that fails.
Private Sub Command1_Click()
Dim strQuery As String
Dim mobjSybaseConnector As libEFTSybase.EFTSybaseConn
Dim madconDB As ADODB.Connection ' Database Connection
Set mobjSybaseConnector = New libEFTSybase.EFTSybaseConn
Set madconDB = mobjSybaseConnector.adconG
strQuery = "SELECT amount from teft"
Dim objCmd As ADODB.Command
Dim objRS As ADODB.Recordset
Set objCmd = New ADODB.Command
With objCmd
Set .ActiveConnection = madconDB
.CommandType = adCmdText
'Input Parameters
.CommandText = strQuery
Set objRS = .Execute
End With
While Not objRS.EOF
objRS.MoveNext
Wend
End Sub
supunr: The database is on a different computer. It is running sybase on a unix machine. I am not using cursors.
Sethi: I tried installing MDAC 2.7 Refresh. No luck. I still get the same result. I am not sure what you mean by using the default MDASQL drivers. How do I do that?
Asif_Saadat: Here is the code I am using. The query really doesn't matter. I will just show you a very simple example that fails.
Private Sub Command1_Click()
Dim strQuery As String
Dim mobjSybaseConnector As libEFTSybase.EFTSybaseConn
Dim madconDB As ADODB.Connection ' Database Connection
Set mobjSybaseConnector = New libEFTSybase.EFTSybaseConn
Set madconDB = mobjSybaseConnector.adconG
strQuery = "SELECT amount from teft"
Dim objCmd As ADODB.Command
Dim objRS As ADODB.Recordset
Set objCmd = New ADODB.Command
With objCmd
Set .ActiveConnection = madconDB
.CommandType = adCmdText
'Input Parameters
.CommandText = strQuery
Set objRS = .Execute
End With
While Not objRS.EOF
objRS.MoveNext
Wend
End Sub
When you create a DSN specify provider other than Sybase. Try OLEDB for ODBC. Although this is not a permanent solution but it will help in determining the source of error.
ASKER
I do not have anything called OLEDB for ODBC or any default driver close to that on my PC. Is there a web site that I can download such driver from?
When you install MDAC these drivers are installed on your machine. How are you creating your DSN?
ASKER
I am reading the values from the Registry. Here is the code that creates the connection:
' get connection variables from the registry
strUserId = objRegistry.ReadDataWithDe
mstrREG_ROOTKEY, _
mstrREG_USERID_KEY, _
mstrREG_DEFAULT_USID)
Set objEnv = New libEnvironment.Environment
strEncryptedPwd = objRegistry.ReadDataWithDe
mstrREG_ROOTKEY, _
mstrREG_PASSWORD_KEY, _
objEnv.Encrypt(mstrREG_DEF
strDriver = objRegistry.ReadDataWithDe
mstrREG_ROOTKEY, _
mstrREG_DRIVER_KEY, _
mstrREG_DEFAULT_DRIVER)
strLibraryName = objRegistry.ReadDataWithDe
mstrREG_ROOTKEY, _
mstrREG_LIBRARY_KEY, _
mstrREG_DEFAULT_LIBRARY)
strOther = objRegistry.ReadDataWithDe
mstrREG_ROOTKEY, _
mstrREG_OTHER_KEY, _
mstrREG_DEFAULT_OTHER)
Set objRegistry = Nothing
' the password in the registry is encrypted so decrypt it
strPassword = objEnv.Decrypt(strEncrypte
Set objEnv = Nothing
Set adconThis = New ADODB.Connection
With adconThis
' build dsnless connection string
.ConnectionString = "Driver=" & strDriver & ";" & _
"UID=" & strUserId & ";" & _
"PWD=" & strPassword & ";" & _
"SRVR=" & strLibraryName & ";" & _
strOther
.Open
End With
' get connection variables from the registry
strUserId = objRegistry.ReadDataWithDe
mstrREG_ROOTKEY, _
mstrREG_USERID_KEY, _
mstrREG_DEFAULT_USID)
Set objEnv = New libEnvironment.Environment
strEncryptedPwd = objRegistry.ReadDataWithDe
mstrREG_ROOTKEY, _
mstrREG_PASSWORD_KEY, _
objEnv.Encrypt(mstrREG_DEF
strDriver = objRegistry.ReadDataWithDe
mstrREG_ROOTKEY, _
mstrREG_DRIVER_KEY, _
mstrREG_DEFAULT_DRIVER)
strLibraryName = objRegistry.ReadDataWithDe
mstrREG_ROOTKEY, _
mstrREG_LIBRARY_KEY, _
mstrREG_DEFAULT_LIBRARY)
strOther = objRegistry.ReadDataWithDe
mstrREG_ROOTKEY, _
mstrREG_OTHER_KEY, _
mstrREG_DEFAULT_OTHER)
Set objRegistry = Nothing
' the password in the registry is encrypted so decrypt it
strPassword = objEnv.Decrypt(strEncrypte
Set objEnv = Nothing
Set adconThis = New ADODB.Connection
With adconThis
' build dsnless connection string
.ConnectionString = "Driver=" & strDriver & ";" & _
"UID=" & strUserId & ";" & _
"PWD=" & strPassword & ";" & _
"SRVR=" & strLibraryName & ";" & _
strOther
.Open
End With
ASKER
supunr:
As it turns out, your comment had the answer in it. I didn't realize that by default ADO uses Server side cursors. I changed the code to use Client side cursers by using the command objRS.CursorLocation = adUseClient.
Thanks for your help.
As it turns out, your comment had the answer in it. I didn't realize that by default ADO uses Server side cursors. I changed the code to use Client side cursers by using the command objRS.CursorLocation = adUseClient.
Thanks for your help.