CODEWITHSUNDEEP
c#datagridviewdatatabletype-conversionsqlcommand
Thom Ash
Thom Ash

Reputation: 221

Error when updating SQL from datatable using adapter.update()

Based on answers so far it appears I'm not creating parameters correctly and not passing values correctly. I want to update SQL table from DataTable (datatable was populated from SQL table and column names are same) When I created the parameter I thought the 2nd argument represented the datatable column. How do I set this up where the value of datatable "DWFieldScale" is passed to SQL column DWFieldScale??? (Same for every other parameter I created)

command.Parameters.AddWithValue("@DWFieldScale", "DWFieldScale");

I'm getting a SQL exception when adapter.update() is called because I don't understand how to set up the adapter correctly for purpose in subject title. I've only been working with C# a few months so I'm still very green.

Anyway I've tried a dozen things and have gotten to the point where I 'MIGHT BE' on the right track but am getting an 'Can't convert NVARCHAR to INT' What bothers me about this is this is a DataGridView bound to a datatable where the column is type INT. (In SQL the column is nullable and there are nulls)

  1. Where is NVARCHAR coming from?
  2. If NVARCHAR is coming from DataGridView, why isn't it autogenerating with matching types?
  3. Where can I make the conversion to allow my da.Update(tblvAttributes); command work? I've tried several things when defining parameters but must not be holding my jaw right. HELP

My binding code:

private void tvVX130_AfterSelect(object sender, TreeViewEventArgs e)
        {
            string t = tvVX130.SelectedNode.Text;
            BindingSource bs1 = new BindingSource();
            bs1.PositionChanged += bindingSource1_PositionChanged;
            bs1.DataSource = tblvAttributes;
            dgvVX130.DataSource = bs1;
            string dwTN = tvVX130.SelectedNode.Text.Substring(0, tvVX130.SelectedNode.Text.IndexOf("  -  "));
            bs1.Filter = "DWPhysicalTableName = '" + dwTN +  "' AND DWPhysicalSchemaName = '" + t.Substring(t.IndexOf("  -  ") + 5) + "'";
            dgvVX130.DataSource = bs1;

      public static SqlDataAdapter CreateSQLAdapter(SqlConnection vx130)
    {
        SqlDataAdapter da = new SqlDataAdapter();

       command = new SqlCommand(
            "UPDATE [Meta].[AttributeMap] "+
            "SET DatabaseName = @DatabaseName, DWPhysicalSchemaName = @DWPhysicalSchemaName, " +
            "DWPhysicalTableName=@DWPhysicalTableName, DWFieldName=@DWFieldName, DataDomain=@DataDomain," +
            "DWFieldDataType=@DWFieldDataType, DWFieldLength=@DWFieldLength, DWFieldScale=@DWFieldScale," +
            "SourceAttributeSID=@SourceAttributeSID  " +

            "WHERE DWPhysicalSchemaName = @DWPhysicalSchemaName and DWPhysicalTableName= @DWPhysicalTableName and DWFieldName=@DWFieldName", vx130);

        command.Parameters.AddWithValue("@DatabaseName", "DatabaseName");
        command.Parameters.AddWithValue("@DWPhysicalSchemaName", "DWPhysicalSchemaName");
        command.Parameters.AddWithValue("@DWPhysicalTableName", "DWPhysicalTableName");
        command.Parameters.AddWithValue("@DWFieldName", "DWFieldName");
        command.Parameters.AddWithValue("@DWFieldDataType", "DWFieldDataType");
        command.Parameters.AddWithValue("@DWFieldLength", "DWFieldLength");
        //command.Parameters.AddWithValue("@DWFieldScale",  "DWFieldScale");  gives can't convert NVARCHAR to INT

        //if (!String.IsNullOrWhiteSpace("DWFieldScale"))       Doesn't recognize "DWFieldScale" as column
        //    command.Parameters.AddWithValue("@DWFieldScale", "DWFieldScale");
        //else
        //    command.Parameters.AddWithValue("@DWFieldScale", DBNull.Value);

        //command.Parameters.Add("@DWFieldScale", SqlDbType.Int);
        //command.Parameters["@DWFieldScale"].Value = "DWFieldScale";  Doesn't recognize "DWFieldScale" as column

        //command.Parameters.AddWithValue("@DWFieldScale", int.Parse("DWFieldScale".ToString()));   gives input incorrect format

        command.Parameters.AddWithValue("@SourceAttributeSID",  "SourceAttributeSID");  //this is also integer

        da.UpdateCommand = command;

Upvotes: 1

Views: 918

Answers (4)

Thom Ash
Thom Ash

Reputation: 221

For the next programmer starting out, this is my complete solution to autoupdate SQL Database/Table when editing DataGridView bound to DataTable:

Bind: (Ties together dgView, dTable, & PositionChange event)

private void tvVX130_AfterSelect(object sender, TreeViewEventArgs e)
        {
            string t = tvVX130.SelectedNode.Text;
            BindingSource bs1 = new BindingSource();
            bs1.PositionChanged += bindingSource1_PositionChanged;
            bs1.DataSource = tblvAttributes;
            dgvVX130.DataSource = bs1;
            string dwTN = tvVX130.SelectedNode.Text.Substring(0, tvVX130.SelectedNode.Text.IndexOf("  -  "));
            bs1.Filter = "DWPhysicalTableName = '" + dwTN +  "' AND DWPhysicalSchemaName = '" + t.Substring(t.IndexOf("  -  ") + 5) + "'";
            dgvVX130.DataSource = bs1;
        }

Create event from which to execute adapter update: private void bindingSource1_PositionChanged(object sender, EventArgs e) { var config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None); AppSettingsSection appSettingSection = (AppSettingsSection)config.GetSection("cbSettings"); SqlConnection vx130 = new SqlConnection(appSettingSection.Settings[cbRegion.SelectedItem.ToString()].Value); SqlDataAdapter da = CreateSQLAdapter(vx130); da.Update(tblvAttributes); }

Set up SQL Adapter: (Where all the goodies are. It's also verbose. An alternative would be to rephrase statements to call a SQL Stored Procedure. I didn't.)

public static SqlDataAdapter CreateSQLAdapter(SqlConnection vx130)
        {
            SqlDataAdapter da = new SqlDataAdapter();

            // Create the SelectCommand.
            SqlCommand command = new SqlCommand("Select DatabaseName, DWPhysicalSchemaName, DWPhysicalTableName, " + 
                "DWFieldName ,DataDomain, DWFieldDataType, DWFieldLength, DWFieldScale, SourceAttributeSID, "+
                "ResolvedValue, PointedToField, MapComments, PrimaryKeyEntitySID, SpecialHandlingFlag, "+
                "DWFieldTechnicalDescription, BuildStatus from meta.attributemap", vx130);

            da.SelectCommand = command;

            // Create the InsertCommand.
            command = new SqlCommand(
             "Insert Into [Meta].[AttributeMap] " +
                "(DatabaseName, DWPhysicalSchemaName, DWPhysicalTableName, " +
                "DWFieldName ,DataDomain, DWFieldDataType, DWFieldLength, DWFieldScale, SourceAttributeSID, " +
                "ResolvedValue, PointedToField, MapComments, PrimaryKeyEntitySID, SpecialHandlingFlag, " +
                "DWFieldTechnicalDescription, BuildStatus ) " +


             "Values (@DatabaseName, @DWPhysicalSchemaName, @DWPhysicalTableName, " +
                "@DWFieldName ,@DataDomain, @DWFieldDataType, @DWFieldLength, @DWFieldScale, @SourceAttributeSID, " +
                "@ResolvedValue, @PointedToField, @MapComments, @PrimaryKeyEntitySID, @SpecialHandlingFlag, " +
                "@DWFieldTechnicalDescription, @BuildStatus)" , vx130);

            // Add the parameters for the InsertCommand.
            command.Parameters.Add(new SqlParameter("@DatabaseName", SqlDbType.VarChar));
            command.Parameters["@DatabaseName"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@DatabaseName"].SourceColumn = "DatabaseName";

            command.Parameters.Add(new SqlParameter("@DWPhysicalSchemaName", SqlDbType.VarChar));
            command.Parameters["@DWPhysicalSchemaName"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@DWPhysicalSchemaName"].SourceColumn = "DWPhysicalSchemaName";

            command.Parameters.Add(new SqlParameter("@DWPhysicalTableName", SqlDbType.VarChar));
            command.Parameters["@DWPhysicalTableName"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@DWPhysicalTableName"].SourceColumn = "DWPhysicalTableName";

            command.Parameters.Add(new SqlParameter("@DWFieldName", SqlDbType.VarChar));
            command.Parameters["@DWFieldName"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@DWFieldName"].SourceColumn = "DWFieldName";

            command.Parameters.Add(new SqlParameter("@DataDomain", SqlDbType.VarChar));
            command.Parameters["@DataDomain"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@DataDomain"].SourceColumn = "DataDomain";

            command.Parameters.Add(new SqlParameter("@DWFieldDataType", SqlDbType.VarChar));
            command.Parameters["@DWFieldDataType"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@DWFieldDataType"].SourceColumn = "DWFieldDataType";

            command.Parameters.Add(new SqlParameter("@DWFieldLength", SqlDbType.VarChar));
            command.Parameters["@DWFieldLength"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@DWFieldLength"].SourceColumn = "DWFieldLength";

            command.Parameters.Add(new SqlParameter("@DWFieldScale", SqlDbType.Int));
            command.Parameters["@DWFieldScale"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@DWFieldScale"].SourceColumn = "DWFieldScale";

            command.Parameters.Add(new SqlParameter("@SourceAttributeSID", SqlDbType.Int));
            command.Parameters["@SourceAttributeSID"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@SourceAttributeSID"].SourceColumn = "SourceAttributeSID";

            command.Parameters.Add(new SqlParameter("@ResolvedValue", SqlDbType.VarChar));
            command.Parameters["@ResolvedValue"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@ResolvedValue"].SourceColumn = "ResolvedValue";

            command.Parameters.Add(new SqlParameter("@PointedToField", SqlDbType.VarChar));
            command.Parameters["@PointedToField"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@PointedToField"].SourceColumn = "PointedToField";

            command.Parameters.Add(new SqlParameter("@MapComments", SqlDbType.VarChar));
            command.Parameters["@MapComments"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@MapComments"].SourceColumn = "MapComments";

            command.Parameters.Add(new SqlParameter("@PrimaryKeyEntitySID", SqlDbType.Int));
            command.Parameters["@PrimaryKeyEntitySID"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@PrimaryKeyEntitySID"].SourceColumn = "PrimaryKeyEntitySID";

            command.Parameters.Add(new SqlParameter("@SpecialHandlingFlag", SqlDbType.VarChar));
            command.Parameters["@SpecialHandlingFlag"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@SpecialHandlingFlag"].SourceColumn = "SpecialHandlingFlag";

            command.Parameters.Add(new SqlParameter("@DWFieldTechnicalDescription", SqlDbType.VarChar));
            command.Parameters["@DWFieldTechnicalDescription"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@DWFieldTechnicalDescription"].SourceColumn = "DWFieldTechnicalDescription";

            command.Parameters.Add(new SqlParameter("@BuildStatus", SqlDbType.VarChar));
            command.Parameters["@BuildStatus"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@BuildStatus"].SourceColumn = "BuildStatus";

            da.InsertCommand = command;

            // Create the UpdateCommand.
            command = new SqlCommand(
                "UPDATE [Meta].[AttributeMap] "+
                "SET DatabaseName = @DatabaseName, DWPhysicalSchemaName = @DWPhysicalSchemaName, " +
                "DWPhysicalTableName=@DWPhysicalTableName, DWFieldName=@DWFieldName, DataDomain=@DataDomain," +
                "DWFieldDataType=@DWFieldDataType, DWFieldLength=@DWFieldLength, DWFieldScale=@DWFieldScale," +
                "SourceAttributeSID=@SourceAttributeSID, ResolvedValue=@ResolvedValue, @PointedToField=@PointedToField," +
                "MapComments=@MapComments, PrimaryKeyEntitySID=@PrimaryKeyEntitySID, SpecialHandlingFlag=@SpecialHandlingFlag," +
                "DWFieldTechnicalDescription=@DWFieldTechnicalDescription, BuildStatus=@BuildStatus  " +

                "WHERE DWPhysicalSchemaName = @DWPhysicalSchemaName and DWPhysicalTableName= @DWPhysicalTableName and DWFieldName=@DWFieldName", vx130);

            command.Parameters.Add(new SqlParameter("@DatabaseName", SqlDbType.VarChar));
            command.Parameters["@DatabaseName"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@DatabaseName"].SourceColumn = "DatabaseName";

            command.Parameters.Add(new SqlParameter("@DWPhysicalSchemaName", SqlDbType.VarChar));
            command.Parameters["@DWPhysicalSchemaName"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@DWPhysicalSchemaName"].SourceColumn = "DWPhysicalSchemaName";

            command.Parameters.Add(new SqlParameter("@DWPhysicalTableName", SqlDbType.VarChar));
            command.Parameters["@DWPhysicalTableName"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@DWPhysicalTableName"].SourceColumn = "DWPhysicalTableName";

            command.Parameters.Add(new SqlParameter("@DWFieldName", SqlDbType.VarChar));
            command.Parameters["@DWFieldName"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@DWFieldName"].SourceColumn = "DWFieldName";

            command.Parameters.Add(new SqlParameter("@DataDomain", SqlDbType.VarChar));
            command.Parameters["@DataDomain"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@DataDomain"].SourceColumn = "DataDomain";

            command.Parameters.Add(new SqlParameter("@DWFieldDataType", SqlDbType.VarChar));
            command.Parameters["@DWFieldDataType"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@DWFieldDataType"].SourceColumn = "DWFieldDataType";

            command.Parameters.Add(new SqlParameter("@DWFieldLength", SqlDbType.VarChar));
            command.Parameters["@DWFieldLength"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@DWFieldLength"].SourceColumn = "DWFieldLength";

            command.Parameters.Add(new SqlParameter("@DWFieldScale", SqlDbType.Int));
            command.Parameters["@DWFieldScale"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@DWFieldScale"].SourceColumn = "DWFieldScale";

            command.Parameters.Add(new SqlParameter("@SourceAttributeSID", SqlDbType.Int));
            command.Parameters["@SourceAttributeSID"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@SourceAttributeSID"].SourceColumn = "SourceAttributeSID";

            command.Parameters.Add(new SqlParameter("@ResolvedValue", SqlDbType.VarChar));
            command.Parameters["@ResolvedValue"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@ResolvedValue"].SourceColumn = "ResolvedValue";

            command.Parameters.Add(new SqlParameter("@PointedToField", SqlDbType.VarChar));
            command.Parameters["@PointedToField"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@PointedToField"].SourceColumn = "PointedToField";

            command.Parameters.Add(new SqlParameter("@MapComments", SqlDbType.VarChar));
            command.Parameters["@MapComments"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@MapComments"].SourceColumn = "MapComments";

            command.Parameters.Add(new SqlParameter("@PrimaryKeyEntitySID", SqlDbType.Int));
            command.Parameters["@PrimaryKeyEntitySID"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@PrimaryKeyEntitySID"].SourceColumn = "PrimaryKeyEntitySID";

            command.Parameters.Add(new SqlParameter("@SpecialHandlingFlag", SqlDbType.VarChar));
            command.Parameters["@SpecialHandlingFlag"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@SpecialHandlingFlag"].SourceColumn = "SpecialHandlingFlag";

            command.Parameters.Add(new SqlParameter("@DWFieldTechnicalDescription", SqlDbType.VarChar));
            command.Parameters["@DWFieldTechnicalDescription"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@DWFieldTechnicalDescription"].SourceColumn = "DWFieldTechnicalDescription";

            command.Parameters.Add(new SqlParameter("@BuildStatus", SqlDbType.VarChar));
            command.Parameters["@BuildStatus"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@BuildStatus"].SourceColumn = "BuildStatus";

            da.UpdateCommand = command;

            // Create the DeleteCommand.
            command = new SqlCommand(
                 "delete from vx130.Meta.AttributeMap " +
                    " where DWPhysicalSchemaName =   @DWPhysicalSchemaName  AND " +
                       " DWPhysicalTableName =  @DWPhysicalTableName  AND  DWFieldName = @DWFieldName", vx130);

            // Add the parameters for the DeleteCommand.
            command.Parameters.Add(new SqlParameter("@DWPhysicalSchemaName", SqlDbType.VarChar));
            command.Parameters["@DWPhysicalSchemaName"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@DWPhysicalSchemaName"].SourceColumn = "DWPhysicalSchemaName";

            command.Parameters.Add(new SqlParameter("@DWPhysicalTableName", SqlDbType.VarChar));
            command.Parameters["@DWPhysicalTableName"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@DWPhysicalTableName"].SourceColumn = "DWPhysicalTableName";

            command.Parameters.Add(new SqlParameter("@DWFieldName", SqlDbType.VarChar));
            command.Parameters["@DWFieldName"].SourceVersion = DataRowVersion.Current;
            command.Parameters["@DWFieldName"].SourceColumn = "DWFieldName";

            da.DeleteCommand = command;

            return da;
        }
    }

}

Upvotes: 1

Jauch
Jauch

Reputation: 1518

The following line will give an error if the database field is an "int": 

command.Parameters.AddWithValue("@DWFieldScale", "DWFieldScale");

It will give an error because you are passing as value to the field a string "DWFieldScale". The idea behind the command.Parameters is the control to make any conversion required for you.

See this:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue%28v=vs.110%29.aspx

The NVARCHAR is the type the connection thinks you are trying to pass to the parameter. It's a database field type.

Also, the following line is weird:

if (!String.IsNullOrWhiteSpace("DWFieldScale"))

String.IsNullOrWhiteSpace was meant to be used with "variables". You are passing a constant string. The result of the function will always be true, as THERE IS a string, and the result of the if, will always be FALSE, as you are negating the result of the function.

And in the end, these two lines will fail for the same reason at the beginning, you are setting a int parameter, but is passing a string as value:

command.Parameters.Add("@DWFieldScale", SqlDbType.Int);
command.Parameters["@DWFieldScale"].Value = "DWFieldScale";

The right way to use the parameters is more like this:

command.Parameters.Add("@DWFieldScale", SqlDbType.Int);
command.Parameters["@DWFieldScale"].Value = 10;

So, you have to pass a VALUE, can ba a constant, a variable with the same type, the result of a function with the same type, etc. But actually, must be the valyue you want to be in the sql command.

But this is when you want to EXECUTE the command. If you are binding this to a datagrid or something of the type, just ADD the parameters. Do not pass values, as the values will be set when you update the datagrid.

So, simply use the lines like this:

command.Parameters.Add("@DWFieldScale", SqlDbType.Int);

And let the view take care of the values for you.

Here there is a nice example on how to use a DAtaSet (in memory)

http://msdn.microsoft.com/en-us/library/system.data.dataset%28v=vs.110%29.aspx

The example is for the "Select" statement, but you will get the idea :)

And here some information about the SQLDataAdapter: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter%28v=vs.110%29.aspx

Upvotes: 1

void
void

Reputation: 7890

in two lines in your code I think you are assigning String values to int parameters I mean these:

command.Parameters.AddWithValue("@DWFieldLength", "DWFieldLength");
command.Parameters.AddWithValue("@SourceAttributeSID",  "SourceAttributeSID");

try to change the value of your parameters to int also you need to change your command text and put your varchar parameters inside single qoto marks; however for this type of database operations it's better to use Stored Procedures instead of using plain text

Upvotes: 0

Phenix_yu
Phenix_yu

Reputation: 304

I think the issue is because you add wrong value to parameters collection.in your code,all columns,you add string value.Maybe some columns are int type

command.Parameters.AddWithValue("@DatabaseName", "DatabaseName");
    command.Parameters.AddWithValue("@DWPhysicalSchemaName", "DWPhysicalSchemaName");
    command.Parameters.AddWithValue("@DWPhysicalTableName", "DWPhysicalTableName");
    command.Parameters.AddWithValue("@DWFieldName", "DWFieldName");
    command.Parameters.AddWithValue("@DWFieldDataType", "DWFieldDataType");
    command.Parameters.AddWithValue("@DWFieldLength", "DWFieldLength");

Upvotes: 0

Related Questions