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

Re: formatting in sql server 80 80

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.

Re: formatting in sql server 80 80

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

Kischi

Re: formatting in sql server 80 80

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

Re: formatting in sql server 80 80

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

Kischi

Re: formatting in sql server 80 80

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.

Re: formatting in sql server 80 80

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

Re: formatting in sql server 80 80

Because you've Express Edition!!

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

Re: formatting in sql server 80 80

Look, send me the Excel file and the table scheme you need to insert into on ramyamahrous@hotmail.com I'll debug your code.

Re: formatting in sql server 80 80

Ok I sent it to you

Re: formatting in sql server 80 80

In your Select statement add this

select CONVERT(VARCHAR(5), [dateTimeColumn], 14), ..... from [table name];
Re: formatting in sql server 80 80

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

Re: formatting in sql server 80 80

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())";
Re: formatting in sql server 80 80

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

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

Re: formatting in sql server 80 80

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.

Re: formatting in sql server 80 80

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?

Re: formatting in sql server 80 80

Remove the container control (DataList, Repeater, etc...) and insert someone again... or try asking asp.net forum http://www.daniweb.com/forums/forum18.html

Re: formatting in sql server 80 80

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

Re: formatting in sql server 80 80

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.

Re: formatting in sql server 80 80

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

Re: formatting in sql server 80 80

works?

Re: formatting in sql server 80 80

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

Re: formatting in sql server 80 80

loool, you're welcome :)

Be a part of the DaniWeb community

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