从 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屋！