一： 创建连接

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,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),"") 13 objMSRS("LongDesci") = Replace(Trim(objItemmas.LongDesci),"") 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),"") 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),"") 34 objMSRS("Desci") = Replace(Trim(objItemmas.Desci),"") 35 objMSRS("LongDesci") = Replace(Trim(objItemmas.LongDesci),"") 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),"") 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