ASP.NET

1-Jun-10 1:26

Dear friends,
I want to fetch data from Excel sheet sheet1 using ASP.NET with C# and store the data in SqlServer database table using BulkCopy method. I am able to fetch complete excelsheet data, but I want only some rows. I want sql statement to fetch the data. Excel sheet contains one Date column. I want to fetch data from only particular dated rows. I am writing the following query

select * from [Sheet1$] where Date=21/04/10 It is giving me error datatype mismatch. Please, can anyone help me. It's very urgent. Pls....

Thanks,
Dileep.

Anurag Gandhi1-Jun-10 1:35

Anurag Gandhi

1-Jun-10 1:35

Try the following query:

select * from [Sheet1$] where Date='04/21/2010'

Note that the date should be in mm/dd/yyyy format and within single quote(').

Anurag Gandhi.
http://www.gandhisoft.com
Life is a computer program and every one is the programmer of his own life.

cheguri1-Jun-10 1:44

1-Jun-10 1:44

Dear Anurag,
I am still getting this following error.

[System.Data.OleDb.OleDbException] = {"Data type mismatch in criteria expression."}

I have written the following code.

filename = path + "SS_EmpLoginTimings.xls";
string s = "provider=Microsoft.Jet.OLEDB.4.0;" + @"data source=" + filename + ";Extended Properties=Excel 8.0;";

OleDbConnection con = new OleDbConnection(s);
con.Open();
OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$] where Date='04/21/2010'", con);
DataSet ds = new DataSet();
da.Fill(ds);
DataTable dt = ds.Tables[0];
BulkCopyData(dt, "SS_EmpLoginTimings");

public bool BulkCopyData(DataTable dt, string tblName)
{
SqlBulkCopy bulk = new SqlBulkCopy(conn);
bulk.DestinationTableName = tblName;

conn.Open();
bulk.WriteToServer(dt);
conn.Close();

return true;
}

Please help me. It's very urgent. I need to finish this work by today evening. Pls pls pls...

Regards,
Dileep.

Baconbutty1-Jun-10 1:48

Baconbutty

1-Jun-10 1:48

Have you got da and ds the wrong way round in the da.fill(ds)?

It's while since I coded anything like this so I could be totally wrong.

Maybe try putting the criteria as Date = '21-Apr-2010'.

Baconbutty1-Jun-10 2:06

Baconbutty

1-Jun-10 2:06

http://www.experts-exchange.com/Programming/Languages/.NET/Q_25020438.html[^]

Try that page - scroll down to the bottom and look for postings by sbsnewbie

T M Gray1-Jun-10 8:19

T M Gray

1-Jun-10 8:19

How consistant is the data in your spreadsheet? Do all of the rows have dates in the date column? The Excel database driver determines the datatype of each column by the values in the first 10 rows. If your first ten rows have nothing in the date column or have other text like N/A or -, then that column will not be considered a date and you can't query it like a date.

cheguri1-Jun-10 18:55

1-Jun-10 18:55

Dear friend,
All the rows in that column are having date only. I don't know what's wrong. I have tried in many ways. I am getting the following error.

[System.Data.OleDb.OleDbException] = {"Data type mismatch in criteria expression."}

Since last day afternoon, I am struggling for this. I have written queries in all following possible ways.

select * from [sheet1$] where LoginDate='21/04/10'
select * from [sheet1$] where LoginDate='21/04/2010'
select * from [sheet1$] where LoginDate='21-04-10'
select * from [sheet1$] where LoginDate='21-04-2010'
select * from [sheet1$] where LoginDate='21-Apr-10'
select * from [sheet1$] where LoginDate='21-Apr-2010'
select * from [sheet1$] where LoginDate='04/21/10'
select * from [sheet1$] where LoginDate='04/21/2010'
select * from [sheet1$] where LoginDate='04-21-10'
select * from [sheet1$] where LoginDate='04-21-2010'
select * from [sheet1$] where LoginDate='Apr-21-10'
select * from [sheet1$] where LoginDate='Apr-21-2010'
select * from [Sheet1$] where [LoginDate] = '"+DateTime.Parse("04-21-10").ToString()+"'"
select * from [Sheet1$] where [LoginDate] = '"+DateTime.Parse("04-21-10")+"'"

Still I am not getting. Can anyone, pls help me. It's very urgent.

Regards,
Dileep

cheguri1-Jun-10 20:21

Need a regular expression validation

1-Jun-10 20:21

Thanks friends,
I have got it in another way.

select * from [sheet1$] where LoginDate=#21/04/10#

It's working fine.

Thanks everyone.

Regards,
Dileep.

Reg MS acess2003

sowjanya331-May-10 22:58

sowjanya3

31-May-10 22:58

how to type the same number in a single column?

Re: Reg MS acess2003

Sandeep Mewara1-Jun-10 0:01

Sandeep Mewara

1-Jun-10 0:01

Can you be a little more elaborate? We cannot read your mind to guess of what you can be talking!

Re: Reg MS acess2003

Baconbutty1-Jun-10 2:50

Baconbutty

1-Jun-10 2:50

1. Enter required number in a grid cell in Access.
2. Use cursor keys to move down to new row, committing the previous row
3. Go to 1.

meeram39531-May-10 21:28

31-May-10 21:28

In my aspx page, I have an input box which should be the format as follows:

1. First 3 or 4 characters should be alphabets (maximum 4).
2. After this, there is a constant text, '2CDSV=DT' (8 characters).
3. The text should not be more than 12 characters.

For eg: the text should be either 'RIN2CDSV=DT' or 'RINJ2CDSV=DT'. Both are valid.

How can I frame a regular expression for this? Anybody has any idea?

I put only this: ("[^0-9]"), but this will check only the alphabets. How can I join the 2nd and 3rd condition.

Please help.

Thanks in advance,
meeram395.

Success is the good fortune that comes from aspiration, desperation, perspiration and inspiration.

Eduard Keilholz31-May-10 21:43

Eduard Keilholz

31-May-10 21:43

I guess this one works...

^([a-zA-Z]{3,4})(2CDSV=DT)$

.: I love it when a plan comes together :.
http://www.zonderpunt.nl

meeram39531-May-10 22:31

31-May-10 22:31

Thanks a lot. Let me check and get back to you.

Success is the good fortune that comes from aspiration, desperation, perspiration and inspiration.

meeram39531-May-10 23:37

31-May-10 23:37

It is partially working, means it is validating properly. But it seems that it is case sensitive.

If I try with RIN2CDss=dt, it is gving error. Any idea how should I make it case-insensitive?

Thanks for your efforts. Please reply if you can help on above.

Thanks
meeram395.

Success is the good fortune that comes from aspiration, desperation, perspiration and inspiration.

Ankur^\m/1-Jun-10 0:23

Ankur^\m/

1-Jun-10 0:23

meeram395 wrote:
If I try with RIN2CDss=dt, it is gving error.

It should. That's how you have written the regex.
See again: ^([a-zA-Z]{3,4})(2CDSV=DT)$

So RIN2CDsv=dt should work.
Thumbs Up | :thumbsup:

..Go Green..

meeram3951-Jun-10 0:46