Writing Record to SQL2005 with VBSCRIPT

Question

Post reply

Writing Record to SQL2005 with VBSCRIPT

ncartwright

SSC Rookie

Points: 33
More actions
April 22, 2009 at 1:18 pm

#208414

I have a vbscript that i'd like to run and store the information in SQL. I was able to get it to work with Access but obviously the procedure is different. Here's the script I have:
sub DbaseUpd (strUser,strSite,strXntVer,strXntDate,strTerm)
Dim conn,sql,strSQLQuery,rsVers,strConnStr
strConnStr = "Provider=sqloledb;Data Source=t2csql02; Initial Catalog=usrinfo;User Id=logonscript;Password=;"
Set conn = CreateObject("ADODB.Connection")
conn.Open strConnStr
strSQLQuery = "SELECT username FROM tblUserInfo WHERE username = '" & strUser & "'"
set rsVers=CreateObject("ADODB.Recordset")
rsVers.open strSQLQuery,conn,3,3
if rsVers.bof and rsVers.eof then
'new user
strSQLQuery = "INSERT INTO tblScribeInfo (username,site,logintime,computername,xntVer,xntdate)"
strSQLQuery = strSQLQuery & " Values('" & strUser & "','" & strSite & "','" & Now() & "','" & strTerm & "','" & strXntVer & "','" & strXntDate & "')"
else
'existing user
strSQLQuery = "UPDATE tblScribeInfo SET username = '" & strUser & "', site = '" & strSite & "', logintime = '" & Now() & "', termserver = '" & strTerm & "', "
strSQLQuery = strSQLQuery & "xntVer = '" & strXntVer & "', xntdate = '" & strXntDate & "' WHERE username = '" & strUser & "'"
end if
rsVers.close
rsVers.open strSQLQuery,conn,3,3
end sub
Set objNet = WScript.CreateObject("WScript.Network")
strDomain = objNet.UserDomain
strUser = objNet.Username
Set objFso = WScript.CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.GetFile("c:\TrackitAudit.id")
strFileVer = objFSO.GetFileVersion("c:\TrackitAudit.id")
strFileDate = objFile.DateLastModified
Set objwsh = WScript.CreateObject("WScript.Shell")
strComputerName = objwsh.RegRead("HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\ComputerName\ActiveComputerName\ComputerName")
conn.Execute "INSERT INTO usrinfo (strUser, "Preston", strFileVer, strFileDate ,strComputerName) VALUES (strUser, "Preston", strFileVer, strFileDate ,strComputerName)"
set rsVers = Nothing
set conn = Nothing
I'm definatly not a VB programmer - but any help would be appricated.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ggoble Hall of Fame Points: 3337 More actions · Answer 1

You could create a stored procedure and execute the stored procedure from vb

WayneS SSC Guru Points: 95460 More actions · Answer 2

I don't do a lot of VBScript programming, but from what I see:

1. your main routine never calls the DbaseUpd subroutine. I think before the conn.Execute line, you need a call to DbaseUpd.

2. I don't see where (in the main section) that you get all of the parameters needed to pass to DbaseUpd.

3. your main routine calls conn.Execute, but it hasn't been created since the DbaseUpd sub-routine hasn't been called.

4. since the conn object is created in the DbaseUpd, will it be available to the main routine?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2