[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.

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

Quote:

---

Originally Posted by MarkT

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.

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.