OK,



The problem I'm having is that I'm trying to add a record into a table for 

every checkbox the user selects.  I have a webpage displaying all of the 

videos in our department, and every video has a checkbox next to it.  The 

user can select as many videos as he/she wants, and then the user clicks 

the Submit button at the bottom of the page.  



I'm trying to write the code to where the user's videos and the checkout 

date can be written to a record for every video the user selects.  The 

table does not have a key.  I'm just trying to do a log which videos are 

being checked-out when.



Here's the code, and I'm borrowing heavily from the Active Server Pages 

3.0 book by Wrox.  The code that I'm basing my code on is on page 57 in 

the topmost gray box.





Dim rst, dbs, LastNameID, SSNID

Set dbs = Server.CreateObject("ADODB.Connection")

dbs.Open "videoPage"

Set rst = Server.CreateObject("ADODB.Recordset")

rst.CursorType = 0

rst.LockType = 2

rst.ActiveConnection = dbs

rst.Source = "SELECT * FROM 

[F:\Inetpub\cte\GregTest\DatabaseWork\processes.mdb].People"

rst.Open

LastNameID = rst("Last_Name")

SSNID = rst("SSN")

rst.Close

strSQLGetCorrectTableFindRecords = "SELECT * FROM 

[F:\Inetpub\cte\GregTest\DatabaseWork\processes.mdb]." & LastNameID 

& "Video WHERE SSN LIKE " & SSNID



dbs.Execute strSQLGetCorrectTableFindRecords



Dim objItem

rst.Open strSQLGetCorrectTableFindRecords

If rst.BOF Then

	If Request.Form(objItem).Count > 1 Then

	For intLoop = 1 To Request.Form(objItem).Count			

		rst.Addnew

			rst("SSN") = SSNID

			rst("Video_Name") = Request.Form(objItem)(intLoop)

		rst.Update

		rst.Movenext

	Next

		rst.Close

Else

		rst.Addnew

			rst("SSN") = SSNID

			rst("Video_Name") = Request.Form(intLoop)

		rst.Update

		rst.Movenext

		rst.Close

	End If

End If



Set rst = Nothing

Set dbs = Nothing



If a better explanation of what I'm trying to accomplish would help with 

the responses, don't hesitate to ask!  Thanks in advance for the help!



Greg Torrence



PS:  If someone could help me figure out how to put the server's date into 

the table, I'd appreciate that, too!

Set dbs = Server.CreateObject("ADODB.Connection")

dbs.Open "videoPage"



If I'm not mistaken you should use a connectionstring when your opening your

connection. The connectionstring depends on your ODBC-driver or something

like that but you can read more about it in Active Server Pages 3.0 I

suppose.



dbs.Open connectionstring



That would already be a nice start...









----- Original Message -----

From: "Greg Torrence" <gtt1803@u...>

To: "Access ASP" <access_asp@p...>

Sent: Monday, November 05, 2001 6:15 PM

Subject: [access_asp] Loop iteration





> OK,

>

> The problem I'm having is that I'm trying to add a record into a table for

> every checkbox the user selects.  I have a webpage displaying all of the

> videos in our department, and every video has a checkbox next to it.  The

> user can select as many videos as he/she wants, and then the user clicks

> the Submit button at the bottom of the page.

>

> I'm trying to write the code to where the user's videos and the checkout

> date can be written to a record for every video the user selects.  The

> table does not have a key.  I'm just trying to do a log which videos are

> being checked-out when.

>

> Here's the code, and I'm borrowing heavily from the Active Server Pages

> 3.0 book by Wrox.  The code that I'm basing my code on is on page 57 in

> the topmost gray box.

>

>

> Dim rst, dbs, LastNameID, SSNID

> Set dbs = Server.CreateObject("ADODB.Connection")

> dbs.Open "videoPage"

> Set rst = Server.CreateObject("ADODB.Recordset")

> rst.CursorType = 0

> rst.LockType = 2

> rst.ActiveConnection = dbs

> rst.Source = "SELECT * FROM

> [F:\Inetpub\cte\GregTest\DatabaseWork\processes.mdb].People"

> rst.Open

> LastNameID = rst("Last_Name")

> SSNID = rst("SSN")

> rst.Close

> strSQLGetCorrectTableFindRecords = "SELECT * FROM

> [F:\Inetpub\cte\GregTest\DatabaseWork\processes.mdb]." & LastNameID

> & "Video WHERE SSN LIKE " & SSNID

>

> dbs.Execute strSQLGetCorrectTableFindRecords

>

> Dim objItem

> rst.Open strSQLGetCorrectTableFindRecords

> If rst.BOF Then

> If Request.Form(objItem).Count > 1 Then

> For intLoop = 1 To Request.Form(objItem).Count

> rst.Addnew

> rst("SSN") = SSNID

> rst("Video_Name") = Request.Form(objItem)(intLoop)

> rst.Update

> rst.Movenext

> Next

> rst.Close

> Else

> rst.Addnew

> rst("SSN") = SSNID

> rst("Video_Name") = Request.Form(intLoop)

> rst.Update

> rst.Movenext

> rst.Close

> End If

> End If

>

> Set rst = Nothing

> Set dbs = Nothing

>

> If a better explanation of what I'm trying to accomplish would help with

> the responses, don't hesitate to ask!  Thanks in advance for the help!

>

> Greg Torrence

>

> PS:  If someone could help me figure out how to put the server's date into

> the table, I'd appreciate that, too!

>

>




>

>

Greg,



A few preliminary points:



a) Naming schema for variables - have a naming schema for your variables.

Prefixing the variable with a type makes your code much more readable.



b) Indenting - indent your code! (apologies if it's just the mail system

that has stripped the code out)



c) Read up on designing databases. You *should* (dare I say, "must") have a

primary key in every table you create...



d) In response to your last question, Access has an inbuilt function called

Date() - you can use that in an SQL statement to insert the current

date/time.



e) Code:



<%

If Request.Form("chkVideoID").Count > 0 then



    Set objConn = Server.CreateObject("ADODB.Connection")

    objConn.Open strDBConnString



    For i = 0 to Request.Form("chkVideoID").Count



        ' Validate that the value is a legitimate

        ' number here!!! I'll leave that for you to do



        strSQL = _

            "INSERT INTO table1 " & _

            "(field1, field2) " & _

            "VALUES(" & _

            "(" & Request.Form("chkVideoID")(i) & _

            ", Date()" & _

            ")"



        objConn.Execute strSQL,,adCmdText+adExecuteNoRecords



    Next



    objConn.Close

    Set objConn = Nothing



End If

%>



HTH



Cheers

Ken









~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Greg Torrence" <gtt1803@u...>

Subject: [access_asp] Loop iteration





: The problem I'm having is that I'm trying to add a record into a table for

: every checkbox the user selects.  I have a webpage displaying all of the

: videos in our department, and every video has a checkbox next to it.  The

: user can select as many videos as he/she wants, and then the user clicks

: the Submit button at the bottom of the page.

:

: I'm trying to write the code to where the user's videos and the checkout

: date can be written to a record for every video the user selects.  The

: table does not have a key.  I'm just trying to do a log which videos are

: being checked-out when.

:

: Here's the code, and I'm borrowing heavily from the Active Server Pages

: 3.0 book by Wrox.  The code that I'm basing my code on is on page 57 in

: the topmost gray box.

:

:

: Dim rst, dbs, LastNameID, SSNID

: Set dbs = Server.CreateObject("ADODB.Connection")

: dbs.Open "videoPage"

: Set rst = Server.CreateObject("ADODB.Recordset")

: rst.CursorType = 0

: rst.LockType = 2

: rst.ActiveConnection = dbs

: rst.Source = "SELECT * FROM

: [F:\Inetpub\cte\GregTest\DatabaseWork\processes.mdb].People"

: rst.Open

: LastNameID = rst("Last_Name")

: SSNID = rst("SSN")

: rst.Close

: strSQLGetCorrectTableFindRecords = "SELECT * FROM

: [F:\Inetpub\cte\GregTest\DatabaseWork\processes.mdb]." & LastNameID

: & "Video WHERE SSN LIKE " & SSNID

:

: dbs.Execute strSQLGetCorrectTableFindRecords

:

: Dim objItem

: rst.Open strSQLGetCorrectTableFindRecords

: If rst.BOF Then

: If Request.Form(objItem).Count > 1 Then

: For intLoop = 1 To Request.Form(objItem).Count

: rst.Addnew

: rst("SSN") = SSNID

: rst("Video_Name") = Request.Form(objItem)(intLoop)

: rst.Update

: rst.Movenext

: Next

: rst.Close

: Else

: rst.Addnew

: rst("SSN") = SSNID

: rst("Video_Name") = Request.Form(intLoop)

: rst.Update

: rst.Movenext

: rst.Close

: End If

: End If

:

: Set rst = Nothing

: Set dbs = Nothing

:

: If a better explanation of what I'm trying to accomplish would help with

: the responses, don't hesitate to ask!  Thanks in advance for the help!

:

: Greg Torrence

:

: PS:  If someone could help me figure out how to put the server's date into

: the table, I'd appreciate that, too!