August 29, 2008 09:28pm pdt

Member Login

Hall of Fame

Yearly

1jason1178 4,000
2Smart_Man 2,000
2smidgie82 2,000
4gdemaria 1,000
4dodge20 1,000
6appari 750

	[x] Attachment Details

Error handling

Zones: Active Server Pages (ASP), Macromedia UltraDev, SQL Server 2005

Tags: Microsoft, SQL, 2000, asp

I have the following code:

I have a system that executes certain transactions based on the users selection. So a user can select to create tables, insert data and create a stored procedure etc.

What I would like to do is perform error checking, so if the same table or stored procedure is already created or there is an error in the code, then it should be validated by the error checking.

I have had a look at a couple of code examples on the internet to check for error handling, these examples are only based if I was to perform one action. But if I use the same example on my system then it wouldnt work.

The following are two example codes, one for building a table, the other for creating a stored procedure.

Im, not sure how to go about this, so please help me achieve this.
I would like to use Transactions to perform this solution.

CREATE PROCEDURE dbo.[SQL_GENERATOR_UPDATE2]

@Ref_SQL_Generator2 varchar(32) = null,
@Project_Code1 varchar(5) = null,
@SQL_Type varchar(32) = null,
@SQL_Code text = null,
@Reason text = null,
@Modified_By varchar(32) = null,
@Modified_DT smalldatetime = null

AS

UPDATE SQL_Generator2

SET
      Project_Code1 = @Project_Code1,
      SQL_Type = @SQL_Type,
      SQL_Code = @SQL_Code,
      Reason = @Reason,
      Modified_By = @Modified_By,
      Modified_DT = @Modified_DT

WHERE Ref_SQL_Generator2 = @Ref_SQL_Generator2
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SQL_Generator2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SQL_Generator2]
GO
CREATE TABLE [dbo].[SQL_Generator2] (
      [Ref_SQL_Generator2] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
      [SQL_Generator2_Number] [bigint] IDENTITY (1, 1) NOT NULL ,
      [Project_Code1] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
      [SQL_Type] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
      [SQL_Code] [text] COLLATE Latin1_General_CI_AS NOT NULL ,
      [Reason] [text] COLLATE Latin1_General_CI_AS NULL ,
      [Added_By] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
      [Added_DT] [smalldatetime] NULL ,
      [Modified_By] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
      [Modified_DT] [smalldatetime] NULL ,
      [Deleted_By] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
      [Deleted_DT] [smalldatetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

The script:

<%
'on error resume next
str_Criteria = fixforsql(request("Search_Criteria"))
str_Project_Name = request("Project_Name")
str_Project_Type = request("Project_Type")
str_confirm = request("confirm")
sql = "exec SQL_GENERATOR_SEARCH @Criteria='" &str_Criteria&"', @Project_Name='" &str_Project_Name& "', @Project_Type='" &str_Project_Type& "'"
Set rs1 = server.CreateObject("ADODB.recordset")
rs1.open sql, conn_SQL_Generator,1,3

if str_confirm = "Yes" then
while not rs1.eof
sql2=""

sql2= sql2 & Replace(rs1("sql_code"), "GO", ";")

Set rs2 = server.CreateObject("ADODB.recordset")
rs2.open sql2, conn_SQL_Generator,1,3

rs1.movenext
wend
rs1.movefirst
response.Redirect("SQL_Generator_Execute_Confirm.asp")
end if

%>

Start your free trial to view this solution

Question Stats

Zone: Web Development

Question Asked By: heyme

Question Asked On: 12.21.2007

Participating Experts: 5

Points: 175

Translate:

Loading Advertisement...

20080723-EE-VQP-34 / EE_QW_2_20070628