[RESOLVED] SQL Datagrid updating

[bvote]

Ok im using a vb form with a datagrid loaded into it

The datagrid loads a button, Firstname, and time into it. What i want to do is when i hit the button on that row i want the new time to update a row with the new information. This is the code i have so far. It runs through but nothing gets updated. I think my problem is that its not on the right row or it does not know where it is. I tried finding help on this but everything ive read people use this in aspx but i want to use it in an actual vb form.

Private Sub dvg1_signout(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dvg1.CellContentClick

Dim datetime As Date
datetime = Now()
Dim connection As New SqlClient.SqlConnection(connection string)
connection.Open()

Dim update As New SqlClient.SqlCommand("UPDATE dbo.guestlog SET outdatetime = @outdatetime WHERE firstname = @firstname AND outdatetime is NULL", connection)

update.Connection = connection
update.Parameters.AddWithValue("@outdatetime", datetime)
update.Parameters.AddWithValue("@firstname", "firstname")

update.ExecuteNonQuery()
connection.Close()
Close()

End Sub

*edit*
using SQL Server 2005
VB 2005
Also no error messages

[si_the_geek]

Your code is perfectly valid for a form, I have no idea why you only found aspx results.

You can find out how many records were affected by your , I think via an argument to ExecuteNonQuery (or perhaps a property of the command). Based on what you have said it will almost certainly be 0, but you should check anyway.

Assuming that returns 0, the next step is to add a Watch for update.Parameters , and check what the values are - you may be surprised.

If that is valid too, check the data in the table to ensure that there is at least one record that exactly matches both conditions in your Where clause (including the parameter value).

[bvote]

Let me show you the grid form its attached.

ok so now this thing returns everything correctly from the sql database. The button on the left under the signout section is suppose to run the code above. after i click the button it does initiate the close command. But if i also double click one of the cells it closes as well. I think im missing something. more and i think my button control is responding to any cell instead of just the button. I think because of this

Handles dvg1.CellContentClick

[si_the_geek]

It should respond to any cell, because that is what Handles dvg1.CellContentClick implies.

If you only want it to work for certain columns, add an If statement which checks the column (presumably e will have something for you to check against).

[bvote]

yeah that is what i figured but im ok with it now. I know that the time is being grabbed right since it list the time within the value given. But since the user is not putting in a first name do i have to use something to telll it to update the cell on the selected row. I have seen c# versions of this but they were refering to an aspx.

[bvote]

UPDATE
Ok so now i have set it up to where it updates the time but since it does not grab the correct row it updates all that the inital query shows so if it shows 3 people it will update all of them on either the button click or if i double click one of the cells

I need something like:

UPDATE dbo.guestlog SET outdatetime = @outdatetime WHERE "row selected" and outdatetime is NULL", connection

I think that would be something but have no idea how to write it

Private Sub dvg1_signoutContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dvg1.CellContentClick

Dim datetime As Date
datetime = Now()
Dim connection As New SqlClient.SqlConnection("server=scan;uid;pwd;database=guestcheck;trusted_connection=True")
connection.Open()

Dim update As New SqlClient.SqlCommand("UPDATE dbo.guestlog SET outdatetime = @outdatetime WHERE outdatetime is NULL", connection)

update.Connection = connection
update.Parameters.AddWithValue("@outdatetime", datetime)

update.ExecuteNonQuery()
connection.Close()
Close()

End Sub

[bvote]

i think i need to do something like
Gridview.CurrentRow.(something)
to select that row to update only is that all im missing or do i have to add something to my update parameter

what if i was able to grab a specific value out of the gridview.currentrow.(get indatetime) = indatetime

then i used my update parameter as

Dim update As New SqlClient.SqlCommand("UPDATE dbo.guestlog SET outdatetime = @outdatetime WHERE outdatetime is NULL AND indatetime = @indatetime", connection)

update.Parameters.AddWithValue("@indatetime", indatetime)

Does anyone know? Im trying all sorts of stuff but still no luck yet

[si_the_geek]

I need something like:

UPDATE dbo.guestlog SET outdatetime = @outdatetime WHERE "row selected" and outdatetime is NULL", connection

Indeed you do, and there are three steps to that.

The most important is to find something about the record which allows you to uniquely identify it. Based on what I have seen, this appears to be a combination of outdatetime is NULL and the value of FirstName. However, this assumes that each user will always sign out properly - so you may also want to specify indatetime (either as the grid value, or the the highest for that user). If the table has a Primary Key (which is almost always a very good idea) you should simply use that.

The next step is to get the relevant values from the grid. You might be able to get that from the e argument, or you may need to use the properties of dvg1

Finally alter your SQL statement to use those conditions, and the parameters to use what you got from the grid.

[bvote]

Private Sub dvg1_signoutContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dvg1.CellContentClick
        Dim datetime As DateTime
        datetime = Now()
 
        Dim fname As String
        fname = dvg1.Rows(e.RowIndex).Cells(2).Clone.ToString()
 
        dvg1.Columns(4).Visible = True
 
        Dim lname As String
        lname = dvg1.Rows(e.RowIndex).Cells(3).Clone.ToString()
 
 
        Dim connection As New SqlClient.SqlConnection("server=scan;database=guestcheck;trusted_connection=True")
        connection.Open()
 
        Dim update As New SqlClient.SqlCommand("UPDATE dbo.guestlog SET outdatetime = @outdatetime WHERE outdatetime is NULL and firstname = '" & fname & "'AND lastname = '" & lname & "'", connection)
 
        update.Connection = connection
        update.Parameters.AddWithValue("@outdatetime", datetime)
 
        update.ExecuteNonQuery()
        connection.Close()
        Close()

This is what i have so far i do have another name in which to reference to but still no luck it is back to not going to the proper row. It is not returning the correct first name lastname. Also i dont know if my sub is correct since i only want this to run if they hit the button. But i should be able to do that with a if then If dvg1.Columns(e.ColumnIndex).Name = "signout" Then. Thanks for the help si


one more question how do i put text into the sign out button?

Another note is that users do not enter data they just have to hit the button next to there name

[bvote]

figured out how to add text to the button

and the button

If e.RowIndex < 0 OrElse Not e.ColumnIndex = _
dvg1.Columns("Sign Out").Index Then Return

[si_the_geek]

Rather than appending fname and lname to your SQL string, you should use parameters for them.

With the method you have used, if there are any special characters in them (such as ' in the surname O'Brien) you will get errors and/or bugs.

It is not returning the correct first name lastname.

Is e.RowIndex the right thing to use?

It could be, but I don't use .Net enough to know.

[bvote]

Private Sub dvg1_signoutContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dvg1.CellContentClick
        If e.RowIndex < 0 OrElse Not e.ColumnIndex = _
                    dvg1.Columns("Sign Out").Index Then Return
 
        Dim datetime As DateTime
        datetime = Now()
 
        Dim fname As String = dvg1.Rows(e.RowIndex) _
                    .Cells(1).Value
 
        Dim lname As String = dvg1.Rows(e.RowIndex) _
                    .Cells(2).Value
        
        Dim connection As New SqlClient.SqlConnection("server=scan;database=guestcheck;trusted_connection=True")
        connection.Open()
 
        Dim update As New SqlClient.SqlCommand("UPDATE dbo.guestlog SET outdatetime = @outdatetime WHERE outdatetime is NULL and firstname = '" & fname & "'AND lastname = '" & lname & "'", connection)
 
        update.Connection = connection
        update.Parameters.AddWithValue("@outdatetime", datetime)
 
        update.ExecuteNonQuery()
        connection.Close()
        Close()
 
    End Sub

Got it