Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  MDAC 2.6, VBScript, SPs and NULL problem

Author  Topic  Magnus_ Starting Member 3 Posts Posted - 2001-09-14 : 14:36:07 Hi. I recently upgraded a shared Win2k/SQL2k server from MDAC 2.5 to MDAC 2.6 SP1. I now have trouble with empty strings in ASP (VBScript) being interpreted as NULL instead of empy strings when I call stored procedures. Example I have a table that stores refering URL. No NULLs allowed, default value is '', in other words a empty string. I use a version of ADOHelper.asp when I call the SPs so my example below will be based on that. Example 1: How it used to be ---------------------------- dim httpreferer httpreferer = Cstr(Request.Servervariables("HTTP_REFERER")) 'Initialize the class set oDB = new ADOHelper 'Name of SP to execute sSQL = "USP_LogReferer" 'Set up SP paramaters sParams = array( _ array("@httpreferer", adVarchar, adParamInput, 500, httpreferer), _ array("@errcode", adInteger, adParamOutput, 8, 0), _ array("@errmsg", adVarchar, adParamOutput, 1000, "")) 'Execute SP, return NO recordset oDB.RunSP sSQL, sParams, OutArray errcode = Cint(OutArray(1)) If errcode = 0 Then exit sub Else 'We had an error errmsg = OutArray(2) Response.Redirect "showerror.asp?&errormsg=" & Server.URLEncode(errmsg) End if ---------------------------- Sometimes httpreferer can be empty. With MDAC 2.6 SP1 I now get the following error: "Cannot insert the value NULL into column httpreferer" So I tried to fix it by doing this before I call the SP: If IsNull(httpreferer) or IsEmpty(httpreferer) Or (Trim(httpreferer)="") Then httpreferer = "" end if But I get the same error since "" doesnt seem to count as an empty string anymore. I could change the "" to " " with a space between but that doesnt look too good. I also have other pages where people submit say 20 fields with info. Having to check all values and change empty ones to " " is very time consuming. So my question is: Have anyone have the same problem before and solved it or do you know a way to send in an empty string to a stored procedure with MDAC 2.6 from ASP? /Magnus Edited by - Magnus_ on 09/14/2001 14:41:13