I am new in VB.NET so if somebody can help me. I have my vb project with some parameter functions to insert data in any table. I retrieve empty rs from database, then I fill that rs, and then call function Insert. It is very convinient because it works for any table. I also have some functions to convert recordset field value to parameter for SQLServer or Mysql.
Could anubody help me how to write this in VB.NET?
Thank you in advance
Function Insert(rs as adodb.recordset, Table as string)
.......
Source = "INSERT INTO " & Table
FieldList = ""
For i = 1 To rs.Fields.Count - 1
FieldList = FieldList & rs.Fields(i).Name & ", "
If gf.FieldToParm(rs.Fields(i).Value, sVal, gf.FType(rsSifarnik.Fields(i).Type)) Then
FieldList = FieldList & sVal & ", "
End If
Next i
......
end function
Public Function FType(T As Integer) As Integer
Select Case T
Case adDate, _
adDBDate, _
adDBTime: FType = icDATE
Case adBoolean: FType = icBOOLEAN
Case adCurrency, adNumeric: FType = icCURRENCY
Case adInteger: FType = icFOREIGN_KEY
Case adVarNumeric, _
adSmallInt, _
adDouble: FType = icNUMBER
Case adChar, _
adVarChar, adVarWChar, _
adLongVarChar, adLongVarWChar, _
adBSTR: FType = icSTRING
Case adVarChar: FType = icNON_EMPTY_STRING
Case adChapter: FType = 999
End Select
End Function
'=======================================================================
'Synopsis: Convert recordset field value to parameter for stored procedure
'Function input:
'Function output: Returns TRUE if the function succeeds.
'=======================================================================
Public Function FieldToParm(ByVal vVar, ByRef sPrm, ByVal iType As Integer) As Boolean
Dim sVal As String
Dim poz As Integer
FieldToParm = False
If IsNull(vVar) Then
sPrm = scNULL
FieldToParm = True
Exit Function
End If
Select Case iType
Case COL_TYPE.icBOOLEAN
sPrm = CInt(vVar)
FieldToParm = True
Case COL_TYPE.icDATE
If vVar Then
' MYSQL
vVar = Format(vVar, "yyyy-MM-dd HH:mm:ss")
sPrm = "'" & vVar & "'"
FieldToParm = True
End If
Case COL_TYPE.icFOREIGN_KEY
sPrm = CStr(vVar)
FieldToParm = True
Case COL_TYPE.icNUMBER
vVar = CStr(vVar) ' mysql
sPrm = Replace(vVar, ",", ".") ' mysql
FieldToParm = True
Case COL_TYPE.icSTRING
sVal = Trim$(vVar)
Debug.Print sVal
sPrm = scSQ & DoQuotes(sVal) & scSQ
Debug.Print sPrm
sPrm = "N" & sPrm
Debug.Print sPrm
FieldToParm = True
Case COL_TYPE.icNON_EMPTY_STRING
sVal = Trim$(vVar)
If sVal <> vbNullString Then
sPrm = scSQ & DoQuotes(sVal) & scSQ
sPrm = "N" & sPrm
FieldToParm = True
End If
Case COL_TYPE.icCURRENCY
sVal = Trim$(vVar) '
sPrm = Replace(sVal, ",", ".")
FieldToParm = True
Case Else
sPrm = "''"
FieldToParm = True
End Select
End Function
