Use parameterised stored procedure in DataAdapter to bind DataGrid

Hi,

I'm trying to use parameterised stored procedures to select, and
eventually update, a sql server database. But i'm not sure i'm passing
the parameters correctly into the sp as no rows are showing in the
datagrid. Here's the code:

int intUserNo = (int)(Session["usrNo"]);
string strPatientCode = patientCodeTxtBx.Text;
DateTime dateProcedureDate = DateTime.Parse(procedureDateTxtBx.Text);

// Set the connection
SqlConnection conn = new
SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
conn.Open();

try
{
SqlCommand cmd = new SqlCommand();

cmd = new SqlCommand("proc_GetSampleAss", conn);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@usrNo", intUserNo));
cmd.Parameters.Add(new SqlParameter("@patientCode", strPatientCode));
cmd.Parameters.Add(new SqlParameter("@procedureDate",
dateProcedureDate));

sqlDataAdapter1 = new SqlDataAdapter(cmd);

sqlDataAdapter1.Fill(dataSet11);

sampleDataGrid.DataBind();

...

Below is the stored procedure:

CREATE PROCEDURE proc_GetSampleAss
(@patientCode varchar(15),
@procedureDate datetime,
@usrNo int
)

AS

SELECT tblPatient.pntUnitID as 'Patient Code',
CONVERT(varchar, tblAssessment.assDate, 103) as 'Assessment Date',
tblLesion.bodyRegion as 'Body Region',
tblLesion.lesLocation as 'Lesion Location',
tblSample.lesHistology as 'Histology',
tblSample.sampleNo

FROM tblPatient, tblSample, tblAssessment, tblLesion

WHERE tblPatient.patientNo = tblSample.patientNo
AND tblPatient.patientNo = tblLesion.patientNo
AND tblPatient.patientNo = tblAssessment.patientNo
AND tblLesion.lesNo = tblSample.lesNo
AND tblPatient.pntUnitID = @patientCode
AND tblAssessment.assDate = @procedureDate
AND tblPatient.usrNo = @usrNo
GO

This sp is used for SqlDataAdapter1 and in the cmd = new SqlCommand
statement. Is this necessary?

The problem I am currently having is that no rows are being found and
displayed in the datagrid. One row should be shown according to Query
Analyser given the parameters i'm supplying.

Anyone know how to get this to work?

Thanks

Assimalyst
Thu Sep 01 10:26:11 CDT 2005

I've managed to get it to work, changed

sqlDataAdapter1 = new SqlDataAdapter(cmd);
to
sqlDataAdapter1.SelectCommand = cmd;

However, I'm now having a problem with the update, it isn't finding any
rows as changed. Here's the code:

try
{
DataSet dsSampleChanges = this.dataSet11.GetChanges();
if (dsSampleChanges != null)
{
int modifiedRows = this.sqlDataAdapter1.Update(dsSampleChanges);
messageLbl2.Text = "Data updated successfully";
this.dataSet11.AcceptChanges();
}
else
{
messageLbl2.Text = "No changes to save";
}
}

But even if I change the value in the histology row it skips straight
to the else section.

The stored procedure within SqlDataAdapter1 is as follows:

CREATE PROCEDURE proc_UpdateSampleAss
(@histology varchar(50),
@sampleNo int
)

AS

UPDATE tblSample
SET lesHistology = @histology
WHERE sampleNo = @sampleNo
GO

Parameter @histology has source column set to 'Histology' and @sampleNo
has source column 'ID' set using the wizard.

A column sampleNo does not appear in the dropDownMenu for source
column, why? Could this be the problem?