Problem with Date
-
hi,
I am working in asp.net 2.0 with vb script. I have few projects which were working fine. But suddent from last few days all are giving the error that is related to date function. Date is not getting stored in database. It gives the error that string cant be converted to date. But it was working fine. and also when i select data from database to bind in a datatagrid then also it is giving the same error. What might be the reason? The datatype in database is DateTime.
The syntax i am using is
text1.text=calendar1.selecteddate
insert table1(ddate) values ('" & text1.text.trim & "')
-
Did the problem happen to start on the 13th of July? Sounds like someone may have changed the default date format from dmy to mdy and your code isn't handling it correctly.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
If I assume it had happened so, how to find it and correct it. I am going panic since it is really an urgent work.
-
Run the following...
DBCC USEROPTIONS
... which will produce something like this...
Set Option Value
textsize 64512
language us_english
dateformat mdy
datefirst 7
quoted_identifier SET
arithabort SET
ansi_null_dflt_on SET
ansi_defaults SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_null SET
(12 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
... and check the "dateformat" line. If it's "dmy", that may be your problem and you may need to change it back to "mdy"... BUT WARNING!!!!! AS YOU MAY HAVE ALREADY FOUND IT, CHANGING THIS DEFAULT FORMAT SERVERWIDE MAY BRING ON A WORLD OF HURT AND CAUSE MUCH POORLY WRITTEN CODE TO FAIL.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff, do you suggest to add a line in the open connection such as : SET DATEFORMAT YMD. So that all incoming connections from now on are certain to have the same settings?
What other methods are you suggesting?
-
No... not ready to make that recommendation. We first need to identify what change was made to cause the code to suddenly go south several days ago. Quick fix would be to change it back as not much code should have been written since the problem began.
If this turns out to be the problem, it's yet another proof as to why folks shouldn't write date-format dependent code
Of course, it might be something else... I'm kinda shooting in the dark based on the OPs description of the timing of the problem. 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Chaitra,
Any feed back on this? Was that the problem or something else?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Thank you.
I used
set language 'us_english'
Now I am not getting the error. But now facing another problem ofcourse it not related to date format. This is regarding update command.
I have the following query used in .net 2.0. When I click btnsave button though it is giving 'Projected has been started 'message, the data is not getting updated.
Protected
Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
start(txtpno.Text.Trim())
End Sub
Function start(ByVal pno As String)
Dim sqlstr1 As String
Dim dr As SqlClient.SqlDataReader
sqlstr1 =
"update D_task_det set str_date='" & Now.Date() & "',status='In_Progress' where pno='" & pno & "'and flag='0'and task='" & lbltask.Text.Trim() & "' and status='Pending'"
Try
If dbconn.State <> ConnectionState.Open Then
dbconn.Open()
End If
Dim sqlcomm1 As New SqlClient.SqlCommand(sqlstr1, dbconn)
sqlcomm1.ExecuteNonQuery()
dbconn.Close()
lblmsg.ForeColor = Drawing.Color.DarkGreen
lblmsg.Text =
"Project has been started."
tbentervalue.Visible =
False
tbtelescopic.Visible =
False
tbpto.Visible =
False
tbsystem.Visible =
False
tbaccessories.Visible =
False
tbvalves.Visible =
False
tbpumps.Visible =
False
tbtipper.Visible =
False
tbentervalue.Visible =
False
tboth.Visible =
False
lbltask.Visible =
False
' clear()
newproject()
Catch ex As Exception
lblmsg.Text =
"Transaction failed." ' + ex.ToString()
dbconn.Close()
End Try
End Function
-
If you do a select from that table with the same where clause, do you get any rows back?
Unrelated to your question, but serious anyway. Are you aware that your code is very vulnerable to SQL injection attacks?
Imagine someone entering the following in the text box txtpno "1';delete from D_task_det --"
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Ya I do get rows back when i run the select with same where clause in query analyzer. txtpno is readonly. if u say my code is not very secure i agree since i am very new to this. But please let me know what else I can do.
And also I think, here it is because of the date only it is not getting updated. Because I have another function which is used for updation. There everything is getting updated but date. There also it gives "Successful updation" message.
-
I don't know what sqlcomm1.ExecuteNonQuery() does because I'm no GUI programmer... UPDATE's are actually a form of a query and that may be your problem (but dunno for sure). But I'd probably start by printing the content of the sqlstr1 variable and running it in Query Analyzer (with a rollback, of course) to see what it was doing before I starting looking for anything else.
So far as "security" goes, I have to agree with Gail... you're code is very vulnerable to SQL Injection. Most GUI programmers don't like it, but one of the easiest ways to prevent such attacks is to simply pass all the variables to a stored procedure on the server... that way, things like "1';delete from D_task_det --" will simply be treated as an operand instead of code (unless you mess up by using Dynamic SQL there, too). Another way is to check all of your variables for special characters like [ ' ] and [ ; ]... if any are present, then DO NOTHING in the code but a "return"... leaves the attacker totally clueless as to what happened.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
What do you do when the user genuinly wants to type in those characters??
I mean the apostrophe in french, and even english, is a pretty common character, ain't it?
-
ExecuteNonQuery is the .Net method to execute a command and not return any results.
Alternatives are ExecuteScalar, ExecuteReader and ExecuteXMLReader. (At least in .Net 2.0)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
IF the application is tight, i.e., well written, then there are mechanisms in place to parse the GUI-supplied input and edit it before processing, so that a single quote aka apostrophe is reformatted to be treated as apostrophe/single quote when the command is passed through to SQL. In particular, the apostrophe may be replaced with ''' so that it is correctly interpreted as an apostrophe instead of the beginning/ending of a quoted string.
Defensive programming is generally a way to save time, not spend more time in debugging. Further, there are examples on msdn and other sites of best practices or recommended ways of doing things - the trick is to find them efficiently.
-
That's why the stored proc method is better.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply