Web Analytics Made Easy -
StatCounter How to get rid of this comma!!?! - CodingForum

Announcement

Collapse
No announcement yet.

How to get rid of this comma!!?!

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to get rid of this comma!!?!

    THe code is this:
    Code:
    If Trim(Request.QueryString("id")) <> "" Then
            myarray = split(id," ")
            whereclause=" id = " & myarray(0)
            for counter=1 to ubound(myarray)
                whereclause = whereclause & " OR id = " & myarray(counter)
            next
         SQL = SQL & whereclause
         iCounter = iCounter + 1
         End If
    It gives this error:
    [QUOTE]
    SELECT * FROM bible WHERE id = 1, OR id = 2

    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    [Microsoft][ODBC Microsoft Access Driver] Syntax error (comma) in query expression 'id = 1, OR id = 2'.
    [\QUOTE]

    How do you get rid of this comma?

    -----
    Problem fixed, when I replaced
    Code:
    '            whereclause = whereclause & " OR id = " & myarray(counter)
    with
    Code:
            whereclause = left(whereclause,len(whereclause)-1) & " OR id = " & myarray(counter)
    I don't know why, even though the query states:
    SELECT * FROM bible WHERE id = 1 OR id = 2
    The result writes one id alone.
    -----
    To give more details, I believe that the reason that it didn't pick up the 2nd id was because it didn't recognize the 2nd selected checkbox. Here is the question/response's question page. It picked up the first checkbox but not the 2nd.
    Code:
    <%
    Do until RS.eof
    Dim strText, strFind
    %>
    <input type="checkbox" value="<%=RS("id")%>" name="id"><br/>
    <%
    Response.Write("<textarea ")
    response.write("name=" & chr(34) & "Keywordh" & RS("id") & chr(34))
    response.write(" rows=" & chr(34) &  "3" & chr(34))
    response.write(" cols=" & chr(34) & "64" & chr(34) & ">")
    response.write("</textarea>")
    rs.movenext
    -------
    This is what I did:
    Code:
        For each x in myarray
    
    SQL= "SELECT * "
    SQL = SQL & " FROM bible WHERE "
    
    If Trim(Request.QueryString("id")) <> "" Then
            myarray = split(id," ")
            whereclause=" id = " & x
            for counter=1 to ubound(myarray)
    '            whereclause = whereclause & " OR id = " & myarray(counter)
    '        whereclause = left(whereclause,len(whereclause)-1) & " OR id = " & myarray(counter)
            next
         SQL = SQL & whereclause
         iCounter = iCounter + 1
         End If
    next
    This is what I got:
    Microsoft VBScript runtime error '800a01c3'

    Object not a collection
    and points to
    For each x in myarray
    My youtube channel:
    https://www.youtube.com/channel/UCme...H42czlwGaBX8JA

    Compare bible texts (and other tools):
    The Biblewheel

  • #2
    I don't have much time, but use the replace function to get rid of the ","
    I was wondering why frisbees got bigger as they got closer, then it hit me.

    Comment


    • #3
      Ok now, what's wrong with this?
      Code:
      <td>
      <%
      TheString = rs("text_data")
      ArrayTemp = split(TheString, " ")
      NumberOfWords = UBound(ArrayTemp) + 1
      
      Response.Write "<P><i>Number of words:</i>" & NumberOfWords & "</br>"
      i=0
      
         For Each Word In ArrayTemp
         Response.Write "<input type=""checkbox"" value=""" & Trim(ArrayTemp(i)) & """ name=""Keywordh""><font size=""2"" face=""Verdana"">" & word & "</font></br>"
      i=i+1
        next
        Response.Write "</br>"
      
      %>
      </td>
      response page:
      Code:
           If Keywordh <> "" Then
                  ArrayTemp = split(Keywordh," ")
                      whereclause = whereclause + " text_data LIKE '" & Trim(ArrayTemp(0)) & "'"
                      i=i+1
                  for i=1 to ubound(ArrayTemp)
                      whereclause = whereclause + " AND text_data LIKE '" & Trim(ArrayTemp(i)) & "'"
                  next
               SQL = SQL & whereclause
          End If
      It's able to search:
      SELECT * FROM bible WHERE text_data LIKE 'after,' AND text_data LIKE 'death,' AND text_data LIKE 'Moses,' AND text_data LIKE 'servant,' AND text_data LIKE 'LORD'
      but says
      We did not find a match of "after, death, Moses, servant, LORD"!
      My youtube channel:
      https://www.youtube.com/channel/UCme...H42czlwGaBX8JA

      Compare bible texts (and other tools):
      The Biblewheel

      Comment


      • #4
        You should use the wildcard character % and use OR instead of AND if you want just any of them to be satisfied.

        SELECT * FROM bible WHERE text_data LIKE '%after,%'

        That means you want to search for 'after,' that may be in between characters.
        If you want it to find that starts with that string, then you use 'after,%'
        For those ending with that string, use '%after,'
        Glenn
        vBulletin Mods That Rock!

        Comment


        • #5
          @gilgalbiblewhee,
          You still have the comma in your sql statement. That's why it's not returning anything. I am assuming from your code snipets that you have a collections of input items on a page named "id" and you want to use what the user submitted to search for results. Therefore, here is how I would do it:

          If Trim(Request.QueryString("id")) <> "" Then
          myarray = split(id,", ")
          whereclause = ""
          For counter = 0 To Ubound(myarray)
          If whereclause <> "" Then whereclause = whereclause & " OR "
          whereclause = "id = " & myarray(counter)
          Next
          SQL = SQL & whereclause
          End If
          If Keywordh <> "" Then
          ArrayTemp = split(Keywordh,", ")
          whereclause = whereclause + " text_data LIKE '" & Trim(ArrayTemp(0)) & "'"
          i=i+1
          for i=1 to ubound(ArrayTemp)
          whereclause = whereclause + " AND text_data LIKE '" & Trim(ArrayTemp(i)) & "'"
          next
          SQL = SQL & whereclause
          End If
          Hope that helps.

          --Neo

          Comment


          • #6
            OK. Now, how do you get rid of punctuation from within sql? Because I did the split function on the space bar to separate the words but the punctuation remains.

            I want to get rid of punctuations like:"; : , . ? "
            My youtube channel:
            https://www.youtube.com/channel/UCme...H42czlwGaBX8JA

            Compare bible texts (and other tools):
            The Biblewheel

            Comment


            • #7
              If you are wanting to do this directly in sql, you'll need to run an update statement.

              UPDATE [tablename] SET [column] = [new value] WHERE [column] = [value]

              The 2nd column reference does not have to be the same as the first and should be the primary key field unless you need to make numerous updates of the same column and value.

              Comment


              • #8
                Ok that is fixed. What I have is a phrase that is broken into a list of checkboxed words with a textarea underneath. I would like to check any words and it should appear in the textarea. How can I do it? I have this so far but it's not showing the checked words in the textarea:

                Code:
                <script language='javascript'>
                 function addCheck() {
                 document.getElementById('KeywordhText').value = '';
                   for (var i = 0; i < document.getElementById('Keywordh').length;i++) {
                     if (document.yourForm.Keywordh[i].checked == true) {
                         document.getElementById('KeywordhText').value = document.getElementById('KeywordhText').value + document.yourForm.Keywordh[i].value + ' ';
                     }
                   }
                 }
                 </script>
                 
                 <%
                    For Each Word In ArrayTemp
                    Response.Write "<input type=""checkbox"" value=""" & Trim(ArrayTemp(i)) & """ name=""Keywordh"" onChange=""javascript:addCheck();""><font size=""2"" face=""Verdana"">" & word & "</font></br>"
                 i=i+1
                   next
                   Response.Write "</br>"
                 
                 %>
                 </td>
                 <%
                   rs.MoveNext
                 loop%></br>
                <%
                Response.Write("<textarea name='""KeywordhText""' rows='3' cols='64'>")
                response.write("</textarea></br>")
                %>
                <%' <input size="10" name="KeywordhText" value="<%=Request("Keywordh")%"></br>%>
                 <input type="submit" value=" Search " size="100" title="Search everything selected">
                 </form>
                My youtube channel:
                https://www.youtube.com/channel/UCme...H42czlwGaBX8JA

                Compare bible texts (and other tools):
                The Biblewheel

                Comment


                • #9
                  @gilgalbiblewhee,
                  If I am reading your posted code correctly, getElementById is looking for the id attribute of your textbox, not the name. So you add id="KeywordhText" to your textbox. In addition, the first line in your javascript function would clear all of the words in the textbox (document.getElementById('KeywordhText').value = ''). If your asp variable (ArrayTemp) is a single dimension array (which I assume that it is) then instead of having Trim(ArrayTemp(i)), you should be able to just use the variable "word". I have never used For Each when looping through arrays, so I'm not even sure if that works. I loop through arrays like so:
                  For iArrayLoop = Lbound(Array) To Ubound(Array)
                  ...Array(iArrayLoop)
                  Next
                  I also don't see your open form tag, so I am assuming that it comes before the javascript. I would also recommend moving your javascript code outside of the form tag. I've seen some freaky stuff happen with having javascript code that does something to a form when the code is inside the open and close form tags.
                  <script language='javascript'>
                  function addCheck() {
                  // document.getElementById('KeywordhText').value = '';
                  for (var i = 0; i < document.getElementById('Keywordh').length;i++) {
                  if (document.yourForm.Keywordh[i].checked == true) {
                  document.getElementById('KeywordhText').value = document.getElementById('KeywordhText').value + document.yourForm.Keywordh[i].value + ' ';
                  }
                  }
                  }
                  </script>

                  <%
                  Dim iArrayLoop
                  For iArrayLoop = Lbound(ArrayTemp) To Ubound(ArrayTemp)
                  Response.Write "<input type=""checkbox"" value=""" & Trim(ArrayTemp(iArrayLoop)) & """ name=""Keywordh"" onChange=""javascript:addCheck();""><font size=""2"" face=""Verdana"">" & Trim(ArrayTemp(iArrayLoop)) & "</font></br>"

                  next
                  Response.Write "</br>"

                  %>
                  </td>
                  <%
                  rs.MoveNext
                  loop%></br>
                  <textarea name="KeywordhText" rows="3" cols="64"></textarea></br>
                  <%' <input size="10" name="KeywordhText" value="<%=Request("Keywordh")%"></br>%>
                  <input type="submit" value=" Search " size="100" title="Search everything selected">
                  </form>
                  Last edited by neocool00; Aug 11, 2005, 10:26 AM.

                  Comment


                  • #10
                    I don't know if this makes it clear but this is the page source:
                    Code:
                    <html>
                    <body>
                    This is the SQL statement: SELECT *  FROM bible WHERE  id = 1<br/><b>1</b> verses have been selected.</br>
                    <form name="yourForm" action="kjvresp.asp" method="get" target="_blank">
                    <td>
                    <P><i>Number of words:</i>10</br>
                    <script type='text/javascript'>
                     function addCheck() {
                     document.getElementById('KeywordhText').value = '';
                       for (var i = 0; i < document.getElementById('Keywordh').length;i++) {
                         if (document.yourForm.Keywordh[i].checked == true) {
                             document.getElementById('KeywordhText').value = document.getElementById('KeywordhText').value + document.yourForm.Keywordh[i].value + ' ';
                         }
                       }
                     }
                     </script>
                     
                     <input type="checkbox" value="In" name="Keywordh" id="Keywordh" onChange="addCheck();"><font size="2" face="Verdana">In</font></br><input type="checkbox" value="the" name="Keywordh" id="Keywordh" onChange="addCheck();"><font size="2" face="Verdana">the</font></br><input type="checkbox" value="beginning" name="Keywordh" id="Keywordh" onChange="addCheck();"><font size="2" face="Verdana">beginning</font></br><input type="checkbox" value="God" name="Keywordh" id="Keywordh" onChange="addCheck();"><font size="2" face="Verdana">God</font></br><input type="checkbox" value="created" name="Keywordh" id="Keywordh" onChange="addCheck();"><font size="2" face="Verdana">created</font></br><input type="checkbox" value="the" name="Keywordh" id="Keywordh" onChange="addCheck();"><font size="2" face="Verdana">the</font></br><input type="checkbox" value="heaven" name="Keywordh" id="Keywordh" onChange="addCheck();"><font size="2" face="Verdana">heaven</font></br><input type="checkbox" value="and" name="Keywordh" id="Keywordh" onChange="addCheck();"><font size="2" face="Verdana">and</font></br><input type="checkbox" value="the" name="Keywordh" id="Keywordh" onChange="addCheck();"><font size="2" face="Verdana">the</font></br><input type="checkbox" value="earth" name="Keywordh" id="Keywordh" onChange="addCheck();"><font size="2" face="Verdana">earth</font></br></br>
                    
                     </td>
                     </br>
                    <textarea name="KeywordhText" id="KeywordhText" rows='3' cols='64'></textarea></br>
                     <input type="submit" value=" Search " size="100" title="Search everything selected">
                     </form>
                    
                    </body>
                    </html>
                    My youtube channel:
                    https://www.youtube.com/channel/UCme...H42czlwGaBX8JA

                    Compare bible texts (and other tools):
                    The Biblewheel

                    Comment


                    • #11
                      Ok, looks like you just comment out the first line of javascript function and it should work. I would also consider moving your javascript code so that it outside of your form tags. One other suggestion would be to add "& vbcrlf" at the end of your response.write line that prints your checkboxes to the screen. This will help you when you view source of the resulting page by adding a carriage return between each checkbox line of code (instead of having it all on one line).

                      Comment

                      Working...
                      X