Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post

    how to update this database table

    I need some guidance. I have:
    Code:
    <td><%=rs("id")%></br>
    <input type="hidden" name="id_<%=intRec%>"
     value="<%=rs.Fields("id").Value%>">
    
    <textarea name="gemetria_<%=intRec%>"
    id="gemetria_<%=intRec%>">
    </textarea>
    
               <select name="insert" id="insert" size="1" style="width:150;">
               <option value="passages">passages</option>
               <option value="projective link">projective link</option>
               <option value="book number linked to book number">book number linked to book number</option>
               <option value="book number linked to chapter number">book number linked to chapter number</option>
               <option value="book number linked to verse number">book number linked to verse number</option>
               <option value="book number linked to number in text">book number linked to number in text</option>
               <option value="chapter number linked to book number">chapter number linked to book number </option>
               <option value="chapter number linked to book number">chapter number linked to book number</option>
               <option value="chapter number linked to chapter number">chapter number linked to chapter number</option>
               <option value="verse number linked to verse number">verse number linked to verse number</option>
               <option value="book number linked to number in text">book number linked to number in text</option>
               <option value="number linked to book">number linked to book</option>
               <option value="number linked to chapter">number linked to chapter</option>
               <option value="number linked to verse">number linked to verse</option>
               <option value="number linked to number">number linked to number</option>
               <option value="alphanumeric word">alphanumeric word</option>
               <option value="non-alphanumeric word">non-alphanumeric word</option>
               <option value="spoke of number in text">spoke of number in text</option>
               <option value="word gemetrial passage">word gemetrial passage</option>
               </select>
    </td>
    To each record I have assigned a textarea, a hidden and a select option dropdown. Within the table where updating is made I already have ID numbered. It has to update where the ID = <%=intRec%>.

    I have difficulty setting up the response page. This was a model code where changes should be made:

    Code:
    Dim connStr, ans, i, rs, SQL, objConn
      Dim TheString, ArrayTemp, NumberOfWords, Word
    
    Dim insert
    Dim passages
    Dim intRec
    
    
    insert = Request.Querystring("insert")
    gemetria = Request.Querystring("gemetria")
    
    connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("Kjv.mdb")
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Open connStr
    Set rs = objConn.Execute("SELECT * FROM insertanswer")
    
    If Not rs.EOF Then
        Dim gemetria
        Dim iCounter
        iCounter = 0
        Do Until rs.EOF
    
    SQL = "UPDATE insertanswer "
    SQL = SQL & "SET passages = '" & gemetria & "'"
    SQL = SQL & " WHERE id = id_" & intRec
            objConn.Execute(SQL)
    I get:
    Microsoft JET Database Engine error '80040e10'

    No value given for one or more required parameters

  • #2
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    SQL = SQL & " WHERE id = id_" & intRec

    Where is that intRec coming from? I see no assignment.
    If you wanted it from the form or querystring, you have to get it yourself.

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #3
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post
    Maybe I'm not clear with you. The ID's are set already in the table, leaving the rest of the fields with blank left to be updated. Let's say I have:
    id
    1
    2
    3
    4
    5
    .
    .
    .
    and want to update ID = 3 only. How would I write sql?

  • #4
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    No, that was clear.
    What isn't clear is where you are getting the id to update.
    You have it as a hidden field in the form, but in the action page, you either don't get it or didn't post the part where you get it.
    The sql I quoted was fine, but only if that variable exists in the first place.

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #5
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post
    The id DOES exist in the table. The rest of the fields are blank.

  • #6
    Senior Coder TheShaner's Avatar
    Join Date
    Sep 2005
    Location
    Orlando, FL
    Posts
    1,126
    Thanks
    2
    Thanked 40 Times in 40 Posts
    NikkiH is trying to tell you that the variable intRec is never assigned any values. You declare it and then that's it! You don't bother to assign the variable intRec to an ID value from the table in your database or anything.

    -Shane

  • #7
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    No, no.
    Same book, different chapter.

    SQL = "UPDATE insertanswer "
    SQL = SQL & "SET passages = '" & gemetria & "'"
    SQL = SQL & " WHERE id = id_" & intRec

    See that?
    See the variable called intRec?
    It has no value here. Neither does gemetria, coincidentally, I noticed after checking your html.
    Nothing. Nada. You never assigned it anything. Therefore, the sql getting executed looks like this:

    UPDATE insertanswer
    SET passages = ''
    WHERE id = id_

    See this html?

    <input type="hidden" name="id_<%=intRec%>"
    value="<%=rs.Fields("id").Value%>">

    <textarea name="gemetria_<%=intRec%>"
    id="gemetria_<%=intRec%>">

    Those are being posted to the server as
    id_x=idxValue
    gemetria_x=gemetria_xValue

    You are not getting your parameters properly, so the sql constructed from them is not what you wanted.

    What you wanted was this in the first page
    <input type="hidden" name="id"
    value="<%=rs.Fields("id").Value%>">

    <textarea name="gemetria"
    id="gemetria_<%=intRec%>">

    then this on the second

    id= Request.Querystring("id")
    gemetria = Request.Querystring("gemetria")

    ...

    SQL = "UPDATE insertanswer "
    SQL = SQL & "SET passages = '" & gemetria & "'"
    SQL = SQL & " WHERE id = & id
    objConn.Execute(SQL)

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #8
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post
    I found the error. I used post Method instead of Get. But after adding back parts I got error again:
    UPDATE insertanswer SET passages = '' WHERE id = ;

    Microsoft JET Database Engine error '80040e14'

    Syntax error (missing operator) in query expression 'id ='.
    Code:
    <form method="get"...
    
    <td><%=rs("id")%></br>
    <input type="hidden" name="<%=intRec%>" id="<%=intRec%>" value="<%=rs.Fields("id").Value%>">
    
    <textarea name="Keyword_<%=intRec%>" id="Keyword_<%=intRec%>"></textarea>
    
               <select name="insert" id="insert" size="1" style="width:150;">
               <option value="passages">passages</option>
               <option value="projective link">projective link</option>
               <option value="book number linked to book number">book number linked to book number</option>
               <option value="book number linked to chapter number">book number linked to chapter number</option>
               <option value="book number linked to verse number">book number linked to verse number</option>
               <option value="book number linked to number in text">book number linked to number in text</option>
               <option value="chapter number linked to book number">chapter number linked to book number </option>
               <option value="chapter number linked to book number">chapter number linked to book number</option>
               <option value="chapter number linked to chapter number">chapter number linked to chapter number</option>
               <option value="verse number linked to verse number">verse number linked to verse number</option>
               <option value="book number linked to number in text">book number linked to number in text</option>
               <option value="number linked to book">number linked to book</option>
               <option value="number linked to chapter">number linked to chapter</option>
               <option value="number linked to verse">number linked to verse</option>
               <option value="number linked to number">number linked to number</option>
               <option value="alphanumeric word">alphanumeric word</option>
               <option value="non-alphanumeric word">non-alphanumeric word</option>
               <option value="spoke of number in text">spoke of number in text</option>
               <option value="word gemetrial passage">word gemetrial passage</option>
               </select>
    </td>
    I haven't worked on the select yet. I think the problem should be in:

    Code:
    <td><%=rs("id")%></br>
    <input type="hidden" name="<%=intRec%>" id="<%=intRec%>" value="<%=rs.Fields("id").Value%>">
    
    <textarea name="Keyword_<%=intRec%>" id="Keyword_<%=intRec%>"></textarea>
    and the sql statement:
    Code:
            SQL = "UPDATE insertanswer "
            SQL = SQL & "SET passages = '" & request.querystring("Keyword_") & intRec & "'"
            SQL = SQL & " WHERE id = " & request.querystring("id_") & intRec & ";"

  • #9
    Regular Coder
    Join Date
    Mar 2005
    Posts
    735
    Thanks
    4
    Thanked 1 Time in 1 Post

    avoid looping of sql

    I have:
    Code:
            SQL = "UPDATE insertanswer "
            SQL = SQL & "SET "
    
            SQL = SQL & "passages = """ & request.querystring("Keyword_" & rs.Fields("id").value ) & """"
    
            SQL = SQL & " WHERE [id] = " & request.querystring("id_") & rs.Fields("id").value & ";"
    but because of a loop it goes on and on. I only want to update the textareas where there are things written therein.

    How should I do it?

  • #10
    Regular Coder
    Join Date
    Sep 2004
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts
    @gilgalbiblewhee,
    Ok, I think I see what is that you are trying to do. Try something like this:
    Form Code:
    Code:
    <td><%=rs("id")%></br>
    <input type="hidden" name="id_<%=rs.Fields("id").Value%>"
     value="<%=rs.Fields("id").Value%>">
    
    <textarea name="gemetria_<%=rs.Fields("id").Value%>"
    id="gemetria_<%=rs.Fields("id").Value%>">
    </textarea>
    
               <select name="insert_<%=rs.Fields("id").Value%>" id="insert_<%=rs.Fields("id").Value%>" size="1" style="width:150;">
               <option value="passages">passages</option>
               <option value="projective link">projective link</option>
               <option value="book number linked to book number">book number linked to book number</option>
               <option value="book number linked to chapter number">book number linked to chapter number</option>
               <option value="book number linked to verse number">book number linked to verse number</option>
               <option value="book number linked to number in text">book number linked to number in text</option>
               <option value="chapter number linked to book number">chapter number linked to book number </option>
               <option value="chapter number linked to book number">chapter number linked to book number</option>
               <option value="chapter number linked to chapter number">chapter number linked to chapter number</option>
               <option value="verse number linked to verse number">verse number linked to verse number</option>
               <option value="book number linked to number in text">book number linked to number in text</option>
               <option value="number linked to book">number linked to book</option>
               <option value="number linked to chapter">number linked to chapter</option>
               <option value="number linked to verse">number linked to verse</option>
               <option value="number linked to number">number linked to number</option>
               <option value="alphanumeric word">alphanumeric word</option>
               <option value="non-alphanumeric word">non-alphanumeric word</option>
               <option value="spoke of number in text">spoke of number in text</option>
               <option value="word gemetrial passage">word gemetrial passage</option>
               </select>
    </td>
    ASP Code:
    Code:
    Dim connStr, ans, i, rs, SQL, objConn
    Dim TheString, ArrayTemp, NumberOfWords, Word
    
    Dim insert
    Dim passages
    Dim intRec
    
    connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("Kjv.mdb")
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Open connStr
    Set rs = objConn.Execute("SELECT * FROM insertanswer")
    
    If Not rs.EOF Then
    	Do Until rs.EOF
    		SQL = "UPDATE insertanswer " & _
    					"SET passages = '" & Replace(Request("gemetria_" & rs.Fields("id").Value), "'", "''") & "'" & _
    					" WHERE id = " & Request("id_" & rs.Fields("id").Value)
    		objConn.Execute(SQL)


  •  

    Posting Permissions

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