February 20, 2010 at 1:04 pm
ok here is the error, i know heres not an asp forum but i think the error is on the sql server configuration
Microsoft SQL Server Native Client 10.0 error '80040e07'
La conversión del tipo de datos nvarchar en datetime produjo un valor fuera de intervalo.
/ModifyRecord.asp, línea 180
the two servers have Modern_Spanish_CI_AS in Server Collation
the one that works has the language set to English (United States) and is the english version of SQL Server, the other has Español (España, Internacional) and is the spanish version of SQL Server
the two machines have Español (Mexico) in regional settings
the only difference i see is the language, some one can help me with this.
February 20, 2010 at 1:36 pm
If you are passing in your date in a format that is affected by regional settings, you would get this error. For example:
01/02/2010 can be interpreted by SQL Server as either January 2nd or February 1st depending upon the server settings. So:
20/02/2010 would fail on the English system because there isn't a 20th month (MM/DD/YYYY).
To fix this issue, make sure you use a unambiguouse format such as: YYYYMMDD or YYYY-MM-DDTHH:MM:SS.mmm
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 20, 2010 at 1:38 pm
Also, since you are using ASP - you probably want to make sure you are using parameters instead of concatenating the date to a string and passing the string to SQL Server. If that is what you are doing, it is subject to SQL Injection and you need to fix it.
By using parameters, you wouldn't have to worry about the actual format of the date - that will be taken care of for you.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 20, 2010 at 2:09 pm
ok conclusion, i make it work by changing the language of the user used for the asp connection, in this case, user ''alex"
i put it to english, and the system works now like in the machine that have sql server in english, why i do that? mmm well the user in the sql server english version has the language in english. and when i develop the asp system all code i made works with that language, i supossed that it changes something in the dates.
well i hope this help. ( sorry my english not so good, im from mexico)
another question, how you see y change the user language to english, but just that user, is there a way to change all laguage to the sql server spanish version, and i mean the language property not the language of the software
February 20, 2010 at 2:19 pm
Like I said before, if you are concatenating strings and passing those strings you really should change your code. In it's current form, you are open to a SQL injection attack.
Instead of concatenating, you need to use command parameters. You then write the query with variables and populate those variables through the parameters. When you code that way, you don't have to worry about the users regional settings. The parameter will be defined as a datetime and it will be passed to the query in the correct format and will be interpreted correctly.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 20, 2010 at 2:27 pm
yes i know, i see that but its a lot of code i have to change, the best for now is that, i didn't use the yyyymmdd format in the inserts and now i know thats the best way to send the dates.
Instead of concatenating, you need to use command parameters. You then write the query with variables and populate those variables through the parameters. <----- can you give a little example just for my knowledge and for using it in a future.
MUCHAS GRACIAS!
February 20, 2010 at 3:43 pm
cmd.CommandType = System.Data.CommandType.Text
cmd.CommandText = _
" SELECT O.OrderID, SUM(OD.UnitPrice * OD.Quantity)" & _
" FROM dbo.Orders O " & _
" JOIN dbo.[Order Details] OD ON O.OrderID = OD.OrderID" & _
" WHERE O.OrderDate BETWEEN @from AND @to" & _
" AND EXISTS (SELECT *" & _
" FROM dbo.[Order Details] OD2" & _
" WHERE O.OrderID = OD2.OrderID" & _
" AND OD2.ProductID = @prodid)" & _
" GROUP BY O.OrderID"
cmd.Parameters.Add("@from", SqlDbType.Datetime)
cmd.Parameters("@from").Value = "1998-02-01"
cmd.Parameters.Add("@to", SqlDbType.Datetime)
cmd.Parameters("@to").Value = "1998-02-28"
cmd.Parameters.Add("@prodid", SqlDbType.Int)
cmd.Parameters("@prodid").Value = 76
Here is an example - you'll have to convert to your specific language as this was done in VB.Net usng SQLClient. Review the article at: http://www.sommarskog.se/dynamic_sql.html which goes into great detail about using dynamic SQL from both SQL Server and the client.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply