[Webinar] Streamline your web hosting managementRegister Today


Dynamic Sql not populating asp.net datatable

Posted on 2010-04-06
Medium Priority
Last Modified: 2012-05-09
I am new to using Dynamic Sqls and in my first attempt I have a stored procedure listed below that is dynamically building my sql string. When I execute the stored procedure with Sql Manager I get results back. But when i run it from my application the Datatable is not populated. Can someone take a look to make sure I am not missing anything within my procedure to return the results...

@StartDate Datetime,
@EndDate DateTime,
@RoleID int,
@tx nvarchar(50) = NULL,
@docid nvarchar(5)= NULL

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

   /* Variable Declaration */
    Declare @SQLQuery AS NVarchar(4000)
    Declare @ParamDefinition AS NVarchar(2000) 

    /* Build the Transact-SQL String with the input parameters */ 
    Set @SQLQuery = 'Select * FROM   CMSC.Orders o
   INNER JOIN   CMSC.WorkItems wi ON o.Orderid = wi.Orderid
   INNER JOIN   CMSC.WorkItemStatusDef wis on wis.StatusCode = wi.StatusCode
   INNER JOIN   CMSC.Inventory i on o.InvID = i.InvID
	 where (1=1) ' 

    /* check for the condition and build the WHERE clause accordingly */
	If (@StartDate Is Not Null) AND (@EndDate Is Not Null)
         Set @SQLQuery = @SQLQuery + ' And (o.OrderDate
         BETWEEN @StartDate AND @EndDate)'
	If @RoleID Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (o.RoleID = @RoleID)'

    If @tx Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (o.DocNum = @tx)'
	If @docid Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (o.DocID = @docid)'
	Set @ParamDefinition = '@StartDate DateTime,
                @EndDate DateTime,
				@RoleID int,
                @tx nvarchar(50),
				@docid nvarchar(5)'

    /* Execute the Transact-SQL String with all parameter value's 
       Using sp_executesql Command */
    Execute sp_Executesql     @SQLQuery, 

Open in new window

Question by:DStarkey
  • 3
  • 3

Author Comment

ID: 29906281
Here is a copy of my function that calls the stored procedure. I use this type of function for all my procedures and all have have worked fine until now.

     Public Function ClientQuery(ByVal roleid As Integer, ByVal startdate As Date, ByVal enddate As Date, ByVal tx As String, _
                                 ByVal docid As String) As DataTable
        'Dim _string As String = ""

            Dim connString As String = _
            'Create a SqlConnection instance
            Using myConnection As New SqlConnection(connString)

                If myConnection.State = ConnectionState.Closed Then


                End If
                'Create a SqlCommand instance
                Dim myCommand As New SqlCommand("CMSC.GetClientQuery", myConnection)

                myCommand.CommandType = CommandType.StoredProcedure
                myCommand.Parameters.Add("@RoleID", SqlDbType.VarChar).Value = roleid
                myCommand.Parameters.Add("@StartDate", SqlDbType.VarChar).Value = startdate
                myCommand.Parameters.Add("@EndDate", SqlDbType.VarChar).Value = enddate
                myCommand.Parameters.Add("@tx", SqlDbType.VarChar).Value = tx
                myCommand.Parameters.Add("@docid", SqlDbType.VarChar).Value = docid

                'Get back a DataTable
                Dim myDataTable As New DataTable

                'Create a SqlDataAdapter instance
                Dim myAdapter As New SqlDataAdapter(myCommand)

                ClientSearchResults = myDataTable

                'Close the connection

            End Using
        Catch ex As Exception
            lblError = ex.Message
        End Try
    End Function

Open in new window

LVL 25

Expert Comment

by:Lee Savidge
ID: 29906799
Run SQL profiler when you run your application to see what the application is actually sending to the SQL Database. See if that matches what you manually type in to get results. I suspect you have not defined a parameter correctly in your application if the SQL works fine.


Author Comment

ID: 29909949
Thansk for the sugestion I never used the profiler before. Very handy...

Here is what the Profiler shows

exec CMSC.GetClientQuery @RoleID='76',@StartDate='1/1/2010 12:00:00 AM',@EndDate='4/6/2010 12:00:00 AM',@tx='',@docid=''

I was able to extract the sql and found out that if I remove @tx and @docid from the line above, results come back. Also if I add NULL to the last two parameters I get the same resutls.

exec CMSC.GetClientQuery @RoleID='76',@StartDate='1/1/2010 12:00:00 AM',@EndDate='4/6/2010 12:00:00 AM', @tx=NULL, @docid=NULL

How can I get the results to pass NULL instead of ' ' , Any ideas?  Or do I need to look at doing something different within the stored procedure?

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

LVL 25

Accepted Solution

Lee Savidge earned 2000 total points
ID: 29914594
When you declare you variables in the calling application, set them to null so when the SQL is constructed a null is passed.


Author Closing Comment

ID: 31711308
Thanks for the help...
LVL 25

Expert Comment

by:Lee Savidge
ID: 29926086
You're welcome.



Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
There is a wide range of advantages associated with the use of ASP.NET. This is why this programming framework is used to create excellent enterprise-class websites, technologies, and web applications.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question