Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Posts
    3

    Question Unanswered: connection to MS SQL using VBscript

    Hi,

    I would like to write a logon script for win 2000 that writes data to ms sql database whenever a user logs on. Is it possible to connect to MS SQL using vbs or jscript?

    Nase
    Last edited by nase; 08-15-03 at 09:01.

  2. #2
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Using VBScript:

    Function WriteToDatabase(sUserName)

    Dim mConnection

    Set mConnection = CreateObject("ADODB.Connection")
    mConnection.Open "Provider=SQLOLEDB.1;Data Source=myServerName;Initial Catalog=myDatabaseName","myUserName","myPassword"

    mConnection.Execute "INSERT INTO tbl_user_log (user_name, login_date) VALUES ('" & sUserName" & ", GetUTCDate())"

    Set mConnection = Nothing

    End Function


    This will write the username and current GMT date/time to a database table called tbl_user_log, with the fields user_name (VARCHAR) and login_date (DATETIME).

    If you want to retrieve values, use a recordset object such as:

    Set mRecordset = CreateObject("ADODB.Recordset")
    mRecordset = "your SQL command here", mConnection

    good luck....
    -bpd

  3. #3
    Join Date
    Aug 2003
    Posts
    3

    Thumbs up It works, but I can't call stored procedure

    I am in the right way, but I don't have any programing ecxperiance in vbscript, so I am just modifying scripts and obviously I am doing something wrong. I am trying to call a stored procedure in MS SQL server, and pass computer name as a parameter to it. Using the script below I can pass string like:

    cmd.CommandText = "{Call logon ('string1', 'string2')}"

    but when I try to pass variable like:

    cmd.CommandText = "{Call logon (comp, 'test')}"

    I get "Invalid character value for cast specification" error. The script is expecting varchar parameters.
    Can somebody tell me what is wrong with the script ?

    Function GetComputerName()
    Dim InfoNT
    Set InfoNT = CreateObject("WinNTSystemInfo")
    GetComputerName = lcase(InfoNT.ComputerName)
    End Function

    Dim con
    Dim comp
    comp = GetComputerName()
    Set con = CreateObject("ADODB.Connection")
    Set cmd = CreateObject("ADODB.Command")
    con.Open "Provider=SQLOLEDB.1;Data Source=server;Initial Catalog=database","user","password"
    Set cmd.ActiveConnection = con
    cmd.CommandText = "{Call logon (comp, 'test')}"
    cmd.Execute
    Set cmd = Nothing
    Set con = Nothing

  4. #4
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    My guess is that you have been programming in java (based on the command text you used)?

    You seems to have a small syntax problem, but that's eay to fix. You are also not getting anything back from the stored procedure. Are you expecting a return value (parameter) or a recordset?

    If you want a return parameter, try this:

    Dim con
    Dim comp
    Dim cmd
    Dim retval
    comp = GetComputerName()
    Set con = CreateObject("ADODB.Connection")
    Set cmd = CreateObject("ADODB.Command")
    con.Open "Provider=SQLOLEDB.1;Data Source=server;Initial Catalog=database","user","password"
    With cmd
    .CommandType = 11 'adCmdStoredProc
    .ActiveConnection = con
    .CommandText = "logon" 'stored proc name only
    .Parameters.Add .CreateParameter ("@compname",200,1,30,comp) 'parameter called @compname (names should match the sp names), 200 means varchar, 1 means input parameterm 30 is length, then value of comp
    .Parameters.Add .CreateParameter ("@otherparam",200,1,30,'test') 'same as above, used @otherparam to hold your test value?
    .Parameters.Add .CreateParameter ("@outval",3,2,4)
    .Execute
    retval = cmd.parameters("@outval").value
    End With
    Set cmd = Nothing
    Set con = Nothing

    Looks messy here, but should clean up if you paste into your editor. This bit of code is calling a sp called "logon." It creates two input parameters called @compname and @otherparam, but you should change these names to match the names inside your stored procedure. They are both VARCHAR(30) in the example. The 200 used to represent VARCHAR is from the set of ADO Constants (small list below). The output parameter is expecting an INT, but could be anything (4 is the length of an SQL INT). The with block just cleans it up a bit by allowing you to not have to put in the cmd for each property assignment (there is supposed to be a space before the .CreateParameter's above).

    If you want to return a Recordset, you can use the same code, but declare a recordset object first, and assign it when you perform the execute (Set rs=cmd.Execute). You can still have a return value, but it is optional.

    Here are some common ADO const values (good to declare somewhere in your code):
    adCmdStoredProc = 11
    adParamInput = 1
    adParamOutput = 2
    adParamInputOutput = 3
    adInteger = 3
    adVarchar = 200
    adBoolean = 11
    adChar = 129
    adDate = 7
    adNumeric = 131
    -bpd

  5. #5
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Just reading the original post. If you don't want to return values, that;s fine too: no output param, and no recordset.
    -bpd

  6. #6
    Join Date
    Aug 2003
    Posts
    3
    Thank you for your patience bpdWork,

    I have tried to use "CommandType = adCmdStoredProc" before, but I don't know why this CommandType is not working, and I receive error message on line "cmd.CommandType = 11 'adCmdStoredProc":

    "Arguments are of wrong type, are out of acceptable range, or are in conflict with one another"
    Source: ADODB command

    So I tried to use "cmd.CommandText = "{Call logon ('comp', 'test')}"
    instead, and it works, but only with string.

    Any suggestions?

Posting Permissions

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