Tutorials to .com

Tutorials to .com » Asp » Database » Application database unique constraint and capture asp database errors

Application database unique constraint and capture asp database errors

Print View , by: iSee ,Total views: 7 ,Word Count: 1604 ,Date: Tue, 26 May 2009 Time: 10:41 AM

This article mentioned in the asp page, with the exception of special statements, refer to <@ PAGE LANGUEGE = "VBSCRIPT">
Asp and the database to write the combination of the program, often encountered the same data table are not allowed to repeat the value of the existence of problems, such as user registration process is not allowed to appear in the same user ID. Under these circumstances, I have taken is to read the database already exists, and then with the value entered by the user, if the same has been registered are prompted to re-fill. This will achieve the intended purpose, but because there is an additional access to the database operation, comparative waste of resources.
We all know that data tables can be set out in the unique constraint is to limit the field from the database does not allow duplication of value, in violation of unique constraint, the database will return an error 2627, suggesting that "violation of the% 1! Bound ''% 2!''.''% can not be the object 4!''insert duplicate key ", which is the output of the database, SQL Server all the errors are stored in system tables in master.dbo.sysmessages. User-defined message can be stored in the sysmessages in. In asp stored procedure call, because there is no fault-tolerant storage process will be prompted to return to the page, and the implementation of the termination statement, @ @ Error return value is 0. If the page set up a fault-tolerant, error will not occur, but because the error is appearing in the database, it can not be its Err.Number No. error. (Also may not know my reasons for database :-) also please point prawns). Recent research ADO, found a more simple way to achieve this control.
ADO (ActiveX Data Objects) to be used for a wide range of applications and databases, asp, he can also be achieved through the operation of the database. The ADO 2.1, there is a Connection object Error object, which can capture the error from the database in order to control in the asp page. Property of the objects are commonly used in the following are read-only:
Description: character, return the error description from the database
Source: Character, error sources
SQLState: Character, ODBC API (driver manager) error, SQL Server 2000 in their description are as follows:
SQLSTATE returned by the two character string value of the type of the value of the three characters followed by the value of the composition of a sub-category. 01 show that the type of value is a warning, after the return code SQL_SUCCESS_WITH_INFO follow. As long as the type of value is not 01 (IM, except), means that is a mistake to follow after the return code SQL_ERROR. Designed to show that the type of IM to achieve from the ODBC warnings and errors generated. Any type of neutron-type value 000 are used to achieve a given category as defined in the conditions. This type of value and value of sub-class distribution defined by the SQL-92.
Stray from the point of a stray from the point and return to the topic discussed just now ~ ~ ~
Number: the return of error associated with the numeric value is a 32-bit value. Higher 16-bit code word is facilities, and the lower word is the real error code.
NativeError: This is what we use today to, MSDN is a description of him:
The NativeError property on a Error (the M $ is BUG? I do not pass in English :-)) object indicates the provider-specific error code for a given Error object. This property returns aa Long value that indicates the error code.
There HelpFile and Helpcontext, are related to help, not detailed here.
Here we use the NativeError, use it to capture the error from the database, he returned to the wrong number. Source code fragment as follows:
<% ON ERROR RESUME NEXT 'VBSCRIPT fault-tolerant
Set Conn = Server.CreateObject ( "ADODB.Connection")
Set RS = Server.CreateObject ( "ADODB.RecordSet")

Conn.Open "DSN = BBII; UID = sa; PWD ="
SQL = "Exec ch_insNovel 'd','22 ','33','44 ','55'"
'SQL = "Insert Into Novel (Name, Author, yy, ee) VALUES (' nn ',' ee ',' jj ')", and the effect is the same sentence
RS.Open SQL, Conn
If Conn.Errors.Count = 0 Then 'determine the number of errors
IsSucc = "success" '
Else
select case Conn.Errors.Item (0). NativeError
case 2627
IsSucc = "ddddddd" 'you can, the definition of a number of its own error return values
End Select
End if
Response.Write "<br> :::"& IsSucc
%>
The attention of a select case ... the "Conn.Errors.Item (0). NativeError", the return of a group of the first item. In our example, the 2627 measures to promote the same time accompanied by a "statement has been terminated" (3621), strictly speaking, he was not wrong, but a general warning (severe level = 10). Therefore, we need to determine the first error, of course, you can define i = i + 1 to see all the errors. In this way, we will be able to know SQL Server from the specific content of the error, you can more easily control.

Attachment: error code
<!-ErrLp.asp-->
<% ON ERROR RESUME NEXT
Set Conn = Server.CreateObject ( "ADODB.Connection")
Conn.Open "Driver = (SQL Server); Server = 127.0.0.1; database = master; UID = sa; pwd ="
set rs = Server.CreateObject ( "ADODB.RecordSet")
SQL = "Select * From sysmessages Where msglangid = 2052 Order By error"
rs.open sql, conn, 3,2
PAGE = CLng (REQUEST ( "txtpage"))
RS.PageSize = 100
If PAGE <1 Then PAGE = 1
If PAGE> RS.PageCount Then PAGE = RS.PageCount
RS.AbsolutePage = PAGE
%>
<style>
td
(
font-family: Verdana;
font-size: 10pt;
)
</ style>
<Div>
<FORM METHOD="GET">
<TABLE WIDTH="100%" BORDER="0" CELLSPACING="0" CELLPADDING="0" BGCOLOR="#999999">
<TR>
<TD Width="100"> Total number: <% = RS.RecordCount%> </ td>
<TD Width="80"> the total number of pages: <% = RS.PageCount%> </ TD>
Currently <TD width="90"> Page: <% = page%> </ TD>
<TD Width="80"> to <INPUT TYPE = "text" NAME = "txtpage" SIZE = "2" style = "font-family: Verdana; font-size: 8pt; border-style: solid; border - width: 1 "> Page </ TD>
<TD Width="60">
<%
If page <> 1 Then
Response.Write "<a href=ErrorLp.asp?txtpage=1> the first page </ a>"%> </ TD>
<TD Width="60">
<% Response.Write "<a href=ErrorLp.asp?txtpage="&(page-1)&"> Previous </ a>"%> </ TD>
<%
End If

If page <> RS.PageCount Then%>
<TD Width="60">
<% Response.Write "<a href=ErrorLp.asp?txtpage="&(page + 1)&"> Next </ a>"%> </ TD>
<TD Width="70"> <% Response.Write "<a href=ErrorLp.asp?txtpage="&RS.PageCount&"> the last page </ a>"
End If
%>
</ TD>
<TD> </ TD>
</ TR>
</ TABLE>
</ FORM>
</ DIV>
<p>
<DIV>
<TABLE CELLSPACING="0" CELLPADDING="0" BORDER="1" WIDTH="100%" bordercolor="#999999">
<TR>
No. <TD> error </ TD>
<TD> Serious level </ TD>
<TD> DLEVEL </ TD>
<TD> Information </ TD>
</ TR>

<% For ipage = 1 To RS.PageSize%>

<TR OnmouseOver="javascript:this.style.background='#dddddd';" onmouseOut="javascript:this.style.background='';">
<TD> <% = RS ( "error ")%></ TD>
<TD> <% = RS ( "severity ")%></ TD>
<TD> <% = RS ( "dlevel ")%></ TD>
<TD> <% = RS ( "description ")%></ TD>
</ TR>
<% RS.MoveNext
IF RS.EOF THEN EXIT FOR
NEXT
SET RS = NOTHING
SET Conn = NOTHING
%>
</ TABLE>
</ DIV>
<! - CorpRight By Cheery_Ke ->
Another: The ADO2.1 with Conn.Errors note, see ms-help: / / MS.MSDNVS.2052/dnaxctrl/html/ado_objm.htm


ASP Database Related Articles


Can't Find What You're Looking For?


Rating: 0.0

Comments

No comments posted.