PDA

View Full Version : Updating two access tables

hughesmi
06-08-2005, 06:24 PM
Hi all.

I need help with updating two access db tables.

I need to update the "Data" table with the info from the form, but I also need the Voted feild on the "Name" table to say they have voted.


Here is my code so far.



<%

'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsAddComments 'Holds the recordset for the new record to be added to the database
Dim strSQL 'Holds the SQL query for the database

'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("database.mdb")


'Create an ADO recordset object
Set rsAddComments = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database

strSQL = "SELECT * FROM Data"

'Set the cursor type we are using so we can navigate through the recordset
rsAddComments.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated
rsAddComments.LockType = 3

'Open the tblComments table using the SQL query held in the strSQL varaiable
rsAddComments.Open strSQL, adoCon

'Tell the recordset we are adding a new record to it
rsAddComments.AddNew

'Add a new record to the recordset
rsAddComments.Fields("NameID") = Request.Form("NameID")
rsAddComments.Fields("Ch1") = Request.Form("Ch1")
rsAddComments.Fields("Ch2") = Request.Form("Ch2")
rsAddComments.Fields("Ch3") = Request.Form("Ch3")
rsAddComments.Fields("Date_Entered") = now


rsAddComments.Fields("Voted") = True

'Write the updated recordset to the database
rsAddComments.Update

'Reset server objects
rsAddComments.Close
Set rsAddComments = Nothing
Set adoCon = Nothing
%>


<p>&nbsp;</p>
<font face="Tahoma">&nbsp;<%
'Redirect to the guestbook.asp page
'Response.Redirect "guestbook.asp"
%></font>
<p align="center"><font face="Tahoma"><b>Thank you.</b></font></p>
miranda
06-08-2005, 08:20 PM
what is the unique identifier in the name table? I have provided 2 ways to do this pick one of them. One uses the ADO recordset object and the other doesn't. The later being faster and uses less resources. Also add the code hilighted in red.


<%

'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsAddComments 'Holds the recordset for the new record to be added to the database
Dim strSQL 'Holds the SQL query for the database

'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("database.mdb")


'Create an ADO recordset object
Set rsAddComments = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database

strSQL = "SELECT * FROM Data WHERE 1=2"
'the WHERE statement will return no records and allow you to add a new record

'Set the cursor type we are using so we can navigate through the recordset
rsAddComments.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated
rsAddComments.LockType = 3

'Open the tblComments table using the SQL query held in the strSQL varaiable
rsAddComments.Open strSQL, adoCon

'Tell the recordset we are adding a new record to it
rsAddComments.AddNew

'Add a new record to the recordset
rsAddComments.Fields("NameID") = Request.Form("NameID")
rsAddComments.Fields("Ch1") = Request.Form("Ch1")
rsAddComments.Fields("Ch2") = Request.Form("Ch2")
rsAddComments.Fields("Ch3") = Request.Form("Ch3")
rsAddComments.Fields("Date_Entered") = now

'Write the updated recordset to the database
rsAddComments.Update

'Done using ADO
rsAddComments.Close 'close this so it can be reused
strSQL = "SELECT voted FROM Name WHERE nameID = '" & Request.Form("NameID") & "';"
rsAddComments.Open strSQL, adoCon, 2, 3
rsAddComments.Fields("Voted") = True
rsAddComments.Update

'done with no recordset object
strSQL = "UPDATE Name SET voted = True WHERE nameID = '" & Request.Form("NameID") & "';"
adoCon.Execute(strSQL)

'Reset server objects
adoCon.Close
rsAddComments.Close
Set rsAddComments = Nothing
Set adoCon = Nothing
%>


<p>&nbsp;</p>
<font face="Tahoma">&nbsp;<%
'Redirect to the guestbook.asp page
'Response.Redirect "guestbook.asp"
%></font>
'if the redirect is used, none of the HTML on the page will ever be displayed.
'Server side code is done before any HTML is rendered to the screen.
'Best to use a meta redirect tag or use a javascript redirect here.
'This will allow you to show html on the page and then move automatically

<p align="center"><font face="Tahoma"><b>Thank you.</b></font></p>
hughesmi
06-08-2005, 08:43 PM
Thank you for this. I will opt for ADO example I understand it more. However, I'm still getting errors. I have highlited line 48 in orgnge. Could you help a little more? I did check for obvious things, but I see nothing obvious.


Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
add.asp, line 48




My update code.


<%

'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsAddComments 'Holds the recordset for the new record to be added to the database
Dim strSQL 'Holds the SQL query for the database

'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("database.mdb")


'Create an ADO recordset object
Set rsAddComments = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database

strSQL = "SELECT * FROM Data WHERE 1=2"
'the WHERE statement will return no records and allow you to add a new record

'Set the cursor type we are using so we can navigate through the recordset
rsAddComments.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated
rsAddComments.LockType = 3

'Open the tblComments table using the SQL query held in the strSQL varaiable
rsAddComments.Open strSQL, adoCon

'Tell the recordset we are adding a new record to it
rsAddComments.AddNew

'Add a new record to the recordset
rsAddComments.Fields("NameID") = Request.Form("NameID")
rsAddComments.Fields("Ch1") = Request.Form("Ch1")
rsAddComments.Fields("Ch2") = Request.Form("Ch2")
rsAddComments.Fields("Ch3") = Request.Form("Ch3")
rsAddComments.Fields("Date_Entered") = now

'Write the updated recordset to the database
rsAddComments.Update

'Done using ADO
rsAddComments.Close 'close this so it can be reused
strSQL = "SELECT Voted FROM Name WHERE NameID = '" & Request.Form("NameID") & "';"
rsAddComments.Open strSQL, adoCon, 2, 3
rsAddComments.Fields("Voted") = True
rsAddComments.Update

'Reset server objects
adoCon.Close
rsAddComments.Close
Set rsAddComments = Nothing
Set adoCon = Nothing
%>


<p>&nbsp;</p>
<font face="Tahoma">&nbsp;<%
'Redirect to the guestbook.asp page
'Response.Redirect "guestbook.asp"
%></font>


<p align="center"><font face="Tahoma"><b>Thank you.</b></font></p>
miranda
06-08-2005, 09:20 PM
The error points to this sql statement.
strSQL = "SELECT Voted FROM Name WHERE NameID = '" & Request.Form("NameID") & "';"

Is NameID the name of the field? is it of the datatype of text? You may have to alter that sql statement to match your database tables design.
hughesmi
06-08-2005, 09:35 PM
See this is where I am screwing it up.

If could ask you to look at my db stuctire i have place a copy on my sever the address is

http://www.mikeyhughes.me.uk/code/database.mdb

Then you will understand me more.



All I want is to is add to 5 fields under the "Data" table and then update the Voted field in the "Name" table.
miranda
06-08-2005, 10:02 PM
you need to attach it or just list the tables, the columns, and the datatypes of the columns
hughesmi
06-08-2005, 10:05 PM
her you go. it'a attached,
miranda
06-08-2005, 11:05 PM
ok do DataID and NameID share a relationship? These are both datatype long integer fields.

so any select statement involving either one does not have the single quotes around it.

so now your 2nd sql statement will read
strSQL = "SELECT Voted FROM Name WHERE NameID = " & Request.Form("NameID") & ";"
hughesmi
06-09-2005, 11:11 PM
Nice. I have it working. Thanks for your help

:thumbsup:
miranda
06-10-2005, 01:53 AM
You are welcome. I am glad that I could help.