Insert Query Fails

[tgcfl]

Hello,

I am attempting to execute a VBScript that will insert the results returned into a SQL table. The script executes without error, but when I check the SQL table nothing has been inserted. Could someone please take a look at the script below and let me know where I have gone wrong?

Thanks in advance for your help.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
<job id="Export_Userids">
<runtime>
<description>
</description>
</runtime>

<object id="iLETS" progid="iCONECT.iLETS"/>
<script language="VBScript">

Dim AllTheUsers
Dim ClientsAndCases
Dim OneUser
Dim UserName
Dim UserID
Dim EmailAddress
Dim FirstName
Dim LastName
Dim IsActive
Dim Description
Dim ObjConn

Set AllTheUsers = iLETS.AllUsers

' Retrieve and Print Host Computer Name
Set objComputer = CreateObject("Shell.LocalMachine")
Wscript.Echo "Running script on -" & objComputer.MachineName

' Establish Connection to SQL Database

Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Driver={SQL Server};" & _
"Server=xxx.xxx.xxx.x;" & _
"Database=xxxxxxxxxxx;" & _
"user id=xxxxxxxx;" & _
"password=xxxxxxxx;"

' Create Loop to Append SQL table

LowerBound = AllTheUsers.LowerBound
UpperBound = AllTheUsers.UpperBound
J = 1
For I = LowerBound To UpperBound
Set OneUser = AllTheUsers.UserAt (I)
UserName = OneUser.Name
'WScript.Echo "User name:" & UserName
'WScript.Echo "Variable type:" & typename(UserName)
UserID = cstr(OneUser.ID)
' WScript.Echo "User ID:" & UserID
' WScript.Echo "Variable type:" & typename (UserID)
EmailAddress = OneUser.EmailAddress
' WScript.Echo "EmailAddress:" & EmailAddress
' WScript.Echo "Variable type:" & typename (EmailAddress)
FirstName = OneUser.FirstName
' WScript.Echo "First Name:" & FirstName
' WScript.Echo "Variable type:" & typename (FirstName)
LastName = OneUser.LastName
' WScript.Echo "Last Name:" & LastName
' WScript.Echo "Variable type:" & typename (LastName)
Phone = OneUser.ContactNumber
' WScript.Echo "Phone:" & Phone
' WScript.Echo "Variable type:" & typename (Phone)
IsActive = OneUser.IsActive
Description = OneUser.Description
' Execute SQL Insert Query
strSQL = "INSERT INTO xxxxxxxx_users_report (UserName," & _
"UserID, EmailAddress, FirstName, LastName, Description, Isactive, Phone, Server) VALUES (" & _
"'" & UserName & "'," & _
"'" & UserID & "'," & _
"'" & EmailAddress & "'," & _
"'" & FirstName & "'," & _
"'" & LastName & "'," & _
"'" & Description & "'," & _
"'" & IsActive & "'," & _
"'" & Phone & "'," & _
"'" & objComputer.MachineName & "')"
J = J + 1
Next
objConn.close

' Loop completes, list exported.

WScript.Echo "List of Users Exported."

</script>
</job>

 

[tsuji]

I don't see where you've executed the sql, hence the observation.
[tt]
'etc etc
[red]objConn.execute strSQL[/red]
J = J + 1
'etc etc
[/tt]

 

[tgcfl]

Hey, thanks alot. That was it! We had sort of thought that was missing, but none of the code examples we had contained the execute statement.

One last problem...we have some usernames in our database that have a single quote symbol (like D'Angelo). When the script hits this kind of name, it errors out. I think we need to somehow escape out the special character but I can't remember at all how to do that.

Can you help out one more time?

 

[tsuji]

Before setting up the strSQL, prepare the variable such as UserName, ... etc, by doubling up the apostrophe.
[tt] UserName=replace(UserName,"'","''")
[/tt]