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.

0
  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".

    
  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
  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