Hi
Whats the best way to display data on company intranet or web
where they can make little choices also ( say between date so and so)
some thing like a report, with charts etc
with some graphical easy to learn language
I have strong knowledge of SQL and ok with HTMLYou can use any language which runs on a server, like ASP or JSP. I made something like what you want with ASP, and its really easy, you can search the web for tutorials or help.
Ask me what you will.|||You can also take a look at the new MS Reporting Services. I have downloaded it and installed it and it is a very strong reporting engine. I think the only significant drawback (and it is significant) is that to design reports, you must have a licensed copy of Visual Studio .NET.
You could also go with Crystal Reports (which I hate and about which have heard many negative things).
Regards,
hmscott|||I dont know asp
whether its easy for me to make a user choose dates to restrict
number of records or mentioning dates between so and so
and some other choice i can give
I also downloaded reporting service from microsoft but they are telling
to go to .net (say visual basic .net) to design reports and learn asp too to display
I already know about oracle reports and that should have being easy for me
Does crystal reports have automatic web display
salim
Showing posts with label date. Show all posts
Showing posts with label date. Show all posts
Wednesday, March 7, 2012
Thursday, February 16, 2012
Being DateTime Formats aware when accessing database
Maybe has been answered many times but here I go:
I pick up a date from MonthCalendar control in my device running
Spanish OS version. The format can be this: 23/11/2005 00:00:00.
The server side SQL Server 2005 April CTP is in English and I'm using
Merge Replication to fill a SQL Mobile 2005 database, also in English.
Logically, I'm having trouble with datetime columns.
Do I have to convert Spanish datetime picked up in the interface to
English datetime format everytime I must do an update or insert?
Is there any way to avoid this, to be database datetime format aware?
I was initially trying this, but failed of course:
UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
WHERE appointmentId = 1
Then, after manually building English DateTime format, this query
worked.
Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
0:0:00'
WHERE appointmentId = 1
But that's not the way. I have to avoid manally building string that
will be inserted as DateTimes.
Any solution will be greatly appreciated.
I think that this will answer most of your questions:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegro ups.com...
> Maybe has been answered many times but here I go:
> I pick up a date from MonthCalendar control in my device running
> Spanish OS version. The format can be this: 23/11/2005 00:00:00.
> The server side SQL Server 2005 April CTP is in English and I'm using
> Merge Replication to fill a SQL Mobile 2005 database, also in English.
> Logically, I'm having trouble with datetime columns.
> Do I have to convert Spanish datetime picked up in the interface to
> English datetime format everytime I must do an update or insert?
> Is there any way to avoid this, to be database datetime format aware?
> I was initially trying this, but failed of course:
> UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
> WHERE appointmentId = 1
> Then, after manually building English DateTime format, this query
> worked.
> Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
> 0:0:00'
> WHERE appointmentId = 1
> But that's not the way. I have to avoid manally building string that
> will be inserted as DateTimes.
> Any solution will be greatly appreciated.
>
|||Thanks Tibor, but I still have got one question:
If I use language neutral unseparated format ('19980223 14:23:05') and
execute an insert or and update statement both against Spanish SQL
Server and English SQL Server, both statements will work without
problem?
Therefore, don't worrying about SQL Server language, if I convert my
DateTime value (programming in C# for example) to string datatype in
unseparated format and execute the statement, will work? That is, SQL
Server will be able to understand it and store it in the appropiate
format?
I'll try tomorrow but can't be so easy.
What about finding rows that match a concrete date where clause? I'm
using LIKE operator but it is rather language dependant: If SQL Server
in Spanish, "Ago 10,2005%" is the pattern. If in English, "Aug
10,2005%" is the pattern. Can this also be solved wihout using BETWEEN
and "<" and ">" operators, only with LIKE?
Thanks very much.
|||You should always use parameters and pass DateTime instead of string.
That would work for any locale and would also improve performance.
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
*** Want to find answers instantly? Here's how... ***
1. Go to
http://groups-beta.google.com/group/...mework ?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegro ups.com...
> Maybe has been answered many times but here I go:
> I pick up a date from MonthCalendar control in my device running
> Spanish OS version. The format can be this: 23/11/2005 00:00:00.
> The server side SQL Server 2005 April CTP is in English and I'm using
> Merge Replication to fill a SQL Mobile 2005 database, also in English.
> Logically, I'm having trouble with datetime columns.
> Do I have to convert Spanish datetime picked up in the interface to
> English datetime format everytime I must do an update or insert?
> Is there any way to avoid this, to be database datetime format aware?
> I was initially trying this, but failed of course:
> UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
> WHERE appointmentId = 1
> Then, after manually building English DateTime format, this query
> worked.
> Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
> 0:0:00'
> WHERE appointmentId = 1
> But that's not the way. I have to avoid manally building string that
> will be inserted as DateTimes.
> Any solution will be greatly appreciated.
>
|||Do you mean always use parameters with update/insert statements against
SQL Mobile 2005 and SQL Server CE databases?
Any known problems when synchronizing with SQL Server (Spanish and
English versions mainly) via Merge Replication?
Could you please post an example of an update statement using
parameters?
Thanks very much. I'm willing to try tomorrow morning.
|||Yes, always. That makes conversion unnecessary and eliminates format
problems completely.
For example, DateTime is format-less, it's just a 64 bit integer.
The problem with formats only arises if you convert it to/from string. Which
also takes a lot of time.
Update/Insert sample is available in VS documentation, just look up
SqlCeCommand.Parameters property.
If your SQL Server DB uses collation which is not supported on the device,
you'll get NotSupportedException on attempt to replicate.
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
*** Want to find answers instantly? Here's how... ***
1. Go to
http://groups-beta.google.com/group/...mework ?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131564804.366046.310110@.g43g2000cwa.googlegr oups.com...
> Do you mean always use parameters with update/insert statements against
> SQL Mobile 2005 and SQL Server CE databases?
> Any known problems when synchronizing with SQL Server (Spanish and
> English versions mainly) via Merge Replication?
> Could you please post an example of an update statement using
> parameters?
> Thanks very much. I'm willing to try tomorrow morning.
>
|||> If I use language neutral unseparated format ('19980223 14:23:05') and
> execute an insert or and update statement both against Spanish SQL
> Server and English SQL Server, both statements will work without
> problem?
Yes.
> What about finding rows that match a concrete date where clause?
Did you read the article? I have a long section about just that:
http://www.karaszi.com/SQLServer/inf...asp#Searching
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131563864.355665.61160@.g14g2000cwa.googlegro ups.com...
> Thanks Tibor, but I still have got one question:
> If I use language neutral unseparated format ('19980223 14:23:05') and
> execute an insert or and update statement both against Spanish SQL
> Server and English SQL Server, both statements will work without
> problem?
> Therefore, don't worrying about SQL Server language, if I convert my
> DateTime value (programming in C# for example) to string datatype in
> unseparated format and execute the statement, will work? That is, SQL
> Server will be able to understand it and store it in the appropiate
> format?
> I'll try tomorrow but can't be so easy.
> What about finding rows that match a concrete date where clause? I'm
> using LIKE operator but it is rather language dependant: If SQL Server
> in Spanish, "Ago 10,2005%" is the pattern. If in English, "Aug
> 10,2005%" is the pattern. Can this also be solved wihout using BETWEEN
> and "<" and ">" operators, only with LIKE?
> Thanks very much.
>
|||Thanks Ilya for the "using parameters" advice. Many problems solved
this way.
Regards.
|||Thanks Tibor for that great article but I think parameters have solved
all my headaches ;-)
Kind regards,
|||Yep, I should add to the article that parametizing queries has bunch of advantages, among others
that ADO (or whatever API is used) will handle these things for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131612217.129555.137660@.g47g2000cwa.googlegr oups.com...
> Thanks Tibor for that great article but I think parameters have solved
> all my headaches ;-)
> Kind regards,
>
I pick up a date from MonthCalendar control in my device running
Spanish OS version. The format can be this: 23/11/2005 00:00:00.
The server side SQL Server 2005 April CTP is in English and I'm using
Merge Replication to fill a SQL Mobile 2005 database, also in English.
Logically, I'm having trouble with datetime columns.
Do I have to convert Spanish datetime picked up in the interface to
English datetime format everytime I must do an update or insert?
Is there any way to avoid this, to be database datetime format aware?
I was initially trying this, but failed of course:
UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
WHERE appointmentId = 1
Then, after manually building English DateTime format, this query
worked.
Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
0:0:00'
WHERE appointmentId = 1
But that's not the way. I have to avoid manally building string that
will be inserted as DateTimes.
Any solution will be greatly appreciated.
I think that this will answer most of your questions:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegro ups.com...
> Maybe has been answered many times but here I go:
> I pick up a date from MonthCalendar control in my device running
> Spanish OS version. The format can be this: 23/11/2005 00:00:00.
> The server side SQL Server 2005 April CTP is in English and I'm using
> Merge Replication to fill a SQL Mobile 2005 database, also in English.
> Logically, I'm having trouble with datetime columns.
> Do I have to convert Spanish datetime picked up in the interface to
> English datetime format everytime I must do an update or insert?
> Is there any way to avoid this, to be database datetime format aware?
> I was initially trying this, but failed of course:
> UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
> WHERE appointmentId = 1
> Then, after manually building English DateTime format, this query
> worked.
> Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
> 0:0:00'
> WHERE appointmentId = 1
> But that's not the way. I have to avoid manally building string that
> will be inserted as DateTimes.
> Any solution will be greatly appreciated.
>
|||Thanks Tibor, but I still have got one question:
If I use language neutral unseparated format ('19980223 14:23:05') and
execute an insert or and update statement both against Spanish SQL
Server and English SQL Server, both statements will work without
problem?
Therefore, don't worrying about SQL Server language, if I convert my
DateTime value (programming in C# for example) to string datatype in
unseparated format and execute the statement, will work? That is, SQL
Server will be able to understand it and store it in the appropiate
format?
I'll try tomorrow but can't be so easy.
What about finding rows that match a concrete date where clause? I'm
using LIKE operator but it is rather language dependant: If SQL Server
in Spanish, "Ago 10,2005%" is the pattern. If in English, "Aug
10,2005%" is the pattern. Can this also be solved wihout using BETWEEN
and "<" and ">" operators, only with LIKE?
Thanks very much.
|||You should always use parameters and pass DateTime instead of string.
That would work for any locale and would also improve performance.
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
*** Want to find answers instantly? Here's how... ***
1. Go to
http://groups-beta.google.com/group/...mework ?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegro ups.com...
> Maybe has been answered many times but here I go:
> I pick up a date from MonthCalendar control in my device running
> Spanish OS version. The format can be this: 23/11/2005 00:00:00.
> The server side SQL Server 2005 April CTP is in English and I'm using
> Merge Replication to fill a SQL Mobile 2005 database, also in English.
> Logically, I'm having trouble with datetime columns.
> Do I have to convert Spanish datetime picked up in the interface to
> English datetime format everytime I must do an update or insert?
> Is there any way to avoid this, to be database datetime format aware?
> I was initially trying this, but failed of course:
> UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
> WHERE appointmentId = 1
> Then, after manually building English DateTime format, this query
> worked.
> Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
> 0:0:00'
> WHERE appointmentId = 1
> But that's not the way. I have to avoid manally building string that
> will be inserted as DateTimes.
> Any solution will be greatly appreciated.
>
|||Do you mean always use parameters with update/insert statements against
SQL Mobile 2005 and SQL Server CE databases?
Any known problems when synchronizing with SQL Server (Spanish and
English versions mainly) via Merge Replication?
Could you please post an example of an update statement using
parameters?
Thanks very much. I'm willing to try tomorrow morning.
|||Yes, always. That makes conversion unnecessary and eliminates format
problems completely.
For example, DateTime is format-less, it's just a 64 bit integer.
The problem with formats only arises if you convert it to/from string. Which
also takes a lot of time.
Update/Insert sample is available in VS documentation, just look up
SqlCeCommand.Parameters property.
If your SQL Server DB uses collation which is not supported on the device,
you'll get NotSupportedException on attempt to replicate.
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
*** Want to find answers instantly? Here's how... ***
1. Go to
http://groups-beta.google.com/group/...mework ?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131564804.366046.310110@.g43g2000cwa.googlegr oups.com...
> Do you mean always use parameters with update/insert statements against
> SQL Mobile 2005 and SQL Server CE databases?
> Any known problems when synchronizing with SQL Server (Spanish and
> English versions mainly) via Merge Replication?
> Could you please post an example of an update statement using
> parameters?
> Thanks very much. I'm willing to try tomorrow morning.
>
|||> If I use language neutral unseparated format ('19980223 14:23:05') and
> execute an insert or and update statement both against Spanish SQL
> Server and English SQL Server, both statements will work without
> problem?
Yes.
> What about finding rows that match a concrete date where clause?
Did you read the article? I have a long section about just that:
http://www.karaszi.com/SQLServer/inf...asp#Searching
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131563864.355665.61160@.g14g2000cwa.googlegro ups.com...
> Thanks Tibor, but I still have got one question:
> If I use language neutral unseparated format ('19980223 14:23:05') and
> execute an insert or and update statement both against Spanish SQL
> Server and English SQL Server, both statements will work without
> problem?
> Therefore, don't worrying about SQL Server language, if I convert my
> DateTime value (programming in C# for example) to string datatype in
> unseparated format and execute the statement, will work? That is, SQL
> Server will be able to understand it and store it in the appropiate
> format?
> I'll try tomorrow but can't be so easy.
> What about finding rows that match a concrete date where clause? I'm
> using LIKE operator but it is rather language dependant: If SQL Server
> in Spanish, "Ago 10,2005%" is the pattern. If in English, "Aug
> 10,2005%" is the pattern. Can this also be solved wihout using BETWEEN
> and "<" and ">" operators, only with LIKE?
> Thanks very much.
>
|||Thanks Ilya for the "using parameters" advice. Many problems solved
this way.
Regards.
|||Thanks Tibor for that great article but I think parameters have solved
all my headaches ;-)
Kind regards,
|||Yep, I should add to the article that parametizing queries has bunch of advantages, among others
that ADO (or whatever API is used) will handle these things for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131612217.129555.137660@.g47g2000cwa.googlegr oups.com...
> Thanks Tibor for that great article but I think parameters have solved
> all my headaches ;-)
> Kind regards,
>
Being DateTime Formats aware when accessing database
Maybe has been answered many times but here I go:
I pick up a date from MonthCalendar control in my device running
Spanish OS version. The format can be this: 23/11/2005 00:00:00.
The server side SQL Server 2005 April CTP is in English and I'm using
Merge Replication to fill a SQL Mobile 2005 database, also in English.
Logically, I'm having trouble with datetime columns.
Do I have to convert Spanish datetime picked up in the interface to
English datetime format everytime I must do an update or insert?
Is there any way to avoid this, to be database datetime format aware?
I was initially trying this, but failed of course:
UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
WHERE appointmentId = 1
Then, after manually building English DateTime format, this query
worked.
Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
0:0:00'
WHERE appointmentId = 1
But that's not the way. I have to avoid manally building string that
will be inserted as DateTimes.
Any solution will be greatly appreciated.I think that this will answer most of your questions:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegroups.com...
> Maybe has been answered many times but here I go:
> I pick up a date from MonthCalendar control in my device running
> Spanish OS version. The format can be this: 23/11/2005 00:00:00.
> The server side SQL Server 2005 April CTP is in English and I'm using
> Merge Replication to fill a SQL Mobile 2005 database, also in English.
> Logically, I'm having trouble with datetime columns.
> Do I have to convert Spanish datetime picked up in the interface to
> English datetime format everytime I must do an update or insert?
> Is there any way to avoid this, to be database datetime format aware?
> I was initially trying this, but failed of course:
> UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
> WHERE appointmentId = 1
> Then, after manually building English DateTime format, this query
> worked.
> Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
> 0:0:00'
> WHERE appointmentId = 1
> But that's not the way. I have to avoid manally building string that
> will be inserted as DateTimes.
> Any solution will be greatly appreciated.
>|||Thanks Tibor, but I still have got one question:
If I use language neutral unseparated format ('19980223 14:23:05') and
execute an insert or and update statement both against Spanish SQL
Server and English SQL Server, both statements will work without
problem?
Therefore, don't worrying about SQL Server language, if I convert my
DateTime value (programming in C# for example) to string datatype in
unseparated format and execute the statement, will work? That is, SQL
Server will be able to understand it and store it in the appropiate
format?
I'll try tomorrow but can't be so easy.
What about finding rows that match a concrete date where clause? I'm
using LIKE operator but it is rather language dependant: If SQL Server
in Spanish, "Ago 10,2005%" is the pattern. If in English, "Aug
10,2005%" is the pattern. Can this also be solved wihout using BETWEEN
and "<" and ">" operators, only with LIKE?
Thanks very much.|||You should always use parameters and pass DateTime instead of string.
That would work for any locale and would also improve performance.
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
*** Want to find answers instantly? Here's how... ***
1. Go to
http://groups-beta.google.com/group... />
work?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegroups.com...
> Maybe has been answered many times but here I go:
> I pick up a date from MonthCalendar control in my device running
> Spanish OS version. The format can be this: 23/11/2005 00:00:00.
> The server side SQL Server 2005 April CTP is in English and I'm using
> Merge Replication to fill a SQL Mobile 2005 database, also in English.
> Logically, I'm having trouble with datetime columns.
> Do I have to convert Spanish datetime picked up in the interface to
> English datetime format everytime I must do an update or insert?
> Is there any way to avoid this, to be database datetime format aware?
> I was initially trying this, but failed of course:
> UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
> WHERE appointmentId = 1
> Then, after manually building English DateTime format, this query
> worked.
> Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
> 0:0:00'
> WHERE appointmentId = 1
> But that's not the way. I have to avoid manally building string that
> will be inserted as DateTimes.
> Any solution will be greatly appreciated.
>|||Do you mean always use parameters with update/insert statements against
SQL Mobile 2005 and SQL Server CE databases?
Any known problems when synchronizing with SQL Server (Spanish and
English versions mainly) via Merge Replication?
Could you please post an example of an update statement using
parameters?
Thanks very much. I'm willing to try tomorrow morning.|||Yes, always. That makes conversion unnecessary and eliminates format
problems completely.
For example, DateTime is format-less, it's just a 64 bit integer.
The problem with formats only arises if you convert it to/from string. Which
also takes a lot of time.
Update/Insert sample is available in VS documentation, just look up
SqlCeCommand.Parameters property.
If your SQL Server DB uses collation which is not supported on the device,
you'll get NotSupportedException on attempt to replicate.
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
*** Want to find answers instantly? Here's how... ***
1. Go to
http://groups-beta.google.com/group... />
work?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131564804.366046.310110@.g43g2000cwa.googlegroups.com...
> Do you mean always use parameters with update/insert statements against
> SQL Mobile 2005 and SQL Server CE databases?
> Any known problems when synchronizing with SQL Server (Spanish and
> English versions mainly) via Merge Replication?
> Could you please post an example of an update statement using
> parameters?
> Thanks very much. I'm willing to try tomorrow morning.
>|||> If I use language neutral unseparated format ('19980223 14:23:05') and
> execute an insert or and update statement both against Spanish SQL
> Server and English SQL Server, both statements will work without
> problem?
Yes.
> What about finding rows that match a concrete date where clause?
Did you read the article? I have a long section about just that:
http://www.karaszi.com/SQLServer/in...e.asp#Searching
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131563864.355665.61160@.g14g2000cwa.googlegroups.com...
> Thanks Tibor, but I still have got one question:
> If I use language neutral unseparated format ('19980223 14:23:05') and
> execute an insert or and update statement both against Spanish SQL
> Server and English SQL Server, both statements will work without
> problem?
> Therefore, don't worrying about SQL Server language, if I convert my
> DateTime value (programming in C# for example) to string datatype in
> unseparated format and execute the statement, will work? That is, SQL
> Server will be able to understand it and store it in the appropiate
> format?
> I'll try tomorrow but can't be so easy.
> What about finding rows that match a concrete date where clause? I'm
> using LIKE operator but it is rather language dependant: If SQL Server
> in Spanish, "Ago 10,2005%" is the pattern. If in English, "Aug
> 10,2005%" is the pattern. Can this also be solved wihout using BETWEEN
> and "<" and ">" operators, only with LIKE?
> Thanks very much.
>|||Thanks Ilya for the "using parameters" advice. Many problems solved
this way.
Regards.|||Thanks Tibor for that great article but I think parameters have solved
all my headaches ;-)
Kind regards,|||Yep, I should add to the article that parametizing queries has bunch of adva
ntages, among others
that ADO (or whatever API is used) will handle these things for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131612217.129555.137660@.g47g2000cwa.googlegroups.com...
> Thanks Tibor for that great article but I think parameters have solved
> all my headaches ;-)
> Kind regards,
>
I pick up a date from MonthCalendar control in my device running
Spanish OS version. The format can be this: 23/11/2005 00:00:00.
The server side SQL Server 2005 April CTP is in English and I'm using
Merge Replication to fill a SQL Mobile 2005 database, also in English.
Logically, I'm having trouble with datetime columns.
Do I have to convert Spanish datetime picked up in the interface to
English datetime format everytime I must do an update or insert?
Is there any way to avoid this, to be database datetime format aware?
I was initially trying this, but failed of course:
UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
WHERE appointmentId = 1
Then, after manually building English DateTime format, this query
worked.
Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
0:0:00'
WHERE appointmentId = 1
But that's not the way. I have to avoid manally building string that
will be inserted as DateTimes.
Any solution will be greatly appreciated.I think that this will answer most of your questions:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegroups.com...
> Maybe has been answered many times but here I go:
> I pick up a date from MonthCalendar control in my device running
> Spanish OS version. The format can be this: 23/11/2005 00:00:00.
> The server side SQL Server 2005 April CTP is in English and I'm using
> Merge Replication to fill a SQL Mobile 2005 database, also in English.
> Logically, I'm having trouble with datetime columns.
> Do I have to convert Spanish datetime picked up in the interface to
> English datetime format everytime I must do an update or insert?
> Is there any way to avoid this, to be database datetime format aware?
> I was initially trying this, but failed of course:
> UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
> WHERE appointmentId = 1
> Then, after manually building English DateTime format, this query
> worked.
> Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
> 0:0:00'
> WHERE appointmentId = 1
> But that's not the way. I have to avoid manally building string that
> will be inserted as DateTimes.
> Any solution will be greatly appreciated.
>|||Thanks Tibor, but I still have got one question:
If I use language neutral unseparated format ('19980223 14:23:05') and
execute an insert or and update statement both against Spanish SQL
Server and English SQL Server, both statements will work without
problem?
Therefore, don't worrying about SQL Server language, if I convert my
DateTime value (programming in C# for example) to string datatype in
unseparated format and execute the statement, will work? That is, SQL
Server will be able to understand it and store it in the appropiate
format?
I'll try tomorrow but can't be so easy.
What about finding rows that match a concrete date where clause? I'm
using LIKE operator but it is rather language dependant: If SQL Server
in Spanish, "Ago 10,2005%" is the pattern. If in English, "Aug
10,2005%" is the pattern. Can this also be solved wihout using BETWEEN
and "<" and ">" operators, only with LIKE?
Thanks very much.|||You should always use parameters and pass DateTime instead of string.
That would work for any locale and would also improve performance.
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
*** Want to find answers instantly? Here's how... ***
1. Go to
http://groups-beta.google.com/group... />
work?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegroups.com...
> Maybe has been answered many times but here I go:
> I pick up a date from MonthCalendar control in my device running
> Spanish OS version. The format can be this: 23/11/2005 00:00:00.
> The server side SQL Server 2005 April CTP is in English and I'm using
> Merge Replication to fill a SQL Mobile 2005 database, also in English.
> Logically, I'm having trouble with datetime columns.
> Do I have to convert Spanish datetime picked up in the interface to
> English datetime format everytime I must do an update or insert?
> Is there any way to avoid this, to be database datetime format aware?
> I was initially trying this, but failed of course:
> UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
> WHERE appointmentId = 1
> Then, after manually building English DateTime format, this query
> worked.
> Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
> 0:0:00'
> WHERE appointmentId = 1
> But that's not the way. I have to avoid manally building string that
> will be inserted as DateTimes.
> Any solution will be greatly appreciated.
>|||Do you mean always use parameters with update/insert statements against
SQL Mobile 2005 and SQL Server CE databases?
Any known problems when synchronizing with SQL Server (Spanish and
English versions mainly) via Merge Replication?
Could you please post an example of an update statement using
parameters?
Thanks very much. I'm willing to try tomorrow morning.|||Yes, always. That makes conversion unnecessary and eliminates format
problems completely.
For example, DateTime is format-less, it's just a 64 bit integer.
The problem with formats only arises if you convert it to/from string. Which
also takes a lot of time.
Update/Insert sample is available in VS documentation, just look up
SqlCeCommand.Parameters property.
If your SQL Server DB uses collation which is not supported on the device,
you'll get NotSupportedException on attempt to replicate.
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
*** Want to find answers instantly? Here's how... ***
1. Go to
http://groups-beta.google.com/group... />
work?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131564804.366046.310110@.g43g2000cwa.googlegroups.com...
> Do you mean always use parameters with update/insert statements against
> SQL Mobile 2005 and SQL Server CE databases?
> Any known problems when synchronizing with SQL Server (Spanish and
> English versions mainly) via Merge Replication?
> Could you please post an example of an update statement using
> parameters?
> Thanks very much. I'm willing to try tomorrow morning.
>|||> If I use language neutral unseparated format ('19980223 14:23:05') and
> execute an insert or and update statement both against Spanish SQL
> Server and English SQL Server, both statements will work without
> problem?
Yes.
> What about finding rows that match a concrete date where clause?
Did you read the article? I have a long section about just that:
http://www.karaszi.com/SQLServer/in...e.asp#Searching
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131563864.355665.61160@.g14g2000cwa.googlegroups.com...
> Thanks Tibor, but I still have got one question:
> If I use language neutral unseparated format ('19980223 14:23:05') and
> execute an insert or and update statement both against Spanish SQL
> Server and English SQL Server, both statements will work without
> problem?
> Therefore, don't worrying about SQL Server language, if I convert my
> DateTime value (programming in C# for example) to string datatype in
> unseparated format and execute the statement, will work? That is, SQL
> Server will be able to understand it and store it in the appropiate
> format?
> I'll try tomorrow but can't be so easy.
> What about finding rows that match a concrete date where clause? I'm
> using LIKE operator but it is rather language dependant: If SQL Server
> in Spanish, "Ago 10,2005%" is the pattern. If in English, "Aug
> 10,2005%" is the pattern. Can this also be solved wihout using BETWEEN
> and "<" and ">" operators, only with LIKE?
> Thanks very much.
>|||Thanks Ilya for the "using parameters" advice. Many problems solved
this way.
Regards.|||Thanks Tibor for that great article but I think parameters have solved
all my headaches ;-)
Kind regards,|||Yep, I should add to the article that parametizing queries has bunch of adva
ntages, among others
that ADO (or whatever API is used) will handle these things for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131612217.129555.137660@.g47g2000cwa.googlegroups.com...
> Thanks Tibor for that great article but I think parameters have solved
> all my headaches ;-)
> Kind regards,
>
Being DateTime Formats aware when accessing database
Maybe has been answered many times but here I go:
I pick up a date from MonthCalendar control in my device running
Spanish OS version. The format can be this: 23/11/2005 00:00:00.
The server side SQL Server 2005 April CTP is in English and I'm using
Merge Replication to fill a SQL Mobile 2005 database, also in English.
Logically, I'm having trouble with datetime columns.
Do I have to convert Spanish datetime picked up in the interface to
English datetime format everytime I must do an update or insert?
Is there any way to avoid this, to be database datetime format aware?
I was initially trying this, but failed of course:
UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
WHERE appointmentId = 1
Then, after manually building English DateTime format, this query
worked.
Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
0:0:00'
WHERE appointmentId = 1
But that's not the way. I have to avoid manally building string that
will be inserted as DateTimes.
Any solution will be greatly appreciated.I think that this will answer most of your questions:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegroups.com...
> Maybe has been answered many times but here I go:
> I pick up a date from MonthCalendar control in my device running
> Spanish OS version. The format can be this: 23/11/2005 00:00:00.
> The server side SQL Server 2005 April CTP is in English and I'm using
> Merge Replication to fill a SQL Mobile 2005 database, also in English.
> Logically, I'm having trouble with datetime columns.
> Do I have to convert Spanish datetime picked up in the interface to
> English datetime format everytime I must do an update or insert?
> Is there any way to avoid this, to be database datetime format aware?
> I was initially trying this, but failed of course:
> UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
> WHERE appointmentId = 1
> Then, after manually building English DateTime format, this query
> worked.
> Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
> 0:0:00'
> WHERE appointmentId = 1
> But that's not the way. I have to avoid manally building string that
> will be inserted as DateTimes.
> Any solution will be greatly appreciated.
>|||Thanks Tibor, but I still have got one question:
If I use language neutral unseparated format ('19980223 14:23:05') and
execute an insert or and update statement both against Spanish SQL
Server and English SQL Server, both statements will work without
problem?
Therefore, don't worrying about SQL Server language, if I convert my
DateTime value (programming in C# for example) to string datatype in
unseparated format and execute the statement, will work? That is, SQL
Server will be able to understand it and store it in the appropiate
format?
I'll try tomorrow but can't be so easy.
What about finding rows that match a concrete date where clause? I'm
using LIKE operator but it is rather language dependant: If SQL Server
in Spanish, "Ago 10,2005%" is the pattern. If in English, "Aug
10,2005%" is the pattern. Can this also be solved wihout using BETWEEN
and "<" and ">" operators, only with LIKE?
Thanks very much.|||You should always use parameters and pass DateTime instead of string.
That would work for any locale and would also improve performance.
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
*** Want to find answers instantly? Here's how... ***
1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegroups.com...
> Maybe has been answered many times but here I go:
> I pick up a date from MonthCalendar control in my device running
> Spanish OS version. The format can be this: 23/11/2005 00:00:00.
> The server side SQL Server 2005 April CTP is in English and I'm using
> Merge Replication to fill a SQL Mobile 2005 database, also in English.
> Logically, I'm having trouble with datetime columns.
> Do I have to convert Spanish datetime picked up in the interface to
> English datetime format everytime I must do an update or insert?
> Is there any way to avoid this, to be database datetime format aware?
> I was initially trying this, but failed of course:
> UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
> WHERE appointmentId = 1
> Then, after manually building English DateTime format, this query
> worked.
> Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
> 0:0:00'
> WHERE appointmentId = 1
> But that's not the way. I have to avoid manally building string that
> will be inserted as DateTimes.
> Any solution will be greatly appreciated.
>|||Do you mean always use parameters with update/insert statements against
SQL Mobile 2005 and SQL Server CE databases?
Any known problems when synchronizing with SQL Server (Spanish and
English versions mainly) via Merge Replication?
Could you please post an example of an update statement using
parameters?
Thanks very much. I'm willing to try tomorrow morning.|||Yes, always. That makes conversion unnecessary and eliminates format
problems completely.
For example, DateTime is format-less, it's just a 64 bit integer.
The problem with formats only arises if you convert it to/from string. Which
also takes a lot of time.
Update/Insert sample is available in VS documentation, just look up
SqlCeCommand.Parameters property.
If your SQL Server DB uses collation which is not supported on the device,
you'll get NotSupportedException on attempt to replicate.
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
*** Want to find answers instantly? Here's how... ***
1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131564804.366046.310110@.g43g2000cwa.googlegroups.com...
> Do you mean always use parameters with update/insert statements against
> SQL Mobile 2005 and SQL Server CE databases?
> Any known problems when synchronizing with SQL Server (Spanish and
> English versions mainly) via Merge Replication?
> Could you please post an example of an update statement using
> parameters?
> Thanks very much. I'm willing to try tomorrow morning.
>|||> If I use language neutral unseparated format ('19980223 14:23:05') and
> execute an insert or and update statement both against Spanish SQL
> Server and English SQL Server, both statements will work without
> problem?
Yes.
> What about finding rows that match a concrete date where clause?
Did you read the article? I have a long section about just that:
http://www.karaszi.com/SQLServer/info_datetime.asp#Searching
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131563864.355665.61160@.g14g2000cwa.googlegroups.com...
> Thanks Tibor, but I still have got one question:
> If I use language neutral unseparated format ('19980223 14:23:05') and
> execute an insert or and update statement both against Spanish SQL
> Server and English SQL Server, both statements will work without
> problem?
> Therefore, don't worrying about SQL Server language, if I convert my
> DateTime value (programming in C# for example) to string datatype in
> unseparated format and execute the statement, will work? That is, SQL
> Server will be able to understand it and store it in the appropiate
> format?
> I'll try tomorrow but can't be so easy.
> What about finding rows that match a concrete date where clause? I'm
> using LIKE operator but it is rather language dependant: If SQL Server
> in Spanish, "Ago 10,2005%" is the pattern. If in English, "Aug
> 10,2005%" is the pattern. Can this also be solved wihout using BETWEEN
> and "<" and ">" operators, only with LIKE?
> Thanks very much.
>|||Thanks Ilya for the "using parameters" advice. Many problems solved
this way.
Regards.|||Thanks Tibor for that great article but I think parameters have solved
all my headaches ;-)
Kind regards,|||Yep, I should add to the article that parametizing queries has bunch of advantages, among others
that ADO (or whatever API is used) will handle these things for you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131612217.129555.137660@.g47g2000cwa.googlegroups.com...
> Thanks Tibor for that great article but I think parameters have solved
> all my headaches ;-)
> Kind regards,
>
I pick up a date from MonthCalendar control in my device running
Spanish OS version. The format can be this: 23/11/2005 00:00:00.
The server side SQL Server 2005 April CTP is in English and I'm using
Merge Replication to fill a SQL Mobile 2005 database, also in English.
Logically, I'm having trouble with datetime columns.
Do I have to convert Spanish datetime picked up in the interface to
English datetime format everytime I must do an update or insert?
Is there any way to avoid this, to be database datetime format aware?
I was initially trying this, but failed of course:
UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
WHERE appointmentId = 1
Then, after manually building English DateTime format, this query
worked.
Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
0:0:00'
WHERE appointmentId = 1
But that's not the way. I have to avoid manally building string that
will be inserted as DateTimes.
Any solution will be greatly appreciated.I think that this will answer most of your questions:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegroups.com...
> Maybe has been answered many times but here I go:
> I pick up a date from MonthCalendar control in my device running
> Spanish OS version. The format can be this: 23/11/2005 00:00:00.
> The server side SQL Server 2005 April CTP is in English and I'm using
> Merge Replication to fill a SQL Mobile 2005 database, also in English.
> Logically, I'm having trouble with datetime columns.
> Do I have to convert Spanish datetime picked up in the interface to
> English datetime format everytime I must do an update or insert?
> Is there any way to avoid this, to be database datetime format aware?
> I was initially trying this, but failed of course:
> UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
> WHERE appointmentId = 1
> Then, after manually building English DateTime format, this query
> worked.
> Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
> 0:0:00'
> WHERE appointmentId = 1
> But that's not the way. I have to avoid manally building string that
> will be inserted as DateTimes.
> Any solution will be greatly appreciated.
>|||Thanks Tibor, but I still have got one question:
If I use language neutral unseparated format ('19980223 14:23:05') and
execute an insert or and update statement both against Spanish SQL
Server and English SQL Server, both statements will work without
problem?
Therefore, don't worrying about SQL Server language, if I convert my
DateTime value (programming in C# for example) to string datatype in
unseparated format and execute the statement, will work? That is, SQL
Server will be able to understand it and store it in the appropiate
format?
I'll try tomorrow but can't be so easy.
What about finding rows that match a concrete date where clause? I'm
using LIKE operator but it is rather language dependant: If SQL Server
in Spanish, "Ago 10,2005%" is the pattern. If in English, "Aug
10,2005%" is the pattern. Can this also be solved wihout using BETWEEN
and "<" and ">" operators, only with LIKE?
Thanks very much.|||You should always use parameters and pass DateTime instead of string.
That would work for any locale and would also improve performance.
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
*** Want to find answers instantly? Here's how... ***
1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegroups.com...
> Maybe has been answered many times but here I go:
> I pick up a date from MonthCalendar control in my device running
> Spanish OS version. The format can be this: 23/11/2005 00:00:00.
> The server side SQL Server 2005 April CTP is in English and I'm using
> Merge Replication to fill a SQL Mobile 2005 database, also in English.
> Logically, I'm having trouble with datetime columns.
> Do I have to convert Spanish datetime picked up in the interface to
> English datetime format everytime I must do an update or insert?
> Is there any way to avoid this, to be database datetime format aware?
> I was initially trying this, but failed of course:
> UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
> WHERE appointmentId = 1
> Then, after manually building English DateTime format, this query
> worked.
> Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
> 0:0:00'
> WHERE appointmentId = 1
> But that's not the way. I have to avoid manally building string that
> will be inserted as DateTimes.
> Any solution will be greatly appreciated.
>|||Do you mean always use parameters with update/insert statements against
SQL Mobile 2005 and SQL Server CE databases?
Any known problems when synchronizing with SQL Server (Spanish and
English versions mainly) via Merge Replication?
Could you please post an example of an update statement using
parameters?
Thanks very much. I'm willing to try tomorrow morning.|||Yes, always. That makes conversion unnecessary and eliminates format
problems completely.
For example, DateTime is format-less, it's just a 64 bit integer.
The problem with formats only arises if you convert it to/from string. Which
also takes a lot of time.
Update/Insert sample is available in VS documentation, just look up
SqlCeCommand.Parameters property.
If your SQL Server DB uses collation which is not supported on the device,
you'll get NotSupportedException on attempt to replicate.
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
*** Want to find answers instantly? Here's how... ***
1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131564804.366046.310110@.g43g2000cwa.googlegroups.com...
> Do you mean always use parameters with update/insert statements against
> SQL Mobile 2005 and SQL Server CE databases?
> Any known problems when synchronizing with SQL Server (Spanish and
> English versions mainly) via Merge Replication?
> Could you please post an example of an update statement using
> parameters?
> Thanks very much. I'm willing to try tomorrow morning.
>|||> If I use language neutral unseparated format ('19980223 14:23:05') and
> execute an insert or and update statement both against Spanish SQL
> Server and English SQL Server, both statements will work without
> problem?
Yes.
> What about finding rows that match a concrete date where clause?
Did you read the article? I have a long section about just that:
http://www.karaszi.com/SQLServer/info_datetime.asp#Searching
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131563864.355665.61160@.g14g2000cwa.googlegroups.com...
> Thanks Tibor, but I still have got one question:
> If I use language neutral unseparated format ('19980223 14:23:05') and
> execute an insert or and update statement both against Spanish SQL
> Server and English SQL Server, both statements will work without
> problem?
> Therefore, don't worrying about SQL Server language, if I convert my
> DateTime value (programming in C# for example) to string datatype in
> unseparated format and execute the statement, will work? That is, SQL
> Server will be able to understand it and store it in the appropiate
> format?
> I'll try tomorrow but can't be so easy.
> What about finding rows that match a concrete date where clause? I'm
> using LIKE operator but it is rather language dependant: If SQL Server
> in Spanish, "Ago 10,2005%" is the pattern. If in English, "Aug
> 10,2005%" is the pattern. Can this also be solved wihout using BETWEEN
> and "<" and ">" operators, only with LIKE?
> Thanks very much.
>|||Thanks Ilya for the "using parameters" advice. Many problems solved
this way.
Regards.|||Thanks Tibor for that great article but I think parameters have solved
all my headaches ;-)
Kind regards,|||Yep, I should add to the article that parametizing queries has bunch of advantages, among others
that ADO (or whatever API is used) will handle these things for you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131612217.129555.137660@.g47g2000cwa.googlegroups.com...
> Thanks Tibor for that great article but I think parameters have solved
> all my headaches ;-)
> Kind regards,
>
Beginning and End of Month Dates
Basically what I am trying to do is as Crystal reports goes through records I want it to check a date field to see if they are the first day or last day of the month. If the first day of the month falls on a Saturday, (DayofWeek = 7), I want it to decrement the date by 1 and if the last day of month falls on a Sunday, (DayofWeek = 1), I want it to decrement the date by 2.
Any ideas?The way of distinguishing between both months can be done like this:
If Month({DateField}) <> Month({DateField}+1) //Obviously is -1 to check for the 1st day
if DayOfWeek({DateField})=6 then
Do This
else if DayOfWeek({DateField})=7 then
Do That
I cant recall if DayOfWeek was 1-based or 0-based but the concept applies. :)|||I don't understand what you mean by:
"If Month({DateField}) <> Month({DateField}+1)"
What does this formula do?|||This formula only does the next set of steps IF the datefield is the last day of the month.
Ie, if you ran this today (31/5/05) :
"If Month({DateField}) <> Month({DateField}+1)"
If Month(31/5/05) <> Month(1/6/05)
...which is true. Today is the last day of the month.
If you want to know if the date is the first of the month, use -1.
Any ideas?The way of distinguishing between both months can be done like this:
If Month({DateField}) <> Month({DateField}+1) //Obviously is -1 to check for the 1st day
if DayOfWeek({DateField})=6 then
Do This
else if DayOfWeek({DateField})=7 then
Do That
I cant recall if DayOfWeek was 1-based or 0-based but the concept applies. :)|||I don't understand what you mean by:
"If Month({DateField}) <> Month({DateField}+1)"
What does this formula do?|||This formula only does the next set of steps IF the datefield is the last day of the month.
Ie, if you ran this today (31/5/05) :
"If Month({DateField}) <> Month({DateField}+1)"
If Month(31/5/05) <> Month(1/6/05)
...which is true. Today is the last day of the month.
If you want to know if the date is the first of the month, use -1.
Sunday, February 12, 2012
Beginner - Help needed in parameter passing
Hello,
I want to pass the parameter from VB6 to crystal report to get the reports. I want to pass the string and date parameters. I tried. But, I am getting errors. Pl. help and give the syntax for passing (both the types of parameters).To pass a string
Cr.Parameter(0)='" & string & "'
To pass a Date
Cr.Parameter(0)=#" & date & "#|||When passing parameters like the one you give, it shows syntax error (expecting expression) during the run time. Is anything has to be done in CR?|||Which version of CR you are using?|||CR version 8.5. VB 6 - Actually there is no "parameter" property. I selected parameter field property. It shows the syntax error. When parameter alone is given it shows "missing expression" error. Pl. help .
Meenakshi.R|||Pl. help me in parameter passing. I used the following code
crystalreport1.selectionformula="{servmst.mrtcd}=' "&wmrtcd&" ' "
by following a book I referred. wmrtcd is the variable I assigned to get the value at run time. But the code shows the error message 'Syntax error'. Pl. help me in completion of my project|||What is the datatype of mrtcd?
If it is numeric, then
crystalreport1.selectionformula="{servmst.mrtcd}= "&val(wmrtcd)&""|||wmrtcd is a string value. I typed like this
CrystalReport1.SelectionFormula = "{servmst.mrtcd}= "&wmrtcd&""
It shows syntax error.|||Anybody Pl. help me in parameter passing.|||How about using Selection formula from the menu?
Open the report goto Report->Selection Formulas->Record
then write this code
{servmst.mrtcd}= parametername|||Sir,
I wrote the code as you given. But whenever I select the parameter from the VB code it displays the first record only. What to do? But I am selecting some other record. Here I have listed the code I used. Pl. guide me
Private Sub Cmd_run_Click()
MsgBox wmrtcd
CrystalReport1.ReportFileName = "f:\meena\project\test\sermrtcd.rpt"
CrystalReport1.Action = 1
With the message box, I can get the parameter I selected.|||Hi!
plz try this format
String :
---
wmrtcd="{emp_det.emp_id} = '"& trim(your_id) &"'"
CrystalReport1.ReportFileName = "f:\meena\project\test\sermrtcd.rpt"
CrystalReport1.SelectionFormula = wmrtcd
CrystalReport1.Action = 2
Numeric :
----
wmrtcd="{emp_det.emp_id} = "& trim(your_id) &""
CrystalReport1.ReportFileName = "f:\meena\project\test\sermrtcd.rpt"
CrystalReport1.SelectionFormula = wmrtcd
CrystalReport1.Action = 2
Date :
---
Dim da, str As Variant
Dim a, b, c, e As Variant
da=cdate(rsEmp.fields("emp_det.emp_doj").value)
a = Day(da.Value)
b = Month(da.Value)
c = Year(da.Value)
d = "#" & c & "/" & b & "/" & a & "#"
wmrtcd="{emp_det.emp_doj} = '"& d &"'"
CrystalReport1.ReportFileName = "f:\meena\project\test\sermrtcd.rpt"
CrystalReport1.SelectionFormula = wmrtcd
CrystalReport1.Action = 2
note : crystal report date format is "YYYY/MM/DD"|||Hi,
I can't understand what is "your_id". But I tried this code
MsgBox wmrtcd
wmrtcd = "{servmst.mrtcd} = '" & Trim(wmrtcd) & "'"
CrystalReport1.ReportFileName = "f:\meena\project\test\sermrtcd.rpt"
CrystalReport1.SelectionFormula = wmrtcd
CrystalReport1.Action = 1
It shows the following error.
Run Time error '20515'
Error in file f:\meena\project\test\sermst.rpt
Error in formula <Record_Selection>
' The remaining text does not appear to be part of the formula.
What to do? Pl. help me.|||Make sure you have given the correct column name|||Hi!
Its a Parameter Value.
EmpID='ii-0406'
EmpSal=6000
EmpDoj="13/09/2004"
String :
---
wmrtcd="{emp_det.emp_id} = '"& trim(EmpID) &"'"
CrystalReport1.ReportFileName = "f:\meena\project\test\sermrtcd.rpt"
CrystalReport1.SelectionFormula = wmrtcd
CrystalReport1.Action = 2
Numeric :
----
wmrtcd="{emp_det.emp_id} = "& trim(EmpSal) &""
CrystalReport1.ReportFileName = "f:\meena\project\test\sermrtcd.rpt"
CrystalReport1.SelectionFormula = wmrtcd
CrystalReport1.Action = 2
Date :
---
Dim da, str As Variant
Dim a, b, c, e As Variant
da=cdate(EmpDoj)
a = Day(da.Value)
b = Month(da.Value)
c = Year(da.Value)
d = "#" & c & "/" & b & "/" & a & "#"
wmrtcd="{emp_det.emp_doj} = '"& d &"'"
CrystalReport1.ReportFileName = "f:\meena\project\test\sermrtcd.rpt"
CrystalReport1.SelectionFormula = wmrtcd
CrystalReport1.Action = 2|||Hai,
I tried with the following code.
MsgBox wmrtcd
wsel = "{servmst.mrtcd} = '" & Trim(wmrtcd) & "'"
MsgBox wsel
CrystalReport1.ReportFileName = "f:\meena\project\test\sermrtcd.rpt"
CrystalReport1.SelectionFormula = wsel
But still gets the same error. With the mesg box it shows
servmst.mrtcd='A001'
When debugging the error, it shows
wsel = "{servmst.mrtcd}='A001'" (when mouse is dragged over the line.
Pl. help me from coming out of this problem.
Regards|||Hi! see this link
http://support.businessobjects.com/library/kbase/articles/c2010935.asp
else
plz try to add the report as dsr.
1) Visual Basic -->Project Explorere-->Add-->More ActiveXdesigner-->Crystal Reports8.5
then customize ur report as u like.
i am attaching a sample report here plz see this.
regards
muthu|||Hai,
I have included that crystal report using designer. But how to name the object "Crystal viewer". In the sample code it is written as crviewer1. I have tried with crystalviewer, crviewer. But whatever I type it shows " No object". Pl. help.
Regards|||Help me in completion of the project|||You need to use Crystal Viewer and use that name in the code|||Hi! see this link
http://support.businessobjects.com/library/kbase/articles/c2010935.asp
else
plz try to add the report as dsr.
1) Visual Basic -->Project Explorere-->Add-->More ActiveXdesigner-->Crystal Reports8.5
then customize ur report as u like.
i am attaching a sample report here plz see this.
regards
muthu
I want to pass the parameter from VB6 to crystal report to get the reports. I want to pass the string and date parameters. I tried. But, I am getting errors. Pl. help and give the syntax for passing (both the types of parameters).To pass a string
Cr.Parameter(0)='" & string & "'
To pass a Date
Cr.Parameter(0)=#" & date & "#|||When passing parameters like the one you give, it shows syntax error (expecting expression) during the run time. Is anything has to be done in CR?|||Which version of CR you are using?|||CR version 8.5. VB 6 - Actually there is no "parameter" property. I selected parameter field property. It shows the syntax error. When parameter alone is given it shows "missing expression" error. Pl. help .
Meenakshi.R|||Pl. help me in parameter passing. I used the following code
crystalreport1.selectionformula="{servmst.mrtcd}=' "&wmrtcd&" ' "
by following a book I referred. wmrtcd is the variable I assigned to get the value at run time. But the code shows the error message 'Syntax error'. Pl. help me in completion of my project|||What is the datatype of mrtcd?
If it is numeric, then
crystalreport1.selectionformula="{servmst.mrtcd}= "&val(wmrtcd)&""|||wmrtcd is a string value. I typed like this
CrystalReport1.SelectionFormula = "{servmst.mrtcd}= "&wmrtcd&""
It shows syntax error.|||Anybody Pl. help me in parameter passing.|||How about using Selection formula from the menu?
Open the report goto Report->Selection Formulas->Record
then write this code
{servmst.mrtcd}= parametername|||Sir,
I wrote the code as you given. But whenever I select the parameter from the VB code it displays the first record only. What to do? But I am selecting some other record. Here I have listed the code I used. Pl. guide me
Private Sub Cmd_run_Click()
MsgBox wmrtcd
CrystalReport1.ReportFileName = "f:\meena\project\test\sermrtcd.rpt"
CrystalReport1.Action = 1
With the message box, I can get the parameter I selected.|||Hi!
plz try this format
String :
---
wmrtcd="{emp_det.emp_id} = '"& trim(your_id) &"'"
CrystalReport1.ReportFileName = "f:\meena\project\test\sermrtcd.rpt"
CrystalReport1.SelectionFormula = wmrtcd
CrystalReport1.Action = 2
Numeric :
----
wmrtcd="{emp_det.emp_id} = "& trim(your_id) &""
CrystalReport1.ReportFileName = "f:\meena\project\test\sermrtcd.rpt"
CrystalReport1.SelectionFormula = wmrtcd
CrystalReport1.Action = 2
Date :
---
Dim da, str As Variant
Dim a, b, c, e As Variant
da=cdate(rsEmp.fields("emp_det.emp_doj").value)
a = Day(da.Value)
b = Month(da.Value)
c = Year(da.Value)
d = "#" & c & "/" & b & "/" & a & "#"
wmrtcd="{emp_det.emp_doj} = '"& d &"'"
CrystalReport1.ReportFileName = "f:\meena\project\test\sermrtcd.rpt"
CrystalReport1.SelectionFormula = wmrtcd
CrystalReport1.Action = 2
note : crystal report date format is "YYYY/MM/DD"|||Hi,
I can't understand what is "your_id". But I tried this code
MsgBox wmrtcd
wmrtcd = "{servmst.mrtcd} = '" & Trim(wmrtcd) & "'"
CrystalReport1.ReportFileName = "f:\meena\project\test\sermrtcd.rpt"
CrystalReport1.SelectionFormula = wmrtcd
CrystalReport1.Action = 1
It shows the following error.
Run Time error '20515'
Error in file f:\meena\project\test\sermst.rpt
Error in formula <Record_Selection>
' The remaining text does not appear to be part of the formula.
What to do? Pl. help me.|||Make sure you have given the correct column name|||Hi!
Its a Parameter Value.
EmpID='ii-0406'
EmpSal=6000
EmpDoj="13/09/2004"
String :
---
wmrtcd="{emp_det.emp_id} = '"& trim(EmpID) &"'"
CrystalReport1.ReportFileName = "f:\meena\project\test\sermrtcd.rpt"
CrystalReport1.SelectionFormula = wmrtcd
CrystalReport1.Action = 2
Numeric :
----
wmrtcd="{emp_det.emp_id} = "& trim(EmpSal) &""
CrystalReport1.ReportFileName = "f:\meena\project\test\sermrtcd.rpt"
CrystalReport1.SelectionFormula = wmrtcd
CrystalReport1.Action = 2
Date :
---
Dim da, str As Variant
Dim a, b, c, e As Variant
da=cdate(EmpDoj)
a = Day(da.Value)
b = Month(da.Value)
c = Year(da.Value)
d = "#" & c & "/" & b & "/" & a & "#"
wmrtcd="{emp_det.emp_doj} = '"& d &"'"
CrystalReport1.ReportFileName = "f:\meena\project\test\sermrtcd.rpt"
CrystalReport1.SelectionFormula = wmrtcd
CrystalReport1.Action = 2|||Hai,
I tried with the following code.
MsgBox wmrtcd
wsel = "{servmst.mrtcd} = '" & Trim(wmrtcd) & "'"
MsgBox wsel
CrystalReport1.ReportFileName = "f:\meena\project\test\sermrtcd.rpt"
CrystalReport1.SelectionFormula = wsel
But still gets the same error. With the mesg box it shows
servmst.mrtcd='A001'
When debugging the error, it shows
wsel = "{servmst.mrtcd}='A001'" (when mouse is dragged over the line.
Pl. help me from coming out of this problem.
Regards|||Hi! see this link
http://support.businessobjects.com/library/kbase/articles/c2010935.asp
else
plz try to add the report as dsr.
1) Visual Basic -->Project Explorere-->Add-->More ActiveXdesigner-->Crystal Reports8.5
then customize ur report as u like.
i am attaching a sample report here plz see this.
regards
muthu|||Hai,
I have included that crystal report using designer. But how to name the object "Crystal viewer". In the sample code it is written as crviewer1. I have tried with crystalviewer, crviewer. But whatever I type it shows " No object". Pl. help.
Regards|||Help me in completion of the project|||You need to use Crystal Viewer and use that name in the code|||Hi! see this link
http://support.businessobjects.com/library/kbase/articles/c2010935.asp
else
plz try to add the report as dsr.
1) Visual Basic -->Project Explorere-->Add-->More ActiveXdesigner-->Crystal Reports8.5
then customize ur report as u like.
i am attaching a sample report here plz see this.
regards
muthu
Begin Date and End Date
Hi all
I want my users to be able to pick between the beginning date and the end date, for Example with the code I have below they can enter in a final suit date and then they get the results through query or report or whatever, what I want for them to be able to do is enter a beginning date and an end date Between [Enter the Start Date] And [Enter the End Date]
thats how access interprets it (Jet SQL) how would I do that in SQL Server?? Help Please
SELECT TM#, LASTNAME, FIRSTNAME, CONDITIONAL, DATEOFCONDITIONAL, FINALSUITDONE
FROM dbo.EmployeeGamingLicense
WHERE (FINALSUITDONE = @.Enter_FINALSUITDONE)I would have my stored procedure accept those dates as parameters. Presuming Access is still your front end, get the values from the user via a form, then pass them to the SP.|||Yes thats the front end, but can you elaborate because I'm am not getting anywhere at all..Oh GOOD GRAVY THE FRUSTRATION LEVELS IS CLIMBING..LOL|||What exactly are you trying to accomplish? How are you calling/executing the SP?|||I am trying to get it so that users can enter the beginning of the date like 12/1/2004 to 12/31/2004
I have a parameter in an ADP that allows them to enter the FinalSuit date 12/31/2005 and they see all the final suit for that date but I was wanting to give them a range, does that make sense|||To be honest, I don't use ADP's so I'm probably not your best source of info. I don't see the advantages of them, and I know of 3 Access MVP's that recommend against them.|||But when you work for a company that refuses to shell out money for a better front end then you use what you got|||Oh, Access is fine; I use it all the time. I simply stick with MDB/MDE front ends, not ADP. They seem to handle form references differently, so I figure someone with more ADP experience will be better able to help.|||Thanks for trying Its just frustrating cause Jet SQL and Sql server can be like apples and oranges|||SELECT [Main Table].[IR Number], [Main Table].Date, [Main Table].Inspector, [Main Table].Area, [Main Table].Violation, [Main Table].[Violation Type], [Main Table].Loss, [Main Table].[Loss Type], [Main Table].Employee, [Main Table].Action, [Main Table].[Action Type], [Main Table].Notes
FROM [Main Table]
WHERE ((([Main Table].Date) Between [Enter the Start Date] And [Enter the End Date]))
ORDER BY [Main Table].[IR Number]
GO
I want my users to be able to pick between the beginning date and the end date, for Example with the code I have below they can enter in a final suit date and then they get the results through query or report or whatever, what I want for them to be able to do is enter a beginning date and an end date Between [Enter the Start Date] And [Enter the End Date]
thats how access interprets it (Jet SQL) how would I do that in SQL Server?? Help Please
SELECT TM#, LASTNAME, FIRSTNAME, CONDITIONAL, DATEOFCONDITIONAL, FINALSUITDONE
FROM dbo.EmployeeGamingLicense
WHERE (FINALSUITDONE = @.Enter_FINALSUITDONE)I would have my stored procedure accept those dates as parameters. Presuming Access is still your front end, get the values from the user via a form, then pass them to the SP.|||Yes thats the front end, but can you elaborate because I'm am not getting anywhere at all..Oh GOOD GRAVY THE FRUSTRATION LEVELS IS CLIMBING..LOL|||What exactly are you trying to accomplish? How are you calling/executing the SP?|||I am trying to get it so that users can enter the beginning of the date like 12/1/2004 to 12/31/2004
I have a parameter in an ADP that allows them to enter the FinalSuit date 12/31/2005 and they see all the final suit for that date but I was wanting to give them a range, does that make sense|||To be honest, I don't use ADP's so I'm probably not your best source of info. I don't see the advantages of them, and I know of 3 Access MVP's that recommend against them.|||But when you work for a company that refuses to shell out money for a better front end then you use what you got|||Oh, Access is fine; I use it all the time. I simply stick with MDB/MDE front ends, not ADP. They seem to handle form references differently, so I figure someone with more ADP experience will be better able to help.|||Thanks for trying Its just frustrating cause Jet SQL and Sql server can be like apples and oranges|||SELECT [Main Table].[IR Number], [Main Table].Date, [Main Table].Inspector, [Main Table].Area, [Main Table].Violation, [Main Table].[Violation Type], [Main Table].Loss, [Main Table].[Loss Type], [Main Table].Employee, [Main Table].Action, [Main Table].[Action Type], [Main Table].Notes
FROM [Main Table]
WHERE ((([Main Table].Date) Between [Enter the Start Date] And [Enter the End Date]))
ORDER BY [Main Table].[IR Number]
GO
Subscribe to:
Posts (Atom)