Error in DataAdapter.Update() method in C#

Question

I am trying to do following Insert Command .But it is not working neither showing any exception. Can anybody tell me where I am doing wrong thing?

using System;
using System.Data;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Principal;

public partial class StoredProcedures
{
   [Microsoft.SqlServer.Server.SqlProcedure]
   public static void USP_tempSP()
   {   
       DataTable objDataTable = new DataTable();
       using (SqlConnection objConn = new SqlConnection("context connection=true"))
       {
           objConn.Open();
           SqlCommand objCmd = new SqlCommand();
           objCmd.CommandType = CommandType.Text;

           objCmd.Connection = objConn;
           objCmd.CommandText = "select * from temp"; 
           /*table Structure
            *CREATE TABLE [dbo].[temp]([empname] [varchar](20)  NULL,[createdon] [datetime] NULL,[emp_id] [varchar](4) NULL
           ) 
           */
           SqlDataAdapter objDataAdapter = new SqlDataAdapter(objCmd.CommandText, objConn);
           objDataAdapter.Fill(objDataTable);

           SqlCommand objCmd1 = new SqlCommand();
           objCmd1.CommandType = CommandType.Text;
           objCmd1.UpdatedRowSource = UpdateRowSource.None;
           objCmd.CommandText = "Insert into tempCopy " +
                               " values(@empname,@createdon)";
           /*table Structure
            *CREATE TABLE [dbo].[tempCopy]([empname] [varchar](20)  NULL,[createdon] [datetime] NULL
            ) 
           */
           objCmd.Parameters.Add("@empname", SqlDbType.VarChar, 20, objDataTable.Columns["empname"].ColumnName);
           objCmd.Parameters.Add("@createdon", SqlDbType.DateTime, 8, objDataTable.Columns["createdon"].ColumnName);

           SqlDataAdapter adpt = new SqlDataAdapter();
           adpt.InsertCommand = objCmd;
           adpt.UpdateBatchSize = objDataTable.Rows.Count;
           try
           {
               int recordsInserted = adpt.Update(objDataTable);
           }
           catch(Exception ex)
           {
               //
           }

           objConn.Close();
        }
    }
}

How do you know it's not throwing an exception? You're explicitly ignoring exceptions. Define "it's not working." What indication do you have that it's not working. How specifically does the observed behavior differ from the expected behavior? Where in the code does this difference occur? What are the states of the runtime values when that happens? — David, Oct 08 '12 at 11:57
for start, take out the comment in the `catch` block, and write/show the any exception that might occur, at the moment your just swallowing it. — cjb110, Oct 08 '12 at 11:59
So.... It's a table copy by giving an `Insert` to an `Update` command and passing a table which hasn't changed? anyone else confused? — James, Oct 08 '12 at 12:01
Thanks for immediate attention. It is not working means program is not inserting anything in intended table tempCopy.I am studying CLR stored procedures.I just wanted to how can I copy content of one table to other table both are having different column structure. — Sachin, Oct 08 '12 at 12:14
@Sachin: When you debug this, what are the values being inserted? Can you attach a SQL Profiler to the database and see the actual query being executed against it? — David, Oct 08 '12 at 12:15

score 0 · Accepted Answer · answered Oct 09 '12 at 08:38

I really think this is because you are using the update method on an table which is tracking it's own changes. The problem is that nothing has changed. Set AcceptChangesDuringFill = false so that the rows are marked as added to the table.

       objConn.Open();
       /* This doesn't really do anything.
       SqlCommand objCmd = new SqlCommand();
       objCmd.CommandType = CommandType.Text;

       objCmd.Connection = objConn;
       objCmd.CommandText = "select * from temp"; */
       /*table Structure
        *CREATE TABLE [dbo].[temp]([empname] [varchar](20)  NULL,[createdon] [datetime] NULL,[emp_id] [varchar](4) NULL
       ) 
       */
       SqlDataAdapter objDataAdapter = new SqlDataAdapter("select * from temp", objConn);
       //Make sure we don't mark the new rows as unchanged
       objDataAdapter.AcceptChangesDuringFill = false;
       objDataAdapter.Fill(objDataTable);

       SqlCommand objCmd1 = new SqlCommand();
       objCmd1.CommandType = CommandType.Text;
       objCmd1.UpdatedRowSource = UpdateRowSource.None;
       //This is supposed to be objCmd1 !!
       objCmd1.CommandText = "Insert into tempCopy " +
                           " values(@empname,@createdon)";
       /*table Structure
        *CREATE TABLE [dbo].[tempCopy]([empname] [varchar](20)  NULL,[createdon] [datetime] NULL
        ) 
       */
       //This is supposed to be objCmd1 !!
       objCmd1.Parameters.Add("@empname", SqlDbType.VarChar, 20, objDataTable.Columns["empname"].ColumnName);
       objCmd1.Parameters.Add("@createdon", SqlDbType.DateTime, 8, objDataTable.Columns["createdon"].ColumnName);

       SqlDataAdapter adpt = new SqlDataAdapter();
       //This is supposed to be objCmd1 !!
       adpt.InsertCommand = objCmd1;
       adpt.UpdateBatchSize = objDataTable.Rows.Count;
       try
       {
           int recordsInserted = adpt.Update(objDataTable);
       }
       catch(Exception ex)
       {
           //
       }

       objConn.Close();

I think this should work, you need to be more careful with your variable naming. objCmd is fairly meaningless. If the command has been cmdSelectData and cmdInsertData it would have been much clearer. Also pretty much everything is an object so obj just clutters up the code.

Hi JamesB, Thanks for code snippet.I did changes as u told.Now my code giving exception saying 'Message = "The StatementType enumeration value, 4, is invalid.\r\nParameter name: StatementType"'.What is it? Meanwhile m searching on google ,if you know any solution, heartily welcome ... — Sachin, Oct 09 '12 at 13:37
I resolved above error.Reason was 'Batch Update is not allowed in Context Connection'.Hence I tried to open connection using connection string,it gave me following error `System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.`.Do you have any Idea about this? — Sachin, Oct 09 '12 at 14:05
@Sachin Not really, I tested the code in a standard winforms app, using a Connection string to localhost. There are a number of questions here on that error message though. [1](http://stackoverflow.com/questions/4105252/tsql-executing-clr-permision), [2](http://stackoverflow.com/questions/2335463/system-security-securityexception), [3](http://stackoverflow.com/questions/4096309/request-for-the-permission-of-type-system-data-sqlclient-sqlclientpermission-fa) — James, Oct 09 '12 at 14:14
@JanmesB, I did it..finally.I just set SET `permission level` to `external` and it worked.Thanks bro.. — Sachin, Oct 09 '12 at 14:30

Error in DataAdapter.Update() method in C#

1 Answers1