CODEWITHSUNDEEP
sqlstored-procedures
Stalli
Stalli

Reputation: 394

SQL UPDATE stored procedure from Visual studio

I have an UPDATE stored procedure that works good from SQL Server query:

GO
ALTER PROCEDURE [dbo].[UpdateMedicalCard]
@RecordingCode int,
@BeginTreatmentDate date,
@EndTreatmentDate date,
@MainDiagnosis nchar(100),
@AttendantDiagnosis nchar(100),
@TreatmentResult nchar(50)
AS
BEGIN
    UPDATE MedicalCard
    SET BeginTreatmentDate = @BeginTreatmentDate,
        EndTreatmentDate = @EndTreatmentDate,
        MainDiagnosis = @MainDiagnosis,
        AttendantDiagnosis = @AttendantDiagnosis,
        TreatmentResult = @TreatmentResult
    WHERE RecordingCode = @RecordingCode
END

But when i call this procedure from Visual studio it does not update. 

SqlConnection connection = new SqlConnection();
            connection.ConnectionString = @"Data Source=.;Initial Catalog=Policlinic;Integrated Security=SSPI";

            connection.Open();

            SqlCommand myCommand = connection.CreateCommand();
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.CommandText = "UpdateMedicalCard";

            myCommand.Parameters.Add("@RecordingCode", System.Data.SqlDbType.Int);
            myCommand.Parameters["@RecordingCode"].Value = dataGridView1.CurrentRow.Cells[0].Value;

            myCommand.Parameters.Add("@BeginTreatmentDate", System.Data.SqlDbType.Date);
            myCommand.Parameters["@BeginTreatmentDate"].Value = dataGridView1.CurrentRow.Cells[3].Value;

            myCommand.Parameters.Add("@EndTreatmentDate", System.Data.SqlDbType.Date);
            myCommand.Parameters["@EndTreatmentDate"].Value = dataGridView1.CurrentRow.Cells[4].Value;

            myCommand.Parameters.Add("@MainDiagnosis", System.Data.SqlDbType.NChar);
            myCommand.Parameters["@MainDiagnosis"].Value = "qwe";

            myCommand.Parameters.Add("@AttendantDiagnosis", System.Data.SqlDbType.NChar);
            myCommand.Parameters["@AttendantDiagnosis"].Value = dataGridView1.CurrentRow.Cells[6].Value;

            myCommand.Parameters.Add("@TreatmentResult", System.Data.SqlDbType.NChar);
            myCommand.Parameters["@TreatmentResult"].Value = dataGridView1.CurrentRow.Cells[7].Value;

            var dataAdapter = new SqlDataAdapter(myCommand);
            var dataTable = new DataTable();
            dataAdapter.Update(dataTable);
        connection.Close();

I think i do smth wrong at the last 4 rows. Help please.

Upvotes: 0

Views: 1926

Answers (1)

Justin Niessner
Justin Niessner

Reputation: 245499

Your command isn't going to return any result rows, so you don't need to use a DataTable or DataAdapter. You just need to call connection.ExecuteNonQuery() instead.

You may also want to double check that the data (specifically the dates, as they can be tricky since the field may or may not also store a time component depending on how the table is defined, match an existing row.

Upvotes: 2

Related Questions