sql server - Unable to access vbscript variable after using it in sql string - Variable is not null, but errors when used -

sql server - Unable to access vbscript variable after using it in sql string - Variable is not null, but errors when used -

i have block of vbscript takes in variable, builds sql string it, executes sql string, passes results of sql query along original variable sub. works pretty much time, except in 1 instance. here's code (i added line numbers reference... also, know record_source isn't used, left during alter made months ago backwards compatibility):

1224 sub updatecontact1 (record_source, user_id) 1225 response.write("updatecontact1 - user_id: " & user_id & "<br />") 1226 strsql = "exec usp_gm_getinfoforcontact1 " & user_id 1227 set rs = con.execute(strsql) 1228 response.write("updatecontact1 after sql - user_id: " & user_id & "<br />") 1229 if not rs.eof 1230 response.write("updatecontact1 within of if<br />") 1231 response.write("user_id: " & user_id & "<br />") 1232 insertfullcontact1 user_id, rs("goldmineaccountnumber"), rs("businessname"), rs("fullname"), rs("lastname"), rs("salutation"), rs("title"), rs("workphone"), rs("cellphone"), rs("homephone"), rs("primaryfax"), rs("workext"), rs("address1"), rs("address2"), rs("city"), rs("state"), rs("postalcode"), rs("country"), rs("firstname"), rs("referralname"), rs("privatetitle"), rs("specialty"), "", rs("gla"), rs("groupsdesc"), rs("rolesdesc") 1233 end if 1234 end sub

here result of code:

updatecontact1 - user_id: 34838 error '80020009' /includes/gm_functions.asp, line 1228

initial research suggests error 80020009 indicate variable null. checked using isnull:

1224 sub updatecontact1 (record_source, user_id) 1225 response.write("updatecontact1 - user_id: " & user_id & "<br />") 1226 strsql = "exec usp_gm_getinfoforcontact1 " & user_id 1227 set rs = con.execute(strsql) 1228 if isnull(user_id) 1229 response.write("user_id null") 1230 else 1231 response.write("user_id not null<br />") 1232 response.write("length: " & len(user_id) & "<br />") 1233 end if 1234 if not rs.eof 1235 response.write("updatecontact1 within of if<br />") 1236 response.write("user_id: " & user_id & "<br />") 1237 insertfullcontact1 user_id, rs("goldmineaccountnumber"), rs("businessname"), rs("fullname"), rs("lastname"), rs("salutation"), rs("title"), rs("workphone"), rs("cellphone"), rs("homephone"), rs("primaryfax"), rs("workext"), rs("address1"), rs("address2"), rs("city"), rs("state"), rs("postalcode"), rs("country"), rs("firstname"), rs("referralname"), rs("privatetitle"), rs("specialty"), "", rs("gla"), rs("groupsdesc"), rs("rolesdesc") 1238 end if 1239 end sub

and output:

updatecontact1 - user_id: 34838 user_id not null error '80020009' /includes/gm_functions.asp, line 1232

executing stored procedure manually in ssms using same user_id works fine. i'm @ loss understand happening variable, much less prepare it. i'd appreciate help.

thanks...

i'm pretty sure issue connected defining variables. allow me explain why think so. created test page (test.asp)

<!--#include virtual="/includes/gm_functions.asp"--> <% updatecontact1 "who cares", 34838 'updatecontact1definenewvar "who cares", 34838 response.write("finished") %>

and here updatecontact1 , updatecontact1definenewvar functions

sub updatecontact1 (record_source, user_id) response.write("updatecontact1 - user_id: " & user_id & "<br />") strsql = "exec usp_gm_getinfoforcontact1 " & user_id response.write("hey created sql query <br />") set rs = con.execute(strsql) response.write("hey got data<br/>") if not rs.eof dim ''code end if end sub sub updatecontact1definenewvar (record_source, user_id) dim strsql dim rs response.write("updatecontact1definenewvar - user_id: " & user_id & "<br />") strsql = "exec usp_gm_getinfoforcontact1 " & user_id response.write("hey created sql query <br />") set rs = con.execute(strsql) response.write("hey got data<br/>") if not rs.eof dim ''code end if end sub

the outputs are:

updatecontact1 - user_id: 34838

microsoft vbscript runtime error '800a01f4'

variable undefined: 'strsql'

/includes/gm_functions.asp, line 1217

and

updatecontact1definenewvar - user_id: 34838 hey created sql query

microsoft vbscript runtime error '800a01a8'

object required: ''

/includes/gm_functions.asp, line 1232

so hey created sql query makes me think variables using not defined (include file missing?). i'm not expert in vbscript, hope help you.

update

i extended code adding response.write(vartype(strsql))

so first output

microsoft vbscript runtime error '800a01f4' variable undefined: 'updatecontact1' /test.asp, line 5

and sec

0 updatecontact1definenewvar - user_id: 34838 hey created sql query microsoft vbscript runtime error '800a01a8' object required: '' /includes/gm_functions.asp, line 1221

and 0 means vbempty - indicates empty (uninitialized) http://www.w3schools.com/vbscript/func_vartype.asp

sql sql-server vbscript asp-classic