1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

SQL UPDATE statement problem

Discussion in 'C#' started by gilgalbiblewheel, Apr 15, 2005.

  1. #1
    How do I insert this VBScript? Please correct me from the 1st response .write to the "next"

    In case you're wondering, I am inserting some mathematical result from the formula between the 1st response .write to the "next".
    SEMrush
    
      TheString = Request.QueryString("new_text_data")
      ArrayTemp = split(TheString, " ")
      NumberOfWords = UBound(ArrayTemp) + 1
    
        SQL = "UPDATE 2"
        SQL = SQL & "SET new_gemetria = "
    
       Response.Write "<P><i>Number of words:</i> " & NumberOfWords
       Response.Write "<BR>Total=" & computeValue(TheString)
      For Each Word In ArrayTemp
       Response.Write "<BR><font size=""5"" face=""BSTHebrew"">" & word & "</font>="
       Response.write computeValue(word)
      next
        SQL = SQL & "WHERE new_text_data = ''"
    
    Code (markup):
    And I don't know if this is right but "new_text_data" should be the "new_text_data" of every single record in the table. So I left the '' blank. How should I write that?
     
    gilgalbiblewheel, Apr 15, 2005 IP
    SEMrush
  2. riziko

    riziko Peon

    Messages:
    189
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    0
    #2
    What is that code supposed to do? I can't understand it.

    You need to give some sample data like what is in new_text_data? and what is the structure of the database table?

    Ross
     
    riziko, Apr 17, 2005 IP
  3. nevetS

    nevetS Evolving Dragon

    Messages:
    2,544
    Likes Received:
    211
    Best Answers:
    0
    Trophy Points:
    135
    #3
    Well, first off, the NumberOfWords variable isn't being appended to your SQL statement.

    So you are updating every record to be the same?

    If that's the case, you don't need a WHERE clause.

    Second, you'll have to convert your variable to a number, but it looks like you are using vbscript, which I believe will convert text to numbers easily.
    
    Update 2 set new_text_data = 'text';
    or
    Update 2 set new_text_data = X; where x is a number if new_text_data is a number.
    
    Code (markup):
    Assuming that 2 is the name of your table.
     
    nevetS, Apr 17, 2005 IP
  4. davedx

    davedx Peon

    Messages:
    429
    Likes Received:
    21
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Make sure you have a space after the table name too... i.e. NOT: UPDATE 2SET new_blah blah blah...
     
    davedx, Apr 17, 2005 IP
  5. gilgalbiblewheel

    gilgalbiblewheel Well-Known Member

    Messages:
    435
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    101
    #5
    
    <% Option Explicit 
    
    ' ADO constants used in this page
    Const adOpenForwardOnly = 0
    Const adLockReadOnly = 1
    Const adCmdTableDirect = &H0200
    Const adUseClient = 3
    %>
    <html>
    <head>
      <style>
      body { font-family : Verdana; font-size : 8pt; }
      a { font-family : Verdana; font-size : 8pt; text-decoration : none; }
      </style>
    </head>
    
    <body>
    <%
      Dim connStr
        connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("hebrewbible.mdb")
    
            Dim ans
            Dim i
            Dim rs
            Dim SQL
            Dim TheString, ArrayTemp, NumberOfWords, Word
    
    function getKeyValue_h(chr)
      //A select statement is more efficient for this
      select case chr
       case "a"
        getKeyValue_h = 1 
       case "b"  
        getKeyValue_h = 2 
       case "g"
        getKeyValue_h = 3 
       case "d"
        getKeyValue_h = 4 
       case "h"
        getKeyValue_h = 5 
       case "w"
        getKeyValue_h = 6 
       case "z"
        getKeyValue_h = 7 
       case "x"
        getKeyValue_h = 8 
       case "j"
        getKeyValue_h = 9 
       case "y"
        getKeyValue_h = 10 
       case "k","$"
        getKeyValue_h = 20 
       case "l"
        getKeyValue_h = 30 
       case "m","~"
        getKeyValue_h = 40 
       case "n","!"
        getKeyValue_h = 50 
       case "s"
        getKeyValue_h = 60 
       case "["
        getKeyValue_h = 70 
       case "p","@"
        getKeyValue_h = 80 
       case "c","#"
        getKeyValue_h = 90 
       case "q"
        getKeyValue_h = 100 
       case "r"
        getKeyValue_h = 200
       case "f","v"
        getKeyValue_h = 300 
       case "t"
        getKeyValue_h = 400
       case else
        getKeyValue_h = 0
      end select
    end function 
    
    function computeValue(str)
      ans = 0
      for i = 0 to len(str)
        ans = ans + getKeyValue_h(mid(str,i+1,1))
      next
      computeValue = ans 
    end function
    
        
    
        Set rs = Server.CreateObject("ADODB.Recordset")
        
        rs.PageSize = 10
        rs.CacheSize = 5
        rs.CursorLocation = adUseClient
    '  TheString = rs("new_text_data")
      TheString = Request.QueryString("new_text_data")
      ArrayTemp = split(TheString, " ")
      NumberOfWords = UBound(ArrayTemp) + 1
        SQL = "UPDATE 2 "
        SQL = SQL & "SET new_gemetria = "
    
    '  Response.Write "<P>The String is: " & TheString
       Response.Write "<P><i>Number of words:</i> " & NumberOfWords
       Response.Write "<BR>Total=" & computeValue(TheString)
    '  Response.Write "<P>Here are the words which compose that string: "
      For Each Word In ArrayTemp
       Response.Write "<BR><font size=""5"" face=""BSTHebrew"">" & word & "</font>="
       Response.write computeValue(word)
      next
        SQL = SQL & "WHERE new_text_data = ''"
    '    SQL = SQL & "ALTER TABLE 2 ADD new_gemetria memo"
    
    
    
        rs.Open SQL, connStr, adOpenForwardOnly, adLockReadOnly, adCmdTableDirect
        
        
        If Len(Request("pagenum")) = 0  Then
    '        rs.AbsolutePage = 1
          Else
            If CInt(Request("pagenum")) <= rs.PageCount Then
                rs.AbsolutePage = Request("pagenum")
              Else
                rs.AbsolutePage = 1
            End If
        End If
        
        Dim abspage, pagecnt
          abspage = rs.AbsolutePage
          pagecnt = rs.PageCount
        
        If Not rs.EOF Then
          Response.Write "PageCount : " & rs.PageCount & "<br>" & vbcrlf
          Response.Write "Absolute Page : " & rs.AbsolutePage & "<br>" & vbcrlf
          
            Response.Write "Total number of records : " & rs.RecordCount & "<br><br>" & vbcrlf
            
            Dim fldF, intRec
            
            Response.Write "<table border=1 align=center cellpadding=3 cellspacing=0><thead><tr>"
            For Each fldF in rs.Fields
              Response.Write "<td>" & fldF.Name & "</td>"
            Next
            Response.Write "</tr></thead><tbody>"
            
            For intRec=1 To rs.PageSize
              If Not rs.EOF Then
                Response.Write "<tr>"
                For Each fldF in rs.Fields
                  Response.Write "<td>" & fldF.Value & "</td>"
                Next
                Response.Write "<tr>"
                rs.MoveNext
              End If
            Next
            Response.Write "</tbody></table><p>"
            
            ' Now showing first, next, back, last buttons.
            Response.Write "<div align=""center"">" & vbcrlf
            Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=1"">First Page</a>"
            Response.Write "&nbsp;|&nbsp;"
            
            If abspage = 1 Then
            Response.Write "<span style=""color:silver;"">Previous Page</span>"
            Else
            Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=" & abspage - 1 & """>Previous Page</a>"
            End If
                    Response.Write "&nbsp;|&nbsp;"
            
            If abspage < pagecnt Then
            Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=" & abspage + 1 & """>Next Page</a>"
            Else
            Response.Write "<span style=""color:silver;"">Next Page</span>"
            End If
            Response.Write "&nbsp;|&nbsp;"
            Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=" & pagecnt & """>Last Page</a>"
            Response.Write "</div>" & vbcrlf
            
        Else
          Response.Write "No records found!"
        End If
                
        rs.Close
        Set rs = Nothing
    %>
    </body>
    </html>
    Code (markup):
    What it is supposed to do is to take "new_text_data" and convert the letters into numercal values. Then it should add the letters of words together ( the original coding of the functions that do this job are found in http://k.domaindlx.com/gemetria/kjv.asp. To see how it works just type in "bra" in the "look for:" section and press "search". Pressing "Enter" won't work.) So you'll see the results under the "Gemetria value" column. I want to add these values to the database table so that I can "search" those numbers as well.
     
    gilgalbiblewheel, Apr 17, 2005 IP