p2p.wrox.com Forums - Classic ASP Databases

Retrieve error desription for SQL Stored Procedure

srotondo — Fri, 13 Nov 2009 19:16:45 GMT

is it possible to retrieve an error message from sql server stored prodedure and read it in my asp code?

for example, if i try to enter a dup key, can i pass the following stock SQL error message to asp from sql:

Server: Msg 547, Level 16, State 1, Procedure procReenrollmentContractHistoryAddUpdate, Line 50
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_reenrollmentContractHistory_reenrollmentContra ctTypeMaster'. The conflict occurred in database 'cmProd', table 'reenrollmentContractTypeMaster', column 'contractType'.
The statement has been terminated.

If i am executing one insert statement in my stored procedure and there's a problem, my asp WILL get this error message. However, i'm having a problem when I execute a select statement (which runs fine) and then execute an update statement which fails with a dup key. For some reason, my asp does not get the dup key message (probably b/c the select statement completed successfully).

See below. this is the code i'm having trouble with: (note that when i run it as is and force a dup key error, my asp shows no error. however when i comment out the exec procUpdateGetVariousNumbers2 statement, I DO receive a sql error in asp),

BEGIN TRANSACTION
set @logStatus = 'ADD'
exec procUpdateGetVariousNumbers2 'reenrollmentContractHistoryID', @logUser, @reenrollmentContractHistoryRecID output

insert reenrollmentContractHistory
select
@reenrollmentContractHistoryRecID,
@contractType,
@academicYear,
null, --paymentOption,
null, --tuitionRefundPlanYN,
null, --lunchPlanOption,
null, --tuitionAmount,
null, --tuitionDepositAmount,
null, --faAwardAmount,
null, --faScholarshipName,
null, --faScholarshipAmount,
null, --faDepositAmount,
null, --lunchAmount,
@contractText,
null, --@cshAdminUpdateName,
null, --@cshAdminUpdateDateTime,
null, --@cshAdminUpdateExplanation,
null, --@cshAdminApprovaleSignature,
null, --@cshAdminApprovaleSignatureDateTime,
parentID,
PrimaryAddressee,
PrimarySalutation,
NamePrefix,
FirstName,
MiddleName,
LastName,
NameSuffix,
reenrollmentStudentGrade,
reenrollmentStudentReenrollmentStatus,
reenrollmentStudentType,
constituentStatus,
@logUser,
@logDateTime,
@logStatus

Error In ASP Code

DeepakAryan — Fri, 13 Nov 2009 16:47:15 GMT

<%

' variables
dim dcnDB 'Connection String
dim rsQuery 'Record Set
dim FacultyID 'Faculty Id For Faculty Members
dim Password 'Password For Faculty Members
dim Department 'Department Of The Faculty Members
dim FullName 'Name of The Faculty Members

'store form input into variables
FacultyID = Request.Form("FacultyID")
Password = Request.Form("Password")
Department=Request.Form("Department")
FullName=Request.Form("FullName")

' Code To Handle Error Code

' execute sql and open as recordset

Set dcnDB = Server.CreateObject("ADODB.Connection")

dcnDB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.MapPath ("DATABASE\Database.mdb")
dcnDB.Open

Set rsQuery = Server.CreateObject("ADODB.Recordset")

rsQuery.open "SELECT * FROM FacultyRecord",_
dcnDB,3,3

'Check if facultyid doesn't already exist
do while not rsQuery.EOF
if rsQuery("FacultyID")=FacultyID then
set rsQuery=nothing
set dcnDB=nothing
Response.Redirect("create.asp")
end if
rsQuery.MoveNext
loop
' Opens the returned values from the SQL as a recordset, ready for iteration by ASP
'set rcSet = cnStr.Execute(sqlStr)

rsQuery.Addnew

rsQuery ("FacultyID")= Request.Form("FacultyID")
rsQuery ("Password")= Request.Form("Password")
rsQuery ("Department")= Request.Form("Department")
rsQuery ("FullName")= Request.Form("FullName")

rsQuery.Update

' validate variables against database
If (not rsQuery.BOF) and (not rsQuery.EOF) then

Response.cookies("FacultyID") = FacultyID
Response.cookies("FullName") = FullName

Response.write("WELCOME ")
response.write(" ")
Response.write("")
response.write rsQuery.fields("FullName") & " "
response.write("
")

response.write("FacultyID")
response.write(" : ")
response.write rsQuery.fields("FacultyID") & "
"

response.write("Department")

response.write(" &nbs p; : ")
response.write rsQuery.fields("Department") & "
"

else
response.write "Incorrect FacultyID and/or Department and/or FullName"
end if

rsQuery .close
dcnDB.close
%>

In the code above I have got the follwing error

Microsoft JET Database Engine (0x80040E09)
Cannot update. Database or object is read-only.
/szone/welcome.asp, line 236

Search String?

S Shirani — Fri, 23 Oct 2009 16:42:28 GMT

Hello,
I need a complete script or function for search into string,
I have a query for oracle suppose
RecordSet_Object= "Select Col1,Col2 From Schema.ViewName "
Now i want a function to pass value/parameter which extract direct ViewName/TableName from Query

Thanks