Update sql Statement based on a GridView field

Hello,
I am trying run an update statement based on a current GridView.  The reason is that if the sql table data is updated while in the current GridView, I only want to set an update for each record of the currently selected data.
Insert Statement:
using (SqlCommand command = new SqlCommand())
                        {command.Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
command.CommandText = @"insert into [Comm](CommType, StudentID)
                                                    values(@CommType, @StudentID)";
command.Parameters.Add("@CommType", SqlDbType.VarChar, 16).Value = "Emailed";
command.Parameters.Add("@StudentID", SqlDbType.VarChar, 9).Value = **How do I add a GridView Column Name here for each record in my GridView?***;                           
                            command.Connection.Open();
                            command.ExecuteNonQuery();
                            command.Parameters.Clear();
                            command.Connection.Close();

Open in new window

Comm Table:
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[CommType] [varchar](16) NULL,
	[StudentID] [varchar](9) NULL,

Open in new window

The field I want to grab from the GridView is:
asp:BoundField DataField="StudentID"

Is is possible to do this, or is there a better way to achieve this?

Thank you for any assistance.
javierpdxAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Vadim RappConnect With a Mentor Commented:
This is not a direct answer, since I don't fully understand the problem... rather considerations.

1. The title of your question says it's about UPDATE sql statement, but the code has only INSERT. Confused.

2. Your code implies that you want to indicate in the database that you have sent email to each selected student. If so, then I think you will do better by making it as "transactional" as possible, i.e. you want to update each student not in the gridview, but in the routine that actually sends the email, after it has successfully sent it. It's in fact the opposite to  CodeCruiser's suggestion above, which is to collect all the id's and then update them all in one operation.

Here's one example where this can be important. Let's say, the user has selected 10 students to send email to. The code that sends email successfully sends to 9, but 10th can't resolve the recipient (assuming that it tries to), because that one student is no longer with the institution. If updating is based on the selection in the grid, it will mark what it should have done, rather than what it has actually done.

Another: you are sending to 100 recipients, and half way through the application has crashed. Unless you update the records per one, you either will have noone marked, or everyone.
0
 
k_murli_krishnaConnect With a Mentor Commented:
See if this helps:

UPDATE       CommTable ct1
SET               ct1.CommType = @CommType, ct1.StudentID = @StudentID
WHERE      ct1.ID = (SELECT ct2.ID
                           FROM CommTable ct2
                           WHERE ct2.ID = ct1.ID)
AND              ct1.CommType = (SELECT ct2.CommType
                                       FROM CommTable ct2
                                       WHERE ct2.ID = ct1.ID)
AND       ct1.StudentID = (SELECT ct2.StudentID
                                     FROM CommTable ct2
                                     WHERE ct2.ID = ct1.ID);
0
 
CodeCruiserConnect With a Mentor Commented:
>command.Parameters.Add("@StudentID", SqlDbType.VarChar, 9).Value = **How do I add a GridView Column Name here for each record in my GridView?***;

So you have to do this for each record in grid.

One option is to loop through the rows, create a CSV of IDs and use that with IN clause.

For Each Row As GridViewRow In grid.Rows
   ID = GridViewRow.Cells(index).Text
Next
0
 
javierpdxAuthor Commented:
Sorry for the delay in responding to your suggestions.  I have been away due to the holiday break.  I will test this over the weekend and respond as soon as I am able.  Thanks.
0
 
javierpdxAuthor Commented:
Thanks for the expert advise.  I appreciate it.

This ended up working:

command.CommandText = @"insert into [Communication](CommType, ID)
values(@CommType, @ID)"
command.Parameters.Add("@CommType", SqlDbType.VarChar, 16).Value = Row.Cells[12].Text;
command.Parameters.Add("@ID", SqlDbType.VarChar, 9).Value = "0" + Row.Cells[2].Text;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.