DaniWeb IT Discussion Community - formatting in sql server

DaniWeb IT Discussion Community (http://www.daniweb.com/forums/index.php)

- MS SQL (http://www.daniweb.com/forums/forum127.html)

- - formatting in sql server (http://www.daniweb.com/forums/thread163646.html)

formatting in sql server

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