从VB6向MS Access插入订单 [英] Insert Order to MS Access from VB6
问题描述
我得到了我们用于培训管理的旧VB应用程序之一,它是用VB6编写的,数据库是MsAccess. 当我使用该应用程序时,在保存培训课程的同时,所有记录都保存在先前的记录之间(不按顺序).它不会在最后一行中添加.应用程序还从数据库中获取数据并在gridview中显示.因此,最终的显示将以未排序的方式进行.就像我使用表单添加的最新数据一样,显示在中间行的某些位置. 当我看到数据库表时,所有新数据都添加到中间行中. 在这里,我将显示代码:
i got one of our old VB application for training management.It was written in VB6 and the database is MsAccess. When im using that application, while saving the training sessions ,all records are saved in between the previous records(not in order). It is not get added in last row. And also the application fetching the data from database and showed it in gridview. So the final display is in unsorted way.Like latest data which i was added using form,displaying in some where in middle row. When i see the database table, all new data were get added in middle rows. Here i will show the code:
sql = "INSERT INTO TrAssignment (BatchID,Category,CourseNumber,CourseTitle,FromDate,ToDate,Duration,Location, Trainer, FixedCost,DefaultStudentCost) VALUES ('" & CStr(txtBatchid.Text) & "','" & CStr(cmbCrscategory) & "','" & CStr(sCourNo) & "', '" & CStr(sCourTitle) & "','" & SchfromDTPick.Value & "','" & SchtoDTPicker.Value & "','" & CStr(txtSchduration.Text) & "','" & cmbLocation & "','" & CStr(cmbTrainer) & "','" & CStr(Trim(txtFixedcost.Text)) & "','" & CStr(Trim(txtDefault.Text)) & "')"
rs.Open sql, conn, adOpenDynamic, adLockOptimistic
日期格式也类似于dd-mm-yyyy,对于某些记录,日期也以这种精确格式保存.但对于某些人来说,日期格式就像d/m/yyyy. 只是他们正在从日期控件中获取日期,而无需格式化代码.
And also the date format in form is like dd-mm-yyyy,for some records the date get saved in this exact format. But for some, the date format is like d/m/yyyy. Just they are fetching the date from the date control,no formatting in code.
推荐答案
您的日期格式不正确.通常,您可以从此功能中受益:
Your date format is wrong. In general, you could benefit from this function:
' Converts a value of any type to its string representation.
' The function can be concatenated into an SQL expression as is
' without any delimiters or leading/trailing white-space.
'
' Examples:
' SQL = "Select * From TableTest Where [Amount]>" & CSql(12.5) & "And [DueDate]<" & CSql(Date) & ""
' SQL -> Select * From TableTest Where [Amount]> 12.5 And [DueDate]< #2016/01/30 00:00:00#
'
' SQL = "Insert Into TableTest ( [Street] ) Values (" & CSql(" ") & ")"
' SQL -> Insert Into TableTest ( [Street] ) Values ( Null )
'
' Trims text variables for leading/trailing Space and secures single quotes.
' Replaces zero length strings with Null.
' Formats date/time variables as safe string expressions.
' Uses Str to format decimal values to string expressions.
' Returns Null for values that cannot be expressed with a string expression.
'
' 2016-01-30. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function CSql( _
ByVal Value As Variant) _
As String
Const vbLongLong As Integer = 20
Const SqlNull As String = " Null"
Dim Sql As String
Dim LongLong As Integer
#If Win32 Then
LongLong = vbLongLong
#End If
#If Win64 Then
LongLong = VBA.vbLongLong
#End If
Select Case VarType(Value)
Case vbEmpty ' 0 Empty (uninitialized).
Sql = SqlNull
Case vbNull ' 1 Null (no valid data).
Sql = SqlNull
Case vbInteger ' 2 Integer.
Sql = Str(Value)
Case vbLong ' 3 Long integer.
Sql = Str(Value)
Case vbSingle ' 4 Single-precision floating-point number.
Sql = Str(Value)
Case vbDouble ' 5 Double-precision floating-point number.
Sql = Str(Value)
Case vbCurrency ' 6 Currency.
Sql = Str(Value)
Case vbDate ' 7 Date.
Sql = Format(Value, " \#yyyy\/mm\/dd hh\:nn\:ss\#")
Case vbString ' 8 String.
Sql = Replace(Trim(Value), "'", "''")
If Sql = "" Then
Sql = SqlNull
Else
Sql = " '" & Sql & "'"
End If
Case vbObject ' 9 Object.
Sql = SqlNull
Case vbError ' 10 Error.
Sql = SqlNull
Case vbBoolean ' 11 Boolean.
Sql = Str(Abs(Value))
Case vbVariant ' 12 Variant (used only with arrays of variants).
Sql = SqlNull
Case vbDataObject ' 13 A data access object.
Sql = SqlNull
Case vbDecimal ' 14 Decimal.
Sql = Str(Value)
Case vbByte ' 17 Byte.
Sql = Str(Value)
Case LongLong ' 20 LongLong integer (Valid on 64-bit platforms only).
Sql = Str(Value)
Case vbUserDefinedType ' 36 Variants that contain user-defined types.
Sql = SqlNull
Case vbArray ' 8192 Array.
Sql = SqlNull
Case Else ' Should not happen.
Sql = SqlNull
End Select
CSql = Sql & " "
End Function
这篇关于从VB6向MS Access插入订单的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!