Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Jul 2002
    Location
    UK
    Posts
    302
    Thanks
    16
    Thanked 0 Times in 0 Posts

    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.
    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
    Kind regards,
    Mike Hughes

  • #2
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    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.

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #3
    Regular Coder
    Join Date
    Jul 2002
    Location
    UK
    Posts
    302
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Hi thanks for your reply. The data types are correct. Have you any other suggestions?
    Kind regards,
    Mike Hughes

  • #4
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    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?

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •