|
Depends on what daabase you are using. If it's MSSQL you could cast/convert the varchar to a date before comparing it in the query:
select * from [PoorlyDesignedTable] where Cast([ShouldBeADateTimeField] as Datetime) = MyDate
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
Rob thanks for your quick response!
Yes it is MSSQL 2000.
Now please pardon my ignorance, but, if I have records with dates greater than 9/1/05, the following statement should return all those records rigt?
SELECT field7, field13, field14<br />
FROM myTable<br />
WHERE (CAST(field14 AS Datetime) >= '2005-09-01') AND (field7 = 'Work Order')
But my result set is empty.
What am I missing?
|
|
|
|
|
SQL Server will return those records for which
CAST(field14 AS Datetime) is greather than '2005-09-01' only if value for filed7 is equal with 'Work Order'
Your result may be empty if settings for database COLLATION or field7 COLLATION is case sensitive CS.
Try to use this condition: ... AND UPPER(field7) = 'WORK ORDER'
Be attention at spaces between WORK and ORDER !
Also, the condition for date may be rewrite thus:
MONTH( field4 ) >= 9 AND YEAR( field4 ) >= 2005
|
|
|
|
|
Thanks everyone for your help!
I ended up using the following statement:
WHERE (UPPER(field7) = 'WORK ORDER') AND (field14 BETWEEN @startdate AND @enddate)
Following airbus' suggestion to use UPPER, and documentation I found in SQL Sever Bible
Thanks again!
|
|
|
|
|
What is datetime format setting for your server ?
Use DBCC USEROPTIONS in SQL Analyzer !
VB6,C#
|
|
|
|
|
If all the values from field4 are in Y-M-D HH:MM format then SQL Server will implicit conversion VARCHAR field to DATETIME.
|
|
|
|
|
See
SQL Server Books Online
topic: CAST and CONVERT
|
|
|
|
|
Hi,
One of my stored procedure returns a string, which is the combination of fields like 'Ship Name : Ship1 Port Name : Port1 City : London' and I want show this data in my Asp.Net page i.e in TextArea control as follows (i.e in multiline).
Ship Name : Ship1
Port Name : Port1
City : London.
I am adding the '\n' charector after each value like.
'Ship Name : Ship1 :XYZ \n Port Name : Port1 \n City : London'.
And in C# i am using Server.HtmlDecode, but it is not working how can I display the data in multiline.
Regards,
Kalyan
|
|
|
|
|
Hi Kaylan
instead using the '\n' character use Environment.NewLine. This should help.
Regards
Sebastian
|
|
|
|
|
|
Try '\r\n' in place of '\n', May it solve your problem
love2code
|
|
|
|
|
Hi, i am new to ado.net so soory, if it seems like a stupid question.
I have filled a datatable with data by a simple query.
i have a datagrid, which doesn't need to show all the columns of the table,
how can i show only some of the columns without needing to query again?
can a dataview show parts of the datatable i have?
thanks,
Nir.
|
|
|
|
|
Hi Nir,
what kind of datagrid do you use (the one for WebApplications or the one for WindowsApplication)?
1) for WebApplications:
just do a right click on your DataGrid and select PropertyBuilder and then add all columns
that should be mapped and displayed
2) for WindowsApplications:
use the property TableStyle. This property defines, like the one for datagrids within WebApplications,
which columns should be mapped and displayed.
If you have any further questions don't hesitate to post again.
Regards
Sebastian
|
|
|
|
|
Also, make sure the auto create columns property is set to false. Otherwise the datagrid will contain all columns in the datatable plus those you added as above.
|
|
|
|
|
thanks for the quick reply,
I'm using WindowsApplication and the datagrid is filled at run time.
i have a function which executes a query and return datatable
this datatable (some columns of this table) should be the datasource for the datagrid.
I tried to use the TableStyle like this whit no success
QueryStr = QueryStr + "select * from Trainee "
DT = db.selectQuery(QueryStr) ' returns datatable
Dim TS As New DataGridTableStyle
Dim CS As New DataGridTextBoxColumn
TS.MappingName = "UpdateTable"
CS = New DataGridTextBoxColumn
CS.MappingName = "FirstName"
CS.Width = 50
TS.GridColumnStyles.Add(CS)
CS = New DataGridTextBoxColumn
CS.MappingName = "LastName"
CS.Width = 50
TS.GridColumnStyles.Add(CS)
CS = New DataGridTextBoxColumn
CS.MappingName = "ID"
CS.Width = 50
TS.GridColumnStyles.Add(CS)
CS = New DataGridTextBoxColumn
CS.MappingName = "Mobile"
CS.Width = 50
TS.GridColumnStyles.Add(CS)
UpdateGrid.TableStyles.Add(TS)
UpdateGrid.DataSource = DT
how shpuld i connect the datasource?
thanks
nir.
|
|
|
|
|
Hi Nir,
look at the message from mark to turn autocreatecolumn off and be sure that you only add those columns to the tablestyle that should be displayed. Did you named the datatable like the mapping name of the table style ('UpdateTable')? Try to use a UpdateGrid.DataBind() to be sure that data will be binded to the grid.
Please let me know if this hint had helped you.
Regards,
Sebastian
|
|
|
|
|
Thanks for your help, it worked.
|
|
|
|
|
Clear the tablestyle first and then add the new tablestyle. Dont create DataGridTextBoxColumn for the columns you dont want to display. If you want I can send you a small sample.
|
|
|
|
|
Thanks for your help, i managed to create the table style.
nir.
|
|
|
|
|
I have a list box which shows the tables in the database.I don't want to show the real table name instead an alias for those tables names should go how this can be done
|
|
|
|
|
Just don't show the names. Replace the displayed text with whatever you want the user to see. You can use the tag property to store the actual name or maintain an array indexed by the listbox index.
|
|
|
|
|
Create one DataView from Data Source or Manually replace those table name with your logic...
I have created a fake table, in real situation , query from database
/// Create DataSource :: select tablename,tablealias from all_tables
/*Code::*/
ICollection CreateDataSource() {
DataTable dt = new DataTable();
DataRow dr;
dt.Columns.Add(new DataColumn("TableName", typeof(string)));
dt.Columns.Add(new DataColumn("TableAlias", typeof(string)));
for (int i = 0; i < 9; i++) {
dr = dt.NewRow();
dr[0] = i.ToString() + " No Table";
dr[1] = i.ToString() + " No Alias";
dt.Rows.Add(dr);
}
DataView dv = new DataView(dt);
return dv;
}
// Fill the list with above mentioned datasource
private void FillList()
{
ListBox1.DataSource = CreateDataSource();
ListBox1..DataTextField="TableName";
ListBox1.DataValueField="TableAlias";
}
happy programming..
Pijush Biswas, Jaisalmer
softestpk@yahoo.com
love2code
|
|
|
|
|
hi
Im trying to use reporting services in sql 2005.
ı want to ask that can ı change my datasource in my report project dynamically? ı have another .net 2005 project and ı have a datagrid here. ım using this report for printing this datagrid. can ı pass this datagrid(or its datasource) to my report?
thanx...
Ali Sapancı
alisapanci@gmail.com
|
|
|
|
|
are there anyone whom thinking about something about that matter? or, can't ı explain my problem to you? 
ı think ı must explain this again;
ı have a report project in a server and ı have a datagrid in my other project. ı want to report this grid with using this report project. when ım doing this, ı want to use this datagrid's datasource in my report project. can ı pass this datasource in my report project dynamically?
thanks for your help,
Ali
|
|
|
|
|
I posted this question before bit I think I may have asked it wrong. I want to set up a program to query 1 field in the DB by pressing a button and then displaying the results in a listbox. At which point I can click in the listbox and display the record in the textboxes.
BINARY
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
