Solution to 'cant update a multi table dataset error'

divjoy

Well-known member
Joined
Aug 25, 2013
Messages
159
Programming Experience
1-3
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!
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"))
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,723
Location
Sydney, Australia
Programming Experience
10+
Firstly, there's no point calling FillSchema and Fill. Fill creates the schema anyway.

As for the issue, can you show the code that saves the data and provide the exception type and the exact error message, if what you already provided is not it.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,723
Location
Sydney, Australia
Programming Experience
10+
By the way, there is no SqlParameter constructor with a signature matching the way you're calling it. I'm not sure whether that's related to your issue or not but this is wrong:
Me.da1.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@AddedBy", System.Data.SqlDbType.NVarChar, "AddedBy"))
You must have Option Strict Off for that to even compile so I suggest that you turn Option Strict On and leave it On for every project in future. There's only one constructor with three parameters and the third of those is type Integer, not String. If you want to specify a source column name then you have to provide at least four arguments, with the third one being the size of the data. If, for instance, your database column is type nvarchar(50) then the size is 50:
Me.da1.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@AddedBy", System.Data.SqlDbType.NVarChar, 50, "AddedBy"))
Also, it's pointless using the SqlParameter constructor there when the Add method is overloaded and has the same parameters:
Me.da1.UpdateCommand.Parameters.Add("@AddedBy", System.Data.SqlDbType.NVarChar, 50, "AddedBy")
You really only have to specify the actual size when dealing with variable-size types, e.g. varchar and varbinary. For fixed-size types, e.g. int, you can specify the correct size if you want but, unless you know the correct size for every type, I'd suggest just using zero each time. The system will still use the correct size.
 

divjoy

Well-known member
Joined
Aug 25, 2013
Messages
159
Programming Experience
1-3
Hi,

Your right the constructor is wrong I'm just looking for a simple example of creating an update command for a multi table dataset that I can use.

Regards
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,723
Location
Sydney, Australia
Programming Experience
10+
I've never had any issue saving changes from a DataSet with multiple DataTables in it so, as far as I'm concerned, any example of saving data from a DataSet is one you can use. That's why I asked that you provide the code you're using to save. Did you think I asked that for no reason so not providing your code was a good idea?
 

divjoy

Well-known member
Joined
Aug 25, 2013
Messages
159
Programming Experience
1-3
Hi,

I will do when I get a chance, my code is on a different computer, in the meantime I was hoping you'd have come across the problem before.

No worries I have a work around in the meantime.

Regards
 
Top Bottom