Acessing MS SQL Data through vb script

[tn_sudheer]

Good Evening Members,

Here i want to access data from Database using VBScript, so regarding that i have gone through some of the examples ,but none of them working in my case.

My DB name Dummy
ODBC Data Source connection DDSN
Logging in to db using windows authantication.

Some how by seeing some examples i prepared my script which is not working..

so by some basics i understand that we want to make connection to database & table, open that connection, execute the required query, close the connection

here is my script

dim obj,obj1, str
dim dbquery
set obj = createobject("adodb.connection")
set obj1 = createobject("adodb.recordset")
obj.open "dsn=ddsn;uid=DESKTOP-FUJ7MOA\Shanthkumar_PIASPL,password=,database=dummy"
obj1.open "insert into table_1(empid)values(100)"
obj1.execute
obj1.close
obj.close
set obj1 = nothing
set obj = nothing

when i run my bat file it showing an error

Plz can anyone help me where its went wrong.

 

[kray4660]

Nothing I see looks like an issue (not real sure on the syntax of the open command). But a couple of questions about the database. What is the datatype of the empid field and is empid a auto increment field? And of course I am assuming that you have rights to do an insert on that table.

 

[Andrzejek]

The only line I would question (knowing not much about VBS) is:
[tt]obj1.open "insert into table_1(empid)values(100)"[/tt]
With obj1 as a recordset, in my world [tt].Open[/tt] would populate such recordset with the data from the DB, which would require a Select statement.

To execute Insert, Delete, or Update statements, I would expect:
[tt]obj.Execute ("insert into table_1(empid)values(100)")[/tt]

You Can see more information in thread329-633788

BTW, I would also suggest changing names of your variables:
[tt]dim [blue]objConn, objRst[/blue], str
dim dbquery
set [blue]objConn[/blue] = createobject("adodb.[blue]conn[/blue]ection")
set [blue]objRst[/blue] = createobject("adodb.[blue]r[/blue]ecord[blue]s[/blue]e[blue]t[/blue]")[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[tn_sudheer]

thankyou #Andrzejek,

Hello members,

ya i got it where i went wrong. so right now i have established connection with my recordset and able to write read data.

my next task is , i want to fetch some data , and write it to excel which i have done partly..

so as per my code i am reading data of empid, emp names, emp salary whose salary is equal to 20000, able to write it in excel.

as per my database i have only 3 records according to my query, as soon as it writes 3 records in my excel the "EOF" will become true which should exit from do while loop. but here its throwing a message attached below.

hope that u people got my intension.

kindly let me know where it went wrong..

here is my code
DIM OBJCONNECTION,OBJRECORDSET,XLAPP,NR
SET XLAPP = CreateObject("EXCEL.APPLICATION")
SET OBJCONNECTION=CREATEOBJECT("ADODB.CONNECTION")
SET OBJRECORDSET=CREATEOBJECT("ADODB.RECORDSET")
OBJCONNECTION.OPEN"DSN=DDSN;UID=DESKTOP-FUJ7MOA\Shanthkumar_PIASPL,password=,database=DUMMY"
OBJRECORDSET.OPEN "SELECT * from TABLE_1 where EMPSALARY = 20000" , objConnection, 1, 3 ' the last two arguments are adOpenKeySet and adLockOptimistic

dim row,col2,COL3
row = 2
col2 = 2
COL3 = 3
WITH OBJRECORDSET
DO WHILE NOT EOF

'WSCRIPT.ECHO OBJRECORDSET("EMPID")' & " BEARING NAME OF " & OBJRECORDSET("EMPNAME")& " PAYING A SALARY OF " & OBJRECORDSET("EMPSALARY")
XLAPP.VISIBLE = True
XLAPP.WORKBOOKS.OPEN("C:\Users\Shanthkumar_PIASPL\Documents\Test\Book1.xlsx")


XLAPP.CELLS(row,1) = OBJRECORDSET("EMPID")
XLAPP.CELLS(row,col2) = OBJRECORDSET("EMPNAME")
XLAPP.CELLS(row,col3) = OBJRECORDSET("EMPSALARY")
XLAPP.ACTIVEWORKBOOK.SAVE
.MOVENEXT
row = row + 1

LOOP
XLAPP.WORKBOOKS.CLOSE
XLAPP.QUIT
SET XLAPP = Nothing

END WITH
OBJRECORDSET.CLOSE
OBJCONNECTION.CLOSE

 

[Andrzejek]

Looks to me like you have a missing period here:

...
WITH OBJRECORDSET
   DO WHILE NOT [highlight #FCE94F].[/highlight]EOF
...

I see you took my advise and renamed your objects. But I would not use all CAPS. It is hard to read. IMHO

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[tn_sudheer]

hankyou Andrzejek I got it.actually I has to take recordset.eof

I modified it now it's okay..

So now my next task is to select records based on time string..

For suppose let's say I am logging my data for every 5Min..

So from an third party interface I will give start time and end time. Based on that timings my script has to execute and show that particular records..

Let's say I have log my data at following Timestamps

1. 5/17/2021 10:00:00 AM
2. 5/17/2021 10:05:00 AM
3. 5/17/2021 10:10:00 AM
4. 5/17/2021 10:15:00 AM
5. 5/17/2021 10:20:00 AM

so directly on mssql I executed my query line

" Select * from log_10 where timestring >= 5/17/2021 10:05:00 AM and timestring >= 5/17/2021 10:15:00 AM "

or
" Select * from log_10 where timestring between 5/17/2021 10:05:00 AM and 5/17/2021 10:15:00 AM "

Both of them works fine in sql.

So now i trying to pass the timestamps through variables. like (not in sql in notepad++)
A = 5/17/2021 10:05:00 AM
B = 5/17/2021 10:15:00 AM

and my query is like

" Select * from log_10 where timestring between A and B "

But not working is this case..,is there anything to do kindly let me know..

Thankyou

Best Regards
TN Sudheer

 

[Andrzejek]

You may want to see How to Pass Parameters to VBScript

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson