SqlBulkCopy not working properly ?

Question

0.00/5 (No votes)

See more:

Excel sheet data

VB

  0        1       2       3       4       5
InTime  9:00 AM 9:00 AM 9:00 AM 9:00 AM 9:00 AM
OutTime 7:00 PM 7:00 PM 7:00 PM 7:00 PM 7:00 PM

C#

using (OleDbDataReader dReader = cmd.ExecuteReader())
      {
       using (SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection))
          {
           //Give your Destination table name
           sqlBulk.DestinationTableName = "tablename";
           DataTable dt = new DataTable();
           dt.Load(dReader);
           sqlBulk.WriteToServer(dt);
           }
      }

In above code datattable show all the data of excel sheet but when sqlBulk.WriteToServer(dt); it not write 0 column in database

Table structur
0 column varchar
1 column datetime
2 column datetime
3 column datetime
4 column datetime
5 column datetime

column0 column1 column2 column3 column4 column5
        9:00 AM 9:00 AM 9:00 AM 9:00 AM 9:00 AM
        7:00 PM 7:00 PM 7:00 PM 7:00 PM 7:00 PM

column 0 show blank value

Posted 11-Apr-14 2:19am

$*Developer - Vaibhav*$

Add a Solution

Comments

Shweta N Mishra 24-Nov-14 4:43am

0 column does that have any defined length for varchar ?

like varchar(20) or its only varchar

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2014-04-11T02:30:00

Solution 1

Start by looking at how you read the data from Excel: Check the SELECT command, then check that the Column0 data is included in the read and that it is loaded correctly into the DataTable - the chances are that your problem is in that area (I have no problem reading data from Excel with an OleDbConnection and updating SQL using BulkCopy)

Posted 11-Apr-14 2:30am

OriginalGriff

Comments

$*Developer - Vaibhav*$ 11-Apr-14 8:35am

in datatable all excel sheet data show properly. when swritetosql its write all the data except/ exclude the column 0

OriginalGriff 11-Apr-14 8:50am

Yes, but what's in the DataTable?
You need to know exactly where the problem is coming from - and I'm betting the DataTable you pass over to SqlBulkCopy doesn't have info in column0.
If it doesn't, then the problem is "why not?" - which means code I can't see
If it does, then we need to look ate the SQL side - which means SQL table definitions I can't see either!

So start with the simple stuff: which side is the problem on? Read, or write?

$*Developer - Vaibhav*$ · Answer 2 · 2014-11-23T22:40:00

SQL

DECLARE @StrNtLogin VARCHAR(4000)
SET NOCOUNT ON
SET @StrNtLogin = 'Deposit'
SET @StrNtLogin = @StrNtLogin + ','
declare @SplitOn CHAR(1)
SET @SplitOn= ','
DECLARE @SearchStr nvarchar(100)
Declare @Cnt int
Set @Cnt = 1
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
WHILE  (CHARINDEX(@SplitOn,@StrNtLogin)>0)
BEGIN
SELECT   @SearchStr = ltrim(rtrim(Substring(@StrNtLogin,1,Charindex(@SplitOn,@StrNtLogin)-1)))

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL
    BEGIN
        SET @ColumnName = ''
        SET @TableName =
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM    INFORMATION_SCHEMA.TABLES
            WHERE       TABLE_TYPE = 'BASE TABLE'
                AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM    INFORMATION_SCHEMA.COLUMNS
                WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND TABLE_NAME  = PARSENAME(@TableName, 1)
                    AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                    AND QUOTENAME(COLUMN_NAME) > @ColumnName
            )

            IF @ColumnName IS NOT NULL
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END
    END

SET @StrNtLogin = Substring(@StrNtLogin,Charindex(@SplitOn,@StrNtLogin)+1,len(@StrNtLogin))
SET @Cnt = @Cnt + 1
END


SELECT ColumnName, ColumnValue FROM #Results