Hi, Im using vb.net 2010 ans sql server express.
I have a dataset that is based on a View (which is only 2 tables linked by a common column.
But when I try to update the datatset back to the database I get the error msg cant update a multi table dataset!
So on researching this I find I have to create my own update command using slqcommandbuilder.
So I have made an attempt at this see code below, but I am unsure how to assign the changed values in my dataset/datagridview to the Update parameters!!!!
Obviously I have the parameters wrong but I do'nt know what to put in their place!
I have a dataset that is based on a View (which is only 2 tables linked by a common column.
But when I try to update the datatset back to the database I get the error msg cant update a multi table dataset!
So on researching this I find I have to create my own update command using slqcommandbuilder.
So I have made an attempt at this see code below, but I am unsure how to assign the changed values in my dataset/datagridview to the Update parameters!!!!
Obviously I have the parameters wrong but I do'nt know what to put in their place!
VB.NET:
cmd = New SqlCommand("SELECT * FROM VwStaff WHERE AreaID=@AreaID AND (LeaveDate Between DATEADD(Day,-27,@PPEndDate) AND @PPEndDate) ORDER BY HomeHelp", conn)
cmd.Parameters.Add("@AreaID", SqlDbType.Int).Value = AreaID
cmd.Parameters.Add("@PPEndDate", SqlDbType.Date).Value = CDate(Me.cbPPEndDate.SelectedItem.ToString)
Me.da1 = New SqlDataAdapter(cmd)
Me.da1.FillSchema(ds, SchemaType.Source, "Staff")
Me.da1.Fill(ds, "Staff")
'...Build SQL Update Command
Me.da1.UpdateCommand = New SqlCommand
Me.da1.UpdateCommand.CommandText = "UPDATE TblStaffLeave SET PersonnelNo = @PersonnelNo, LeaveDate = @LeaveDate, LeaveReason = 1, NoHours = @NoHours, Comment = @Comment, AddedBy = @AddedBy WHERE (ID = @ID);"
Me.da1.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ID", System.Data.SqlDbType.Int, "ID"))
Me.da1.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@PersonnelNo", System.Data.SqlDbType.Int, "PersonnelNo"))
Me.da1.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@LeaveDate", System.Data.SqlDbType.Date, "LeaveDate"))
Me.da1.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@NoHours", System.Data.SqlDbType.Int, "NoHours"))
Me.da1.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Comment", System.Data.SqlDbType.NVarChar, "Comment"))
Me.da1.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@AddedBy", System.Data.SqlDbType.NVarChar, "AddedBy"))