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

Originally Posted by Kischi

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

If 'allow null' is not checked then wouldn't that mean that it is not going to allow null? Im not sure if Ive misunderstood.

in the table design that particlular column is set to not null.so when u copy the record from excel , in the excel that column is empty