Results 1 to 4 of 4

Thread: [RESOLVED] ODBC DSN with vbscript and some simple questions

  1. #1

    Thread Starter
    Addicted Member scsfdev's Avatar
    Join Date
    Feb 2008
    Location
    Singapore
    Posts
    224

    Resolved [RESOLVED] ODBC DSN with vbscript and some simple questions

    Hi all,

    I have some questions to ask you guys.

    1) How do i run my vbscript **.vbs file on windows XP???
    2) If I have to connect to one of the DSN in ODBC, what is the connection string I should use??
    3) If I have to connect to MS SQL Server, where should I put that datasource=xx, initital catalogue=xxx, uid=xx, etc???
    For connection string, I got it from another post. I just don't know where to put it. Put it the whole string? after objConn.Open xxxxx ? or ??

    Currently, my method is like this (The below code is for my question 2):

    Code:
    Dim objConn, objErr
    Dim strSQL
    Set objConn = CreateObject("ADODB.Connection")
    
    On Error Resume Next
    objConn.Open "MiniHostel"
    If objConn.State <> adStateOpen Then
    Set objConn = Nothing
    ' Write to Log file here.
    End If
    
    strSQL = "INSERT INTO tblStudentMiniInfo " & _
    "(stuID, flatID, rmNo, pub, recDate) " &_
    "VALUES ('" & sstuID & "', '" & sflatID & "', '" & srmNo & "', '" & dpub & "', " & "TO_DATE('" CCPolDate & "','DD/MM/YYYY'))" 
    
    ' insert information to database
    objConn.Execute strSQL
    
    If objConn.Errors.Count > 0 Then
    For Each objErr in objConn.Errors
    ' Write to Log file here.
    Next
    Set objConn = Nothing
    WScript.Quit(1)
    End If
    Set objConn = Nothing
    Note: "MiniHostel" is my System DSN name which is already point to my SQL Server.
    Is it true that I don't need to enter my SQL server log in User ID and password while I connect because it already included in System DSN?

    This is my very first vbscript application.
    So, my code may not be in a good way or may be i use some object without needing.
    The above code includes my understanding on vbscript and some codes snippet from online.
    Because I don't know how to test my vbscript, so the above code, I already write but no place to test. So don't know whether its working or not.

    Thanks.
    And sorry for my long and complex questions.
    Last edited by scsfdev; Aug 25th, 2009 at 01:51 AM.
    I'm using VS 2005 & 2008 & 2010 with SQL Server 2005 Express.

    My hobby beside programming: http://dslrstranger.wordpress.com

  2. #2
    PowerPoster
    Join Date
    Jun 2001
    Location
    Trafalgar, IN
    Posts
    4,141

    Re: ODBC DSN with vbscript and some simple questions

    A few things.
    1. Until you have things working I would remove the On Error Resume next. This will make it easier to debug.
    2. For the connection string I usually do it in 2 line.
      Code:
      objConn.ConnectionString = "the connectionstring"
      objConn
    3. VBScript knows nothing about constants so you have to either have to declare the constants or use literals.
      Code:
      'This will cause an error
      If objConn.State <> adStateOpen Then
      'Try this instead
      If objConn.State <> 1 Then

    You can find a list of connection string from Connectionstrings.com

  3. #3

    Thread Starter
    Addicted Member scsfdev's Avatar
    Join Date
    Feb 2008
    Location
    Singapore
    Posts
    224

    Re: ODBC DSN with vbscript and some simple questions

    Quote Originally Posted by MarkT View Post
    A few things.
    1. Until you have things working I would remove the On Error Resume next. This will make it easier to debug.
    2. For the connection string I usually do it in 2 line.
      Code:
      objConn.ConnectionString = "the connectionstring"
      objConn
    3. VBScript knows nothing about constants so you have to either have to declare the constants or use literals.
      Code:
      'This will cause an error
      If objConn.State <> adStateOpen Then
      'Try this instead
      If objConn.State <> 1 Then

    You can find a list of connection string from Connectionstrings.com
    Hi MarkT,

    Thanks for your info.
    I'm using VS 2005 & 2008 & 2010 with SQL Server 2005 Express.

    My hobby beside programming: http://dslrstranger.wordpress.com

  4. #4

    Thread Starter
    Addicted Member scsfdev's Avatar
    Join Date
    Feb 2008
    Location
    Singapore
    Posts
    224

    Re: ODBC DSN with vbscript and some simple questions

    After digging the internet for a few days, I have found some of my questions.

    Note: There are different ways for my answers. Below are just what I found.

    1. How do I run vbscript in windows?
    Answer: I use Command Prompt. [ Start>>Run>>CMD>> cscript vbscriptname.vbs ]

    2. How to connect ODBC DSN?
    Answer: For connect to ODBC DSN, I declare a DSN in ODBC under [ Control Panel>>Administrative Tools>>Data Sources (ODBC)]
    ** In authentication step, I have to choose "With Windows NT authentication using the network login ID". I don't know why, I just can't choose second option. If I choose second option which is "With SQL Server authentication using a login ID and password entered by the user.", it will prompt error in vbscript about not trusted user or connection something although I test connection in ODBC is success.
    And then I use the DSN name to connect it. [ Instead of objConn.Open "MiniHostel", I must use objConn.Open "DSN=MiniHostel" ]

    3. How to connect to MS SQL Server Express from vbscript?
    Answer: Like all others are saying, the method is simple. I just forgot to put "Provider = SQLOLEDB;" so at first I can't connect. After digging and finding, I found that I need this values.
    [ objConn.Open "Provider=SQLOLEDB;Data Source=computername\SQLEXPRESS;Initial Catalog=MiniHostel_Live;User ID=sa;Password=sa" ]

    For all the above tracking and finding problem, I output a log file to indicate where is my cursor while I running the application. So, it make easy for me to debug and trace where went wrong.


    If you know it, its very simple to use. But if you don't know it, it takes some time to discover to know it. Sometimes we are just poor in English keywords or sometimes Google just does a lot of work for us.

    For those who also face the same problem like me or for those who is new to vbscript, I hope this post can help them and reduce their searching time.

    Thanks.
    Last edited by scsfdev; Aug 30th, 2009 at 11:25 PM.
    I'm using VS 2005 & 2008 & 2010 with SQL Server 2005 Express.

    My hobby beside programming: http://dslrstranger.wordpress.com

Posting Permissions

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



Click Here to Expand Forum to Full Width