Problem in adding Multiple rows in a datagridview using dataAdapter

[mdfayazi]

I am using dataadapter to Add,Update,Delete rows in a datagridview.Update and delete are working but while adding a rows the problem comes.When I add more than one row only first row values are copied all other rows. Can anybody help me what to do.
Dim myBuilder As SqlCommandBuilder = New SqlCommandBuilder(adapterGetBudget)

Dim dtdate As Date = Date.Now

Dim cmd As New SqlCommand()

With cmd

.CommandType = CommandType.StoredProcedure

.CommandText = "sp_AddDetailsBudget"

.Connection = Conn

.Parameters.Add("@User", SqlDbType.VarChar).Value = currUserName.ToString

.Parameters.Add("@Date", SqlDbType.SmallDateTime).Value = dtdate.Date.ToString

.Parameters.Add("@Time", SqlDbType.VarChar).Value = dtdate.ToShortTimeString.ToString

.Parameters.Add("@Lock", SqlDbType.Bit).Value = 0

.Parameters.Add("Description", SqlDbType.VarChar).Value = txtBudgetDesc.Text.Trim

.Parameters.Add("@BudgetID", SqlDbType.BigInt).Value = Me.txtBudgetID.Text

.Parameters.Add("@Amount", SqlDbType.Real).Value = Me.dgbudgetdet.Rows(0).Cells(1).Value

.Parameters.Add("@AccountID", SqlDbType.BigInt).Value = Me.dgbudgetdet.Rows(0).Cells(2).Value

.Parameters.Add("@CostCenter", SqlDbType.VarChar).Value = Me.dgbudgetdet.Rows(0).Cells(3).Value

.Parameters.Add("@Notes", SqlDbType.VarChar).Value = Me.dgbudgetdet.Rows(0).Cells(4).Value

End With

If Conn.State = ConnectionState.Open Then Conn.Close()

Conn.Open()

adapterGetBudget.InsertCommand = cmd

If (ds.HasChanges) Then
adapterGetBudget.InsertCommand
adapterGetBudget.UpdateCommand = myBuilder.GetUpdateCommand()
adapterGetBudget.DeleteCommand = myBuilder.GetDeleteCommand()
adapterGetBudget.Update(ds)
End If
ds.AcceptChanges()

Thanks in Advance

[nateraaaa]

I am using dataadapter to Add,Update,Delete rows in a datagridview.Update and delete are working but while adding a rows the problem comes.When I add more than one row only first row values are copied all other rows. Can anybody help me what to do.
Dim myBuilder As SqlCommandBuilder = New SqlCommandBuilder(adapterGetBudget)

Dim dtdate As Date = Date.Now

Dim cmd As New SqlCommand()

With cmd

.CommandType = CommandType.StoredProcedure

.CommandText = "sp_AddDetailsBudget"

.Connection = Conn

.Parameters.Add("@User", SqlDbType.VarChar).Value = currUserName.ToString

.Parameters.Add("@Date", SqlDbType.SmallDateTime).Value = dtdate.Date.ToString

.Parameters.Add("@Time", SqlDbType.VarChar).Value = dtdate.ToShortTimeString.ToString

.Parameters.Add("@Lock", SqlDbType.Bit).Value = 0

.Parameters.Add("Description", SqlDbType.VarChar).Value = txtBudgetDesc.Text.Trim

.Parameters.Add("@BudgetID", SqlDbType.BigInt).Value = Me.txtBudgetID.Text

.Parameters.Add("@Amount", SqlDbType.Real).Value = Me.dgbudgetdet.Rows(0).Cells(1).Value

.Parameters.Add("@AccountID", SqlDbType.BigInt).Value = Me.dgbudgetdet.Rows(0).Cells(2).Value

.Parameters.Add("@CostCenter", SqlDbType.VarChar).Value = Me.dgbudgetdet.Rows(0).Cells(3).Value

.Parameters.Add("@Notes", SqlDbType.VarChar).Value = Me.dgbudgetdet.Rows(0).Cells(4).Value

End With

If Conn.State = ConnectionState.Open Then Conn.Close()

Conn.Open()

adapterGetBudget.InsertCommand = cmd

If (ds.HasChanges) Then
adapterGetBudget.InsertCommand
adapterGetBudget.UpdateCommand = myBuilder.GetUpdateCommand()
adapterGetBudget.DeleteCommand = myBuilder.GetDeleteCommand()
adapterGetBudget.Update(ds)
End If
ds.AcceptChanges()

Thanks in Advance

Are you changing the parameters passed to the Insert proc for each row you are trying to add? It sounds like you keep passing the same parameters for each row. Try looping through the rows you want to add and setting the parameters with the row[columnname].Value for each parameter. Let us know if this works for you.

Nathan

[mdfayazi]

Thanks Me nateraaaa . I understood that I am passing only first row parameters . I tried your suggestion by keeping parameters in loop but the result is same. this is my code below . I have to make any changes.Please suggest me what changes.
Dim myBuilder As SqlCommandBuilder = New SqlCommandBuilder(adapterGetBudget)
If (ds.HasChanges(DataRowState.Added)) Then
Dim rowcount As Integer
Dim ds_changes As DataSet = ds.GetChanges(DataRowState.Added)
rowcount = ds_changes.Tables(0).Rows.Count
Dim i As Integer
For i = 0 To rowcount - 1
Dim dtdate As Date = Date.Now
Dim cmd As New SqlCommand()
With cmd
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_AddDetailsBudget"
.Connection = Conn
.Parameters.Add("@User", SqlDbType.VarChar).Value = currUserName.ToString
.Parameters.Add("@Date", SqlDbType.SmallDateTime).Value = dtdate.Date.ToString
.Parameters.Add("@Time", SqlDbType.VarChar).Value = dtdate.ToShortTimeString.ToString
.Parameters.Add("@Lock", SqlDbType.Bit).Value = 0
.Parameters.Add("Description", SqlDbType.VarChar).Value = txtBudgetDesc.Text.Trim
.Parameters.Add("@BudgetID", SqlDbType.BigInt).Value = Me.txtBudgetID.Text
.Parameters.Add("@Amount", SqlDbType.Real).Value = Me.dgbudgetdet.Rows(i).Cells(1).Value
.Parameters.Add("@AccountID", SqlDbType.BigInt).Value = Me.dgbudgetdet.Rows(i).Cells(2).Value
.Parameters.Add("@CostCenter", SqlDbType.VarChar).Value = Me.dgbudgetdet.Rows(i).Cells(3).Value
.Parameters.Add("@Notes", SqlDbType.VarChar).Value = Me.dgbudgetdet.Rows(i).Cells(4).Value
End With
If Conn.State = ConnectionState.Open Then Conn.Close()
Conn.Open()
adapterGetBudget.InsertCommand = cmd
adapterGetBudget.Update(ds_changes)
Next

ds.AcceptChanges()