插入日期时间值的问题 [英] inserting problem with datetime value

问题描述

我有问题连接存储日期时间值到数据库表我的系统日期格式是dd / mm / yyyy日期存储在mm / dd / yyyy formate有什么问题

我的代码是如下$ / $

公共函数AddIndent（ByVal cnSQL作为SqlConnection，ByVal ds作为SqlTransaction，ByVal strIndentNo作为字符串，ByVal dteIndentDate作为日期，ByVal strIndentForDept作为字符串，ByVal strlocation作为字符串，_

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作为SqlCommand

Dim strSQL As String

Dim intRowsAffectedAffected as Integer



试试

昏暗SQLConfirm作为整数

Dim SQLCancel作为整数

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）


''构建插入语句以将新产品插入到产品表

strSQL =" INSERT INTO workindents（WorkIndentNo，WorkIndentDate，OriginDept Name，WorkLocation，WorkDeptName，Job，NatureofExpense，workCategory，WorkDescription，ImportLocal，ConfirmF lag，" &安培; _

" CancelFlag，orderedflag，CloseFlag，MakerID，dtmCreat ed）值（''"& strIndentNo&"''，cast（''"& dteIndentDate&" ;''as datetime），''"& strIndentForDept& _

"''，''"& strlocation&"''，''"& strworkDept&"''，''"& strjob&"''，''"& strnatureofexpense&"''，''"& strCategory&"''， ''"& strdesc&"''，''"& strIL& _

"''，"& SQLConfirm&"，"& ; SQLCancel&"，"& SQLOrdered&""& SQLClosed& _

"，''"& strMakerId&"''， ''"& Today&"''）"



''cnSQL =新的SqlConnection（strconn）

''cnSQL.Open（）

cmSQL =新的SqlCommand（strSQL，cnSQL，ds）

cmSQL.ExecuteNonQuery（）


''关闭并清理对象

''cnSQL.Close（）

cmSQL.Dispose（）

''cnSQL.Dispose（）

AddIndent = True


''刷新产品列表

''PopulateProductList（）

''FindProductByName（lstProducts，ProductName）


Catch Exp为SqlException

MsgBox（Exp.Message，MsgBoxStyle.Critical，SQL Error）

AddIndent = False

Catch Exp As Exception

MsgBox（Exp.Message，MsgBoxStyle.Critical，General Error）

AddIndent = False

结束尝试

结束函数

解决方案

这个问题正在转移到 Visual Basic 论坛。


ADMIN

我想你会发现那个SQL （或者可能是MS Access）要求以美国格式传递日期。没有必要担心它。只要它是一个日期值，您就可以以任意方式显示 - 只需让数据库在存储时就可以了。

我认为您会发现SQL（或MS Access）需要以美国格式传递日期。没有必要担心它。只要它是一个日期值，您就可以以任何您想要的方式显示 - 只需让数据库在存储时就可以了。

当我将区域设置设为MM / dd / yyyy然后它存储正确但我的reginol设置为dd / MM / yyyy然后如果给我错误超出范围日期时间值

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

解决方案

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

这篇关于插入日期时间值的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！