none
How do I get a VBScript to send a parameter value to sql sp RRS feed

  • Question

  • I want the user to enter the month into the input box: Example: Nov

    My parameter in sql is named: @Commissionsmonth

    I know my sp works if ran within sql.  Any help with this would be greatly appreciated!

    returnvalue=MsgBox ("ARE YOU SURE YOU WANT TO UPDATE COMMISSIONS PAID?",36,"CONFIRM UPDATE!!")
    If returnvalue=6 Then
    Dim cmd
    Dim sp
    sp = "asp_update_commissions_paid"
    Set cmd = CreateObject("ADODB.Command")
    With cmd
    .ActiveConnection = "Provider=sqloledb;Data Source=myserver;Initial Catalog=mydatabase;Integrated Security=SSPI"
     .CommandType = 4
     .CommandText = sp


    sInput = InputBox("Enter Month being Paid")

    returnvalue=MsgBox ("Commissions Paid has been updated",36,"CONFIRM UPDATE!!")
     .Execute
    End With
    Set cmd = Nothing
    End If

     

    Saturday, August 22, 2015 12:53 AM

Answers

All replies

  •  .CommandText = "exec asp_update_commissions_paid '" & sInput & "'"

    \_(ツ)_/

    • Marked as answer by Scoobydoo11 Tuesday, September 1, 2015 11:47 AM
    Saturday, August 29, 2015 7:39 PM
  • Thank you so much.  This worked like a charm. You are the best. Below is my script for others.

    Dim sServer, sConn, oConn, sDatabaseName, sUser, sPassword
    sDatabaseName="datbase name"
    sServer="server name"
    sUser="user name"
    sPassword="password"
    sConn="provider=sqloledb;data source=" & sServer & ";initial catalog=" & sDatabaseName
    Set oConn = CreateObject("ADODB.Connection")
    oConn.Open sConn, sUser, sPassword

    sInput = InputBox("Enter Month being Paid") 
    oConn.Execute "exec asp_update_commissions_paid '" & sInput & "'"

     

    WScript.Echo "Executed"
    oConn.Close
    Set oConn = Nothing

    Tuesday, September 1, 2015 11:47 AM
  • Great.  Glad you were able to understand it.  Use that as a good interface method.  Also look closely at SSIS.


    \_(ツ)_/

    Tuesday, September 1, 2015 12:04 PM