#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    1
    Rep Power
    0

    Creating code originally in classic asp


    hi,
    am new to vb.net and and cannot see how to do what I need to do.
    I have a process which works with the original program written in classic asp.
    I need to read one row from an access table on the local computer, format an SQL Update query from that data and then run that query against an online sql server database.
    The program only updates one sql server row.
    I can get the row from the access table, but cannot see how to format and issue the SQL call.
    Thanks
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    289
    Rep Power
    44
    I use this generalized routine to create the SQL statement:

    Code:
    Public Function CreateInsert(TableName As String, _
      FieldList As String, FieldValue As Variant, _
      FieldType As String) As Variant
        Dim temp$, N%, M%, M1%
        Dim LineProp() As String
        ReDim LineProp(UBound(FieldValue, 1))
        N% = 0: M% = 1
        M1% = InStr(FieldList, ",")
        Do Until M1% = 0 'Check bounds on FieldList string
            M% = M1% + 1
            M1% = InStr(M%, FieldList, ",")
            N% = N% + 1
        Loop
        If N% <> UBound(FieldValue, 1) Then
            MsgBox "Number of Field Names supplied does not match Fields requested!"
            CreateInsert = ""
            Exit Function
        End If
        N% = 0: M% = 1
        M1% = InStr(FieldType, ",")
        Do Until M1% = 0 'Create array form FieldType string
            LineProp(N%) = Mid$(FieldType, M%, M1% - M%)
            M% = M1% + 1
            M1% = InStr(M%, FieldType, ",")
            N% = N% + 1
        Loop
        If N% <> UBound(FieldValue, 1) Then 'Check bounds
            MsgBox "Number of Field Types supplied does not match Fields requested!"
            CreateInsert = ""
            Exit Function
        End If
        For N% = 0 To UBound(FieldValue, 1)
            If IsNumeric(LineProp(N%)) Then 'String Value
                temp$ = temp$ & QUOTE & FieldValue(N%, 0) & QUOTE & ", "
            ElseIf LineProp(N%) = "T" Then 'Date/Time Value
                temp$ = temp$ & DateSep & FieldValue(N%, 0) & DateSep & ", "
            Else 'Numeric Value
                temp$ = temp$ & Trim(FieldValue(N%, 0)) & ", "
            End If
        Next N%
        If Len(TableName) > 0 Then
            Mid$(temp$, Len(temp$) - 1, 2) = ");"
            CreateInsert = "INSERT INTO " & TableName & "(" _
              & FieldList & ") VALUES(" & temp$
        Else  'Used for creating text file output
            CreateInsert = temp$
        End If
    End Function
    J.A. Coutts

IMN logo majestic logo threadwatch logo seochat tools logo