September 9, 2008 at 12:18 pm
I wrote a VBscript that works fine. Overview:
1. Code prompts for an asset ID
2. Code retrieves the row from the asset table that matches that asset ID
3. Insert a duplicate record, skipping the identity column (pk_auto_id)
4. Retrieve the indentity of the new row and save it for later use
5. Locate the "extended data table" for that asset (one for servers, one for laptops, etc.) that contains additional info unique to that asset type.
6. Open that extended table and select the row with a matching pk_auto_id from the master asset file
7. Write out that new "cloned" extended row with the pk_auto_id of the newly written asset row from step 4
Everything seems to work fine EXCEPT I cannot do additional record opens to get the data for step 4 and step 6. The rowcount returned for both is "-1".
Can anyone help me figure out how to open the RS recordset again for those additional inputs? It has taken me 3 days to get this script ready to go.
Here is the code (heavily commented):
' This script will select records from an SQL statement and put them in an csv file on the Desktop
' for processing anyway we want in Excel.
' This version will read all software licensing information from Supportworks tables and put it in an Excel and then email it to itops
' Create the database connection (in this version it is MS SQL Server with no DSN)
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLNCLI;" _
& "Server=itopshelpdesk;" _
& "Database=sworks;" _
& "Uid=sw;" _
& "Pwd=sw&*9y;"
Set RS = CreateObject("ADODB.RecordSet")
' now create the record set with a particular SQL. It is easier to create the SQL from a view in
' Microsoft Mgt Studio or Quest Toad and paste it in here. Note that for long SQL statements you must use continuation characters and quotes
' for putting it all in a variable with ligibility. A length sample is below to use as a guide
'RS.Open "SELECT dbo.config_itemi.ck_config_type, dbo.config_itemi.ck_config_item, dbo.config_itemi.description, dbo.config_itemi.ci_support_analyst," _
' & " dbo.tq_citype_software.tq_media_type, dbo.tq_citype_software.tq_media_count, dbo.tq_citype_software.tq_num_licenses," _
' & " dbo.tq_citype_software.tq_num_used_licenses, dbo.tq_citype_software.tq_num_unused_licenses" _
' & " FROM dbo.config_itemi INNER JOIN" _
'& " dbo.tq_citype_software ON dbo.config_itemi.pk_auto_id = dbo.tq_citype_software.pk_ci_id" _
'& " WHERE (dbo.tq_citype_software.tq_num_licenses > 0)", conn
' ask the user for a CI number
CInumber = InputBox("Which CI Do You Want To Clone?","Hornbill CI Clone")
If CInumber = "" Then
wscript.quit
End If
RS.Open "SELECT * from dbo.config_itemi WHERE (dbo.config_itemi.ck_config_item = '" & CINumber & "'" & ") AND (dbo.config_itemi.isactivebaseline = 'Yes')", conn
' Now create a text file with the same name as the table but with a "csv" extention
Set shell = CreateObject("WScript.Shell")
Set fso = CreateObject("Scripting.FileSystemObject")
' let's make sure we got some records in case a invalid CI number was entered
If RS.EOF Then
MsgBox ("There is no CI with a number of " & CINumber)
wscript.quit
Else
'we got some records so continue
End If
' create the INSERT header
insert = "INSERT INTO CONFIG_ITEMI (" & Chr(13)
For fnum = 0 To RS.Fields.Count - 1
' each element in the Fields collection is an ADODB.Field object. And the Field object has various
' properties, including its Name:
' skip the identity column
If RS.Fields(fnum).Name <> "pk_auto_id" Then
insert = insert & RS.Fields(fnum).Name & ", " & Chr(13)
End If
Next
' remove our trailing comma from the last entry
inslength = Len(insert)
insert = Left(insert, inslength - 3)
' now let's get the actual values that we retrieved and start with the SQL clause
insert = insert & ")" & Chr(13) & "VALUES(" & Chr(13)
'although we should only have one record, use a structured loop for good practice
'Do Until RS.EOF
' loop through every column
For fnum = 0 To RS.Fields.Count - 1
' skip the identity column
If RS.Fields(fnum).Name <> "pk_auto_id" Then
' if this is a null value, code it as such for SQL
If RS.Fields(fnum).Name = "ck_config_type" Then
configtype = RS.Fields(fnum).Value
End If
If IsNull(RS.Fields(fnum).Value) Then
' build the insert
insert = insert & "NULL, " & Chr(13)
Else
'otherwise add the field value with our desired delimiters
' we must remove embedded " ' " single quotes because SQL will think they are a delimiter .. use a hypen instead
valuestring = RS.Fields(fnum).Value
valuestring = Replace(valuestring, "'", "-")
' if we have a numeric style atttribute, do not enclose in quotes
If RS.Fields(fnum).Type <> 131 And RS.Fields(fnum).Type <> 5 And RS.Fields(fnum).Type <> 3 Then
' SQL statement values cannot be larger than 128 so truncate this if that is the case
If Len(valuestring) > 128 Then
insert = insert & "'" & Left(valuestring, 128) & "', " & Chr(13)
Else
insert = insert & "'" & valuestring & "', " & Chr(13)
End If
Else
' numeric columns do not have a quote around them
insert = insert & valuestring & ", " & Chr(13)
End If
End If
Else
' save the old pk_auto_id value
pkautoidold = RS.Fields(fnum).Value
MsgBox ("old pk is " & pkautoidold)
End If
' get the next column
Next
' get another record, if any
RS.MoveNext
' go back and process this next record
'Loop
' remove our trailing commas from the last entry
inslength = Len(insert)
insert = Left(insert, inslength - 3) & ")"
' write out the new table record via our pre-built INSERT statement
Set rsA2 = conn.Execute(insert)
RS.Close
'=================================================================================================
'=================================================================================================
' now get the identity value of the record we just wrote
RS.Open "SELECT TOP 1 * FROM dbo.config_itemi ORDER BY pk_auto_id DESC", conn
msgbox (RS.Recordcount)
pkautoidnew = RS.Fields(0).Value
MsgBox ("new pk is " & pkautoidnew)
pkautoidnew=2097
RS.Close
'=================================================================================================
'=================================================================================================
' now get the name of the extended table
MsgBox (configtype)
RS.Open "SELECT * from dbo.config_typei WHERE (dbo.config_typei.pk_config_type = '" & configtype & "'" & ")", conn
msgbox (RS.Recordcount)
' create the INSERT header (the extended table is in the 7th column
extendedtable = RS.Field(7).Value
MsgBox ("extended is " & extendedtable)
'=================================================================================================
'=================================================================================================
' now get the record from the extended table that we want to clone
RS.Close
RS.Open "SELECT * from dbo." & extendedtable & " WHERE (dbo." & extendedtable & ".pk_ci_id = " & pkautoidold & ")", conn
' begin to build the extended table insert
insert = "INSERT INTO " & extendedtable & "(" & Chr(13)
' let's get all our colulmn names first
For fnum = 0 To RS.Fields.Count - 1
insert = insert & RS.Fields(fnum).Name & ", " & Chr(13)
Next
' remove our trailing comma from the last entry
inslength = Len(insert)
insert = Left(insert, inslength - 3)
' now let's get the actual values that we retrieved and start with the SQL clause again
insert = insert & ")" & Chr(13) & "VALUES(" & Chr(13)
'although we should only have one record, use a structured loop for good practice
'Do Until RS.EOF
' loop through every column
For fnum = 0 To RS.Fields.Count - 1
' if this is a null value, code it as such for SQL
If IsNull(RS.Fields(fnum).Value) Then
' build the insert
insert = insert & "NULL, " & Chr(13)
Else
'otherwise add the field value with our desired delimiters
' we must remove embedded " ' " single quotes because SQL will think they are a delimiter .. use a hypen instead
valuestring = RS.Fields(fnum).Value
' change the identity column value to our new key
If RS.Fields(fnum).Name = "pk_ci_id" Then
valuestring = pkautoidnew
End If
valuestring = Replace(valuestring, "'", "-")
' if we have a numeric style atttribute, do not enclose in quotes
If RS.Fields(fnum).Type <> 131 And RS.Fields(fnum).Type <> 5 And RS.Fields(fnum).Type <> 3 Then
' SQL statement values cannot be larger than 128 so truncate this if that is the case
If Len(valuestring) > 128 Then
insert = insert & "'" & Left(valuestring, 128) & "', " & Chr(13)
Else
insert = insert & "'" & valuestring & "', " & Chr(13)
End If
Else
' numeric columns do not have a quote around them
insert = insert & valuestring & ", " & Chr(13)
End If
End If
' get the next column
Next
' get another record, if any
RS.MoveNext
' go back and process this next record
'Loop
' remove our trailing commas from the last entry
inslength = Len(insert)
insert = Left(insert, inslength - 3) & ")"
'=========S T A R T ==O F == D E B U G == R O U T I N E ==================================
' put debug data on clipboard
StrClipboard = insert
With CreateObject("InternetExplorer.Application")
.Navigate "about:blank"
Do until .ReadyState = 4 : Wscript.Sleep 100 : Loop
.visible = False
With .document
.writeln(" ")
.writeln(StrClipboard)
.writeln(" ")
.execcommand "SelectAll"
.execcommand "Copy"
End With ' document
End With 'Createobject
'=========E N D ==O F == D E B U G == R O U T I N E ==================================
MsgBox ("pause")
' write out the new table record via our pre-built INSERT statement
Set rsA2 = conn.Execute(insert)
' let's close all open files and tables and db connections for good housekeeping
RS.Close
conn.Close
wscript.quit
September 9, 2008 at 2:22 pm
You need to change either the cursor location to client side or the cursor type in order to get a recordcount. Here is an link that explains it: http://support.microsoft.com/kb/194973
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 10, 2008 at 6:23 am
Thank you.
September 11, 2008 at 6:56 am
Alternatively, you could dump the whole recordset into an array, and then work with the array. This is usually my preferred method. Just use:
RS.GetRows
You can then use UBound to calculate the row count.
Andy
September 11, 2008 at 7:32 am
Thanks. I already use that for a smaller script and it works great. This script I have been working on is now finished. Using JUST VBscript, I had to prompt for a filter argument, get the data from table1, find the value of the identity column in table1 and save it, determine the asset type by examing yet another column of table1. Then I wrote out a du0licate of the row from from table1. Next I had to obtain the new identity of the written clone row. Then I had to open table 2 or table 3 or table 4 or table 5 depending on the asset type and save that data so I could wrote a duplicate with the new key from table one. I ran into a trigger constraint so my SQL had to disable the trigger just before the INSERT. Then I send a completion msgbox with the the new asset number. I was amazed at what could be done in a script language. It's about 300 lines long but it is a free language so I use it.
If anyone can benefit from the script for any of the examples, let me know and I'll publish it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply