INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

escaping / data mismatch problem

escaping / data mismatch problem

(OP)
I put together a simple script that updates an entry in a database table.  The whole thing looks like this:

dim JobID, zidarg
JobID = "1"
zidarg = "2"
Set MyConn = CreateObject("ADODB.Connection")
Wscript.echo "UPDATE TaskTbl Set TaskID = '" & JobID & "' WHERE ID = '" & zidarg & "'"
MyConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\data\test.accdb"
MyConn.Execute "UPDATE TaskTbl Set TaskID = '" & JobID & "' WHERE ID = '" & zidarg & "'"
MyConn.Close
set JobID = nothing
set zidarg = nothing


I think I'm having trouble separating my strings and variables at the end of the 'Execute' line.  When I replace my "zidarg" variable with a number value, it works fine.  When I try to use the variable I get an error.  I feel like I've tried every combination of quotes and ampersands and can't get it to work.  Any suggestions would be greatly appreciated.

Works:
MyConn.Execute "UPDATE zzzTaskScheduler Set TaskID = '" & JobID & "' WHERE ID = 2"

Does not work:
MyConn.Execute "UPDATE zzzTaskScheduler Set TaskID = '" & JobID & "' WHERE ID = '" & zidarg & "'"
 

RE: escaping / data mismatch problem

ID is apparently a numeric field but you are enclosing zidard in quotes which indicate a text field. Try

CODE

dim JobID, zidarg
JobID = "1"
zidarg = 2
Set MyConn = CreateObject("ADODB.Connection")
Wscript.echo "UPDATE TaskTbl Set TaskID = '" & JobID & "' WHERE ID = " & zidarg
MyConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\data\test.accdb"
MyConn.Execute "UPDATE TaskTbl Set TaskID = '" & JobID & "' WHERE ID = " & zidarg
MyConn.Close

RE: escaping / data mismatch problem

If it's a number it doesn't need the single quotes

CODE

MyConn.Execute "UPDATE zzzTaskScheduler Set TaskID = '" & JobID & "' WHERE ID = " & zidarg

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244: How to get the best answers first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

RE: escaping / data mismatch problem

(OP)
That was it guys, thanks.  I didn't originally use quotes around my variables but I must have left that quote on the end of my SQL statement.  Appreciate the help.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close