Adding Multiple records 1

[jedel]

Hi All,

Well, my first try at setting up a form for adding multiple records has tripped me up at the starting line.

I'd appreciate some guidance here as I have never attempted this sort of thing before and am flying blind.

I went to the FAQ on manipulating data and have tried to modify the code to suit what I want but I've obviously done something wrong.

The scenario
I have groups of people who meet on a regular basis. The leader of each group needs to make a report online about the meeting and who attended. The first step was easy enough, a single form with some detail and it gets submitted to a database, no problems. In this process I have a session variable filled to ID the group

Once this step is completed, the form sends the leader to his list of members with a selection from a combo box (yes , No) as to whether they attended the meeting or not. The members are added in a different area which works fine.

When the leader of the group finished the entries of who attended the meeting and who did not, I wanted then to submit the form where the records from the form are all added to an attendance table. This data I can use in a number of ways.

All of the records will be entered regardless of the members attendance(so I can draw different reports).

Where I'm Stuck
Well, The page displaying the records of members came up with the first error(and I'm it will not be the only one)and it was in the generation of the recordset right at the start of the code. Here is the section of ASP code that is causing the error. and I am completely lost with it any help would be greatly appreciated to get me learning and understanding the concept of editing multiple records.

<%
Option Explicit
'Dim some variables
Dim conn, conn_str, sql_users, rs_users
conn_str = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = C:\Inetpub\vhosts\calvaryaog.org.au\httpdocs\database\knect.mdb"

'setup the connection
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open(conn_str)

'make our SQL Statement
sql_users = "SELECT * FROM KnectMbrTBL WHERE (mbr_GroupID = (Session('GpID')) ORDERBY mbr_Surname;"

'pull in the data
Set rs_users = conn.Execute(sql_users)

%>

AS I said earlier, I'm using the code described in the FAQ faq333-4502 by Tarwn.
Remember this is just the start. I'm sure We'll be progressing through this page over the next week or so.

Thanks in advance

Dean

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[gmmastros]

There are several problems with your query.

1. Look at the parenthesis. There are 3 open parenthesis but only 2 close parenthesis.

2. You need a space in Order By

3. You need to concatenate your session value to the string. The way you have this written, the value Session('GpID') is embedded within your query. What you really want is the value this session variable represents.

try this...

sql_users = "SELECT * FROM KnectMbrTBL WHERE (mbr_GroupID = (" & Session('GpID') & ")) ORDER BY mbr_Surname;"

If this works for you, and you would like me to further explain any part of my advice, just let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jedel]

George,

Thanks for your prompt reply. You're right, I should have seen that and you got me off to a great start. I did need to do a few minor adjustments my self.

1. The page would not accept "Option explicit" When I commented it out it went to the connection string.
2. The connection string part that described the Session still needed double quotes. When I put these back in the page opened with all of the records. Awesome. Have a star!! I'm going to bed now, it's 1:30am down under. Here is the code that worked

<%
'Option Explicit
'Dim some variables
Dim conn, conn_str, sql_users, rs_users
conn_str = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = C:\Inetpub\vhosts\calvaryaog.org.au\httpdocs\database\knect.mdb"

'setup the connection
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open(conn_str)

'make our SQL Statement

sql_users = "SELECT * FROM KnectMbrTBL WHERE (mbr_GroupID = (" & Session("GpID") & ")) ORDER BY mbr_Surname;"
'pull in the data
Set rs_users = conn.Execute(sql_users)

%>

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[gmmastros]

Good night.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jedel]

OK,
I'm back and we have made progress. The page where the first code loaded, works great.

The submit page as described in the FAQ thread is a different story. Here is the code where the error is appearing

<%
'Option Explicit

'first things first, Dim some variables.
'We need an array for id's, a database connection, and an SQL string
Dim arr_ids, conn, conn_str, sql_update

'Lets make a function to reduce the number of times we have to type Request.Form and Replace functions
Function CleanInput(strReqName)
CleanInput = Replace(Request.Form(strReqName),"'","''")
End Function

'now lets go ahead and put together our connection, pretend the location for the db is correct
conn_str = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Inetpub\vhosts\calvaryaog.org.au\httpdocs\database\knect.mdb"
conn.Open conn_str

'and fill our arr_ids with the ids - split on comma space because when we have multiple form
'    inputs with the same name the value comes through as a comma-space delimited string.
'    Someone somewhere decided we should be gramatically correct with multiple form inputs,
'I'm not bitter...)
arr_ids = Split(Request.Form("mbr_ID"),", ")

'now loop through each id, build the sql, and execute the sql
Dim id
For Each id in arr_ids
sql_update = "INSERT INTO attendanceTBL (Surname, Firstname, Email, Ph, Mob, Meet_Date, Attended, GroupID)" & _
                   "VALUES('" & txtSurname_ & "','" & txtFirstName_ & "','" & txtemail_ & "','" & txtPh_ & "','" & txtMob_ & "','" & Gp_Date & "','" & GpID & "','" & cbo_Attend & "')"

'And finally the WHERE portion
'sql_update = sql_update & " WHERE mbr_ID = " & Session("GpID")

'Make it so!
conn.Execute sql_update
Next

'And don't forget to clean up after yourself
conn.Close
Set conn = Nothing

'and just to be nice, lets redirect them back to display page.
'we'll pretend I specified somewhere above that it was named Display.asp
Response.Redirect "Index.asp"
%>

One question I do have is the seeing as these records will be added to a new table, do I need to identify the uniques ID from the previous table? I would think not. But then Where do I go from here?

Cheers

Dean

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[jedel]

OK,

I figured out the connection problem with the above code but now I'm getting a Data Type mismatch in criteria expression. I checked the fields and they are all of the same type. for example. There are 9 fields in the table
1. attendID - autonumber
2. Surname - text
3. Firstname - text
4. email - text
5. ph - text
6. mob - text
7. GroupID - number(Long Integer, this is one of the sort fields for the reports and is a primary key from another table)
8. Attended - text (from a yes/No select box)
9. Meet_Date - Short Date

I modified the code below so that the order of fields was to be the same as the order of input. I'm going to try another idea, but if anyone can see why or suggest what the problem is, I'm all ears.

<%
'Option Explicit

'first things first, Dim some variables.
'We need an array for id's, a database connection, and an SQL string
Dim arr_ids, conn, conn_str, sql_update

'Lets make a function to reduce the number of times we have to type Request.Form and Replace functions
Function CleanInput(strReqName)
CleanInput = Replace(Request.Form(strReqName),"'","''")
End Function

conn_str = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = C:\Inetpub\vhosts\calvaryaog.org.au\httpdocs\database\knect.mdb"

'setup the connection
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open(conn_str)

'and fill our arr_ids with the ids - split on comma space because when we have multiple form
'    inputs with the same name the value comes through as a comma-space delimited string.
'    Someone somewhere decided we should be gramatically correct with multiple form inputs,
'I'm not bitter...)
arr_ids = Split(Request.Form("mbr_ID"),", ")

'now loop through each id, build the sql, and execute the sql
Dim id
For Each id in arr_ids
sql_update = "INSERT INTO attendanceTBL (Surname, Firstname, Email, Ph, Mob, Meet_Date, Attended, GroupID)" & _
                   "VALUES('" & txtSurname & "','" & txtFirstName & "','" & txtemail & "','" & txtPh & "','" & txtMob & "','" & Gp_Date & "','" & cbo_Attend & "','" &  GpID & "')"

'And finally the WHERE portion
'sql_update = sql_update & " WHERE mbr_ID = " & Session("GpID")

'Make it so!
conn.Execute sql_update
Next

'And don't forget to clean up after yourself
conn.Close
Set conn = Nothing

'and just to be nice, lets redirect them back to display page.
'we'll pretend I specified somewhere above that it was named Display.asp
Response.Redirect "Index.asp"
%>

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[shaddow]

You could try to use this code and see how SQL with error looks like. Also some tips in there.

Dim arr_ids, conn, conn_str, sql_update

Function CleanInput(strReqName)
    CleanInput = Replace(Request.Form(strReqName),"'","''")
End Function

Function CleanInput1(strReqName)
    CleanInput = Replace(strReqName,"'","''")
End Function

conn_str = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = C:\Inetpub\vhosts\calvaryaog.org.au\httpdocs\database\knect.mdb"

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open(conn_str)

'You dont need to split the variable when you have multiple form inputs with the same name
'because Request.Form("mbr_ID") comes as comma delimited strings but Request.Form("mbr_ID") is an colection you can use it instead
'if you get an value in the field with a comma it will mess up your ", " split.
'for example we have 3 inputs named the same with values "v1, v11"  "v2"  and "v3, v31"
'then Request.Form("input_name") will print "v1, v11, v2, v3, v31", then the split will see 5 variables
'but Request.Form("input_name").Count will be just 3, so you lose some data

'now loop through each id, build the sql, and execute the sql
Dim id
For Each id in Request.Form("mbr_ID") 'now id contains input value
sql_update = "INSERT INTO attendanceTBL (Surname, Firstname, Email, Ph, Mob, Meet_Date, Attended, GroupID) " & _
             "VALUES('" & CleanInput1(txtSurname) & "','" & CleanInput1(txtFirstName) & _"','" & _
             CleanInput1(txtemail) & "','" & CleanInput1(txtPh) & "','" & CleanInput1(txtMob) & "','" & _
             CleanInput1(Gp_Date) & "','" & CleanInput1(cbo_Attend) & "'," & GpID & ")"
'You need to be shure date is in mm/dd/yyyy format, or whatever region setting you use.
'Numbers usualy doesnt need quotes, so lets take those out
' Data Type mismatch in criteria expression - most likely from your date time field or the number field
'You need to test this new code, also try to display the sql_update with the problem it will help a lot.

'Response.Write sql_update

'And finally the WHERE portion
'sql_update = sql_update & " WHERE mbr_ID = " & Session("GpID")

conn.Execute sql_update
Next
...

________
George, M
Searches(faq333-4906),Carts(faq333-4911)

 

[jedel]

George,

Thanks for the assistance. I think we progressed a little further on this one. The SQL Code you made only had one small syntax error that I was able to fix.

When I ran the code through the pages, everything worked fine and I ended up on the second page, however no data went into the database. Now without an error to chase, I'm really stumped... I tried using the "cleanInput" function rather than the "CleanInput1" function, same result.

Could it mean that the query is not picking up the data from the previous form?

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."