"Application uses a value of the wrong type" Error

The following is the code that I am using:
Stored Procedure (Partial code):

CREATE  PROCEDURE GetRespondentDemographics
      @Email nvarchar(75) = null,
      @Respondentid dec(19,0) = null

.......
 
My ASP Code:
<%
      Dim ConnectionString
      Dim ofso
      Dim otmp

        ConnectionString="Provider=SQLOLEDB;Data Source=TEST_W2K_SQL2K;Database=harrispoll;UID=paramtestuser;PWD=paramtestuser;Network Library=dbmssocn;"

      Dim objconn, objRS, adocmd
    Set objconn = Server.CreateObject("ADODB.Connection")
    objconn.Open (ConnectionString)

    Set adocmd = Server.CreateObject("ADODB.Command")
    adocmd.CommandText = "GetRespondentDemographics"
    adocmd.CommandType = adCmdStoredProc
 
    objRS = Server.CreateObject("ADODB.Recordset")
    adocmd.activeConnection = objconn
    adocmd.Parameters.Append adocmd.CreateParameter("@Email",adVarChar, adParamInput, 75)
    adocmd.Parameters.Append adocmd.CreateParameter("@Respondentid",adDecimal, adParamInput, 19)
    adocmd.Parameters("@Respondentid").Precision = 19
    adocmd.Parameters("@Respondentid").NumericScale = 0
    adocmd.Parameters("@Email") = Request.Form("email")
    adocmd.Parameters("@Respondentid") = Request.Form("respondentid")
    set objRS = adocmd.Execute

Ok. I have 2 fields (RespondentID and Email). The user must select at least one of the fields even though both fields can be filled out. Now my problem is that when I only enter the respondentID, I am able to get the results back without a problem BUT when I only enter the email field, I get the following error:
"Error Type:
ADODB.Command (0x800A0D5D)
Application uses a value of the wrong type for the current operation.
/param/HPoll_email_respondent_results.asp, line 28"

The line this is referring to is: adocmd.Parameters("@Respondentid") = Request.Form("respondentid")
When I comment out this field and I go back and enter an email within the email field, the program executes and displays the results without a problem. I do not understand why I am getting this error. The line that it is referring to as incorrect appears correct from my point of view. Any suggestions as to why I might getting this error? Thanks
pimpp1184Asked:
Who is Participating?
 
hclgroupCommented:
Oh, I see, I misread what you had written.

Then put an if statement and only add the respondentid parameter when respondentid is not blank (since the stored procedure defaults it to NULL)
0
 
pimpp1184Author Commented:
Any suggestions at all from anyone on this? Thanks
0
 
hclgroupCommented:
Why are you using nvarchar in your stored proc?
Shouldn't you be using varchar?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
pimpp1184Author Commented:
hclgroup,

That part of the code works perfectly. As I stated above, the email part of the code works fine. The problem exists only when I leave the "respondentid" field blank. If I were to comment out this line, adocmd.Parameters("@Respondentid") = Request.Form("respondentid") and run the program by entering some data in the email field, the program executes.
The program also executes when I have data in the 'respondentid' field. The problem only persists when the 'respondentid' is left empty.
Thanks for your help.
0
 
pimpp1184Author Commented:
Doh! I thought I had tried that. Alright well I created this if-then statement:
      if (Request.Form("respondentid") = "") then
            adocmd.Parameters.Append adocmd.CreateParameter("@Email",adVarChar, adParamInput, 75)
            adocmd.Parameters("@Email") = Request.Form("email")
            set objRS = adocmd.Execute
      else
            adocmd.Parameters.Append adocmd.CreateParameter("@Email",adVarChar, adParamInput, 75)
            adocmd.Parameters.Append adocmd.CreateParameter("@Respondentid",adDecimal, adParamInput, 19)
            adocmd.Parameters("@Respondentid").Precision = 19
            adocmd.Parameters("@Respondentid").NumericScale = 0
            adocmd.Parameters("@Email") = Request.Form("email")
            adocmd.Parameters("@Respondentid") = Request.Form("respondentid")
            set objRS = adocmd.Execute
      end if

It appears to be working fine at a certain extent. The problem I am having now is that if I were to enter both of the fields (Email and respondentid), it will not check to see if the email is in the correct email format (which it will do if ONLY the email field was filled out). Also, if both fields were filled out and the respondentid was the correct one but the email was incorrect, it will still show the results because the respondentid was filled out correctly thus returning the results.
I would like it so if both fields are filled out and if one of the fields are incorrect (as in that it won't match the database table), the output will show 'no results found'.
Am I making sense or not? Let me know if you got some suggestions. Thanks
0
 
pimpp1184Author Commented:
Alright I figured out what I was doing wrong BUT I have another question if you can assist me on it.
As you know I have two fields (Email and RespondentID). The user has the option of choosing either or but never both of them.
Is there a javascript code which will disable one field if the user decides to type in another field.
Ex: The user types in an E-mail, the RespondentID field box will be disabled in that the user will be Unable to type in anything in there; and vice versa.

I have found codes which disable and enable but not what I am looking for as stated above.
Any Help wil be appreciated. Thanks
0
 
pimpp1184Author Commented:
Nevermind I fugured it all.
0
 
hclgroupCommented:
I object, since my last response answered his original question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.