We help IT Professionals succeed at work.

sqlbulkinsert error

randycdouglas
on
VS2010, SQL Server 2008
Short version: I'm uploading data from an excel spreadsheet, making some string manipulations, and then using sqlbulkinsert to insert it. Besides the data I'm pulling form the spreadsheet, I'm manually adding an ID I get from elsewhere. I'm adding this in the Excel query like this "string excelQuery = SELECT *, " + hdnRigID.Value + " FROM [Sheet1$" + txtFromCell.Text.Trim() + ":" + txtToCell.Text.Trim() + "]";

The field in the database is an int. Upon writing to the server I get the following error: "The given value of type String from the data source cannot be converted to type int of the specified target column."

Is there anyway I can make these datatypes match???  
protected void ButtonUploadFile_Click(object sender, System.EventArgs e)
{                      
//...  
string excelQuery = "SELECT *, " + hdnRigID.Value + " FROM [Sheet1$" + txtFromCell.Text.Trim() + ":" + txtToCell.Text.Trim() + "]";

//...
if (File.Exists(uploadFile.SavePath))
{
BulkSqlShipperImporter importer = new BulkSqlShipperImporter();
connectionMessage = uploadFile.GetOleDbConnectionString();
importer.SourceConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:(...)\\bf6a119e2fcc4a58aa8226cfa1ec0a08_DCL TOI POB.xls;Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\";

importer.Import(excelQuery, hdnCustomerID.Value);
}
}
catch 
{}
}
}

importer.Import class:

public override void Import(string query, string customer)
    {
        string destinationConnectionString = ...
        using (OleDbConnection myConnection = new OleDbConnection(base.SourceConnectionString))
        using (SqlConnection destinationConnection = new SqlConnection(destinationConnectionString))
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
        {
            bulkCopy.ColumnMappings.Add(0, "Name");
            bulkCopy.ColumnMappings.Add(1, "RigID");
            
            bulkCopy.DestinationTableName = "dbo.LookUp";

            using (var myCommand = new OleDbCommand(query, myConnection))
            {
                myConnection.Open();
                destinationConnection.Open();
                
                DataTable dt = new DataTable();
                OleDbDataAdapter da = new OleDbDataAdapter(myCommand);

                da.Fill(dt);                
                try
                {
                    bulkCopy.WriteToServer(dt);
                }
                catch
                {}
                finally
                {
                    bulkCopy.Close();
                    myConnection.Close();
                    destinationConnection.Close();
                }
            }
        }    
    }
}

Open in new window

Comment
Watch Question

Try:
string excelQuery = "SELECT *, " + convert(int,hdnRigID.Value) + " FROM [Sheet1$" + txtFromCell.Text.Trim() + ":" + txtToCell.Text.Trim() + "]";

If that doesn't work, we would need to investigate where the hdnRigID.Value is coming from.  Perhaps the value is empty?  or null?

Author

Commented:
No luck. It didn't work. hdnRigID.Value is just a hidden field. But really it could be any string variable. My excel query looks fine once it's built. I'm clueless on what the problem could be.
I figured it out. Eventhough I queried the excel spreadsheet like this Select Name, RigID From...
I had to do the mappings like this:
bulkCopy.ColumnMappings.Add(1, "Name");
bulkCopy.ColumnMappings.Add(0, "RigID");

instead of like this:
bulkCopy.ColumnMappings.Add(0, "Name");
bulkCopy.ColumnMappings.Add(1, "RigID");

My column mappings were swiched. Not sure why, just happy I figured it out.

Author

Commented:
I figured it out. Eventhough I queried the excel spreadsheet like this Select Name, RigID From...
I had to do the mappings like this:
bulkCopy.ColumnMappings.Add(1, "Name");
bulkCopy.ColumnMappings.Add(0, "RigID");

instead of like this:
bulkCopy.ColumnMappings.Add(0, "Name");
bulkCopy.ColumnMappings.Add(1, "RigID");

My column mappings were swiched. Not sure why, just happy I figured it out.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.