October 17th, 2013, 08:38 AM
-
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
October 17th, 2013, 09:34 PM
-
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