CODEWITHSUNDEEP
c#sql-server-2008stored-proceduressqldatatypessqldataadapter
xizwyck
xizwyck

Reputation: 63

Stored procedure with table type parameter returns data but SqlDataAdapter will not fill

I'm passing a data table to stored procedure (using a table type) to update multiple records at once. SQL profiler shows the stored procedure executing and the records are updated; however, I'm also returning a data set to a SqlDataAdapter. When I execute this in SSMS, I get the results. When I run this in code, rows are not being received.

Any ideas? I'm clueless on why the SqlDataAdapter isn't being filled. Oddly enough, I can get a tables count, but no rows for any tables.

CREATE PROCEDURE [dbo].[SGT_UpdateSGT]
 @SGT_GuidelinesTbl As [dbo].[SGT_GuidelinesTbl] Readonly
AS
BEGIN
 SET NOCOUNT ON;
 update g set Disposition=sgt.Disposition, DispositionDate=GETDATE() from SGTGuidelineRequests g inner join @SGT_GuidelinesTbl sgt on g.pkid=sgt.pkid

 select u.ForeName, u.Email, g.* 
 from @SGT_GuidelinesTbl sgt
 inner join SGTGuidelineRequests g on g.pkid=sgt.pkid
 inner join Users u on u.NBID=g.SubmittedBy
END
GO

c# code. The session is being filled

DataSet dataSet = new DataSet();
DataTable queue = (DataTable)Session["SGTKeepDeleteQueue"];
using (SqlConnection sqlConnection = new SqlConnection(SQLConn.CWFMO()))
{
  sqlConnection.Open();
  SqlCommand sqlCommand = new SqlCommand("SGT_UpdateSGT", sqlConnection);
  sqlCommand.CommandType = CommandType.StoredProcedure;

  SqlParameter guidelinesTblParam = new SqlParameter();
  guidelinesTblParam.ParameterName = "@SGT_GuidelinesTbl";
  guidelinesTblParam.SqlDbType = SqlDbType.Structured;
  guidelinesTblParam.Value = queue;
  sqlCommand.Parameters.Add(guidelinesTblParam);

  SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
  sqlDataAdapter.Fill(dataSet);
}

And this is what SQL Profiler receives. When I execute this, I get at least 1 row of data.

declare @p1 dbo.SGT_GuidelinesTbl
insert into @p1 values(1,N'Approved',N'8/23/2012 12:00:00 AM')

exec SGT_UpdateSGT @SGT_GuidelinesTbl=@p1

Upvotes: 0

Views: 2983

Answers (1)

xizwyck
xizwyck

Reputation: 63

I changed the name of the SqlDataAdapter and it works. I'm not sure why, I didn't name it that anywhere else in the file. Cleaning the solution didn't solve this either. At least it works.

Upvotes: 1

Related Questions