Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

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.
0
javierpdx
Asked:
javierpdx
3 Solutions
 
k_murli_krishnaCommented:
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
 
CodeCruiserCommented:
>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
 
Vadim RappCommented:
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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now