Obtaining values from a table in a database

[kupz]

Hi there,

I am trying to obtain values of from a table in a database and write the values to a text file. I am pretty new to VBSript and do not have the slightest clue to go about this but do know that I have to extablish a connection to the database. If you have a sample code, that would really be great for me to get started. Thank you in advance.

 

[link9]

Yes, you'll need the connection to the database. Easiest way to achieve this is via a DSN (Data Source Name) which can be setup by going to:

Control Panel --> ODBC --> System DSN --> Add

Then, just follow the Wizard. Give your DSN a name, point it to a database, tell it what kind of database it is, etc.

Now then, once you do that, I'll assume you have named your DSN 'myDSN', and your database is Access.

The following is ASP, and must be run in a server environment. Win9x is ok, but you must first have PWS installed. I'll leave that for another thread, if you need details there.

<%
'Here are your nuts and bolts
dim con, rs, sql, fso, txtFile

'connection to your database
set con = server.createObject(&quot;ADODB.Connection&quot

'recordset to hold your information
set rs = server.createObject(&quot;ADODB.Recordset&quot

'open your connection
con.open (&quot;DSN=myDSN&quot '<--- name of your DSN, obviously

'setup your select statement
sql = &quot;SELECT * FROM someTable&quot;

'declare your active connection for your recordset
rs.activeConnection = con

'open your recordset
rs.open sql

'AT THIS POINT, YOU HAVE A RECORDSET CONTAINING ALL
' RECORDS FROM THE TABLE, someTable.
'ADJUST YOUR SQL AS NEEDED TO FIT YOUR DATABASE

'create your file system object, which allows the
' creation of your txt file
set fso = server.createObject(&quot;Scripting.fileSystemObject&quot

'create your text file
' this will create a txt file in the same directory
' as your script called myTxtFile.txt
' You can adjust the path as needed
set txtFile = fso.createTextFile(&quot;myTxtFile.txt&quot

'write some info from your recordset to the txt file
txtFile.writeLine(rs(&quot;columnName&quot)

'close your text file
txtFile.close

'clean up your mess
set txtFile = nothing
set fso = nothing
set rs = nothing
set con = nothing
%>

Check your text file, and you will have whatever was in the field, 'columnName', written on the first line.

Here's more information on the fileSystemObject:

http://www.devguru.com/Technologies/vbscript/quickref/filesystemobject.html

hope that helps!
paul

 

[kupz]

Paul,

I really appreciate your help. I'll try it out.

Thanks,
myniguez

 

[robing]

But how do I connect with ODBC without a server environment, not ASP. The VBscript does not understand server in server.createobject(). How do I connect then?

BR
/Canix

 

[codestorm]

I just did this in notepad and saved it to the desktop as a .vbs file.

dim obj_Connection
set obj_Connection= CreateObject(&quot;ADODB.Connection&quot
obj_Connection.open <your connection string here>
obj_Connection.close
set obj_Connection= nothing

<insert witticism here>
codestorm

 

[canix1]

Thanx, That helped this newbie!

BR
Canix