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
15 2580
This question is being moved to the Visual Basic forum.
ADMIN
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.
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
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.
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
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.
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
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.
- 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.
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, OriginDept Name, " & _
-
"WorkLocation, WorkDeptName, Job, NatureofExpense, workCategory, " & _
-
"WorkDescription, ImportLocal, ConfirmFlag, " & _
-
"CancelFlag, orderedflag, CloseFlag, MakerID, dtmCreated) " & _
-
"Values ('" & strIndentNo & "', '" & Format(dteIndentDate, "dd/MM/yyyy") & "', '" & strIndentForDept & _
-
"', '" & strlocation & "', '" & strworkDept & "', '" & strjob & "', '" & strnatureofexpense & "', '" & strCategory & "', '" & strdesc & "', '" & strIL & _
-
"', " & SQLConfirm & ", " & SQLCancel & ", " & SQLOrdered & ", " & SQLClosed & _
-
", '" & strMakerId & "', '" & Today & "')"
Good Luck.
Of course it didn't highlight it in bold. Look for these in your code.
Of course it didn't highlight it in bold. Look for these in your code...
The bold text came out alright for me.
The bold text came out alright for me.
Sorry I fixed it. Should have put a editing tag up.
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]
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 :)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Dave Pylatuk |
last post by:
Hello all.
I am attempting to insert a row into a table with a datetime column:
When the insert statement contains a value for the millisecond portion
of the data time column: ie. {ts...
|
by: Michael Albanese |
last post by:
I am building an application to report on-the-job injuries
and incidents. There are a lot of Date fields, some of
which are optional and can be left blank by the user. I
have allowed Nulls on...
|
by: J. Muenchbourg |
last post by:
while inserting new records into SQL, i'm using the folloinwg
sqlstatement>
Dim MySQL as string = "Insert into roster (pname, pnotes, thedate)
values (@pname, @pnotes, @thedate)"
the sql...
|
by: Ryan Graham |
last post by:
I totally bombed this question in an interview so I'm posting my answer here
for comments and suggestions... perhaps (god help me) I'm just not that
bright, but this works and seems to be fairly...
|
by: Khurram |
last post by:
I have a problem while inserting time value in the datetime Field.
I want to Insert only time value in this format (08:15:39) into the SQL
Date time Field.
I tried to many ways, I can extract...
|
by: Rajani |
last post by:
Hello,
I replaced the calendar control to 3 dropdownlists(day,month and year) coz
i want to display the calendar as just like DTPicker in vb.6. But i cant able
to display like that. The form...
|
by: fniles |
last post by:
I am using VB.NET 2003 and SQL Server 2000.
I have a table with a datetime column type. When inserting into the table
for that column, I set it to Date.Now.ToString("T")
, which is something like...
|
by: Manikandan |
last post by:
Hi,
I need to insert the datetime with milliseconds value into a datarow.
My code as below
DataTable testDataTable=new DataTable();
testDataTable.Columns.Add("updatedDateTime",...
|
by: Manikandan |
last post by:
Hi,
I'm inserting a datetime values into sql server 2000 from c#
SQL server table details
Table name:date_test
columnname datatype
No int
date_t DateTime
...
|
by: Newbie |
last post by:
howdy...
i am trying to execute a statement where i insert a record with datetime
values into a sql database.
Dim sqlcmd As New SqlCommand
sqlcmd.CommandText = "update tbl_event set...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |