Updating a database

mike55

mike55

Well-known member
Joined
Mar 26, 2004
Messages
726
Location
Ireland
Hi all

Am attempting to update a database table after making changes to the table by using the following code:
Code: 
Public Function UpdDataSet(ByVal memberID As Int16, ByVal GroupID As Int16, ByVal OrgID As String, ByVal dataset As DataSet)
        Dim daDataAdapter As New SqlClient.SqlDataAdapter
        Dim nRow As DataRow
           nRow = dataset.Tables("GroupMembership").NewRow
            nRow("Member_ID") = memberID
                    nRow("Group_ID") = GroupID
                    nRow("Org_ID") = OrgID
                    dataset.Tables("GroupMembership").Rows.Add(nRow)

                    Dim cncon As New SqlConnection(clsConnection.osqlStr)
                    cncon.Open()
                    daDataAdapter.Update(dataset, "GroupMembership")

                    If cncon.State = ConnectionState.Open Then
                        cncon.Close()
                    End If
    End Function

I am however getting the following error:
Code: 
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.InvalidOperationException: Update requires a valid InsertCommand when passed DataRow collection with new rows. at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at SureTxtWebService.dsMember.UpdDataSet(Int16 memberID, Int16 GroupID, String OrgID, DataSet dataset) in C:\SureTxtProject\SureTxtWebSolution\SureTxtWebService\Modules\dsMember.vb:line 87 at SureTxtWebService.wsMember.UpdDataSet(Int16 memberID, Int16 groupID, String orgID, DataSet dsRecords) in C:\SureTxtProject\SureTxtWebSolution\SureTxtWebService\wsMember.asmx.vb:line 50 --- End of inner exception stack trace ---

Any suggestions??

Mike55.
 
You dont appear to be defining the necessary update,insert & delete commands and attaching them to the data adapter, plus I cant see where you are linking the connection to the adapter.

Basically when adapter.update is called each changed row in the table to be updated is checked to see whether it is updated, inserted or deleted, the appropriate SQL command connected to the adapter is then fired to update the row in the DB.

There is a command builder class that can do this for you, otherwise you have to build the paramaterised commands yourself. Check the help files for how to do this and if you have any other questions let me know, Il try to dig out a couple of examples for you in the mean time.
 
From what I understand, I am updating the database table one row at a time. However, is it possible to do a batch update of multiple rows with just one communication with my sql database?

Mike55
 
The dataadapter.update will update all rows that have changed since the last update with the one call (if thats what you are asking re the batch update) - however internally im not sure what goes on. To me it appears that within the .update call each row is checked then updated individually but Im not sure.

Insert command example
C#: 
try
{
//overloaded creation of data adapter (to avoid connection)
SqlDataAdapter Adapter=new SqlDataAdapter("SELECT * FROM Pets",
	"Data Source=K2-SQL2;Initial Catalog=Northwind;Integrated Security=SSPI");
//add in insert command
Adapter.InsertCommand=new SqlCommand("INSERT INTO Pets (PetName,Breed,IQ)    VALUES (@Petname,@Breed,@IQ)",
Adapter.SelectCommand.Connection);
//add in parameters for the command
Adapter.InsertCommand.Parameters.Add("@Breed",SqlDbType.VarChar ,50,"Breed");
Adapter.InsertCommand.Parameters.Add("@Petname",SqlDbType.VarChar ,50,"PetName");
Adapter.InsertCommand.Parameters.Add("@IQ",SqlDbType.SmallInt,2,"IQ");
//create & fill dataset
Adapter.Update( petsTable);
					
}
catch(Exception Ex)
{
Console.WriteLine("There was a serious problem....");
Console.WriteLine(Ex.ToString());

}

This assumes Pets table containing newly added rows, similar processes would be required for the UPDATE and DELETE commands to handle rows in the dataset that exist in the DB and have been changed or deleted. ADO.NET may handle this with 1 communication but thats the internal workings of it which I dont know.

Hope that helps.
 
Back
Top