[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ASP dynamic insert issues

Posted on 2005-04-05
20
Medium Priority
?
239 Views
Last Modified: 2006-11-18
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
0
Comment
Question by:liseperu
  • 9
  • 6
  • 5
20 Comments
 
LVL 46

Assisted Solution

by:fritz_the_blank
fritz_the_blank earned 1400 total points
ID: 13712785
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
 

Author Comment

by:liseperu
ID: 13712800
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
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 13712808
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:liseperu
ID: 13712819
hmmmm did the wizardy code give me away?

0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 13712830
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
 
LVL 46

Assisted Solution

by:fritz_the_blank
fritz_the_blank earned 1400 total points
ID: 13712873
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
 
LVL 4

Expert Comment

by:MPKR
ID: 13714696
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
 

Author Comment

by:liseperu
ID: 13714832
hi
thanks mpkr
there is a dim
but i didnt show it in this code
thanks anyway!
0
 
LVL 4

Expert Comment

by:MPKR
ID: 13714876
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
 

Author Comment

by:liseperu
ID: 13714885
dim optCounter
optCounter = 1
dim compCounter
compCounter = 1

is that the correct syntax or does it have to be in quotes?
="1"
0
 

Author Comment

by:liseperu
ID: 13714889
the actually code the variables are set to zero
0
 
LVL 4

Assisted Solution

by:MPKR
MPKR earned 600 total points
ID: 13714989
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
 

Author Comment

by:liseperu
ID: 13715253
yeh that what i meant sorry i posted the wrong code
everything is set to 0 and not 1
0
 
LVL 4

Assisted Solution

by:MPKR
MPKR earned 600 total points
ID: 13715436
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
 

Author Comment

by:liseperu
ID: 13715521
good idea...
will let you know
0
 

Author Comment

by:liseperu
ID: 13715547
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
 
LVL 4

Assisted Solution

by:MPKR
MPKR earned 600 total points
ID: 13715725
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
 
LVL 46

Accepted Solution

by:
fritz_the_blank earned 1400 total points
ID: 13720712
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
 

Author Comment

by:liseperu
ID: 14982763
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
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 14986371
Glad to have helped,

FtB
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question