Datagrid update changes all values 1

[bigmelon]

When I try to Update my datagrid, instead of just updating the affected rows, it changes all the rows to the values in the row i updated. Here is my code:

DA.UpdateCommand = New SqlCommand
With DA.UpdateCommand
.Connection = cnn
.CommandType = CommandType.StoredProcedure
.CommandText = "CardioUP"
.Parameters.Clear()
.Parameters.Add("@Surg", Surg)
.Parameters.Add("@pleg_time", SqlDbType.DateTime, 8, "Time")
'.Parameters("@Pleg_time").SourceColumn = "Time"
'.Parameters("@Pleg_time").Value = dgCardio.Item(dgCardio.CurrentRowIndex, 0)
.Parameters.Add("@Type", SqlDbType.VarChar, 50, "Type")
'.Parameters("@Type").SourceColumn = "Type"
'.Parameters("@Type").Value = dgCardio.Item(dgCardio.CurrentRowIndex, 1)
.Parameters.Add("@Route", SqlDbType.VarChar, 50, "Route")
'.Parameters("@Route").SourceColumn = "Route"
'.Parameters("@Route").Value = dgCardio.Item(dgCardio.CurrentRowIndex, 2)
.Parameters.Add("@Temp", SqlDbType.Float, 8, "Temp")
'.Parameters("@Temp").SourceColumn = "Temp"
'.Parameters("@Temp").Value = dgCardio.Item(dgCardio.CurrentRowIndex, 3)
.Parameters.Add("@K", SqlDbType.VarChar, 10, "K+")
'.Parameters("@K").SourceColumn = "K+"
'.Parameters("@K").Value = dgCardio.Item(dgCardio.CurrentRowIndex, 4)
.Parameters.Add("@Mg", SqlDbType.VarChar, 10, "Mg++")
'.Parameters("@Mg").SourceColumn = "Mg++"
'.Parameters("@Mg").Value = dgCardio.Item(dgCardio.CurrentRowIndex, 5)
.Parameters.Add("@FlowRate", SqlDbType.VarChar, 10, "Flow Rate")
'.Parameters("@FlowRate").SourceColumn = "Flow Rate"
'.Parameters("@FlowRate").Value = dgCardio.Item(dgCardio.CurrentRowIndex, 6)
.Parameters.Add("@Dose", SqlDbType.VarChar, 10, "Dose cc's")
'.Parameters("@Dose").SourceColumn = "Dose cc's"
'.Parameters("@Dose").Value = dgCardio.Item(dgCardio.CurrentRowIndex, 7)
.Parameters.Add("@Cor", SqlDbType.VarChar, 10, "Cor. Sinus")
'.Parameters("@Cor").SourceColumn = "Cor. Sinus"
'.Parameters("@Cor").Value = dgCardio.Item(dgCardio.CurrentRowIndex, 8)
End With

This is the code for Ok button:

Private Sub btnOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOk.Click
Try
DA.Update(DS, "tstDataMember")
DS.AcceptChanges()

Catch ex As Exception
MsgBox(ex.ToString)
End Try

MsgBox("Ok")
End Sub

This is an example of the datagrid before hand:
Time Type Route Temp Etc
1:11 A B C D
2:22 B Q T H

Then if i change the H to a J the datagrid updates like this:
Time Type Route Temp Etc
2:22 B Q T J
2:22 B Q T J

Any Ideas? I have been stuck on this forever. Any help or suggestions would be greatly appreciated.

Thanks,
Jeremy

 

[chrissie1]

i think this

dgCardio.CurrentRowIndex

should be this

dgCardio.CurrentRowIndex-1

but why dont you use the sqlcommandbuilder??



Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
What a wonderfull world - Louis armstrong

 

[bigmelon]

No, I am not even using those lines right now. I had everything else set up so i didn't figure it would be that hard. Plus i have never used the sqlcommandbuilder.

thanks,
Jeremy

 

[RiverGuy]

You're not exposing the SQL in your stored procedure. If you only want to update Row 1, somehow the text "Where RowColumn = 1" must be passed to the database server. If you are just updating without a Where, all of your rows will get updated.

 

[chrissie1]

Plus i have never used the sqlcommandbuilder

its a lot easier then what you are trying to do now

dim cbu as new sqlcommandbuilder
dim da as new sqldataadapter
dim con as new sqlconnection
dim com as new sqlcommand
dim dt as new datatable

--- open connection here

com.connection = con
com.commandtext = "select * fomr table" -- where table has a primary index and is included in the select
da.selectcommand = com
cbu.adapter = da
cbu.quotesuffix = "]"
cbu.quotepreffix = "["
da.updatecommand = cbu.getupdatecommand
da.deletecommand = cbu.getdeletecommand
da.insertcommand = cbu.getinsertcommant
da.fill(dt)
da.fillschema(dt,source)
[/quote]



Christiaan Baes
Belgium

[b]If you want to get an answer read this FAQ faq796-2540[/b]
[i]What a wonderfull world - Louis armstrong[/i]

 

[bigmelon]

Riverguy,

Would i include this in the stored procedure or in the vb code somehow?

Thanks,

Jeremy

 

[RiverGuy]

No. I meant that we don't know what your stored procedure does.....so it would have to be coded into it.

 

[bigmelon]

Here is my stored proc:

CREATE PROCEDURE "CardioUP"
@Surg as varchar(10),
@Pleg_time as datetime,
@Type as varchar(30),
@Route as varchar(35),
@Temp as float(8),
@K as varchar(10),
@Mg as varchar(10),
@FlowRate as varchar(10),
@Dose as varchar(10),
@Cor as varchar(10)
AS
UPDATE plegia SET
pleg_time=@pleg_time,
type=@Type,
route=@Route,
[temp]=@Temp,
k=@K,
mg=@Mg,
rate=@FlowRate,
dose_cc=@Dose,
coronarysinus=@Cor
Where
surg_proc_number = @Surg


GO

How could I code that in there?

Thanks,
Jeremy

 

[RiverGuy]

Yes, your sproc looks right. Is it possible your code is in some sort of loop? It looks as though you are setting the values for the stored procedure....instead of letting the .Update() method do the work for you.

 

[bigmelon]

No, what you said is right. All of my values in the datagrid are from the same surg_proc_number, because it is only 1 patient displayed, I just don't know how to pinpoint the row that they edited. Any Ideas?

Thanks,
Jeremy

 

[RiverGuy]

Ohhh, you need to add logic to only update where your primary key in your table is equal to the one being edited.

 

[bigmelon]

Yea I'm finally getting it figured out. Thanks for all your help!

Jeremy