Apologies for the lengthy post, but I wanted to provide all the necessary details.
I am facing an issue with a JavaScript function that uses ajax to call some asp code, which then executes a stored procedure to check if a record already exists. Depending on the result, different images are displayed. The problem is that I am unable to capture the results in my asp variable, leading to empty returns and false outcomes. Despite testing the SQL procedure separately and confirming the data exists, I can't seem to retrieve it in my code. Here's the code snippet for better understanding:
Stored Proc:
ALTER PROCEDURE [dbo].[UserName_Get]
-- Add the parameters for the stored procedure here
@UserName nvarchar(50)
,@FacilityID INT
,@UserNameExists INT OUT
AS
BEGIN
SELECT @UserNameExists= ISNULL((SELECT UserID FROM MyTable WHERE FacilityID = @FacilityID AND Username = @UserName),0)
END
Classic ASP code:
<% Response.Buffer = True %>
<!-- #include file="../../_app_init.asp" -->
<%
Dim action, p_UserName
action = SqlSanitizeStr(trim(request("action")))
If action = "CheckUserName" Then
p_UserName=SqlSanitizeStr(trim(request("UserName")))
Set objCmd = Server.CreateObject("ADODB.Command")
Set objCmd.ActiveConnection = DB_CONN
objCmd.CommandText = "UserName_Get"
objCmd.CommandType = adCmdStoredProc
objCmd.Parameters.Append objCmd.CreateParameter("@UserName", adLongVarChar, adParamInput, 50 , p_UserName)
objCmd.Parameters.Append objCmd.CreateParameter("@FacilityID", adInteger, adParamInput, , LOGIN_FACILITY_ID)
objCmd.Parameters.Append objCmd.CreateParameter("@UserNameExists", adInteger, adParamReturnValue)
objCmd.Execute ,, adExecuteNoRecords
UserNameExists = objCmd.Parameters("@UserNameExists").Value
If UserNameExists>0 Then
Response.Write ("True")
Else
Response.Write ("False")
End If
End If
%>
JavaScript Ajax Call
<script type="text/javascript">
function CheckUserName(UserName_element) {
var UserName = UserName_element.value;
var valid_UserName_image = document.getElementById('valid_UserName_image');
var invalid_UserName_image = document.getElementById('invalid_UserName_image');
$j.ajax({
data: { action: 'CheckUserName', p_UserName: UserName },
type: 'GET',
url: 'page_logic/_check_username_ajax.asp',
success: function (result) {
//Just to see what the return value is
alert(result)
if (result == "True") {
valid_UserName_image.style.display = "none";
invalid_UserName_image.style.display = "inline";
}
else {
valid_UserName_image.style.display = "inline";
invalid_UserName_image.style.display = "none";
}
}
}
);
}
</script>
An inspection using alert(result) shows that the return value is always empty.
If I modify the stored proc to simply
SELECT @UserNameExists=0 or SELECT @UserNameExists=1
It will return a value, although this approach involves hardcoding the result rather than fetching records from the database.