Updating a database

[mike55]

Hi all

 

Am attempting to update a database table after making changes to the table by using the following 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:

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.

[Afraits]

You don't appear to be defining the necessary update,insert & delete commands and attaching them to the data adapter, plus I can't 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, I'l try to dig out a couple of examples for you in the mean time.

[mike55]

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

[Afraits]

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 i'm not sure what goes on. To me it appears that within the .update call each row is checked then updated individually but I'm not sure.

 

Insert command example

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 don't know.

 

Hope that helps.