inserting problem with datetime value

[mayurtandel]

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 strIndentForDep t As String, ByVal strlocation As String, _
ByVal strworkDept As String, ByVal strjob As String, ByVal strnatureofexpe nse 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(Wor kIndentNo,WorkI ndentDate,Origi nDeptName,WorkL ocation,WorkDep tName,Job,Natur eofExpense,work Category,WorkDe scription,Impor tLocal,ConfirmF lag," & _
"CancelFlag,ord eredflag,CloseF lag,MakerID,dtm Created) Values('" & strIndentNo & " ',cast('" & dteIndentDate & "' as datetime),'" & strIndentForDep t & _
"','" & strlocation & "','" & strworkDept & "','" & strjob & "','" & strnatureofexpe nse & "','" & strCategory & "','" & strdesc & "','" & strIL & _
"'," & SQLConfirm & "," & SQLCancel & "," & SQLOrdered & "," & SQLClosed & _
",'" & strMakerId & "','" & Today & "')"


'cnSQL = New SqlConnection(s trconn)
'cnSQL.Open()

cmSQL = New SqlCommand(strS QL, cnSQL, ds)
cmSQL.ExecuteNo nQuery()

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

' Refresh Product List
'PopulateProduc tList()
'FindProductByN ame(lstProducts , ProductName)

Catch Exp As SqlException
MsgBox(Exp.Mess age, MsgBoxStyle.Cri tical, "SQL Error")
AddIndent = False
Catch Exp As Exception
MsgBox(Exp.Mess age, MsgBoxStyle.Cri tical, "General Error")
AddIndent = False
End Try
End Function

[MMcCarthy]

This question is being moved to the Visual Basic forum.

ADMIN

[Killer42]

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.

[mayurtandel]

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

[Killer42]

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.

[mayurtandel]

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

[Killer42]

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.

[mayurtandel]

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.

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,dtmCreate d) 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

[Killer42]

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.

[SanjuMtr]

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,dtmCreate d) 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

hi,
I just make some changes in your code

  ",'" & strMakerId & "','" & format(Today,"dd/MMM/yyyy") & "')"

Plz try with revised code .Good Luck.

[iburyak]

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.

' Build Insert statement to insert new product into the products table
strSQL = "INSERT INTO workindents (WorkIndentNo, WorkIndentDate, [b]OriginDept Name[/b], " & _
   "WorkLocation, WorkDeptName, Job, NatureofExpense, workCategory, " & _
   "WorkDescription, ImportLocal,[b] ConfirmFlag[/b], " & _
   "CancelFlag, orderedflag, CloseFlag, MakerID, [b]dtmCreated[/b]) " & _
   "Values ('" & strIndentNo & "', '" & Format(dteIndentDate, "dd/MM/yyyy") & "', '" & strIndentForDept & _
 "', '" & strlocation & "', '" & strworkDept & "', '" & strjob & "', '" & strnatureofexpense & "', '" & strCategory & "', '" & strdesc & "', '" & strIL & _
 "', " & SQLConfirm & ", " & SQLCancel & ", " & SQLOrdered & ", " & SQLClosed & _
", '" & strMakerId & "', '" & Today & "')"

Good Luck.

[iburyak]

Of course it didn't highlight it in bold. Look for these in your code.

<B>dtmCreate   d<B>

[Killer42]

Of course it didn't highlight it in bold. Look for these in your code...

The bold text came out alright for me.

[MMcCarthy]

The bold text came out alright for me.

Sorry I fixed it. Should have put a editing tag up.

[iburyak]

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]