By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,805 Members | 1,349 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,805 IT Pros & Developers. It's quick & easy.

inserting problem with datetime value

P: 4
i have problem whith store datetime value to database table the my system date formate is dd/mm/yyyy the date is store in mm/dd/yyyy formate what is the problem
my code is as follow

Public Function AddIndent(ByVal cnSQL As SqlConnection, ByVal ds As SqlTransaction, ByVal strIndentNo As String, ByVal dteIndentDate As Date, ByVal strIndentForDept As String, ByVal strlocation As String, _
ByVal strworkDept As String, ByVal strjob As String, ByVal strnatureofexpense As String, ByVal strCategory As String, _
ByVal strdesc As String, ByVal strIL As String, ByVal blnConfirm As Boolean, ByVal blnCancel As Boolean, _
ByVal blnOrdered As Boolean, ByVal blnClosed As Boolean, ByVal strMakerId As String) As Boolean


Dim cmSQL As SqlCommand
Dim strSQL As String
Dim intRowsAffected As Integer


Try
Dim SQLConfirm As Integer
Dim SQLCancel As Integer
Dim SQLOrdered As Integer
Dim SQLClosed As Integer

SQLConfirm = IIf((blnConfirm), 1, 0)
SQLCancel = IIf((blnCancel), 1, 0)
SQLOrdered = IIf((blnOrdered), 1, 0)
SQLClosed = IIf((blnClosed), 1, 0)


' Build Insert statement to insert new product into the products table
strSQL = "INSERT INTO workindents(WorkIndentNo,WorkIndentDate,OriginDept Name,WorkLocation,WorkDeptName,Job,NatureofExpense ,workCategory,WorkDescription,ImportLocal,ConfirmF lag," & _
"CancelFlag,orderedflag,CloseFlag,MakerID,dtmCreat ed) Values('" & strIndentNo & " ',cast('" & dteIndentDate & "' as datetime),'" & strIndentForDept & _
"','" & strlocation & "','" & strworkDept & "','" & strjob & "','" & strnatureofexpense & "','" & strCategory & "','" & strdesc & "','" & strIL & _
"'," & SQLConfirm & "," & SQLCancel & "," & SQLOrdered & "," & SQLClosed & _
",'" & strMakerId & "','" & Today & "')"


'cnSQL = New SqlConnection(strconn)
'cnSQL.Open()

cmSQL = New SqlCommand(strSQL, cnSQL, ds)
cmSQL.ExecuteNonQuery()

' Close and Clean up objects
'cnSQL.Close()
cmSQL.Dispose()
'cnSQL.Dispose()
AddIndent = True

' Refresh Product List
'PopulateProductList()
'FindProductByName(lstProducts, ProductName)

Catch Exp As SqlException
MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")
AddIndent = False
Catch Exp As Exception
MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
AddIndent = False
End Try
End Function
Apr 6 '07 #1
Share this Question
Share on Google+
15 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
This question is being moved to the Visual Basic forum.

ADMIN
Apr 6 '07 #2

Expert 5K+
P: 8,434
I think you will find that SQL (or perhaps MS Access) requires dates to be passed in U.S. format. There's no point worrying about it. As long as it's a date value, you can display it any way you want - just let the database have its way when storing.
Apr 6 '07 #3

P: 4
I think you will find that SQL (or perhaps MS Access) requires dates to be passed in U.S. format. There's no point worrying about it. As long as it's a date value, you can display it any way you want - just let the database have its way when storing.
when i set regional seting as MM/dd/yyyy then it store correct but my reginol setting is dd/MM/yyyy then if give me the error out of range datetime value
Apr 7 '07 #4

Expert 5K+
P: 8,434
when i set regional seting as MM/dd/yyyy then it store correct but my reginol setting is dd/MM/yyyy then if give me the error out of range datetime value
Just reformat it when putting it in an SQL string. SQL insists on U.S. format, regardless of what your regional settings are. That's why it works if you are using U.S. format, and stuffs up otherwise.

For more info, check out this thread.
Apr 8 '07 #5

P: 4
Just reformat it when putting it in an SQL string. SQL insists on U.S. format, regardless of what your regional settings are. That's why it works if you are using U.S. format, and stuffs up otherwise.

For more info, check out this thread.
i dont want to format the date in my code i am passing the string for date and in quary i am converting it to date & time but that it give the error
the conversion of char type data to a datetime data type resulted in an out-of-range datetime value the statment is termineted.

is any setting in my sqlserver i use server lagnguage as british english for sqlserver
Apr 8 '07 #6

Expert 5K+
P: 8,434
i dont want to format the date in my code i am passing the string for date and in quary i am converting it to date & time but that it give the error
the conversion of char type data to a datetime data type resulted in an out-of-range datetime value the statment is termineted.

is any setting in my sqlserver i use server lagnguage as british english for sqlserver
Can you show us your code?

For more details info on SQL Server, I'd suggest you try the SQL Server forum - they will have much more experience in that area.
Apr 9 '07 #7

P: 4
Can you show us your code?

For more details info on SQL Server, I'd suggest you try the SQL Server forum - they will have much more experience in that area.
Expand|Select|Wrap|Line Numbers
  1. Public Function AddIndent(ByVal cnSQL As SqlConnection, ByVal ds As SqlTransaction, ByVal strIndentNo As String, ByVal dteIndentDate As Date, ByVal strIndentForDept As String, ByVal strlocation As String, _
  2. ByVal strworkDept As String, ByVal strjob As String, ByVal strnatureofexpense As String, ByVal strCategory As String, _
  3. ByVal strdesc As String, ByVal strIL As String, ByVal blnConfirm As Boolean, ByVal blnCancel As Boolean, _
  4. ByVal blnOrdered As Boolean, ByVal blnClosed As Boolean, ByVal strMakerId As String) As Boolean
  5.  
  6.  
  7. Dim cmSQL As SqlCommand
  8. Dim strSQL As String
  9. Dim intRowsAffected As Integer
  10.  
  11.  
  12. Try
  13. Dim SQLConfirm As Integer
  14. Dim SQLCancel As Integer
  15. Dim SQLOrdered As Integer
  16. Dim SQLClosed As Integer
  17.  
  18. SQLConfirm = IIf((blnConfirm), 1, 0)
  19. SQLCancel = IIf((blnCancel), 1, 0)
  20. SQLOrdered = IIf((blnOrdered), 1, 0)
  21. SQLClosed = IIf((blnClosed), 1, 0)
  22.  
  23.  
  24. ' Build Insert statement to insert new product into the products table
  25. strSQL = "INSERT INTO workindents(WorkIndentNo,WorkIndentDate,OriginDept Name,WorkLocation,WorkDeptName,Job,NatureofExpense ,workCategory,WorkDescription,ImportLocal,ConfirmF lag," & _
  26. "CancelFlag,orderedflag,CloseFlag,MakerID,dtmCreate d) Values('" & strIndentNo & " ',cast('" & dteIndentDate & "' as datetime),'" & strIndentForDept & _
  27. "','" & strlocation & "','" & strworkDept & "','" & strjob & "','" & strnatureofexpense & "','" & strCategory & "','" & strdesc & "','" & strIL & _
  28. "'," & SQLConfirm & "," & SQLCancel & "," & SQLOrdered & "," & SQLClosed & _
  29. ",'" & strMakerId & "','" & Today & "')"
  30.  
  31.  
  32. 'cnSQL = New SqlConnection(strconn)
  33. 'cnSQL.Open()
  34.  
  35. cmSQL = New SqlCommand(strSQL, cnSQL, ds)
  36. cmSQL.ExecuteNonQuery()
  37.  
  38. ' Close and Clean up objects
  39. 'cnSQL.Close()
  40. cmSQL.Dispose()
  41. 'cnSQL.Dispose()
  42. AddIndent = True
  43.  
  44. ' Refresh Product List
  45. 'PopulateProductList()
  46. 'FindProductByName(lstProducts, ProductName)
  47.  
  48. Catch Exp As SqlException
  49. MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")
  50. AddIndent = False
  51. Catch Exp As Exception
  52. MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
  53. AddIndent = False
  54. End Try
  55. End Function
Apr 9 '07 #8

Expert 5K+
P: 8,434
Ok, I think I know how we can get you an answer, very quickly. As you know, Mmccarthy moved your post to the VB forum because it was VB code. However, the probably is really not VB-specific - it's simply the SQL string that you are passing to SQL Server.

If you can just copy and paste the value of strSQL here, I'll get the SQL experts to have a look at it and tell us what the problem is.
Apr 9 '07 #9

P: 47
Expand|Select|Wrap|Line Numbers
  1. Public Function AddIndent(ByVal cnSQL As SqlConnection, ByVal ds As SqlTransaction, ByVal strIndentNo As String, ByVal dteIndentDate As Date, ByVal strIndentForDept As String, ByVal strlocation As String, _
  2. ByVal strworkDept As String, ByVal strjob As String, ByVal strnatureofexpense As String, ByVal strCategory As String, _
  3. ByVal strdesc As String, ByVal strIL As String, ByVal blnConfirm As Boolean, ByVal blnCancel As Boolean, _
  4. ByVal blnOrdered As Boolean, ByVal blnClosed As Boolean, ByVal strMakerId As String) As Boolean
  5.  
  6.  
  7. Dim cmSQL As SqlCommand
  8. Dim strSQL As String
  9. Dim intRowsAffected As Integer
  10.  
  11.  
  12. Try
  13. Dim SQLConfirm As Integer
  14. Dim SQLCancel As Integer
  15. Dim SQLOrdered As Integer
  16. Dim SQLClosed As Integer
  17.  
  18. SQLConfirm = IIf((blnConfirm), 1, 0)
  19. SQLCancel = IIf((blnCancel), 1, 0)
  20. SQLOrdered = IIf((blnOrdered), 1, 0)
  21. SQLClosed = IIf((blnClosed), 1, 0)
  22.  
  23.  
  24. ' Build Insert statement to insert new product into the products table
  25. strSQL = "INSERT INTO workindents(WorkIndentNo,WorkIndentDate,OriginDept Name,WorkLocation,WorkDeptName,Job,NatureofExpense ,workCategory,WorkDescription,ImportLocal,ConfirmF lag," & _
  26. "CancelFlag,orderedflag,CloseFlag,MakerID,dtmCreate d) Values('" & strIndentNo & " ',cast('" & dteIndentDate & "' as datetime),'" & strIndentForDept & _
  27. "','" & strlocation & "','" & strworkDept & "','" & strjob & "','" & strnatureofexpense & "','" & strCategory & "','" & strdesc & "','" & strIL & _
  28. "'," & SQLConfirm & "," & SQLCancel & "," & SQLOrdered & "," & SQLClosed & _
  29. ",'" & strMakerId & "','" & Today & "')"
  30.  
  31.  
  32. 'cnSQL = New SqlConnection(strconn)
  33. 'cnSQL.Open()
  34.  
  35. cmSQL = New SqlCommand(strSQL, cnSQL, ds)
  36. cmSQL.ExecuteNonQuery()
  37.  
  38. ' Close and Clean up objects
  39. 'cnSQL.Close()
  40. cmSQL.Dispose()
  41. 'cnSQL.Dispose()
  42. AddIndent = True
  43.  
  44. ' Refresh Product List
  45. 'PopulateProductList()
  46. 'FindProductByName(lstProducts, ProductName)
  47.  
  48. Catch Exp As SqlException
  49. MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")
  50. AddIndent = False
  51. Catch Exp As Exception
  52. MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
  53. AddIndent = False
  54. End Try
  55. End Function
hi,
I just make some changes in your code
Expand|Select|Wrap|Line Numbers
  1.   ",'" & strMakerId & "','" & format(Today,"dd/MMM/yyyy") & "')"
  2.  
Plz try with revised code .Good Luck.
Apr 12 '07 #10

iburyak
Expert 100+
P: 1,017
I agree with above tip... :)

Just have concerns that some column names have unusual spaces in them.
See in bold. Hope it will show it in BOLD... :)

I agree no Cast on server side is required as long as date has proper format and in quotes.
It is very dangerous not to format and store as is.

Example
You entered date in format mm/dd/yyyy - 01/02/2007 as Jan 2 2007 and server will think it is Feb 1 2007. I am sure server doesn't give an error message in this case but see what you store and what you get.
Of course if you insert 01/15/2007 Server thinks it is some 15th month of a year and it doesn't exist in dates and of course you get an error here.



Expand|Select|Wrap|Line Numbers
  1. ' Build Insert statement to insert new product into the products table
  2. strSQL = "INSERT INTO workindents (WorkIndentNo, WorkIndentDate, OriginDept Name, " & _
  3.    "WorkLocation, WorkDeptName, Job, NatureofExpense, workCategory, " & _
  4.    "WorkDescription, ImportLocal, ConfirmFlag, " & _
  5.    "CancelFlag, orderedflag, CloseFlag, MakerID, dtmCreated) " & _
  6.    "Values ('" & strIndentNo & "', '" & Format(dteIndentDate, "dd/MM/yyyy") & "', '" & strIndentForDept & _
  7.  "', '" & strlocation & "', '" & strworkDept & "', '" & strjob & "', '" & strnatureofexpense & "', '" & strCategory & "', '" & strdesc & "', '" & strIL & _
  8.  "', " & SQLConfirm & ", " & SQLCancel & ", " & SQLOrdered & ", " & SQLClosed & _
  9. ", '" & strMakerId & "', '" & Today & "')"
Good Luck.
Apr 12 '07 #11

iburyak
Expert 100+
P: 1,017
Of course it didn't highlight it in bold. Look for these in your code.
Expand|Select|Wrap|Line Numbers
  1. <B>dtmCreate   d<B>
Apr 12 '07 #12

Expert 5K+
P: 8,434
Of course it didn't highlight it in bold. Look for these in your code...
The bold text came out alright for me.
Apr 13 '07 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
The bold text came out alright for me.
Sorry I fixed it. Should have put a editing tag up.
Apr 13 '07 #14

iburyak
Expert 100+
P: 1,017
Sorry I fixed it. Should have put a editing tag up.
Mary, I think you removed original spaces too... :) I know it looks incorrect but I wanted him to see that. This is how it looks when he pasted code from his program and needs to be corrected.

[PHP]
ConfirmF lag
dtmCreate d
[/PHP]
Apr 13 '07 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
Mary, I think you removed original spaces too... :) I know it looks incorrect but I wanted him to see that. This is how it looks when he pasted code from his program and needs to be corrected.

[PHP]
ConfirmF lag
dtmCreate d
[/PHP]
Not to worry. I'll stop being so helpful :rolleyes:

Sorry guys maybe the OP could repost the code.

Mary :)
Apr 13 '07 #16

Post your reply

Sign in to post your reply or Sign up for a free account.