Getting data from SQL server using VBScript

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Getting data from SQL server using VBScript

Author

Topic

arafique
Starting Member

5 Posts

Posted - 2007-08-21 : 03:25:02

Hello All

I want to fetch data from SQL Server using VB Script. Can any one please send me any sample code or article.

Looking forward for your help

Amir

pootle_flump

1064 Posts

Posted - 2007-08-21 : 03:47:30

Hi. Easiest is to use ADO. Try googling for tutorials, or just try this one:
http://www.w3schools.com/ado/ado_intro.asp

arafique
Starting Member

5 Posts

Posted - 2007-08-21 : 03:53:28

Thanks pootle. I m googling this topic. One thing that I want to ask is that Can we use ADO without ASP page?. Actually I want to develop a VBScript routine that will fetch data from SQL Server.

Any help in ths regard will be highly appreciated

Thanks

Amir

pootle_flump

1064 Posts

Posted - 2007-08-21 : 04:40:21

Indeed. ASP just uses vb script as the language. Out of curiosity - what do you want to do once you get the data?

arafique
Starting Member

5 Posts

Posted - 2007-08-21 : 05:56:06

I want to make a .vbs file which on execution fetches data from specified table and shows first row in message box. Please see below code, it gives error saying "Object Required: 'Server'".

call Init

Public Sub Init

if isobject(cn) = false then

msgbox("cn is empty")

Set cn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")

msgbox("cn and rs ibj created")

' cn.ConnectionString = Application("DB_CONN")
'cn.Open

end if

End Sub

Amir

pootle_flump

1064 Posts

Posted - 2007-08-21 : 06:34:17

Yeah - get rid of "Server." - that is ASP specific I am afraid.

arafique
Starting Member

5 Posts

Posted - 2007-08-21 : 08:47:39

Pootle Thanks for ur help. Below is the sample code that fetches data from SQL Server

' VB Script Document
call Init

Public Sub Init

Dim rstAuthors

if isobject(cn) = false then
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.ConnectionString = "Provider=SQLOLEDB.1;Data " & _
"Source=(local); Initial Catalog=pubs;user id = " & _
"'sa';password=''"
cn.Open
strAuthorsQry = "select * from authors " & _
"where au_lname = 'White'"
set rstAuthors = cn.execute(strAuthorsQry)
rstAuthors.MoveFirst
Do Until rstAuthors.EOF
msgbox("Name - " & rstAuthors("au_lname"))
rstAuthors.MoveNext
Loop

end if

End Sub

Amir

pootle_flump

1064 Posts

Posted - 2007-08-22 : 03:32:08

Super.

BTW - I hope that is not your final code. If so:
1) You must set up a password for SA. The first thing a hacker will try is a blank password. Once someone has access to your SQL Server as SA then they can get control of the server and possibly much of your network.
2) VB Script is unencrypted so any credentials you use in the connection string are sat in a file that anyone with permissions can read.
3) Use an account with the minimum permissions that you require. In this instance you certainly don't need to be SA to read a table.

HTH

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 04:00:38

Drop the single quotes around the SA username.

E 12°55'05.25"
N 56°04'39.16"

arafique
Starting Member

5 Posts

Posted - 2007-08-23 : 00:56:09

Hi All

Using code posted previsouly I am able to connect Database at my local machine but when i change the code as given below
cn.ConnectionString = "Provider=SQLOLEDB.1;Data " & _
"Source=(10.12.1.33); Initial Catalog=pubs;user id = " & _
"'sa';password='password'"
It gives me following error
"[DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access denied".
Code: 80004005

I am able to connect this server using same credentials through Enterprise manager and using my java program.

Anybody please help me on this
Thanks

Amir

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources