Update Two Tables

Hi. Can some please look at my code. I am trying to update two tables in my access databse.

I am getting an errror. I have made line 92 bold.


Error Type:
Provider (0x80020005)
Type mismatch.
/FootBallDB/update_player_info.asp, line 103


My code.

'Read in the record number to be updated
lngRecordNo = CLng(Request.Form("EditID"))

'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("footballdb.mdb")


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

'Initialise the strSQL variable with an SQL statement to query the database
'strSQL= "SELECT PlayerName.PlayerID, PlayerName.PlayerName, PlayerName.DateAdded, PlayerName.InfoAdded, PlayerInfo.InfoID, PlayerInfo.ID, PlayerInfo.Notes, PlayerInfo.Date_Signed, PlayerInfo.Last_Club, PlayerInfo.Transfer_Fee, PlayerInfo.Signed_By FROM PlayerName LEFT JOIN PlayerInfo ON PlayerName.PlayerID = PlayerInfo.ID WHERE PlayerName.PlayerID=" & lngRecordNo
strSQL = "SELECT * FROM PlayerInfo WHERE ID=" & lngRecordNo

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

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

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

'Update the record in the recordset

rsUpdatePlayerInfo.Fields("Date_Signed") = Request.Form("Date_Signed")
rsUpdatePlayerInfo.Fields("Last_Club") = PCase(Request.Form("Last_Club"))
rsUpdatePlayerInfo.Fields("Transfer_Fee") = Request.Form("Transfer_Fee")
rsUpdatePlayerInfo.Fields("Signed_By")= PCase(Request.Form("Signed_By"))
rsUpdatePlayerInfo.Fields("Notes") = Request.Form("Notes")

'rsUpdatePlayerInfo.Close 'close this so it can be reused

'Update Player Name in second table.

strSQL = "SELECT * FROM PlayerName WHERE PlayerID = " & Request.QueryString("EditID") & ";"
rsUpdatePlayerInfo.Open strSQL, adoCon, 2, 3
rsUpdatePlayerInfo.Fields("PlayerName") = PCase(Request.Form("PlayerName"))

rsUpdatePlayerInfo.Update

'Reset server objects
'rsUpdatePlayerInfo.Close
Set rsUpdatePlayerInfo = Nothing
Set adoCon = Nothing

Form fields are strings.
I bet one or more of those fields in your DB is a number or currency or a date.
The type mismatch is due to not casting the string to the proper datatype.

Hi thanks for your reply. The data types are correct. Have you any other suggestions?

rsUpdatePlayerInfo.Fields("Transfer_Fee") = Request.Form("Transfer_Fee")

Fee is a text field in the database, not a number, int, or currency?

If so, what's on line 103?