jmedlin6

I have a SQL string that I run in SQL Server Management Studio. It'll query the DB and look for student users and display them so I can copy and paste the command in a command prompt to create the user. I'm trying to set it up so it runs in a VBScript. This is what I currently have for objRecordSet.Open: objRecordSet.Open _ "select cscript 'CreateStudentAccounts.vbs'" & _ "+ rtrim(ps1.EMPLID) + ' '" & _ "+ substring(convert(char(4), ps1.EFFDT, 121), 3, 2) + ' '" & _ "+ rtrim(ps1.LAST_NAME) + ' '" & _ "+ rtrim(ps1.FIRST_NAME) + ' '" & _ "+ case" & _ "when ps1.MI IS NULL then ' '" & _ "else rtrim(ps1.MI)" & _ "end" & _ "from PS_AD_UPDATES ps1" & _ "where not exists (" & _ "select *" & _ "from PS_AD_UPDATES ps2" & _ "where ps1.EMPLID = ps2.EMPLID" & _ "and ps2.ACTION in ('N', 'T', 'D')" & _ "andps1.DATETIME_STAMP < ps2.DATETIME_STAMP" & _ ")" & _ "and ps1.ACTION in ('C', 'H')" & _ "and ps1.EFFDT >= '2009-01-01'" & _ "and lower(ps1.JOBTITLE) like '%student%'" & _ "and ( ps1.NETCONNECT1 is null" & _ "or rtrim(ps1.NETCONNECT1) <= ' '" & _ ")" & _ "order by ps1.LAST_NAME, ps1.FIRST_NAME, ps1.EFFDT", _ objConnection, adOpenStatic, adLockOptimistic Is there an easier way to execute the sql query? My objConnection.Open is fine. I use the same script to do a simple query, but nothing this elaborate. This is what I currently execute in SQL Server Management Studio: select 'cscript CreateStudentAccounts.vbs ' + rtrim(ps1.EMPLID) + ' ' + substring(convert(char(4), ps1.EFFDT, 121), 3, 2) + ' ' + rtrim(ps1.LAST_NAME) + ' ' + rtrim(ps1.FIRST_NAME) + ' ' + case when ps1.MI IS NULL then ' ' else rtrim(ps1.MI) end from PS_AD_UPDATES ps1 where not exists ( select * from PS_AD_UPDATES ps2 where ps1.EMPLID = ps2.EMPLID and ps2.ACTION in ('N', 'T', 'D') and ps1.DATETIME_STAMP < ps2.DATETIME_STAMP ) and ps1.ACTION in ('C', 'H') and ps1.EFFDT >= '2009-01-01' and lower(ps1.JOBTITLE) like '%student%' and ( ps1.NETCONNECT1 is null or rtrim(ps1.NETCONNECT1) <= ' ' ) order by ps1.LAST_NAME, ps1.FIRST_NAME, ps1.EFFDT

Thanks everyone for pointing me in the right direction.

Thanks for pointing me in the right direction. Here is the working string: set rs = conn.execute("SELECT * from PS_AD_UPDATES WHERE FIRST_NAME LIKE '%" & FirstName & "%' and LAST_NAME LIKE '%" & LastName & "%' ORDER BY DATETIME_STAMP;")

I have a script that I can run in by manually putting in the user's First Name and Last Name into the Select String. It'll open the exported info into excel. I'm trying to get it to run by prompting for the first name and last name. It'll work by using the following Example when I manually key in the name into the string. I know it has to be something stupid that I'm doing wrong. ("SELECT * from PS_AD_UPDATES WHERE FIRST_NAME LIKE '%Jas%' and LAST_NAME LIKE '%Medl%' ORDER BY DATETIME_STAMP;") Dim FirstName Dim LastName FirstName=InputBox("Enter The User's First Name") LastName=InputBox("Enter The User's Last Name") '---------------------------------------------------------------------------------------------------------------------------------------------------- set conn = createobject("adodb.connection") conn.open("Provider=SQLOLEDB;Trusted_Connection=Yes;Initial Catalog=ADAdmin;Data Source=db-hvb-03;") set rs = conn.execute("SELECT * from PS_AD_UPDATES WHERE FIRST_NAME LIKE '& FirstName' and LAST_NAME LIKE '& LastName' ORDER BY DATETIME_STAMP;") if not rs.eof then set xlApp = CreateObject("Excel.Application") Set xlWb = xlApp.Workbooks.Add Set xlWs = xlWb.Worksheets("Sheet1") ' Display Excel and give user control of Excel's lifetime xlApp.Visible = False xlApp.UserControl = True ' Copy field names to the first row of the worksheet fldCount = rs.Fields.Count For iCol = 1 To fldCount xlWs.Cells(1, iCol).Value = rs.Fields(iCol - 1).Name Next recArray = rs.GetRows(-1) recCount = UBound(recArray, 2) + 1 xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = TransposeDim(recArray) 'xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = xlApp.WorksheetFunction.Transpose(recArray) xlApp.Selection.CurrentRegion.Columns.AutoFit xlApp.Selection.CurrentRegion.Rows.AutoFit xlApp.visible = true else msgbox "User Does Not Exist" end if rs.close set rs =nothing conn.close set conn = nothing Function TransposeDim(v) 'Dim X As Long, Y As Long, Xupper As Long, Yupper As Long 'Dim tempArray As Variant Xupper = UBound(v, 2) Yupper = UBound(v, 1) ReDim tempArray(Xupper, Yupper) For X = 0 To Xupper For Y = 0 To Yupper tempArray(X, Y) = v(Y, X) Next Next TransposeDim = tempArray End Function

My script works for the most part, however I just think it's really messy and could be cleaned up better. I replaced the actual string values with generic ones as they contained some data pertaining to my organization. Basically I need it to go through a txt files (students.txt) and remove the strings and delete the empty lines. Also at the end of the file there is a string that shows how many rows were affected (i.e. 95 rows affected). I need to remove this as well. The number isn't always the same or I would just set it to remove the specific string. Any ideas would be greatly appreciated. Dim st1, st2, st3, st4, st5, st7, st8, st9, st10, st11, st12, st13, st14, st15, st16, st17, st18, st19, st20, st21 st1 = "This is string 1" st2 = "This is string 2" st3 = "This is string 3" st4 = "This is string 4" st5 = "This is string 5" st6 = "This is string 6" st7 = "This is string 7" st8 = "This is string 8" st9 = "This is string 9" st10 = "This is string 10" st11 = "This is string 11" st12 = "This is string 12" st13 = "This is string 13" st14 = "This is string 14" st15 = "This is string 15" st16 = "This is string 16" st17 = "This is string 17" st18 = "This is string 18" st19 = "This is string 19" st20 = "This is string 20" st21 = "This is string 21" Const ForReading = 1 Const ForWriting = 2 Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading) strText = objFile.ReadAll objFile.Close strNewText = Replace(strText, st1, "") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting) objFile.WriteLine strNewText objFile.Close Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading) strText = objFile.ReadAll objFile.Close strNewText = Replace(strText, st2, "") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting) objFile.WriteLine strNewText objFile.Close Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading) strText = objFile.ReadAll objFile.Close strNewText = Replace(strText, st3, "") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting) objFile.WriteLine strNewText objFile.Close Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading) strText = objFile.ReadAll objFile.Close strNewText = Replace(strText, st4, "") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting) objFile.WriteLine strNewText objFile.Close Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading) strText = objFile.ReadAll objFile.Close strNewText = Replace(strText, st5, "") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting) objFile.WriteLine strNewText objFile.Close Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading) strText = objFile.ReadAll objFile.Close strNewText = Replace(strText, st6, "") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting) objFile.WriteLine strNewText objFile.Close Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading) strText = objFile.ReadAll objFile.Close strNewText = Replace(strText, st7, "") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting) objFile.WriteLine strNewText objFile.Close Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading) strText = objFile.ReadAll objFile.Close strNewText = Replace(strText, st8, "") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting) objFile.WriteLine strNewText objFile.Close Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading) strText = objFile.ReadAll objFile.Close strNewText = Replace(strText, st9, "") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting) objFile.WriteLine strNewText objFile.Close Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading) strText = objFile.ReadAll objFile.Close strNewText = Replace(strText, st10, "") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting) objFile.WriteLine strNewText objFile.Close Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading) strText = objFile.ReadAll objFile.Close strNewText = Replace(strText, st11, "") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting) objFile.WriteLine strNewText objFile.Close Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading) strText = objFile.ReadAll objFile.Close strNewText = Replace(strText, st12, "") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting) objFile.WriteLine strNewText objFile.Close Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading) strText = objFile.ReadAll objFile.Close strNewText = Replace(strText, st13, "") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting) objFile.WriteLine strNewText objFile.Close Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading) strText = objFile.ReadAll objFile.Close strNewText = Replace(strText, st14, "") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting) objFile.WriteLine strNewText objFile.Close Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading) strText = objFile.ReadAll objFile.Close strNewText = Replace(strText, st15, "") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting) objFile.WriteLine strNewText objFile.Close Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading) strText = objFile.ReadAll objFile.Close strNewText = Replace(strText, st16, "") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting) objFile.WriteLine strNewText objFile.Close Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading) strText = objFile.ReadAll objFile.Close strNewText = Replace(strText, st17, "") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting) objFile.WriteLine strNewText objFile.Close Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading) strText = objFile.ReadAll objFile.Close strNewText = Replace(strText, st18, "") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting) objFile.WriteLine strNewText objFile.Close Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading) strText = objFile.ReadAll objFile.Close strNewText = Replace(strText, st19, "") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting) objFile.WriteLine strNewText objFile.Close Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading) strText = objFile.ReadAll objFile.Close strNewText = Replace(strText, st20, "") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting) objFile.WriteLine strNewText objFile.Close Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading) strText = objFile.ReadAll objFile.Close strNewText = Replace(strText, st21, "") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting) objFile.WriteLine strNewText objFile.Close Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading) strText = objFile.ReadAll objFile.Close strNewText = Replace(strText, "-", "") Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting) objFile.WriteLine strNewText objFile.Close '---------------------------------------------------------------------------------------------- 'Deletes Blank Lines Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("C:\Scripts\SQL\students.txt", ForReading) Do Until objFile.AtEndOfStream strLine = objFile.Readline strLine = Trim(strLine) If Len(strLine) > 0 Then strNewContents = strNewContents & strLine & vbCrLf End If Loop objFile.Close Set objFile = objFSO.OpenTextFile("C:\Scripts\SQL\students.txt", ForWriting) objFile.Write strNewContents objFile.Close