vb6.0 连接数据库，查询，插入和修改

一：   创建连接

Public Function CreateSQLConn() As Boolean
Const ProcName = "CreateSQLConn"
Dim strConnection As String
On Error GoTo ErrHandler
1       Set objSQLConn = New ADODB.Connection
       
2           strConnection = "Provider=SQLOLEDB.1;Password=" & gbServerPWD & ";Persist Security Info=True;User ID=" & gbUserID & ";Initial Catalog=" & gbInitCat & ";Data Source=" & gbServerIP & INSTANCENAME
If RUNSERVER = "REMOTE" Then
        strConnection = "Provider=SQLNCLI.1;Password=" & gbServerPWD & ";Persist Security Info=True;User ID=" & gbUserID & ";Initial Catalog=" & gbInitCat & ";Data Source=" & gbServerIP & INSTANCENAME
End If
3       objSQLConn.ConnectionTimeout = 40
4       objSQLConn.Open strConnection
Exit Function
ErrHandler:
    ErrLog Erl, Err.Number, Err.Description, ProcName, True, True
End Function

 

二：查询

Public Function LoadSuppMasterSort(ByVal Sorting As EnSorting) As colGroupMaster
Const ProcName = "LoadSuppMasterSort"
Dim strMSSQL As String
Dim objCol As colGroupMaster
Dim objMSRS As ADODB.Recordset
On Error GoTo ErrHandler
1       Set objCol = New colGroupMaster
2       Set objMSRS = New ADODB.Recordset
3       If Sorting = EnSorting.EnSortByCode Then
4           strMSSQL = "Select SuppCode, SuppName From tblSupplier Order By SuppCode"
5       Else
6           strMSSQL = "Select SuppCode, SuppName From tblSupplier Order By SuppName"
7       End If
8       objMSRS.Open strMSSQL, objSQLConn, adOpenStatic
9       While Not objMSRS.EOF
10          objCol.Add objMSRS("SuppCode"), objMSRS("SuppName")
11          objMSRS.MoveNext
12      Wend
13      objMSRS.Close

 

三：插入和修改记录

Public Function DoSaveItemmas(ByVal objItemmas As clsItemmas, ByVal InStatus As EnUPDATESTATUS) As Boolean
Const ProcName = "DoSaveItemmas"
Dim strMSSQL As String
Dim objMSRS As ADODB.Recordset
Dim strPLU As String
Dim blnRet As Boolean
On Error GoTo ErrHandler
1           strPLU = Trim(objItemmas.PLU)
2           strMSSQL = "Select * From tblItemmas Where PLU = '" & Trim(strPLU) & "'"
3           Set objMSRS = New ADODB.Recordset
4           objMSRS.Open strMSSQL, objSQLConn, 1, 3
5           If InStatus = EnUPDATESTATUS.EnAddNew Then
6               If Not objMSRS.EOF And Not objMSRS.BOF Then
7                   'Find Record To Create and not allow deplicate
8                   blnRet = False
9               Else
10                  objMSRS.AddNew
11                  objMSRS("PLU") = Replace(Trim(objItemmas.PLU), vbCrLf, "")
12                  objMSRS("Desci") = Replace(Trim(objItemmas.Desci), vbCrLf, "")
13                  objMSRS("LongDesci") = Replace(Trim(objItemmas.LongDesci), vbCrLf, "")
14                  objMSRS("Group0") = Trim(objItemmas.Group0)
15                  objMSRS("Group1") = Trim(objItemmas.Group1)
16                  objMSRS("Group2") = Trim(objItemmas.Group2)
17                  objMSRS("SuppCode") = Replace(Trim(objItemmas.SuppCode), vbCrLf, "")
18                  objMSRS("Weight") = objItemmas.Weight
19                  objMSRS("WeightUnit") = objItemmas.WeightUnit
20                  objMSRS("Volumne") = objItemmas.Volumne
21                  objMSRS("ExtCS") = objItemmas.ExtCS
22                  objMSRS("ExtInfo1") = objItemmas.ExtInfo1
23                  objMSRS("ExtInfo2") = objItemmas.ExtInfo2
24                  objMSRS("ExtInfo3") = objItemmas.ExtInfo3
'=============================增加货品资料 czf 2013 10 23============================================
                    objMSRS("PLong") = objItemmas.PLong
                    objMSRS("TaxName") = objItemmas.TaxName
                    objMSRS("PWidth") = objItemmas.PWidth
                    objMSRS("PHeight") = objItemmas.PHeight
                    objMSRS("PVolume") = objItemmas.PVolume
                    objMSRS("OldPlu") = objItemmas.OldPlu
                    objMSRS("Pcs") = objItemmas.Pcs
                    objMSRS("TaxCode") = objItemmas.TaxCode
                    objMSRS("BrandOfCountry") = objItemmas.BrandOfCountry
                    objMSRS("Place") = objItemmas.Place
                    objMSRS("Status") = objItemmas.Status
                    objMSRS("LowPrice") = objItemmas.LowPrice
                    objMSRS("Customs") = objItemmas.Customs
                    objMSRS("UpdateBy") = objItemmas.UpdateBy
                    objMSRS("ImageCode") = objItemmas.ImageCode
                    objMSRS("Introduction") = objItemmas.Introduction
                    objMSRS("Tariff") = objItemmas.Tariff
                    objMSRS("Vat") = objItemmas.Vat
                    objMSRS("OtherCost") = objItemmas.OtherCost
                    objMSRS("ImportCost") = objItemmas.ImportCost
 '                   objMSRS("SuppPlu") = objItemmas.SuppPlu
                    objMSRS("Category") = objItemmas.Category
                    objMSRS("SubCategory") = objItemmas.SubCategory
                    objMSRS("SubSubCategory") = objItemmas.SubSubCategory
                    objMSRS("Currency") = objItemmas.CostCurrency
                    objMSRS("CurrencyWS") = objItemmas.WSRetailCurrency
                   
                    objMSRS("UpdateBy") = gbUID
 '=============================增加货品资料结束 czf 2013 10 23============================================
           
25                  If gbblnDBSync = True Then
26                      objMSRS("UpdateDate") = Now
27                  End If
28                  objMSRS.Update
29                  blnRet = True
30              End If
31          ElseIf InStatus = EnUPDATESTATUS.EnUpdate Or InStatus = EnUPDATESTATUS.EnDeleted Then
32              If Not objMSRS.EOF And Not objMSRS.BOF Then
33                  objMSRS("PLU") = Replace(Trim(objItemmas.PLU), vbCrLf, "")
34                  objMSRS("Desci") = Replace(Trim(objItemmas.Desci), vbCrLf, "")
35                  objMSRS("LongDesci") = Replace(Trim(objItemmas.LongDesci), vbCrLf, "")
36                  objMSRS("Group0") = Trim(objItemmas.Group0)
37                  objMSRS("Group1") = Trim(objItemmas.Group1)
38                  objMSRS("Group2") = Trim(objItemmas.Group2)
39                  objMSRS("SuppCode") = Replace(Trim(objItemmas.SuppCode), vbCrLf, "")
40                  objMSRS("Weight") = objItemmas.Weight
41                  objMSRS("WeightUnit") = objItemmas.WeightUnit
42                  objMSRS("Volumne") = objItemmas.Volumne
43                  objMSRS("ExtCS") = objItemmas.ExtCS
44                  objMSRS("ExtInfo1") = objItemmas.ExtInfo1
45                  objMSRS("ExtInfo2") = objItemmas.ExtInfo2
46                  objMSRS("ExtInfo3") = objItemmas.ExtInfo3
                    '=============================增加货品资料 czf 2013 10 23============================================
                    objMSRS("PLong") = objItemmas.PLong
                    objMSRS("TaxName") = objItemmas.TaxName
                    objMSRS("PWidth") = objItemmas.PWidth
                    objMSRS("PHeight") = objItemmas.PHeight
                    objMSRS("PVolume") = objItemmas.PVolume
                    objMSRS("OldPlu") = objItemmas.OldPlu
                    objMSRS("Pcs") = objItemmas.Pcs
                    objMSRS("TaxCode") = objItemmas.TaxCode
                    objMSRS("BrandOfCountry") = objItemmas.BrandOfCountry
                    objMSRS("Place") = objItemmas.Place
                    objMSRS("Status") = objItemmas.Status
                    objMSRS("LowPrice") = objItemmas.LowPrice
                    objMSRS("Customs") = objItemmas.Customs
                    objMSRS("UpdateBy") = objItemmas.UpdateBy
                    objMSRS("ImageCode") = objItemmas.ImageCode
                    objMSRS("Introduction") = objItemmas.Introduction
                    objMSRS("Tariff") = objItemmas.Tariff
                    objMSRS("Vat") = objItemmas.Vat
                    objMSRS("OtherCost") = objItemmas.OtherCost
                    objMSRS("ImportCost") = objItemmas.ImportCost
  '                  objMSRS("SuppPlu") = objItemmas.SuppPlu
                    objMSRS("Category") = objItemmas.Category
                    objMSRS("SubCategory") = objItemmas.SubCategory
                    objMSRS("SubSubCategory") = objItemmas.SubSubCategory
                    objMSRS("Currency") = objItemmas.CostCurrency
                    objMSRS("CurrencyWS") = objItemmas.WSRetailCurrency
                    objMSRS("UpdateBy") = gbUID
 '=============================增加货品资料结束 czf 2013 10 23============================================
47                  If gbblnDBSync = True Then
48                      objMSRS("UpdateDate") = Now
49                  End If
50                  objMSRS.Update
51                  blnRet = True
52              Else
53                  'Error To Find Record Update.
54                  blnRet = False
55              End If