|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
November 13th, 2006, 01:04 PM
|
||||
|
||||
|
Vbs - Unknown Runtime error
Im recieving the error - Unknown Runtime error on line: 79
Code: 800A03EC Wsheet.Cells(i, 1) = rsitems("order_ID") I posted the whole code below. I cant tell why its doing this. I also put the bad line in red in the code below. Hope you can help, Thanks Code:
Dim adoCon
set WshShell = CreateObject("WScript.Shell")
Set adoCon = CreateObject("ADODB.Connection")
adoCon.Open "Provider=SQLOLEDB.1;Password=password;Persist Security Info=True;User ID=user;Initial Catalog=db_name;Data Source=source"
Set rsItems = CreateObject("ADODB.Recordset")
Set rsItems2 = CreateObject("ADODB.Recordset")
dim fs, f
strsql = "Select status from tb_orders where status='Step 1'"
rsitems.open strsql, adocon
If Not Rsitems.EOF Then
strbatchstart = true
end if
rsitems.close
'-----------Batch Start------------
If strbatchstart = true AND Weekday(Date) <> 7 then
'----------------------------------
'-----------------------------
' Create Orders
'-----------------------------
strdate = replace(date,"/","-") & "test"
FileName = "C:\Inetpub\Batch\NB\" & strdate & "-orders.xls"
Set appxl = CreateObject("Excel.Application")
Set Book = appxl.Workbooks
Set Wsheet = Book.Add.Worksheets(1)
strsql = "Select * from tb_orders where Status='Step 1'"
rsitems.open strsql
Do while not rsitems.Eof
i = i + 1
if rsitems("shipping_Method") = "1" then
strshipping_method = "USPS Standard Mail"
elseif rsitems("shipping_method") = "2" then
strshipping_method = "USPS Express Mail"
end if
Wsheet.Cells(i, 1) = rsitems("order_ID")
Wsheet.Cells(i, 2) = rsitems("s_firstname")
Wsheet.Cells(i, 3) = rsitems("s_lastname")
Wsheet.Cells(i, 4) = rsitems("s_address")
Wsheet.Cells(i, 5) = rsitems("s_address2")
Wsheet.Cells(i, 6) = rsitems("s_city")
Wsheet.Cells(i, 7) = rsitems("s_state")
Wsheet.Cells(i, 8) = rsitems("s_zip")
Wsheet.Cells(i, 9) = rsitems("s_country")
Wsheet.Cells(i, 10) = rsitems("s_phone")
Wsheet.Cells(i, 11) = strshipping_method
Wsheet.Cells(i, 12) = rsitems("email")
rsitems.movenext
Loop
rsitems.close
i = 0
Wsheet.SaveAs (FileName)
Book.Close
appxl.quit
set appxl = nothing
set book = nothing
'-----------------------------
' Create Items
'-----------------------------
FileName = "C:\Inetpub\Batch\NB\" & strdate & "-items.xls"
'Set appxl = CreateObject("Excel.Application")
'Set Book = appxl.Workbooks
'Set Wsheet = Book.Add.Worksheets(1)
strsql = "Select * from tb_orders where Status='Step 1'"
rsitems.open strsql
Do while not rsitems.Eof
strsql2 = "Select * from tb_sold where order_ID ='" & rsitems("order_ID") & "'"
rsitems2.open strsql2, adocon
Do While not rsitems2.eof
Wsheet.Cells(i, 1) = rsitems2("order_ID")
Wsheet.Cells(i, 2) = rsitems2("Item_ID")
Wsheet.Cells(i, 3) = rsitems2("qty")
Wsheet.Cells(i, 4) = rsitems2("Item_Name")
rsitems2.movenext
Loop
rsitems2.close
rsitems.movenext
loop
rsitems.close
i = 0
Wsheet.SaveAs (FileName)
Book.Close
appxl.quit
set appxl = nothing
set book = nothing
'----------------------- Create packing Slips ---------------------------
strsheetcount = 1
xlrow = 1
master_file = "C:\Inetpub\WebSites\domainanme\vbs\pslip-template.xls"
save_as_file = "C:\Inetpub\Batch\NB\" & strdate & "-pslip.xls"
Set xl = CreateObject("Excel.Application")
xl.Visible = False
xl.DisplayAlerts = False
xl.Interactive = False
Set wrk = xl.Workbooks.Open(master_file)
'Set wrkSht = wrk.sheets(1)
'Set to Sheet1 because that is excel default
strsql = "Select * from tb_orders where status='Step 1'"
rsitems.open strsql, adocon
Do While Not Rsitems.EOF
strsql = "UPDATE tb_orders SET Status = 'Step 2'WHERE order_ID ='" & rsitems("order_ID") & "'"
adocon.execute strsql
Set wrkSht = wrk.sheets(strsheetcount)
stritemcount = 0
if rsitems("shipping_Method") = "1" then
strshipping_method = "USPS Standard Mail"
elseif rsitems("shipping_method") = "2" then
strshipping_method = "USPS Express Mail"
end if
'-------------------------------------------------------------------------
wrkSht.Cells(7, 1).Value = "#" & rsitems("order_ID")
wrkSht.Cells(8, 1).Value = "Date:"
wrkSht.Cells(9, 1).Value = "Ship to:"
wrkSht.Cells(13, 1).Value = "Via:"
wrkSht.Cells(15, 1).Value = "Item"
wrkSht.Cells(15, 8).Value = "Code"
wrkSht.Cells(15, 10).Value = "Qty"
wrkSht.Cells(7, 3).Value = "Packing Slip " & rsitems("order_ID") & " for Domainname.com"
wrkSht.Cells(8, 3).Value = now
wrkSht.Cells(9, 3).Value = rsitems("s_firstname") & " " & rsitems("s_lastname")
wrkSht.Cells(10, 3).Value = rsitems("s_address")
wrkSht.Cells(11, 3).Value = rsitems("s_city") & " " & rsitems("s_state") & ", " & rsitems("s_zip")
wrkSht.Cells(13, 3).Value = strshipping_method
strsql2 = "Select * from tb_sold where order_id='" & rsitems("order_ID") & "'"
rsitems2.open strsql2, adocon
strdelcount = 0
Do While Not Rsitems2.EOF
wrkSht.Cells(16 + cint(stritemcount), 1).Value = rsitems2("Item_Name")
wrkSht.Cells(17 + cint(stritemcount), 1).Value = "http://www.domainame.com/item.asp?itemnum=" & rsitems2("Item_ID")
wrkSht.Cells(16 + cint(stritemcount), 8).Value = rsitems2("Item_ID")
wrkSht.Cells(16 + cint(stritemcount), 10).Value = rsitems2("qty")
stritemcount = stritemcount + 2
strdelcount = strdelcount + 1
rsitems2.movenext
loop
xlrow = xlrow + 1
rsitems2.close
rsitems.movenext
If NOT rsItems.EOF then 'IF THERE IS ANOTHER RECORD, COPY/PASTE
'----------------------------------------------------------------------------------------
' PASTE NEW SHEET
'-----------------------------------------------------------------------------------------
strsheetcount = strsheetcount + 1
If strsheetcount > 3 Then
'wrk.sheets.add()
wrk.sheets.add ,wrk.sheets(wrk.sheets.count)
end if
'Set wrk = xl.Workbooks(master_file) 'SELECT THE ORIGINAL
wrk.sheets("Sheet1").Select
wrkSht.Cells.Copy 'COPY CELLS FROM 1st
Set wrkSht = wrk.sheets("Sheet" & strsheetcount) 'Set 2nd wrkSht object to sheet2
'wrkSht.Cells.Select
wrkSht.Paste 'Paste the copied text
xl.CutCopyMode = False 'I have no idea what this means
'------------- Clear items From Last Sheet-------------
For I = 0 to 53
wrkSht.Cells(16 + I, 1).Value = ""
wrkSht.Cells(17 + I, 1).Value = ""
wrkSht.Cells(16 + I, 8).Value = ""
wrkSht.Cells(16 + I, 10).Value = ""
Next
'------------------------------------------------------
End If
loop
rsitems.close
adocon.close
set rsitems = nothing
set rsitems2 = nothing
set adocon = nothing
'----------------------------------
' SAVE MASTER FILE & QUIT
'----------------------------------
wrk.SaveAs save_as_file 'THIS SECTION CAN BE USED TO SET FILE TYPE
'----------------------------------------------------------------------------
' DIFFERENT FILE TYPES
'------------------------------------------------------------------------------
'wrk.SaveAs ave_as_file, -4158 'Save as TAB DELIMITED File
'wrk.SaveAs ave_as_file, 6 'Save as a CSV File
wrk.Close
Set wrkSht = Nothing
Set wrk = Nothing
xl.Quit
Set xl = Nothing
'-------------------------------------------------------------
End if
'--------------------
__________________
That's 'cause droids don't pull people's arms out of their sockets when they lose. Wookies are known to do that.
|
|
#2
November 15th, 2006, 01:30 AM
|
||||
|
||||
|
--moved to the Windows Scripting forum.
you may have NULL value in there, or data type that can't be handled by Excel. so first, check for NULL and try to convert all the data to strings: Code:
If IsNull(rsitems("order_ID")) Then
Wsheet.Cells(i, 1) = ""
Else
Wsheet.Cells(i, 1) = CStr(rsitems("order_ID"))
End If
If IsNull(rsitems("s_firstname")) Then
Wsheet.Cells(i, 2) = ""
Else
Wsheet.Cells(i, 2) = CStr(rsitems("s_firstname"))
End If
(if it works you better make it as function)
|
|
#3
November 15th, 2006, 10:40 AM
|
||||
|
||||
|
I'm going to have to agree with Shadow on this one. I don't think your error is actually in that line, but rather has to do with the data that you're referencing. Double check any definitions.
Here's some more info on your error. Also, make sure that your script is running with permissions to edit your workbook. The closest related possibility for this particular error code involves file/folder permissions. The code structure seems okay, unless I'm overlooking something simple.
__________________
 Click the image if at any point you don't like my decision.Scripting problems? Windows questions? Ask the Windows Guru!
|
|
#4
November 15th, 2006, 12:05 PM
|
||||
|
||||
|
consider this:
when you got such line: Code:
Wsheet.Cells(i, 1) = rsitems("order_ID")
the "compiler" (it's not really compiler, but I prefer to name it like this to keep it simple) ask the database driver to give the value stored in the field "order_ID" in the recordset. now suppose that field got some fancy type in the database. the driver does not care, it would return value of this type. the act of converting the data to "common" type (integer, string etc) is performed by the compiler - but what if the type is unknown? or null? or maybe the data is converted to wrong data type thus causing weird errors later. this is the logic that led me to try and force converting the data to string.
|
|
#5
November 16th, 2006, 01:18 AM
|
||||
|
||||
|
I dont see how the problem that it being null or of the wrong data type would cause this. Becuase the first part of the script is running fine, it creates the first excel sheet even with null items.
Im thinking its something to do with creating to excel files back to back, with the excel objects or the sheets. Hope you can help. Thanks
|
|
#6
November 16th, 2006, 01:24 AM
|
||||
|
||||
|
OMG <~~~ SLAP
I cant belive I missed this, ive spent hours trying to see why this doesnt work. lol What does i = ??? lol Code:
Do While not rsitems2.eof
Wsheet.Cells(i, 1) = rsitems2("order_ID")
Wsheet.Cells(i, 2) = rsitems2("Item_ID")
Wsheet.Cells(i, 3) = rsitems2("qty")
Wsheet.Cells(i, 4) = rsitems2("Item_Name")
rsitems2.movenext
Loop
|
|
#7
November 16th, 2006, 02:48 AM
|
||||
|
||||
|
so it should have overwritten the first line.. can't see why
this caused the problem.
|
|
#8
November 16th, 2006, 08:03 AM
|
||||
|
||||
|
Well, not having a value for i will certainly cause issues, although, I'm not sure why it chose that particular error. That seems a bit odd. Let us know how it goes once you've assigned a value to i.
|
|
#9
November 16th, 2006, 08:48 AM
|
||||
|
||||
|
maybe if you write String value to some cell,
then try to write Integer to that same cell it would give such error.. ?
|
|
| Viewing: ASP Free Forums > System Administration > Windows Scripting > Vbs - Unknown Runtime error |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
