NOT IN

Results 1 to 3 of 3

Thread: NOT IN

  1. #1
    Join Date
    Dec 1969
    Posts
    131

    Default NOT IN

    I have a SQL which is selecting fields according to the querystring which was entered from the previous form&#039s textbox. Like this.....<BR><BR>SQLgetid="SELECT ca.Category_Code, ch.Dept_Code, pe.People_Code, pj.Project_Code, ri.Report_ID, ri.Amount FROM tblCategory AS ca, tblCharge AS ch, tblPeople AS pe, tblProject AS pj, tblReport_Item AS ri WHERE ca.Category_ID=ri.Category_ID AND ch.Dept_ID=ri.Dept_ID AND pe.People_ID=ri.Senior AND pj.Project_ID=ri.Project_ID AND ri.Report_ID IN (" & reportid & ") ORDER BY ri.Report_ID asc"<BR><BR>The problem is, i want to check for non-numeric characters also. If the user tries to submit form without entering a value in the textbox, i want to display an error message. The error checking for non-numeric characters is working but when i tried to submit the form with nothing in the textbox, it gives me an error message which corresponds to the line where my SQL executes. So, i re-wrote my SQL with WHERE NOT IN etc.. like this...<BR><BR>SQLgetid="SELECT ca.Category_Code, ch.Dept_Code, pe.People_Code, pj.Project_Code, ri.Report_ID, ri.Amount FROM tblCategory AS ca, tblCharge AS ch, tblPeople AS pe, tblProject AS pj, tblReport_Item AS ri WHERE ca.Category_ID=ri.Category_ID AND ch.Dept_ID=ri.Dept_ID AND pe.People_ID=ri.Senior AND pj.Project_ID=ri.Project_ID AND ri.Report_ID IN (" & reportid & ") OR ri.Report_ID NOT IN (" & reportid & ") ORDER BY ri.Report_ID asc"<BR><BR>This didn&#039t work either. Could you please tell me where my problem lies . Is it my SQL or error check which is........<BR><BR>For i = 1 to Len(reportid)<BR>ch = Mid(reportid, i, 1)<BR>IF IsNull(reportid) Then<BR> Response.Write "You tried to submit the form without entering a value in the textbox. Please go &#060;a href=&#039javascript:history.go(-1)&#039&#062;back&#060;/a&#062; and enter a ReportID in the area provided. Thank you."<BR>ElseIf IsNumeric(ch)= true or ch = "," or ch = " " Then<BR> &#039do nothing<BR>Else<BR> Response.Write "&#060;font face=&#039Arial, Helvetica, Verdana&#039 size=&#039 2&#039&#062;&#060;b&#062;The ReportID (&#039"& reportid &"&#039) that you&#039ve entered is/are not correct. Please go &#060;a href=&#039javascript:history.go(-1)&#039&#062;back&#060;/a&#062; and correct your entry.&#060;/b.&#060;/font&#062;"<BR> response.end<BR>End If<BR>

  2. #2
    Join Date
    Dec 1969
    Posts
    1,315

    Default RE: NOT IN

    I usually write an if statement that checks if the text box in not equal to blank. Ex. <BR><BR>If request.form("txtbox")&#060;&#062;"" then<BR>sql="select * from table"<BR>else<BR>response.write("You must enter something in the form!"<BR>end if<BR><BR>Good luck

  3. #3
    Join Date
    Dec 1969
    Posts
    131

    Default RE: NOT IN (almost working)

    Thanks John for this suggestion. It is somewhat working except when i submitted the blank form, i got the error message which was in my If statement however, at the end, am getting this<BR><BR>You&#039ve tried to submit the form without entering a value in the textbox. Please go back and do so. Thank you. <BR><BR>Microsoft OLE DB Provider for ODBC Drivers error &#039 80040e0c&#039 <BR><BR>No command has been set for the command object <BR><BR>/rothchild/get_reportid.asp, line 34 <BR><BR>Line 34 corresponds to this.......set oRs=DbObj.execute(SQLgetid)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •