Jump to content

Partial ID in Access database been served and unable to add new entries in ASP

Malla

By Malla
in ASP

Recommended Posts

Malla Newbie

Malla

Posted
Posted

Hi,I am trying to create an asset management system using HTML, ASP and MS Access. I am testing all on this with IIS. Every thing is held on my local machine. I am having two problems at the moment. The database (Assets.mdb) is held in "c:/assets.mdb" and my HTML page and the two ASP pages are held in "C:\Inetpub\wwwroot\TestWeb" which is the default path for IIS. Problem 1:When I try to display all details of the assets held in the table, the asset number which is the primary key, is not displayed in the format I have set it. I have set the asset number as an Auto Number and formatted it as follows: "TACD"0000. When I add a new record directly in to the table, the numbers are set as TACD0001, TACD0002 etc... However when I display the table in my .asp page all information is served correctly except for the Asset Number which only shows up as 1, 2, 3 etc... Any idea on how I can rectify this? I have followed the ASP tutorial and the ADO tutorial on the website but I cant seem to fix this. The code I have used is as follows: <body> <% set conn=Server.CreateObject("ADODB.Connection") conn.Provider="Microsoft.Jet.OLEDB.4.0" conn.Open "c:/Assets.mdb" set rs=Server.CreateObject("ADODB.recordset") rs.Open "SELECT AssetNumber, Title, Classification, Author, Version, Date, NumberOfDisks, Distribution, Location, Description, Notes FROM cdinventory", conn %> <table border="1" width="100%"> <tr> <% for each x in rs.Fields response.write("<th>" & x.name & "</th>") next %> </tr> <% do until rs.EOF %> <tr> <% for each x in rs.Fields %> <td><% Response.Write(x.Value) %></td> <% next rs.MoveNext %> </tr> <% loop rs.close conn.close %> </table> </body> Problem 2:I have a form in a HTML page which takes the details of a new asset from a user and sends it to an .asp page once the submit button is hit. The thing is that the record is not created and only displays the error message that I have specified. inventory.html <html> <head> <title>Inventory System</title> <link rel="stylesheet" type="text/css" href="inventorystyle.css" /> <link rel="shortcut icon" href="favicon.ico" /> </head> <body> <h1>Inventory System</h1> <form method="post" action="addasset.asp"> <table> <tr> <td>Title:</td> <td><input name="title"></td> </tr> <tr> <td>Classification:</td> <td> <select name="classification"> <option value="blank">Select One</option> <option value="operatingsystem">Operating System</option> <option value="applicationsoftware">Application Software</option> <option value="drivercd">Driver CD</option> </select> </td> </tr> <tr> <td>Author:</td> <td><input name="author"></td> </tr> <tr> <td>Version:</td> <td><input name="version"></td> </tr> <tr> <td>Date:</td> <td><input name="date"></td> </tr> <tr> <td>Number Of Disks:</td> <td><input name="numberofdisks"></td> </tr> <tr> <td>Distribution:</td> <td><input name="distribution"></td> </tr> <tr> <td>Location:</td> <td><input name="location"></td> </tr> <tr> <td>Description:</td> <td><textarea name="description"></textarea></td> </tr> <tr> <td>Notes:</td> <td><textarea name="notes"></textarea></td> </tr> </table><br /><br /> <input type="submit" value="Add"> <input type="reset" value="Cancel"> </form> </body></html> addasset.asp<html><body><%set conn=Server.CreateObject("ADODB.Connection")conn.Provider="Microsoft.Jet.OLEDB.4.0"conn.Open "c:/Assets.mdb"sql="INSERT INTO cdinventory (Title,Classification,"sql=sql & "Author,Version,Date,NumberOfDisks,Distribution,Location,Description,Notes)"sql=sql & " VALUES "sql=sql & "('" & Request.Form("title") & "',"sql=sql & "'" & Request.Form("classification") & "',"sql=sql & "'" & Request.Form("author") & "',"sql=sql & "'" & Request.Form("version") & "',"sql=sql & "'" & Request.Form("date") & "',"sql=sql & "'" & Request.Form("numberofdisks") & "',"sql=sql & "'" & Request.Form("distribution") & "',"sql=sql & "'" & Request.Form("location") & "',"sql=sql & "'" & Request.Form("description") & "',"sql=sql & "'" & Request.Form("notes") & "')"on error resume nextconn.Execute sql,recaffectedif err<>0 then Response.Write("Error while adding new record!")else Response.Write("<h3>" & recaffected & " record added successfully!</h3>")end ifconn.close%></body></html> Any help on these two issues will be greatly appreciated. Thanks in advance.

Link to comment
Share on other sites
justsomeguy w3schools Guru

justsomeguy

Posted
Posted

I haven't seen anything where an autonumber would include a prefix. It's probably best to leave it as a regular int autonumber and add the prefix yourself whenever you print it. If the prefix is defined in a common file like an include file then you can change it whenever you want without affecting anything else. You may want to also define a function to format the ID, so you pass the function the ID from the database and it will pad it to 4 digits and add the prefix. The standard SQL doesn't support a prefix for an autonumber, so it might be best to get used to the standard way of doing it. For the second problem, print out err to see why it isn't 0. Find out what the error was that it is reporting.

Link to comment
Share on other sites
Malla Newbie

Malla

Posted
  • Author
Posted

@justsomeguy, Thanks for the info. The method of adding the prefix when its displayed in the page is a good idea and think I will be able to do that. Unfortunately I am just starting to learn scripting and am not sure how to go about printing the errors. I checked the ASP tutorial again and tried to add dim objErrset objErr=Server.GetLastError() response.write("ASPCode=" & objErr.ASPCode) This didnt work though so its obvious I am not doing it right.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...