|
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(').
|
|
|
|
|
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.
|
|
|
|
|
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'.
|
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
how to type the same number in a single column?
|
|
|
|
|
Can you be a little more elaborate? We cannot read your mind to guess of what you can be talking!
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
I guess this one works...
^([a-zA-Z]{3,4})(2CDSV=DT)$
|
|
|
|
|
Thanks a lot. Let me check and get back to you.
Success is the good fortune that comes from aspiration, desperation, perspiration and inspiration.
|
|
|
|
|
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.
|
|
|
|
|
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.
..Go Green..
|
|
|
|
|
Ok. Thanks I understood.
Success is the good fortune that comes from aspiration, desperation, perspiration and inspiration.
|
|
|
|
|
Try here:
RegexBuilder[^]
RegexBuilder is a simple tool which helps developers to create and test regular expressions.
|
|
|
|
|
Hi I want to return a list of records on button click of user control.
I already have the collection ready , but I dont know how to return the collection to the calling page.
How do I bind results of user control with gridview on parent page on user control button click ?
My user control code
public partial class assets_WebControls_ftExclusions : System.Web.UI.UserControl
{
private int _id;
private ftExclusionsList myftExclusions;
public int ID
{
get{return _id;}
set{_id=value;}
}
public ftExclusionsList selectedExclusions
{
get { return myftExclusions; }
set { myftExclusions = value; }
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
cblExclusions.DataBind();
ftExclusionsList myExcluList = ftExclusionsManager.ftExclusionsById(ID);
if (myExcluList != null)
{
if (myExcluList.Count > 0 && myExcluList != null)
{
foreach (ftExclusions ex in myExcluList)
{
cblExclusions.Items.FindByValue(ex.excluId.ToString()).Selected = true;
}
}
}
}
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
ftExclusionsList myFTExclusionList = new ftExclusionsList();
foreach (ListItem li in cblExclusions.Items)
{
if (li.Selected == true)
{
ftExclusions myftExclusion = new ftExclusions();
myftExclusion.excluId = Convert.ToInt32(li.Value);
myftExclusion.excludesc = li.Text.ToString();
myFTExclusionList.Add(myftExclusion);
}
}
selectedExclusions = myFTExclusionList;
}
}
|
|
|
|
|
AFAIK, You can not return something from click event.
To achieve your goal, you can have a public property which holds the list of records. Assign this property with the list in click event. And after this, you can access this property from the page to get the list of records.
|
|
|
|
|
Thanks for the reply.
How about I define a private variable gridview inside my user control.Pass the value of parent page gridview to this private variable of user control.
And on button click of user control, bind the private gridview with collection ? I cant get it to work though but just a question.
|
|
|
|
|
Hi,
if i were you, i would not start passing controls from one page or usercontrol to another.
The method explained in the above post is the way to go. Always keep in mind that your controls/classes should have a (clean seperated) job.
Maybe you wanna reuse this control somewhere else where the calling page doesnt have/need a gridview? keep your "interfaces" as simple as
possible.
So, in your UserControls' Button click event, raise an event or something, which is subscribed in Page load of your Page class.
This ways, your page can access the mentioned public property of your usercontrol once the event occurs. Or you could even pass your "result"
though this event/delegate and access it without the necessity of a public/internal property.
|
|
|
|
|
Hello Michael. Thanks for the reply. Actually I have to show hide the user control on button clicks on page. That is why there is a update button on the user control, which when clicked would update the gridview on parent page and hide the control.
If I were to store the values in the checkbox list click event I still have to pass them on to the parent gridview. Can you elaborate a little with some example or hint what you want me to do ?
|
|
|
|
|
Hi,
i was talking about the button click event in your usercontrol. You have to somehow
let the page know that the user clicked the button within your usercontrol. You could
do this by using a delegate similar to this. I made a comment there with an example.
You have to replace the text-parameter by your custom item collection. This would work.
(Or you just notify the page and the page accesses the items though a public property of
your control).
Does this help or am i confusing?
|
|
|
|
|
Hi thanks for the reply, Ermm its not confusing but maybe too hitech for me.
I took the update button out of user control and made its method as a private method inside user control ,
I am trying to expose the updated collection though a public property of user control now. Only thing bothering me is that I cant call the private method inside user control and populate the collection on call of the public method of user control.
In presentation page I am calling the the public attribute but it is returning null right now because the private method is not called.
public partial class assets_WebControls_ftExclusions : System.Web.UI.UserControl
{
private int _id;
private ftExclusionsList _myftExclusions;
public int ID
{
get{return _id;}
set{_id=value;}
}
public ftExclusionsList selectedExclusions
{
get { return _myftExclusions; }
set { _myftExclusions = value; }
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
cblExclusions.DataBind();
ftExclusionsList myExcluList = ftExclusionsManager.ftExclusionsById(ID);
if (myExcluList != null)
{
if (myExcluList.Count > 0 && myExcluList != null)
{
foreach (ftExclusions ex in myExcluList)
{
cblExclusions.Items.FindByValue(ex.excluId.ToString()).Selected = true;
}
}
}
}
}
private void SelectedExclusions()
{
ftExclusionsList myFTExclusionList = new ftExclusionsList();
foreach (ListItem li in cblExclusions.Items)
{
if (li.Selected == true)
{
ftExclusions myftExclusion = new ftExclusions();
myftExclusion.excluId = Convert.ToInt32(li.Value);
myftExclusion.excludesc = li.Text.ToString();
myFTExclusionList.Add(myftExclusion);
}
}
selectedExclusions = myFTExclusionList;
}
}
|
|
|
|
|
If it is just about the collection you want to expose now, you
can either do this:
public ftExclusions GetSelectedExclusions()
{
ftExclusionsList myFTExclusionList = new ftExclusionsList();
foreach (ListItem li in cblExclusions.Items)
{
if (li.Selected == true)
{
ftExclusions myftExclusion = new ftExclusions();
myftExclusion.excluId = Convert.ToInt32(li.Value);
myftExclusion.excludesc = li.Text.ToString();
myFTExclusionList.Add(myftExclusion);
}
}
return myFTExclusionList;
}
or this (property):
public ftExclusionsList SelectedExclusions
{
get { return GetSelectedExclusions(); }
}
private ftExclusions GetSelectedExclusions()
{
ftExclusionsList myFTExclusionList = new ftExclusionsList();
foreach (ListItem li in cblExclusions.Items)
{
if (li.Selected == true)
{
ftExclusions myftExclusion = new ftExclusions();
myftExclusion.excluId = Convert.ToInt32(li.Value);
myftExclusion.excludesc = li.Text.ToString();
myFTExclusionList.Add(myftExclusion);
}
}
return myFTExclusionList;
}
Then use the property OR method in the desired event of your page. This is all assuming that the rest of the code works.
A hint: Check for null before checking the count of a collection. Otherwise, you could get an exception. It is also usefull to check like
if (null==something)
if (something == null)
This way, you can prevent typing mistakes like If (something = null)...
GL
GL
|
|
|
|