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
