How to return error message from SQL Server to web page

Question

I am currently working on a project that need to connect with database. The syntax for querying the data in database comes from the user input on a textbox. If the user input for the query produce an error then I need to return the error message from the SQL Server, if there is no error then the result of the query (in this case a SELECT statement) will be displayed in a grdiview.

After looking through the web I found that I need to create stored procedure. I never used a stored procedure before so I don't know how to put the retrieving data.

Here is my code so far:

In SQL Server:

Create table tbl_dormproc
(
    [ID] int identity(1,1),
    [Name] varchar(150),
    [Date of Birth] date,
    [Address] varchar(100)
)

CREATE PROCEDURE dormproc(@syntax NVARCHAR(MAX))
AS
BEGIN
BEGIN TRY
    EXEC sp_executesql N'@syntax'
    SELECT NULL
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE()
END CATCH
END
GO

In VB.NET application

Protected Sub btnCheck_Click(sender As Object, e As EventArgs) Handles btnCheck.Click
    Try
        cmd = New SqlCommand("dormproc", con)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@syntax", txtQuery.Text)

        drDataReader = cmd.ExecuteReader
        dtDatatable.Load(drDataReader)
        Gridview1.DataSource = dtDatatable
        Gridview1.DataBind()
    Catch ex As SqlException
        lblMsg.Text = ex.Message         
    End Try
End Sub

but, it produce an error 'must declare the scalar variable @syntax' inside the gridview.

I have set the @syntax parameter in the code above.

So I think the code doesn't read the parameter.

Also why the error displayed in a gridview not in lblmsg.text ?

What I am doing is to return the error from SQL Server into a label.

For example:

select *, from tbl_dormproc

it produces a result :

Incorrect syntax near the keyword 'from'.

Is it possible to pass the whole query into stored procedure?

Through browsing, so far I only found how to pass a certain column name into stored procedure.

Thanks in advance.

Spock · Accepted Answer · 2015-01-18 13:54:59Z

2

You're not using sp_executesql correctly.

Try this

CREATE PROCEDURE dormproc(@syntax NVARCHAR(MAX))
AS
BEGIN
    EXEC sp_executesql @syntax 
    SELECT NULL
END
GO

edited Jan 18, 2015 at 13:54

answered Jan 18, 2015 at 13:41

Spock

4,9802 gold badges19 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Tri.PS Over a year ago

I tried your suggestion. It produce an error 'Incorrect syntax near '@syntax'.'...

Spock Over a year ago

Sorry. I made a mistake. You were actually correct, except you don't have to put @syntax in quotes. I've updated my answer.

Tri.PS Over a year ago

Yeah. It's already works. But, the error also displayed in the gridview. Is there any way how to display it on a label. Thanks.

Spock Over a year ago

remove the try catch block from the procedure. then the error will be returned to the error handler in C# and displayed in the error. Please mark this as the error if it solved your problem

Hamlet Hakobyan · Accepted Answer · 2015-01-18 13:30:24Z

2

You hasn't unhandled exception in your SP so you can't catch it in your client code. In case of exception in SP your error message just the result set of your SP. You can find it in your dtDataTable in first column of the first row. What about EXEC sp_executesql N'@syntax' you should check the syntax. @syntax is variable so should use it as:

EXEC sp_executesql @syntax

Look at the sp_executesql documentation for more information.

edited Jan 18, 2015 at 13:30

answered Jan 18, 2015 at 13:25

Hamlet Hakobyan

33.5k6 gold badges55 silver badges70 bronze badges

2 Comments

Tri.PS Over a year ago

I tried your suggestion. It produce the expected result. Thank you very much Sir.

Tri.PS Over a year ago

But, the error also displayed in the gridview. Is there any way how to display it on a label. Thanks.

Jeremy Caney · Accepted Answer · 2024-09-09 03:28:26Z

0

create table medical_examination(
examination_id      varchar(7)      not null, 
animal_id           varchar(7)      not null,
examination_date        date,
examination_status  varchar(8),
examination_cost        number(7,2),
primary key (examination_id),
foreign key (animal_id) references animals (animal_id)
);

create table vaccination (
vaccination_id      varchar(7)      not null,
animal_id           varchar(7)      not null,
vaccination_date        date,
vaccine_name        varchar(25),
vaccination_status  varchar(15),
vaccination_cost        number(7,2),
primary key (vaccination_id),
foreign key (animal_id) reference animals(animal_id)
);

create table day_care_service (
day_care_service_id  varchar(8) not null,
animal_id            varchar(7) not null,
day_care_service         date,
day_care_service_price number(7,2),
pet_cage             varchar(8),
primary key (day_care_service_id),
foreign key (animal_id) references animals (animal_id)
);

create table invoice (
invoice_no          number(7)        not null,
cust_id         varchar(7)       not null,
payment_id          varchar(6)       not null,
invoice_date        date,
primary key (invoice_no),
foreign key (cust_id) references customer(cust_id),
foreign key (payment_id) references payment(payment_id)
);

edited Sep 9, 2024 at 3:28

Jeremy Caney

7,818115 gold badges58 silver badges86 bronze badges

answered Sep 5, 2024 at 7:32

E-VA TAN

1

1 Comment

Jeremy Caney Over a year ago

Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. Would you kindly edit your answer to include additional details for the benefit of the community?

Collectives™ on Stack Overflow

How to return error message from SQL Server to web page

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related