Update two tables.

[hughesmi]

Ha all.

I'm try to update my two tables "Name" and "Reason" I have got the sql doing that. But I can't get Name to match the ID in the Reason table.

So I guess what I'm asking is how to I set the ID specify for the name?



<%
Dim adoCon
Dim rsAddName
Dim rsAddReason
Dim strSQL1
Dim strSQL2

Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("db1.mdb")

Set rsAddName = Server.CreateObject("ADODB.Recordset")
strSQL1 = "SELECT Name.Name FROM Name"
rsAddName.CursorType = 2
rsAddName.LockType = 2
rsAddName.Open strSQL1, adoCon


rsAddName.AddNew
rsAddName.Fields("Name") = Request.Form("Name")
rsAddName.Update
rsAddName.Close
Set rsAddName = Nothing


Set rsAddReason = Server.CreateObject("ADODB.Recordset")
strSQL2 = "SELECT Reason.Early, Reason.Late, Reason.Date FROM Reason"

rsAddReason.CursorType = 2
rsAddReason.LockType = 2
rsAddReason.Open strSQL2, adoCon
rsAddReason.AddNew
rsAddReason.Fields("Early") = Request.Form("Early")
rsAddReason.Fields("Late") = Request.Form("Late")
rsAddReason.Fields("Date") = now


rsAddReason.Update
rsAddReason.Close

Set rsAddReason = Nothing
Set adoCon = Nothing
Response.Redirect "form.asp"

%>

[ghell]

i dont really know what you are talkin about:confused:

i generally would use
Set rsAddReason = adoCon.Execute("SELECT Early, Late, Date FROM Reason")

instead of
Set rsAddReason = Server.CreateObject("ADODB.Recordset")
strSQL2 = "SELECT Reason.Early, Reason.Late, Reason.Date FROM Reason"

rsAddReason.CursorType = 2
rsAddReason.LockType = 2
rsAddReason.Open strSQL2, adoCon
but hey, maby im just lazy, this needs to execute INSERT and UPDATE etc lines separately but it seems simpler to me :rolleyes:

if you wanted to get the id of a certain name you could do
adoCon.Execute("SELECT ID FROM Name WHERE Name = 'someone'")
If NOT rsAddReason.EOF Then strName = rsAddReason("Name")



i doubt this helps but i have no clue what your question was meant to be

[hughesmi]

I never do seem to make make myself clear!

The project which I'm trying to design, is for capturing timekeeping. i.e.. the Person name, how late and how early they are nasty I know but hey, someone has to do it. My life is one big spreadsheet and I'm totally sick of it. So I feel asp and database's are the way forward - I hope!

Now, what I was trying to explain is the asp which i have done writes to two tables, Name and Reason. The problems I have, is that I only want to enter the name once and and then just update the Reason table with that person data i.e Late Early, and Date. What I cant figure out how I assign a specific ID to the name so it just updates the person name in the Reason table.

I hope that make's it clearer. :thumbsup:

[ghell]

you want the names table to have 2 fields, name and id, id should be an autonumber so that every name has a unique id ;)

anyway, to get the id of the name i would do

Function getUsersID(strUserName)
'---set a default value for if its not found
getUsersUD = 0

'---find it if possible
Set rsGetUsersID = adoCon.Execute("SELECT ID FROM Names WHERE Name = " & strUserName)
If Not rsGetUsersID.EOF then getUsersID = rsgetUsersId("ID")
End Function

with this you can virtually link the 2 tables with a relationship, as your reason table would have the fields id, reason, userID (the 1st id is again to keep the records unique, its not really used, userID is the foreign key that links to the primary key (id) in the names table)

so you could update/insert a reason where userID = getUsersID(strName), for example

strReason = "Missed the Bus"
strName = "Bob"

adoCon.Execute("INSERT into Reason (Reason, userID) VALUES ('" & strReason & "', " & getUsersID(strName) & ")")
where in the names table you may have
id=1, name=bob

edit: if each user only has 1 reason (as suggested by "update") then you could just have 1 table with an id, name and reason, instead of 2 linked tables

[hughesmi]

Okay I well and truly confused. :confused:

I did understand your last post, thanks for that. But could you help a little more by telling me how I put your suggestion into practice?

I think I have bit off more than I can chew, by undertaking this project. :eek: