+ Post New Thread
Results 1 to 3 of 3
Scripts Thread, Query SQL DB from input box in Coding and Web Development; I have a script that I can run in by manually putting in the user's First Name and Last Name ...
  1. #1

    Join Date
    Apr 2012
    Location
    Pittsburgh
    Posts
    5
    Thank Post
    4
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    Query SQL DB from input box

    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=Ye s;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

  2. #2

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,826
    Thank Post
    104
    Thanked 449 Times in 308 Posts
    Blog Entries
    2
    Rep Power
    262
    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.

  3. Thanks to jinnantonnixx from:

    jmedlin6 (25th April 2012)

  4. #3

    Join Date
    Apr 2012
    Location
    Pittsburgh
    Posts
    5
    Thank Post
    4
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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;")

SHARE:
+ Post New Thread

Similar Threads

  1. SIMS <--> SQL DB relationship schema
    By Gdn in forum MIS Systems
    Replies: 38
    Last Post: 7th September 2010, 07:30 PM
  2. Turn off input box history
    By Hightower in forum Web Development
    Replies: 2
    Last Post: 26th May 2010, 03:20 PM
  3. [PHP] read from text box, append to file
    By RabbieBurns in forum Web Development
    Replies: 23
    Last Post: 22nd October 2009, 01:28 PM
  4. Creating query based on multiple combo boxes
    By Shrimpersfan in forum Coding
    Replies: 8
    Last Post: 23rd December 2008, 06:38 PM
  5. PHP input box question.
    By maniac in forum Web Development
    Replies: 6
    Last Post: 27th July 2007, 11:15 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •