ASP dynamic insert issues

Hi
i am trying to create a dynamic form
that inserts into a ms access database
using hiddenfields and check boxes

here is my code

-----------------------------------------------------------------
If (CStr(Request("MM_insert")) = "form1") Then
            
            insertcomp=0
            thisinsert=0            
            'set to equal the number of compulsary units
            insertcomp = (courseOpt.Fields.Item("NoUnits").Value)
            thisuser = Session("MM_Sid")
            
            'start the loop to do insert
            While Not (insertcomp=thisinsert)
                        
                              'to make it easier to read
                              fvalue = request.form("unit"&thisinsert)      
                              'set the query
                              MM_editQuery = "INSERT INTO StudentUnit(StudentID,UnitID) VALUES ("&thisuser&","&fvalue&")"
                              response.Write(MM_editQuery) & vbCrLf
                              ' execute the insert
                              Set MM_editCmd = Server.CreateObject("ADODB.Command")
                              MM_editCmd.ActiveConnection = MM_editConnection
                              MM_editCmd.CommandText = MM_editQuery
                              MM_editCmd.Execute
                              MM_editCmd.ActiveConnection.Close
                              
            'increment the loop
            thisinsert=thisinsert+1  
            'end insert loop
      Wend

'now reset and do exactly the same for the optionalunits
            thisinsert=0
            'set to equal the number of optinal units
            insertcomp = courseOpt("OptUnits")
            
            'lets make sure a n optional unit exists before we try to insert
            if insertcomp > 0 then
            
            'start the loop to do something insert
                  While Not (insertcomp=thisinsert)
                              'to make it easier to read
                              
                              fvalue = request.form("optunit"&thisinsert)
                              'set the query
                              MM2_editQuery = "INSERT INTO StudentUnit(StudentID,UnitID) VALUES ("&thisuser&","&fvalue&")"
                              ' execute the insert
                              Set MM2_editCmd = Server.CreateObject("ADODB.Command")
                              MM2_editCmd.ActiveConnection = MM2_editConnection
                              MM2_editCmd.CommandText = MM2_editQuery
                              MM2_editCmd.Execute
                              MM2_editCmd.ActiveConnection.Close
                              response.Write(MM2_editQuery) & vbCrLf
                        
                  'increment the loop
                  thisinsert=thisinsert+1  
                  'end insert loop
                  Wend
            end if


      'query successful now redirect

      Response.Redirect("step5.asp")
 

End If

%>
------------------------------------------------------------

this is my form........

<form name="form1" method="POST" action="<%=MM_editAction%>">      
        <table width="80%" border="0" cellpadding="0" cellspacing="0">
 
         <% While ((Repeat1__numRows <> 0) AND (NOT courseUnits.EOF)) %>
            <%
            'nested query to show only unit details that relate to the course unit record
            Set unitDetails = Server.CreateObject("ADODB.Recordset")
            unitDetails.ActiveConnection = MM_btec_STRING
            unitDetails.Source = "SELECT *  FROM Unit where UnitID = "&courseUnits("UnitID")&""
            unitDetails.CursorType = 0
            unitDetails.CursorLocation = 2
            unitDetails.LockType = 1
            unitDetails.Open()
            %>      
        <tr>
          <td><%=(unitDetails.Fields.Item("Name").Value)%>
            <input type="hidden" name="unit<%=compCounter%>" value="<%=(unitDetails.Fields.Item("UnitID").Value)%>">--<%=(unitDetails.Fields.Item("UnitID").Value)%>--unit<%=compCounter%></td>
        </tr>
        <%
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  courseUnits.MoveNext()

      compCounter=compCounter+1
Wend
%>
      </table>
      <p>&nbsp;</p>
        
        <strong>Please select <%=(courseOpt.Fields.Item("OptUnits").Value)%> Units from the following options....
        </p>
      <br>
      <br>
</strong>  
        <table width="80%" border="0" cellpadding="0" cellspacing="0">
 
        <% While ((Repeat2__numRows <> 0) AND (NOT optCourseUnits.EOF)) %>
            <%
            'nested query to show only unit details that relate to the course unit record
            Set optUnitDetails = Server.CreateObject("ADODB.Recordset")
            optUnitDetails.ActiveConnection = MM_btec_STRING
            optUnitDetails.Source = "SELECT *  FROM Unit where UnitID = "&optCourseUnits("UnitID")&""
            optUnitDetails.CursorType = 0
            optUnitDetails.CursorLocation = 2
            optUnitDetails.LockType = 1
            optUnitDetails.Open()
            %>
            
            
        <tr>
          <td><input type="checkbox" name="optunit<%=optCounter%>" value="<%=(optCourseUnits.Fields.Item("UnitID").Value)%>">--<%=(optUnitDetails.Fields.Item("UnitID").Value)%>--optunit<%=optCounter%>
         </td>
          <td><%=(optUnitDetails.Fields.Item("Name").Value)%></td>
        </tr>
        <%
  Repeat2__index=Repeat2__index+1
  Repeat2__numRows=Repeat2__numRows-1
  optCourseUnits.MoveNext()
    optCounter=optCounter+1
Wend
%>
      </table>
                <p align="center">
                  <input type="submit" name="Submit" value="Next &gt;&gt;">
        </p>
     
              <input type="hidden" name="MM_insert" value="form1">
      </form>

-------------------------------------------------------------------------------------------
the insert is needless to say not working
as you may have noticed i have response.write the query and it reads.....


INSERT INTO StudentUnit(StudentID,UnitID) VALUES (151,1) INSERT INTO StudentUnit(StudentID,UnitID) VALUES (151,2) INSERT INTO StudentUnit(StudentID,UnitID) VALUES (151,3) INSERT INTO StudentUnit(StudentID,UnitID) VALUES (151,7) INSERT INTO StudentUnit(StudentID,UnitID) VALUES (151,8) INSERT INTO StudentUnit(StudentID,UnitID) VALUES (151,)

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

[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

/registration/step4.asp, line 119

---------------------------------------------------------------------------------------------

which is this line.... MM_editCmd.ActiveConnection.Close

though i must say in some cases when the user selects a specific combination the second query should not execute
and it doesnt and the 1st query works perfectly...
so .....
any ideas???

thanks
liseperuAsked:
Who is Participating?
 
fritz_the_blankCommented:
Here is a good troubleshooting bit to help you out--place it at the top of the processing page:

for each objField in Request.Form
  repsonse.write(objField & ": " & Request.Form(objField) & "<br>")
next
response.end

That will show you exactly what is happening.

And don't forget, checkboxes only pass values when they are checked....

FtB
0
 
fritz_the_blankCommented:
The problem, of course is that the sql insert suffers from an error:

INSERT INTO StudentUnit(StudentID,UnitID) VALUES (151,1) INSERT INTO StudentUnit(StudentID,UnitID) VALUES (151,2) INSERT INTO StudentUnit(StudentID,UnitID) VALUES (151,3) INSERT INTO StudentUnit(StudentID,UnitID) VALUES (151,7) INSERT INTO StudentUnit(StudentID,UnitID) VALUES (151,8) INSERT INTO StudentUnit(StudentID,UnitID) VALUES (151,)

You only have one parameter for the values in the last part, and you should have two. Also, you should separate each of these statements with a semicolon if you are going to execute them at once.

Now, if you can figure out why you are not getting the second parameter for the last insert, you will be set.

FtB
0
 
liseperuAuthor Commented:
hi
that is what i thought
but consider that there are two inserts

when both inserts execute it fails

when only the first insert executes
it is successfull

i spent a long time trying to work this out...
and got nowhere

:(
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
fritz_the_blankCommented:
Part of the problem is that you are using DW, and it is not always easy to follow what the code is doing...

FtB
0
 
liseperuAuthor Commented:
hmmmm did the wizardy code give me away?

0
 
fritz_the_blankCommented:
Oh, yeah! No sane person would write code that way...

DW works fine if you do basic things that it understands. Once you try to go beyond that, then you are in deep doggy doo doo. That is why I gave up FrontPage and DW and learned how to code by hand.

FtB
0
 
fritz_the_blankCommented:
Here is an example of how I do this sort of thing:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE> New Document </TITLE>
<%
if request.form("btnSubmit") = "Update" then
      'create your connection object, let's say objConnection
      'create your recordset object, let's say objRS
      strSQL = "SELECT strFirstName, strLastName FROM tblYourTable WHERE 1=0"
      objRS.Open strSQL,objConnection,3,3
      for i =  1 to Request.form("intRecords")
            objRS.AddNew()
                  objRS("strFirstName") = Request.Form("strFirstName_" & i)
                  objRS("strLastName") = Request.Form("strLastName_" & i)
            objRS.Update()
      next
      objRS.Close
      set objRS = Nothing
      objConnection.Close
      set objConnection = Nothing
end if
%>
</HEAD>

<BODY>
<%
for i = 1 to 10
%>
      First Name:<input type="text" name="strFirstName_<%=i%>"><br />
      Last Name:<input type="text" name="strLastName_<%=i%>"><br />
<%
next
%>
<input type="hidden" name="intRecords" value="<%=i%>">
<input type="submit" name="btnSubmit" value="Update">
</BODY>
</HTML>


FtB
0
 
MPKRCommented:
Just an idea: Maybe it is cause of:

<checkbox name="optunit<%=optCounter%>" >

optcounter starts off without a Dim
So it is NULL when the first option field is created. Then the posted page could not be able to read the posted value cause it does not rebuild the searched fieldname correctly.

Please try to find out what value is missing. Is it the one from the first checkbox?
0
 
liseperuAuthor Commented:
hi
thanks mpkr
there is a dim
but i didnt show it in this code
thanks anyway!
0
 
MPKRCommented:
Ok, but is it also set to a value before the point? A DIM might not be enough to set the variable to a value.
0
 
liseperuAuthor Commented:
dim optCounter
optCounter = 1
dim compCounter
compCounter = 1

is that the correct syntax or does it have to be in quotes?
="1"
0
 
liseperuAuthor Commented:
the actually code the variables are set to zero
0
 
MPKRCommented:
No, that's ok.

But: You start out with value of 0 in the @posted page.

So The first field is not found!


Try starting with optcounter = 0.
0
 
liseperuAuthor Commented:
yeh that what i meant sorry i posted the wrong code
everything is set to 0 and not 1
0
 
MPKRCommented:
Hmmm then I don't know.

Try this :

fvalue = request.form("optunit"&thisinsert)
Response.write "<br>" & thisinsert & " = " fvalue

and try to find out what value is missing.
0
 
liseperuAuthor Commented:
good idea...
will let you know
0
 
liseperuAuthor Commented:
it is the last value

unit1 = 2
INSERT INTO StudentUnit(StudentID,UnitID) VALUES (156,2)
unit2 = 3
INSERT INTO StudentUnit(StudentID,UnitID) VALUES (156,3)
unit3 = 4
INSERT INTO StudentUnit(StudentID,UnitID) VALUES (156,4)
unit4 = 5
INSERT INTO StudentUnit(StudentID,UnitID) VALUES (156,5)
unit5 = 6
INSERT INTO StudentUnit(StudentID,UnitID) VALUES (156,6)
unit6 = 7
INSERT INTO StudentUnit(StudentID,UnitID) VALUES (156,7)
unit7 = 8
INSERT INTO StudentUnit(StudentID,UnitID) VALUES (156,8)
unit8 = 9
INSERT INTO StudentUnit(StudentID,UnitID) VALUES (156,9)
unit9 =
INSERT INTO StudentUnit(StudentID,UnitID) VALUES (156,)

infact now i check that last insert should even execute
0
 
MPKRCommented:
unit1 = 2
unit2 = 3

Seems there is value 1 missing and all values have moved + 1 step forward.
I am sure there is a bug while Form generating.

Take a look at your HTML source code of the Form. Look at the index the enummerated Form fields start and end with. Compare that to the expected index in dthe posted asp page. Then you will find the bug.
0
 
liseperuAuthor Commented:
thanks i managed to work this out using the last bit of advice from fritz.....
but i have adwarded points for everyone that gave me soemthing helpful

thanks again
0
 
fritz_the_blankCommented:
Glad to have helped,

FtB
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.