|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
date/time fieldsI know a lot has already been told about date/time fields in a database but still confuses me, specif when dealing with SQLserver(Express). It seems that sqlserver only accepts the date in a "yyyyMMdd" format? (difference between Express and MSDE2000A ?) What is the one and only true way to deal with this problem in VB2005: Local settings are Dutch (Belgium) ; thus date is in "dd/MM/yy" (or perhaps dd/MM/yyyy) and time in "hh:mm:ss" dim MyDateVar as string, MyIdVar as Integer dim MyCommand = New Sqlcommand("",Connection) MyCommand.commandtext = "Update MyTable Set MyDatefield = '" & MyDateVar & "' Where MyIdField = " & MyIdVar = SomeIntegerValue MyCommand.executenonquery How to deal with the MyDateVar when: 1. The Variable comes from a textbox knowing that the user puts in dd/MMyyyy In this case there is no need to have the time with it. 2. The date comes from a datetimepicker control (MyDateVar = DtPicker.Value)? 3. The date and time comes from the system MyDateVar= Format(DateTime.Now, "yyyyMMdd") seems to work but Format(DateTime.Now, "yyyyMMdd.hhmmss") gives a runtime error So, any help and/or suggestion on this will be greatly appreciated. Thanks and greetings to all Jerome Jerome,
It is very simple you should never use a date/time as a string, however always as a DateTime field. When you present that to a textbox, than you can use the overloaded toString with the Iformatprovider http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemglobalizationdatetimeformatinfoclasstopic.asp If you get it back you can use the Cdate mydateField = Cdate(mytextbox.text) And if you want to supply it to a database you use the parameters. http://www.vb-tips.com/default.aspx?ID=886bba68-8a2f-4b99-8f66-7139b8970071 Maybe even better to show with this more extended but with a Dutch datetime in it and for Access (OleDb) http://www.vb-tips.com/default.aspx?ID=550279ec-6767-44ff-aaa3-eb8b44af0137 In fact is that all. (The datetimepicker.value returns a datetime field). Cor
Show quote
Hide quote
"Jerome" <Jomm***@fake.com> schrieb Store date/time values *always* in variables of type DateTime.> Hallo, > I know a lot has already been told about date/time fields in a > database but still confuses me, specif when dealing with > SQLserver(Express). > It seems that sqlserver only accepts the date in a "yyyyMMdd" > format? (difference between Express and MSDE2000A ?) > What is the one and only true way to deal with this problem in > VB2005: Local settings are Dutch (Belgium) ; thus date is in > "dd/MM/yy" (or perhaps dd/MM/yyyy) and time in "hh:mm:ss" > > dim MyDateVar as string, MyIdVar as Integer > dim MyCommand = New Sqlcommand("",Connection) > MyCommand.commandtext = "Update MyTable Set MyDatefield = '" & > MyDateVar & "' Where MyIdField = " & MyIdVar = SomeIntegerValue > MyCommand.executenonquery > > How to deal with the MyDateVar when: > 1. I guess this is "dd/MM/yyyy"?> The Variable comes from a textbox knowing that the user puts in > dd/MMyyyy In this case there is no need to have the time with it. Use Date.ParseExact (or Date.Parse) to convert form string to DateTime. > 2. Declare MyDateVar as DateTime and everything is fine. Maybe you have to cut > The date comes from a datetimepicker control > (MyDateVar = DtPicker.Value)? off the time: MyDateVar = DtPicker.Value.Date > 3. You don't need this for an SQL.> The date and time comes from the system > MyDateVar= Format(DateTime.Now, "yyyyMMdd") seems to work but > Format(DateTime.Now, "yyyyMMdd.hhmmss") gives a runtime error > So, any help and/or suggestion on this will be greatly appreciated. Use parameters with MyCommand. You won't have to care about the format anymore: dim MyDateVar as datetime dim MyCommand = New Sqlcommand("",Connection) MyDateVar = date.parse(mytextbox.text) - or - MyDateVar = DtPicker.Value.Date MyCommand.commandtext = "Update MyTable Set MyDatefield = @mydate Where MyIdField = @id" with mycommand.parameters .add("@mydate", SqlDbType.DateTime).value = mydatevar .add("@id", SqlDbType.Int).value = SomeIntegerValue end with MyCommand.executenonquery In addition, see the T-SQL reference: http://msdn.microsoft.com/library/en-us/tsqlref/ts_ua-uz_82n9.asp ....leading you to: http://msdn.microsoft.com/library/en-us/tsqlref/ts_ea-ez_4aur.asp ....leading you to: http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_1n1v.asp (see "datetime constants") Armin Hi
This is my little guide : - For dates, use date variables, not into strings. That way you can do arithmetics, format properly , passs parameters without problems etc - Use Cdate when picking up dates/times from text fields - When calling SQLprocedures, use parameters. - When building an SQL string in a (VB) program, use date format yyyy-mm-dd , ie. today is 2006-01-25 , and format explicitly , do not rely on implicit (locale dependent) formatting. ie. SQLtext = ... & format (date_var,"yyyy-mm-dd hh:MM:ss") & ... The somewhat exotic format does not matter inside a program, the important thing is that SQLserver never fails to understand you correctly. No more lottery if 01/04/06 is April 1st or January 4th or ... Matti Show quoteHide quote "Jerome" <Jomm***@fake.com> wrote in message news:ywKBf.209108$DX6.7008400@phobos.telenet-ops.be... > Hallo, > I know a lot has already been told about date/time fields in a database > but still confuses me, specif when dealing with SQLserver(Express). > It seems that sqlserver only accepts the date in a "yyyyMMdd" format? > (difference between Express and MSDE2000A ?) > What is the one and only true way to deal with this problem in VB2005: > Local settings are Dutch (Belgium) ; thus date is in "dd/MM/yy" (or > perhaps dd/MM/yyyy) and time in "hh:mm:ss" > > dim MyDateVar as string, MyIdVar as Integer > dim MyCommand = New Sqlcommand("",Connection) > MyCommand.commandtext = "Update MyTable Set MyDatefield = '" & MyDateVar & > "' Where MyIdField = " & MyIdVar = SomeIntegerValue > MyCommand.executenonquery > > How to deal with the MyDateVar when: > > 1. > The Variable comes from a textbox knowing that the user puts in dd/MMyyyy > In this case there is no need to have the time with it. > > 2. > The date comes from a datetimepicker control > (MyDateVar = DtPicker.Value)? > > 3. > The date and time comes from the system > MyDateVar= Format(DateTime.Now, "yyyyMMdd") seems to work but > Format(DateTime.Now, "yyyyMMdd.hhmmss") gives a runtime error > > So, any help and/or suggestion on this will be greatly appreciated. > Thanks and greetings to all > Jerome > > I'm a database person, so from a database perspective:
Make sure the variable in the client is date datatype, not string. Pass it though a command object and a parameter object to SQL Server = you are safe. ADO will do the string conversion for you. If you absolutely want to pass it as a string to SQL Server, read http://www.karaszi.com/SQLServer/info_datetime.asp -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Jerome" <Jomm***@fake.com> wrote in message news:ywKBf.209108$DX6.7008400@phobos.telenet-ops.be... > Hallo, > I know a lot has already been told about date/time fields in a database but still confuses me, > specif when dealing with SQLserver(Express). > It seems that sqlserver only accepts the date in a "yyyyMMdd" format? (difference between Express > and MSDE2000A ?) > What is the one and only true way to deal with this problem in VB2005: > Local settings are Dutch (Belgium) ; thus date is in "dd/MM/yy" (or perhaps dd/MM/yyyy) and time > in "hh:mm:ss" > > dim MyDateVar as string, MyIdVar as Integer > dim MyCommand = New Sqlcommand("",Connection) > MyCommand.commandtext = "Update MyTable Set MyDatefield = '" & MyDateVar & "' Where MyIdField = " > & MyIdVar = SomeIntegerValue > MyCommand.executenonquery > > How to deal with the MyDateVar when: > > 1. > The Variable comes from a textbox knowing that the user puts in dd/MMyyyy > In this case there is no need to have the time with it. > > 2. > The date comes from a datetimepicker control > (MyDateVar = DtPicker.Value)? > > 3. > The date and time comes from the system > MyDateVar= Format(DateTime.Now, "yyyyMMdd") seems to work but Format(DateTime.Now, > "yyyyMMdd.hhmmss") gives a runtime error > > So, any help and/or suggestion on this will be greatly appreciated. > Thanks and greetings to all > Jerome > > Hoi Friends,
Thanks very much the answers. At least these are short, understandable and valuable answers! Far more better than all the microsoft stuff readings. I will try the suggestions right away when my sqlserverExpress is working again. Yesterday Mr. Murphy came to visit and ruined my VS2005 and Sqlexpress. Nice! Anyway, the answers leaves my with one more question: What are the benifits of using Parameters instead plain variables (for numeric or charachter fields at least)? As i can see at a first glance there is a lot more wrtiting to do for the Parameters. (adding them to a command before they are usable, defining the number of chars for a string param, etc,etc)? For instance: If the client decides that a stringfield should have more characters capacity, one should go trough the whole project and adjust the number of chars for the Params that points to that specific field? Or can one program a param with, let's say 100 chars, where the field is only 50 chars ? The max charachters is limited by the maxlength property of the textbox anyway. Thanks once again for the answers and suggestions Jerome Show quoteHide quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> schreef in bericht news:eapIArdIGHA.2900@TK2MSFTNGP14.phx.gbl... > I'm a database person, so from a database perspective: > > Make sure the variable in the client is date datatype, not string. > Pass it though a command object and a parameter object to SQL Server = you > are safe. ADO will do the string conversion for you. > If you absolutely want to pass it as a string to SQL Server, read > http://www.karaszi.com/SQLServer/info_datetime.asp > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > Blog: http://solidqualitylearning.com/blogs/tibor/ > > > "Jerome" <Jomm***@fake.com> wrote in message > news:ywKBf.209108$DX6.7008400@phobos.telenet-ops.be... >> Hallo, >> I know a lot has already been told about date/time fields in a database >> but still confuses me, specif when dealing with SQLserver(Express). >> It seems that sqlserver only accepts the date in a "yyyyMMdd" format? >> (difference between Express and MSDE2000A ?) >> What is the one and only true way to deal with this problem in VB2005: >> Local settings are Dutch (Belgium) ; thus date is in "dd/MM/yy" (or >> perhaps dd/MM/yyyy) and time in "hh:mm:ss" >> >> dim MyDateVar as string, MyIdVar as Integer >> dim MyCommand = New Sqlcommand("",Connection) >> MyCommand.commandtext = "Update MyTable Set MyDatefield = '" & MyDateVar >> & "' Where MyIdField = " & MyIdVar = SomeIntegerValue >> MyCommand.executenonquery >> >> How to deal with the MyDateVar when: >> >> 1. >> The Variable comes from a textbox knowing that the user puts in dd/MMyyyy >> In this case there is no need to have the time with it. >> >> 2. >> The date comes from a datetimepicker control >> (MyDateVar = DtPicker.Value)? >> >> 3. >> The date and time comes from the system >> MyDateVar= Format(DateTime.Now, "yyyyMMdd") seems to work but >> Format(DateTime.Now, "yyyyMMdd.hhmmss") gives a runtime error >> >> So, any help and/or suggestion on this will be greatly appreciated. >> Thanks and greetings to all >> Jerome >> >> > > What are the benifits of using Parameters instead plain variables (for numeric or charachter * Avoid "SQL Injection" (Google and you will find.)> fields at least)? * Assuming that ADO.NET is smart enough to execute your code using sp_executesql and make parameters for that out of your ADO.NET parameters: You will have a lot greater chance for your query plan to be re-used. If you just build a string and first search for "johnson", then SQL Server can cache that plan. But that cached plan is identified (basically) based on all the text in the query. "johnson" is a part of that text. Next time, you search for "smith", and SQL Server first searches for a plan match. Such doesn't exists (you searched for "johnson" last time). So a new plan will be added to plan cache for this query with "smith" embedded. I've seen installations with 10,000 instances of plans in cache for the same query! And how much memory is now available for caching data? Not to speak about the overhead of searching through many many thousands of plans in cache in order to find a match - every time you execute a query - in vain. If they were parametized, then you'd have only one plan for the query in cache, and SQL Server would substitute the parameters. * Better yet, use stored procedures. This way you also have control over if this plan should be cached in the first place and also plan recompiles. Along with bunch of other advantages of using stored procedures. *"Feels better" I bet others can jump in with other advantages. -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Jerome" <Jomm***@fake.com> wrote in message news:oK2Cf.211227$Xy5.6942778@phobos.telenet-ops.be... > Hoi Friends, > Thanks very much the answers. At least these are short, understandable and valuable answers! Far > more better than all the microsoft stuff readings. > I will try the suggestions right away when my sqlserverExpress is working again. Yesterday Mr. > Murphy came to visit and ruined my VS2005 and Sqlexpress. Nice! > Anyway, the answers leaves my with one more question: > What are the benifits of using Parameters instead plain variables (for numeric or charachter > fields at least)? > As i can see at a first glance there is a lot more wrtiting to do for the Parameters. (adding them > to a command before they are usable, defining the number of chars for a string param, etc,etc)? > For instance: If the client decides that a stringfield should have more characters capacity, one > should go trough the whole project and adjust the number of chars for the Params that points to > that specific field? Or can one program a param with, let's say 100 chars, where the field is only > 50 chars ? The max charachters is limited by the maxlength property of the textbox anyway. > > Thanks once again for the answers and suggestions > Jerome > > > "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> schreef in bericht > news:eapIArdIGHA.2900@TK2MSFTNGP14.phx.gbl... >> I'm a database person, so from a database perspective: >> >> Make sure the variable in the client is date datatype, not string. >> Pass it though a command object and a parameter object to SQL Server = you are safe. ADO will do >> the string conversion for you. >> If you absolutely want to pass it as a string to SQL Server, read >> http://www.karaszi.com/SQLServer/info_datetime.asp >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> Blog: http://solidqualitylearning.com/blogs/tibor/ >> >> >> "Jerome" <Jomm***@fake.com> wrote in message >> news:ywKBf.209108$DX6.7008400@phobos.telenet-ops.be... >>> Hallo, >>> I know a lot has already been told about date/time fields in a database but still confuses me, >>> specif when dealing with SQLserver(Express). >>> It seems that sqlserver only accepts the date in a "yyyyMMdd" format? (difference between >>> Express and MSDE2000A ?) >>> What is the one and only true way to deal with this problem in VB2005: >>> Local settings are Dutch (Belgium) ; thus date is in "dd/MM/yy" (or perhaps dd/MM/yyyy) and time >>> in "hh:mm:ss" >>> >>> dim MyDateVar as string, MyIdVar as Integer >>> dim MyCommand = New Sqlcommand("",Connection) >>> MyCommand.commandtext = "Update MyTable Set MyDatefield = '" & MyDateVar & "' Where MyIdField = >>> " & MyIdVar = SomeIntegerValue >>> MyCommand.executenonquery >>> >>> How to deal with the MyDateVar when: >>> >>> 1. >>> The Variable comes from a textbox knowing that the user puts in dd/MMyyyy >>> In this case there is no need to have the time with it. >>> >>> 2. >>> The date comes from a datetimepicker control >>> (MyDateVar = DtPicker.Value)? >>> >>> 3. >>> The date and time comes from the system >>> MyDateVar= Format(DateTime.Now, "yyyyMMdd") seems to work but Format(DateTime.Now, >>> "yyyyMMdd.hhmmss") gives a runtime error >>> >>> So, any help and/or suggestion on this will be greatly appreciated. >>> Thanks and greetings to all >>> Jerome >>> >>> >> > > Hoi Tibor,
That explains a lot. SQL Injection, in my case, is unlikely to occur.They are not going to tamper with the application. There are a maximu of 4 persons working with the application and the whole bunch is not even connected to the internet. Nobody at the site in question ever heard about Sql not to speak about running a query! I was obliged to use an existing MsAccess Db as backend (they already are working for years with Access) ;-) and i had to enhance and expanding the application. So, rewriting 200+ functions!? Now i'm trying for myself and for learning purposes to rebuild parts of the applic in VB2005 and with a sqlexpress as backend and that's when i ran into those date problems. Perhaps that explains a bit more my questions and i am happy that people like you and others are willing to give advice. If you have to learn it from the books of Microsoft.....pfff. Even for a simple readonly lookup table and a combobox they lead trough a complete strongly-typed dataset! Ridicolous Anyway, thanks a lot for the feedback Jerome Show quoteHide quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> schreef in bericht news:eFrJeZnIGHA.1728@TK2MSFTNGP09.phx.gbl... >> What are the benifits of using Parameters instead plain variables (for >> numeric or charachter fields at least)? > > * Avoid "SQL Injection" (Google and you will find.) > > * Assuming that ADO.NET is smart enough to execute your code using > sp_executesql and make parameters for that out of your ADO.NET parameters: > You will have a lot greater chance for your query plan to be re-used. > If you just build a string and first search for "johnson", then SQL Server > can cache that plan. But that cached plan is identified (basically) based > on all the text in the query. "johnson" is a part of that text. Next time, > you search for "smith", and SQL Server first searches for a plan match. > Such doesn't exists (you searched for "johnson" last time). So a new plan > will be added to plan cache for this query with "smith" embedded. I've > seen installations with 10,000 instances of plans in cache for the same > query! And how much memory is now available for caching data? Not to speak > about the overhead of searching through many many thousands of plans in > cache in order to find a match - every time you execute a query - in vain. > If they were parametized, then you'd have only one plan for the query in > cache, and SQL Server would substitute the parameters. > > * Better yet, use stored procedures. This way you also have control over > if this plan should be cached in the first place and also plan recompiles. > Along with bunch of other advantages of using stored procedures. > > *"Feels better" > > I bet others can jump in with other advantages. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > Blog: http://solidqualitylearning.com/blogs/tibor/ > > > "Jerome" <Jomm***@fake.com> wrote in message > news:oK2Cf.211227$Xy5.6942778@phobos.telenet-ops.be... >> Hoi Friends, >> Thanks very much the answers. At least these are short, understandable >> and valuable answers! Far more better than all the microsoft stuff >> readings. >> I will try the suggestions right away when my sqlserverExpress is working >> again. Yesterday Mr. Murphy came to visit and ruined my VS2005 and >> Sqlexpress. Nice! >> Anyway, the answers leaves my with one more question: >> What are the benifits of using Parameters instead plain variables (for >> numeric or charachter fields at least)? >> As i can see at a first glance there is a lot more wrtiting to do for the >> Parameters. (adding them to a command before they are usable, defining >> the number of chars for a string param, etc,etc)? >> For instance: If the client decides that a stringfield should have more >> characters capacity, one should go trough the whole project and adjust >> the number of chars for the Params that points to that specific field? Or >> can one program a param with, let's say 100 chars, where the field is >> only 50 chars ? The max charachters is limited by the maxlength property >> of the textbox anyway. >> >> Thanks once again for the answers and suggestions >> Jerome >> >> >> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> >> schreef in bericht news:eapIArdIGHA.2900@TK2MSFTNGP14.phx.gbl... >>> I'm a database person, so from a database perspective: >>> >>> Make sure the variable in the client is date datatype, not string. >>> Pass it though a command object and a parameter object to SQL Server = >>> you are safe. ADO will do the string conversion for you. >>> If you absolutely want to pass it as a string to SQL Server, read >>> http://www.karaszi.com/SQLServer/info_datetime.asp >>> >>> -- >>> Tibor Karaszi, SQL Server MVP >>> http://www.karaszi.com/sqlserver/default.asp >>> http://www.solidqualitylearning.com/ >>> Blog: http://solidqualitylearning.com/blogs/tibor/ >>> >>> >>> "Jerome" <Jomm***@fake.com> wrote in message >>> news:ywKBf.209108$DX6.7008400@phobos.telenet-ops.be... >>>> Hallo, >>>> I know a lot has already been told about date/time fields in a database >>>> but still confuses me, specif when dealing with SQLserver(Express). >>>> It seems that sqlserver only accepts the date in a "yyyyMMdd" format? >>>> (difference between Express and MSDE2000A ?) >>>> What is the one and only true way to deal with this problem in VB2005: >>>> Local settings are Dutch (Belgium) ; thus date is in "dd/MM/yy" (or >>>> perhaps dd/MM/yyyy) and time in "hh:mm:ss" >>>> >>>> dim MyDateVar as string, MyIdVar as Integer >>>> dim MyCommand = New Sqlcommand("",Connection) >>>> MyCommand.commandtext = "Update MyTable Set MyDatefield = '" & >>>> MyDateVar & "' Where MyIdField = " & MyIdVar = SomeIntegerValue >>>> MyCommand.executenonquery >>>> >>>> How to deal with the MyDateVar when: >>>> >>>> 1. >>>> The Variable comes from a textbox knowing that the user puts in >>>> dd/MMyyyy >>>> In this case there is no need to have the time with it. >>>> >>>> 2. >>>> The date comes from a datetimepicker control >>>> (MyDateVar = DtPicker.Value)? >>>> >>>> 3. >>>> The date and time comes from the system >>>> MyDateVar= Format(DateTime.Now, "yyyyMMdd") seems to work but >>>> Format(DateTime.Now, "yyyyMMdd.hhmmss") gives a runtime error >>>> >>>> So, any help and/or suggestion on this will be greatly appreciated. >>>> Thanks and greetings to all >>>> Jerome >>>> >>>> >>> >> >> > 
Other interesting topics
throw/handle exceptions while using backgroundworker
DUMB question about IndexOf Strange maximize behaviour Queue of Threads What is the correct case convention for Set? Listbox idiotic example timing accuracy of VB.Net How to use a Generic's base type? deleting files greater than x days RDA with limited connectivity |
|||||||||||||||||||||||
