Recordset return 0 for numeric fields?

[kclow]

Hi, am facing some weird problem as I am running a VBS program (not from ASP) to generate a report to Excel by selecting from a DB.

connString = "DRIVER={Oracle ODBC Driver};DBQ=XXX;UID=xxx;PWD=xxx"

Set conn=WSCRIPT.CreateObject ("Adodb.connection&quot
conn.ConnectionTimeout = 180
conn.CommandTimeout = 180
conn.Open connString

Set rs = WScript.CreateObject("ADODB.Recordset&quot
rs.Open sql_str, conn



After calling
sql_str = "select * from POSITION_HOLD"
Set rs = conn.execute(sql_str)

All the numeric fields selected returns 0 when I do as follow:
Total_Investment_unit = rs("POS_T_INVEST_UNIT&quot


Never faced such a problem before when I am using VBA or ASP.... How do I go about it?

 

[dougcranston]

kclow,

Have built several VBScripts to pull data from Access 97 and then create a workbook and worksheets. Have not seen this situation. Excuse my following statements, part is to make sure we are on the same wave length, and to step through obvious things I am sure you have done, but just to make sure.

Assume you have DIM'd the Total_Investment_unit

Assume you have confirmed that there are actual values in the DB your sourcing from and how they are actually stored.

Assume that you have tried pop'ing a msgbox right after your Total_Investment_unit = rs("POS_T_INVEST_UNIT&quot statement to see that at that point it is a "0".

Assume, since your snippet is not showing it, that you are doing a Csng, Cdbl, CInt, or CLng to convert it from a variant before using it.

Assume that you have successfully dropped this script into an ASP page and confirmed that it is working as expected there. If so, then the same code should run without a problem (sans the <% %> ref's and etc.

To see a sample of my meandering code, not sure it will help you or not, but just a point of reference, see:
thread329-521960 you are still having a problem, maybe alittle more details for the community to understand your issue.

Hope this helps.
DougCranston

 

[kclow]

>Assume you have DIM'd the Total_Investment_unit
==> Initially did not Dim my variables, but after doing so still the same

>Assume you have confirmed that there are actual values in the DB your sourcing from and how they are actually stored.
==> Yes

> Assume that you have tried pop'ing a msgbox right after your Total_Investment_unit = rs(&quot;POS_T_INVEST_UNIT&quot statement to see that at that point it is a &quot;0&quot;.
==> Yes, the value pop up and written to log is &quot;0&quot;

> Assume, since your snippet is not showing it, that you are doing a Csng, Cdbl, CInt, or CLng to convert it from a variant before using it.
==> Yes, I did tried to used Cdbl but no diff

Assume that you have successfully dropped this script into an ASP page and confirmed that it is working as expected there. If so, then the same code should run without a problem (sans the <% %> ref's and etc.
==> I do not have ASP pages here, so I can't try this out

Anyway, here's my complete scripting and hope you can help me find the &quot;bug&quot;... thanks!

'***********************************************************
'Filename : post_conv_holding_rep.vbs
'Description : Reading data from database table and write to Excel file
'Last Updated 10/04/2003 Author -- kclow
'***********************************************************
OPTION EXPLICIT

Dim OutFileObject
Dim TheNewFolder
Dim Getlogfile
Dim ConstDataSource
Dim userid
Dim password
Dim connString
Dim conn
Dim objXLA
Dim objXLB
Dim objXL
Dim Rcount
Dim Gcost_avg
Dim Ghash_total
Dim Grecords
Dim sql_str
Dim rs
Dim Fund_Code
Dim UT_Account
Dim Settlement_Group
Dim Total_Investment_unit
Dim Total_Investment_amt
Dim Fund_Currency
Dim Average_unit_cost


Set OutFileObject = WSCRIPT.CreateObject(&quot;Scripting.FileSystemObject&quot
If OutFileObject.FolderExists(&quot;.\logfiles&quot = False Then
Set TheNewFolder = OutFileObject.CreateFolder(&quot;.\logfiles&quot
End If

If OutFileObject.FileExists(&quot;.\logfiles\post_conv_holding_rep1.log&quot = True Then
OutFileObject.DeleteFile &quot;.\logfiles\post_conv_holding_rep1.log&quot;
End If
Set Getlogfile = OutFileObject.CreateTextFile(&quot;.\logfiles\post_conv_holding_rep1.log&quot;, True)

connString = &quot;DRIVER={Oracle ODBC Driver};DBQ=XXX;UID=xxx;PWD=xxx&quot;

Set conn=WSCRIPT.CreateObject (&quot;Adodb.connection&quot
conn.ConnectionTimeout = 180
conn.CommandTimeout = 180
conn.Open connString

Getlogfile.WriteLine(&quot;Connection to Datasource establish... &quot; & Date() & &quot; &quot; & Time())

Set objXLA = WScript.CreateObject(&quot;Excel.Application&quot
Set objXLB = objXLA.WorkBooks.Add
Set objXL = objXLB.Worksheets(&quot;Sheet1&quot

Getlogfile.WriteLine(&quot;Start creating workbook...&quot

objXL.Cells(1, 1).Value = &quot;Fund_code&quot;
objXL.Cells(1, 2).Value = &quot;Sett_mthd&quot;
objXL.Cells(1, 3).Value = &quot;Acct_No&quot;
objXL.Cells(1, 4).Value = &quot;Unit_Held&quot;
objXL.Cells(1, 5).Value = &quot;Purchase_Cost&quot;
objXL.Cells(1, 6).Value = &quot;Curr&quot;
objXL.Cells(1, 7).Value = &quot;Avg_Cost&quot;

Rcount = 2
Gcost_avg = 0
Ghash_total = 0
Grecords = 0
sql_str = &quot;select * from POSITION_HOLD where gen_insttu_code = 'OCBC' order by FUND_CODE, ACA_AC_NO&quot;
Set rs = WScript.CreateObject(&quot;ADODB.Recordset&quot
rs.Open sql_str, conn, 3, 3

Getlogfile.WriteLine(&quot;Start data extraction...&quot

Do While Not rs.Eof

Fund_Code = rs(&quot;FUND_CODE&quot
UT_Account = rs(&quot;ACA_AC_NO&quot
Settlement_Group = rs(&quot;POS_SETT_GROUP_CODE&quot
Total_Investment_unit = rs(&quot;POS_T_INVEST_UNIT&quot
Total_Investment_amt = rs(&quot;POS_T_INVEST_IN_FUND_CCY_AMT&quot
Fund_Currency = rs(&quot;POS_FUND_CCY_CODE&quot
Average_unit_cost = rs(&quot;POS_AVG_IN_FUND_CCY_PRICE&quot

objXL.Cells(Rcount, 1).Value = Fund_Code
objXL.Cells(Rcount, 2).Value = Settlement_Group
objXL.Cells(Rcount, 3).Value = UT_Account
objXL.Cells(Rcount, 4).Value = CStr(Total_Investment_unit)
objXL.Cells(Rcount, 5).Value = CStr(Total_Investment_amt)
objXL.Cells(Rcount, 6).Value = Fund_Currency
objXL.Cells(Rcount, 7).Value = CStr(Average_unit_cost)

Gcost_avg = CDbl(Average_unit_cost) * 1.28
Ghash_total = Ghash_total + Gcost_avg

Rcount = Rcount + 1
Grecords = Grecords + 1

Getlogfile.WriteLine(&quot;Recordcount:&quot; & Grecords)

If Grecords >= 10 Then
Exit Do
End If

rs.movenext
Loop

objXL.Cells(Rcount, 1).Value = &quot;** &quot;
objXL.Cells(Rcount+1, 1).Value = &quot;Records count: &quot; & Grecords
objXL.Cells(Rcount+2, 1).Value = &quot;Hash total: &quot; & Ghash_total

objXL.Cells.CurrentRegion.select
objXLA.Selection.Font.Bold = True
objXLA.Selection.Font.Size = 6
objXLA.Range(&quot;A1:G1&quot.Select
objXLA.Selection.Font.Size = 6

objXLA.Range(&quot;A1:G1&quot.Select
objXLA.Selection.Font.Bold = True

objXLA.Selection.Interior.ColorIndex = 5
objXLA.Selection.Interior.Pattern = 1
objXLA.Selection.Font.ColorIndex = 2

Getlogfile.WriteLine(&quot;End creating workbook...&quot

objXLB.SaveAs &quot;D:\TEMP\post_conv_holding_rep1.xls&quot;
objXLB.Close
objXLA.Application.Quit

Set objXL = Nothing
Set objXLB = Nothing
Set objXLA = Nothing

Getlogfile.WriteLine(&quot;Complete...&quot; & Date() & &quot; &quot; & Time())

Getlogfile.Close
conn.Close
Set conn = Nothing

Msgbox &quot;Report generated!&quot;
WScript.quit

 

[kclow]

Strange... Feeling unsatisfied, I tried on 2 other machines and surprisingly 1 of the other developer who also run VBS before, I can run the program successfully!

Can anyone know anything that &quot;limit&quot; the return value?
Thanks..

kclow

 

[dougcranston]

kclow,

Unfortunately, no answers, just some more obvious questions that I am sure you have already considered... But here they are any ways.

That raises an interesting question. What version of WSH/VBScript is on your machine vs that of your associates?

Also, what versions of Excel are you vs your associate are being used, including service packs?

The fact that it works on one machine and not yours and apparently a second associates points to installed software and not your coding. This may not be an easy one to fix.

Would suggest judiciously installing the latest WSH and IE from MS. IE installs some things to, so it might be in the mix. What version of IE on yours vs the associates that the script works at?

Good luck.
DougCranston

 

[kclow]

Hi DougCranston,

>What version of WSH/VBScript is on your machine vs that of your associates?
==> How do I check on this?

>Also, what versions of Excel are you vs your associate are being used, including service packs?
==> Both using Excel 97 SR 2

>What version of IE on yours vs the associates that the script works at?
==> Currently I am using IE 5.50.4807.2300 (with update SP2, Q321232), and will check ond his tomorrow...

How about ADO? Do I need to find out and how?
Thanks alot...

kclow

 

[dougcranston]

kclow,

For WSH go to:

http://www.microsoft.com/technet/tr...echnet/scriptcenter/scrguide/sas_wsh_mpud.asp

Shows a sample code to get the information on installed WSH.

For ADO see:

http://www.dmxzone.com/ShowDetail.asp?NewsId=389

Hope this helps.

DougCranston

 

[kclow]

Have checked, both the machine using
ADO Version : 2.1
WSH Version: 5.1 Build: 0

Think the only difference currently is the &quot;workable&quot; machine has NT4.0 SP6 and VB application installed...

So, should I installed both or the more &quot;significant&quot; files (*.dll) from VBA which I don't have?

Thanks...

kclow

 

[dilettante]

What about the ODBC drivers on these machines?

Oracle is notorious for level-incompatabilities. Every time some Oracle box gets upgraded we end up breaking something for a client who uses multiple Oracle servers.

Thank you Larry Ellison.

 

[dougcranston]

Boy, missed an obvious one. Don't work with Oracle, and never even thought about that one.

DougCranston

 

[dilettante]

It's probably something simple we're all missing.

 

[kclow]

Oracle ODBC driver problem? Will check on his version then...

Can't work with Oracle from VBS? Hmm... then really have to look for alternative...

 

[dougcranston]

kclow,

I didn't read that VBS can't do Oracle in dilettante's response. And you may have read into my statement of: &quot;Don't work with Oracle,&quot; all I was saying is that I do not have nor use Oracle in my environment and had not even considered the drivers as potential source of your problem.

I believe what he is saying that the Oracle ODBC drivers frequently go &quot;south&quot; when then make Oracle releases from what I gathered in dilettante's note. Therefore, suggest you look at ODBC and check the versions/releases your using to link to the Oracle db vs what your associate has installed on his PC and see if there is in fact a difference.

Hope this helps.
DougCranston

 

[jonscott8]

As eluded, it's most likely a difference in the ODBC configuration.

Try changing your connection string to use a System DSN, and in the DSN configuration, set the option Force Retrieval of Long Columns.

Jon Hawkins

 

[kclow]

Haha... DougCranston, so sorry to read your statement wrongly!

Well, finally the TRUTH is revealed!! YES, it's the ODBC driver that cause the problem!!!
After replacing my dlls version 8.1.5 with the other guy's 8.1.7 dlls, it works!!

Hehe... finally I can feel satisfied and relief... THANKS to all you guys (esp. DougCranston, dilettante and jonscott8)!!