title: How to return error message from SQL Server to web page
body: <p>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 <code>SELECT</code> statement) will be displayed in a grdiview. </p>
  
  <p>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.</p>
  
  <p>Here is my code so far:</p>
  
  <p>In SQL Server:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>In VB.NET application</p>
  
  <pre><code>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
  </code></pre>
  
  <p>but, it produce an error 'must declare the scalar variable @syntax' inside the gridview.</p>
  
  <p>I have set the <code>@syntax</code> parameter in the code above. </p>
  
  <p>So I think the code doesn't read the parameter.</p>
  
  <p>Also why the error displayed in a <code>gridview</code> not in <code>lblmsg.text</code> ?</p>
  
  <p>What I am doing is to return the error from SQL Server into a label.</p>
  
  <p>For example: </p>
  
  <pre><code>select *, from tbl_dormproc
  </code></pre>
  
  <p>it produces a result : </p>
  
  <blockquote>
    <p>Incorrect syntax near the keyword 'from'.</p>
  </blockquote>
  
  <p>Is it possible to pass the whole query into stored procedure?</p>
  
  <p>Through browsing, so far I only found how to pass a certain column name into stored procedure.</p>
  
  <p>Thanks in advance.</p>