Hi I have a problem when I am copying data from my excel document to sql server 2005.

I have a document called database.xls
Every time I try to copy files from the document to sql server.
It comes up with an error. "Column 'tid' does not allow DBNull.Value."

You can see the error on: http://www.excel.web.surftown.dk/ and then press the button.

but then when I check the rows in the database on "allow null", then it works fine. but it writes the time out like this 30-12-1899 12:00:00
instead of just 12:00:00 as I have written it in the excel file.

So how do I get the error to go away even though "allow null" is not checked?

My excel file looks like this:

id, tid, txt, dato
-----------------------------------
1, 12:00,dnwq, 23-12-2008
2, 14:00, aca, 23-12-2008


and my sql server table looks like this with the datatypes:

ID(bigint),
time(varchar(50),
txt(text),
date(datetime)

This is my code on the page for copying the excel to sql server:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web.Security;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Common;
using System.Data.OleDb;
 



public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }


    protected void Button1_Click(object sender, EventArgs e)
    {
        string excelConnectionString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""", Server.MapPath("database.xls"));



        using (OleDbConnection connection =
             new OleDbConnection(excelConnectionString))
        {

            OleDbCommand command = new OleDbCommand
            ("Select * FROM [Sheet1$]", connection);

            connection.Open();

            using (DbDataReader dr = command.ExecuteReader())
            {
                string sqlConnectionString = "Data Source=212.97.133.33;Initial Catalog=kischi2_database;UID=kischi2_radio;PWD=kischi;";

                using (SqlBulkCopy bulkCopy =
                   new SqlBulkCopy(sqlConnectionString))
                {
                    bulkCopy.DestinationTableName = "PROGRAM";
                    bulkCopy.WriteToServer(dr);

                    connection.Close();

                    
                }
            }
        }
    }
}

I hope you understand what I mean and that you can help?

Kischi

Recommended Answers

All 22 Replies

I can't debug your code right now, but I recommend to transfer data from different data repositories to SQL Server is to use SQL Server Integration Service which in SQL Server Business Intelligence Studio. through it your can transfer\cleans\custom your data.

I really don't understand what you mean.
So I shouldn't use the code I have used or what?

Kischi

No, don't use this code at all, and turn into using SSIS.

Ok but how do I then copy data from an excel document into an sql server?

Kischi

Again, open SQL Server Business Intelligence Development Studio (Shipped with SQL Server all editions expect Express one), create project from type SQL Server Integration Services Project, select Data Flow tab and do it, if you face any problem tell me.

I don't think I have SQL Server Business Intelligence Development Studio I can only see microsoft sql server management studio express.

Because you've Express Edition!!

open SQL Server Business Intelligence Development Studio (Shipped with SQL Server all editions expect Express one

Ok I sent it to you

In your Select statement add this

select CONVERT(VARCHAR(5), [dateTimeColumn], 14), ..... from [table name];

Now my sql statement looks like this:

SELECT CONVERT(VARCHAR(5), [dateTimeColumn], 14) * FROM PROGRAM WHERE DATE >= convert(varchar, GETDATE()) AND DATE <= DATEADD(Month, 2, GETDATE())";

the column that there is a problem with is not a dateTime. It is varchar(50).

her you can see the error message:
http://radio.web.surftown.dk/udsendelser.aspx

Because your SQL Syntax is wrong
the right one is

SELECT CONVERT(VARCHAR(5), [dateTimeColumn], 14) , txt FROM PROGRAM WHERE DATE >= convert(varchar, GETDATE()) AND DATE <= DATEADD(Month, 2, GETDATE())";

ok now there is another error: Invalid column name 'dateTimeColumn'.

What is there supposed tol be in: [dateTimeColumn]?
I think that is the problem.

Kischi, it seems you in hurry!!! please be patient and think in codes before writing it. dateTimeColumn isn't column in your table program, rather I mean the column holds the date data.
Tell me your table columns if you faced another problem.

I'm sorry that I was in such a hurry. But I actually tried something else, but it didn't work.

I have 4 columns in the db. Here is hor it looks with the datatypes:

ID(bigint)
tid(varchar(50))
txt(text)
date(datetime)

It is tid that writes it like this: 30-12-1899 18:00:00

so that is the one I want to format.
so now I wrote [tid] instead of [dateTimeColumn]
But now I get another error: DataBinding: 'System.Data.Common.DataRecordInternal' does not contain a property with the name 'tid'.

You can see it on: http://radio.web.surftown.dk/udsendelser.aspx

Hope this helps?

I found out that it has nothing to do with my coding. It is excel that writes: 30-12-1899 18:00:00 when I write 18:00 automatically.

So do you know anything about excel, because have tried to change it so it dousn't do it, but I don't know how?

Thanks
Kischi

let your SQL holds the date in any format, we won't care!!! what I care about? is date when returns, I suggest you a SQL statement to solve all of your problems.

But is that also possible when the datatype of the column is "Varchar(50)" and it is not "datetime"

works?

Yes now it works, I just changed the column format in excel to text. :-D
Thanks alot

loool, you're welcome :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.