How to add a new record to a sql database using a vbscript

Posted on 2008-11-15
Last Modified: 2012-05-05
Hello Everyone,

I am working on a computer inventory database for my company.  I have a script that will get the username, computer name, and serial number from any computer and enter into a text file.

Now I recently created a sql database to put all these records into.  I would like to modify this script so instead of saving to a text file, it would add a new record to the computerinventory table in my sql database.  Is there anyway I can do this?

Here is the script I have right now.



Dim objWshShell, objWMIService, colSMBIOS, objSMBIOS,objFSO,objFile

	Dim strUserName, strComputerName, strComputer 

	Const ForAppending = 8

	Set objFSO = CreateObject("Scripting.FileSystemObject")

	Set objFile = objFSO.OpenTextFile("\\myserver\directory1$\myinventory.txt", ForAppending)

	Set objWshShell = CreateObject("WScript.Shell") 

	strUserName = objWshShell.ExpandEnvironmentStrings("%USERNAME	strComputerName = objWshShell.ExpandEnvironmentStrings("%COMPUTERNAME%")   

	strComputer = "."

	Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2") 

	Set colSMBIOS = objWMIService.ExecQuery("Select * from Win32_SystemEnclosure") 

	For Each objSMBIOS in colSMBIOS 

		 objFile.WriteLine  strUserName& "," & strComputerName& "," & objSMBIOS.SerialNumber


        Wscript.Echo "Finished! Thank You!"

Open in new window

Question by:bjennings
    LVL 76

    Accepted Solution

    Hi, bjennings.

    You need code like that below.  Here's a link to guidance on the connection string for SQL server:  The code in the first block appears once at the top of the script.  The code in the second block replaces line #12 in your code.
    'Create the connection to the database'
    Set adoCon = CreateObject("ADODB.Connection")
    'Change the connection string on the following line'
    adoCon.Open "SQL Connection String"
    'Insert the data into the database'
    'Change the field names on the following line to those of your fields'
    strFields = "UserName,ComputerName,SerialNumber"
    varValues = "'" & strUserName& "','" & strComputerName& "','" & objSMBIOS.SerialNumber & "'"
    adoCon.Execute "INSERT INTO ComputerInventory (" & strFields & ") VALUES(" & varValues & ")"

    Open in new window


    Author Closing Comment

    Awesome...It worked!  Thank you so much!!!
    LVL 76

    Expert Comment

    by:David Lee
    You're welcome.  Glad I could help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now