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.
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