asked on

Dynamic Sql not populating asp.net datatable

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
 
     As
Begin           
	BEGIN TRY

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

   /* 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, 
                @ParamDefinition, 
                @StartDate, 
                @EndDate,
				@RoleID,
                @tx,
				@docid

Open in new window

DStarkey

ASKER

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.

Thanks

     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 = ""
        Try

            Dim connString As String = _
                   ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString
            'Create a SqlConnection instance
            Using myConnection As New SqlConnection(connString)

                If myConnection.State = ConnectionState.Closed Then

                    myConnection.Open()

                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)
                myAdapter.Fill(myDataTable)

                
                ClientSearchResults = myDataTable

                'Close the connection
                myConnection.Close()
                myConnection.Dispose()
                myDataTable.Dispose()

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

Open in new window

Lee

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.

Lee

DStarkey

ASKER

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?

Thanks

ASKER CERTIFIED SOLUTION

Lee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DStarkey

ASKER

Thanks for the help...

Lee

You're welcome.

Cheers,

Lee