SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot
    Join Date
    May 2002
    Location
    USA
    Posts
    133
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ASP and Access Database question

    I have looked and searched and from what I see I am doing everything right, however, when I tried to write to this database I get a 500 Internal Server Error.

    This is nothing fancy. It is a simple (should be) database with a simple webform that captures four pieces of info.

    TicketNumber (Primary Key)
    BriefDescription
    LastUpdated
    Updatedby

    This is in an Access database called MasterTickets.mdb in a table called tblMasterTickets.

    My ASP code looks like this:

    <%
    'Dimension variables
    Dim adoCon 'Holds the Database Connection Object
    Dim rsAddComments 'Holds the recordset for the new record to be added
    Dim strSQL 'Holds the SQL query to query the database
    'Create an ADO connection object
    Set adoCon = Server.CreateObject("ADODB.Connection")
    'Set an active connection to the Connection object
    adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("MasterTickets.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 tblMasterTickets.TicketNumber, tblMasterTickets.BriefDescription FROM tblMasterTickets;"
    '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 recordset with the SQL query
    rsAddComments.Open strSQL, adoCon

    'Add a new record to the recordset
    rsAddComments.Fields("Ticket Number") = Request.Form("tnumber")
    rsAddComments.Fields("Brief Description") = Request.Form("bdescript")
    'Write the updated recordset to the database
    rsAddComments.Update

    'Reset server objects
    rsAddComments.Close
    Set rsAddComments = Nothing
    Set adoCon = Nothing
    'Redirect to the mastertickets.asp page
    Response.Redirect "mastertickets.asp"
    %>
    I know it's probably something simple I am missing, but I can't find it. Help me if you can!

    Thanks,
    JRF2k
    Man can learn nothing except by going from the known to the the unknown. -- Claude Bernard

  2. #2
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you have any other error information?

  3. #3
    SitePoint Zealot
    Join Date
    May 2002
    Location
    USA
    Posts
    133
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, that's all I get is the 500 Internal Server error message. I receive no ASP errors at all. Other ASP pages on the server I am working on are operating correctly.
    Man can learn nothing except by going from the known to the the unknown. -- Claude Bernard

  4. #4
    SitePoint Wizard gRoberts's Avatar
    Join Date
    Oct 2004
    Location
    Birtley, UK
    Posts
    2,439
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think mozilla and netscape take some settings from IE, as when you goto Tools > Internet Options > Advanced > Untick Show Friendly HTTP Error Messages. Click ok and then refresh the page.


  5. #5
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Looks to me like your recordset is already using a SQL string to select data - you've already given it a job to do and then you're asking it to do something else (add records).

    Try this:

    Code:
    <%
    'Dimension variables
    Dim adoCon 'Holds the Database Connection Object
    Dim rsAddComments 'Holds the recordset for the new record to be added
    Dim strSQL 'Holds the SQL query to query the database 
    
    'Create an ADO connection object
    Set adoCon = Server.CreateObject("ADODB.Connection")
    
    'Set an active connection to the Connection object 
    adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("MasterTickets.mdb")
    
    'Create an ADO recordset object
    Set rsAddComments = Server.CreateObject("ADODB.Recordset")
    'Tell it which connection to use
    rsAddComments.ActiveConnection = adoCon
    rsAddComments.CursorType = 2
    'Set the lock type so that the record is locked by ADO when it is updated
    rsAddComments.LockType = 3
    'Tell your recordset which table you want
    rsAddComments.Source = tblMasterTickets
    'Open the recordset with the SQL query 
    rsAddComments.Open
    
    'Add a new record to the recordset
    rsAddComments.Fields("Ticket Number") = Request.Form("tnumber")
    rsAddComments.Fields("Brief Description") = Request.Form("bdescript")
    'Write the updated recordset to the database
    rsAddComments.Update
    
    'Reset server objects
    rsAddComments.Close
    Set rsAddComments = Nothing
    adoCon.Close
    Set adoCon = Nothing
    'Redirect to the mastertickets.asp page
    Response.Redirect "mastertickets.asp"
    %>

  6. #6
    SitePoint Zealot
    Join Date
    May 2002
    Location
    USA
    Posts
    133
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the help,

    Now I receive this:




    Microsoft OLE DB Provider for ODBC Driverserror '80040e0c'

    Command text was not set for the command object. /Test/Database Testing/add_to_MasterTickets.asp, line 23
    Man can learn nothing except by going from the known to the the unknown. -- Claude Bernard

  7. #7
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmmm... probably my bad.... brb - just checking.

  8. #8
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, you're missing the rsAddComments.AddNew line.

    Code:
    <%
    'Dimension variables
    Dim adoCon 'Holds the Database Connection Object
    Dim rsAddComments 'Holds the recordset for the new record to be added
    Dim strSQL 'Holds the SQL query to query the database 
    
    'Create an ADO connection object
    Set adoCon = Server.CreateObject("ADODB.Connection")
    
    'Set an active connection to the Connection object 
    adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("MasterTickets.mdb")
    
    'Create an ADO recordset object
    Set rsAddComments = Server.CreateObject("ADODB.Recordset")
    'Tell it which connection to use
    rsAddComments.ActiveConnection = adoCon
    rsAddComments.CursorType = 2
    'Set the lock type so that the record is locked by ADO when it is updated
    rsAddComments.LockType = 3
    'Tell your recordset which table you want
    rsAddComments.Source = tblMasterTickets
    'Open the recordset with the SQL query 
    rsAddComments.Open
    
    'Add a new record to the recordset
    rsAddComments.AddNew
    rsAddComments.Fields("Ticket Number") = Request.Form("tnumber")
    rsAddComments.Fields("Brief Description") = Request.Form("bdescript")
    'Write the updated recordset to the database
    rsAddComments.Update
    
    'Reset server objects
    rsAddComments.Close
    Set rsAddComments = Nothing
    adoCon.Close
    Set adoCon = Nothing
    'Redirect to the mastertickets.asp page
    Response.Redirect "mastertickets.asp"
    %>
    That ought to do it (I hope!)

    Ps - you don't really need the .Fields, but if it's not doing any harm...

  9. #9
    SitePoint Zealot
    Join Date
    May 2002
    Location
    USA
    Posts
    133
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Still getting that same error.

    Microsoft OLE DB Provider for ODBC Driverserror '80040e0c'

    Command text was not set for the command object. /Test/Database Testing/add_to_MasterTickets.asp, line 23


    I was using this as my tutorial:

    http://www.webwizguide.info/asp/tuto...tabase_pt2.asp

    No idea why it won't work, but if I download the files and run the turtorial works like a charm, but when I try to customize it for my own something breaks!
    Man can learn nothing except by going from the known to the the unknown. -- Claude Bernard

  10. #10
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That tutorial is for retrieving some information from the database but what you're trying to achieve is adding a record. There are differences. Did you remove the .Fields?

    Code:
    <%
    'Dimension variables
    Dim adoCon 'Holds the Database Connection Object
    Dim rsAddComments 'Holds the recordset for the new record to be added
    
    'Create an ADO connection object
    Set adoCon = Server.CreateObject("ADODB.Connection")
    
    'Set an active connection to the Connection object 
    adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("MasterTickets.mdb")
    
    'Create an ADO recordset object
    Set rsAddComments = Server.CreateObject("ADODB.Recordset")
    
    	'Tell it which connection to use
    	rsAddComments.ActiveConnection = adoCon
    
    	'Set recordset options
    	rsAddComments.CursorLocation = 3
    	rsAddComments.CursorType = 2
    	rsAddComments.LockType = 2
    
    	'Tell your recordset which table you want
    	rsAddComments.Source = tblMasterTickets
    	'Open the recordset with the SQL query 
    	rsAddComments.Open
    
    	'Add a new record to the recordset
    	rsAddComments.AddNew
    
    		'Populate that recordset
    		rsAddComments("Ticket Number") = Request.Form("tnumber")
    		rsAddComments("Brief Description") = Request.Form("bdescript")
    
    	'Write the updated recordset to the database
    	rsAddComments.Update
    
    'close recordset object
    rsAddComments.Close
    Set rsAddComments = Nothing
    
    'close connection object
    adoCon.Close
    Set adoCon = Nothing
    
    'Redirect to the mastertickets.asp page
    Response.Redirect "mastertickets.asp"
    %>
    I can see no reason why this won't work from an ASP perspective. Copy and paste it and see what happens.

    If you still have problems then:

    Do the two fields you're NOT populating in the database allow nulls? Will Access allow them to be empty? If they're set to 'Not Null' then you must assign them a value when inserting a row in the database. Check it out.

    The spaces in your field names are not a great idea. Though Access/ASP/ADO should handle these ok, as a rule of them it's smart to say no spaces in field names - use underscores instead. That probably isn't the issue here though. Just an observation.



Bookmarks

Posting Permissions

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