Query SQL DB from input box

[jmedlin6]

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

[jinnantonnixx]

set rs = conn.execute("SELECT * from PS_AD_UPDATES WHERE FIRST_NAME LIKE '& FirstName' and LAST_NAME LIKE '& LastName' ORDER BY DATETIME_STAMP;")

 

should be

 

set rs = conn.execute("SELECT * from PS_AD_UPDATES WHERE FIRST_NAME LIKE '& FirstName &' and LAST_NAME LIKE '& LastName & ' ORDER BY DATETIME_STAMP;")

 

 

You forgot the additional '&' when building your string.

[jmedlin6]

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;")