ASP transactions and SQL Stored Procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

ASP transactions and SQL Stored Procedures

Author

Topic

afterburn
Starting Member

28 Posts

Posted - 2003-01-16 : 20:41:25

First off I do not know if this belongs in SQL or here but I will try here.

I created a table with constraints to stop someone from not entering a valid email address. However fire it off from a ASP page with a command object and it bypasses the constraints and inserts null even thou the field is specified as not null. And does not fire an error state back into the asp with the server.getlasterror returns .number = 0 and line = 0 everything else is a null string.

So my question would be what in hades is going wrong with the sp or asp transactions? if I run it from the query analyzer it correct stops the record from being inserted but why does the value get inserted through the ASP page when nothing was posted. The only way it fails is the field is defined as not null

Edited by - afterburn on 01/16/2003 20:41:50

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-16 : 20:53:07

There is a difference between a Null and an empty string (""), you are probably passing an empty string, which would not violate the not null constraint. You'd have to post your code here in order for us to check, but that is probably the reason.

The error would not be reported by the Server object, but rather through the ADO Connection object. If you check the Connection object's Errors collection you'd find any errors generated by the database, including constraint violations.

afterburn
Starting Member

28 Posts

Posted - 2003-01-16 : 21:17:20

This is the structure of the tables.

CREATE TABLE [dbo].[tblEmails] (
[EmailID] [int] IDENTITY (1, 1) NOT NULL ,
[EmailAddress] [varchar] (100) NOT NULL,
[RegisterDate] [datetime] NOT NULL ,
[IPAddress] [varchar] (36)
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblEmails] WITH NOCHECK ADD
CONSTRAINT [DF_tblEmails_RegisterDate] DEFAULT (getdate()) FOR [RegisterDate],
CONSTRAINT [PK_tblEmails] PRIMARY KEY CLUSTERED
(
[EmailID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [ckEmails] CHECK ([emailaddress] like '%_%@%_%.%__%' and [IPAddress] like '%_%.%_%.%_%.%_%')
GO

CREATE procedure spAddNewsletterEmail(
@EmailAddress varchar(100) = null,
@IPAddress varchar(36) = null)
as

insert into tblEmails(EmailAddress,IPAddress)
values(@EmailAddress,@IPAddress)

GO

ASP code

<%@ Transaction=required EnableSessionState=False Language=VBScript %>
<%Option Explicit
Response.Buffer = true

Dim Conn,cmd

set conn = server.CreateObject("adodb.connection")
set cmd = server.CreateObject("adodb.command")

With Conn
.ConnectionString = Application("Conn")
.Open
.CursorLocation = adUseClient
End with

with cmd
.ActiveConnection = Conn
.CommandText = "spAddNewsletterEmail"
.CommandType = adCmdStoredProc

.Parameters.Append .CreateParameter("@EmailAddress",adVarChar,adParamInput,100,Request.Form("EmailAddress"))
.Parameters.Append .CreateParameter("@IPAddress",adVarChar,adParamInput,36,Request.ServerVariables("REMOTE_HOST"))
.Execute

end with

Sub OnTransactionCommit()
Response.Write "DONE"
End Sub

Sub OnTransactionAbort()
Response.Write "ERROR"
End Sub

Conn.Close
set conn = nothing
set cmd = nothing

Dim objErrorInfo
Set objErrorInfo = Server.GetLastError

if objErrorInfo.Number > 0 then
Response.Clear
Response.Write objErrorInfo.line
ObjectContext.SetAbort
end if

Set objErrorInfo = nothing

%>

The issue is that the constraints do not hold when inserting a value from this page. I have not on error resume next so it should spit the value to the page but fails to, I believe this issue is directly related to transactions in ASP but I am not sure how so or how to go around it.

the value is inserted nevertheless when just requesting the page without a post. As for the "" is not null it is when the command object is involved. "" = null in a stored procedure.
........

While writing this I tested a change to the stored procedure if I passed '' empty string as default value for the parameter it fails correctly. But the transaction does not clear the buffer. With Response.Clear ?????? damn bugs in ASP suck.....

Edited by - afterburn on 01/16/2003 22:43:58

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-16 : 22:55:42

Instead of using On Error to handle the error, you should add some code like this, immediately after the Response.Buffer line:

If Request.Form("EmailAddress")="" Then
 Response.Write "You must provide an email address."
 Response.End
End If
...remaining code follows

You don't gain anything by having the error handler try to pick this up, because it's a clearly defined exception that's easy to catch and handle, and as you've found the error handling in VBScript isn't particularly robust and won't always report this kind of error. Also, the way you have it now, the ADO connection would be opened by the time the "error" would be thrown, and if something goes wrong it may not close the connection cleanly.

afterburn
Starting Member

28 Posts

Posted - 2003-01-16 : 23:05:56

If you look at the code again you will not see an
on error resume next statement. As for the the code addition. You should not be able to get to this page with out posting to it. There are no links. So I validate it with javascript.

The issue is more SQL than ASP. Because the stored procedure allowed for an insert of null by passing the constraints on table.

If you place this code with a global.asa connection string of
Application("Conn") = "FiLELESS_DNS_STRING"
on a server
You will see that the SQL stored procedure inserts null when requesting this page even with constraints that prohibit just that.

But when inserting this value into a table without a default value of null it correctly errors. However the server.GetLastError function doesn't return any values to the an error state.

Edited by - afterburn on 01/16/2003 23:15:16

Edited by - afterburn on 01/16/2003 23:15:55

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources