帮忙优化一下这段代码

[hzj19800817]

'定义一些公共变量
Public P_username As String
Public P_rydm As String
'连接SQLSERVER数据库信息，用于访问远程数据
'定义一个连接SQLSERVER数据库的连接串
Public connsql As String = "data source=SERVER2;initial catalog=sxht;user id=sxht;password=sxht"
Public objsqlconnection As SqlConnection = New SqlConnection(connsql)
'定义一个数据集对象
Public objdataset As DataSet
'定义一个数据视图对象
Public objdataview As DataView
'定义一个数据适配器对象
Public objdataadpter As SqlDataAdapter
'定义一个返回数据集的查询函数
Public Function GetDataFromDbsql(ByVal sqlstr As String) As DataSet
objdataset = New DataSet()
objdataset.Clear()
Try
'打开数据库连接
objsqlconnection.Open()
'将数据库的数据映射到数据适配器
objdataadpter = New SqlDataAdapter(sqlstr, objsqlconnection)
'填充数据集的数据
objdataadpter.Fill(objdataset)
'关闭连接
objsqlconnection.Close()
Catch
MsgBox("错误号：" & Err.Number & "错误描述：" & Err.Description)
End Try
If objdataset.Tables(0).Rows.Count > 0 Then
Return objdataset
Else
Return Nothing
End If
End Function
'定义一个返回数据库执行成功标志的函数
Public Function updatadta(ByVal strsql As String) As Boolean
Dim objcommand As SqlCommand = New SqlCommand()
Try
objsqlconnection.Open()
objcommand.Connection = objsqlconnection
objcommand.CommandType = CommandType.Text
objcommand.CommandText = strsql
objcommand.ExecuteNonQuery()
objsqlconnection.Close()
Catch
MsgBox("错误号：" & Err.Number & "错误描述：" & Err.Description)
Return False
End Try
Return True
End Function



'连接ASSESS数据库信息：用于本地数据操作
'定义一个连接ACCESS数据库的连接串
Public StartPath As String = Application.StartupPath & "\data\fwjk.mdb"
Public ConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0; "
Public ConnectStr As String = ConnStr & "Data Source=" & StartPath
Public DataAdapter As OleDbDataAdapter
Public DataConnection As OleDbConnection
Public DataSet As DataSet
'定义一个返回数据集的公共查询函数
Public Function GetDataFromDb(ByVal sqlstr As String) As DataSet
Try
DataConnection = New OleDbConnection()
DataConnection.ConnectionString = ConnectStr
DataAdapter = New OleDbDataAdapter(sqlstr, DataConnection)
DataSet = New DataSet()
DataSet.Clear()
DataAdapter.Fill(DataSet)
DataConnection.Close()
Catch
MsgBox("错误号：" & Err.Number & "错误描述：" & Err.Description)
End Try
If DataSet.Tables(0).Rows.Count > 0 Then
Return DataSet
Else
Return Nothing
End If
End Function
'定义一个返回数据库操作成功标志的函数
Public Function UpdateDataBase(ByVal sqlstr As String) As Boolean
Dim sqlconn As New OleDbConnection(ConnectStr)
Try
Dim cmdTable As OleDbCommand = New OleDbCommand(sqlstr, sqlconn)
cmdTable.CommandType = CommandType.Text
sqlconn.Open()
cmdTable.ExecuteNonQuery()
sqlconn.Close()
Catch
MsgBox("错误号：" & Err.Number & "错误描述：" & Err.Description)
Return False
End Try
Return True
End Function
以上为连接两个数据库和操作数据库的函数

[hzj19800817]

'定义一个形成销售单据的函数
'==============================================================================
'参数说明：ps_khdm 客户代码（税号）
' ps_djxx 合并单据号，格式：'10001','10002'
' ps_djrq 所合并单据的最大单据日期
'编写人：
'编写日期：2004.08.11
'=============================================================================
Public Sub load_djxx(ByVal ps_khdm As String, ByVal ps_djxx As String, ByVal djrq As Date)
'取出客户信息
Dim khmc As String '客户名称
Dim khsh As String '客户税号
Dim dzdh As String '地址电话
Dim khyh As String '开户银行
Dim strsql As String
Dim dadapter As OleDb.OleDbDataAdapter
Dim constr As OleDb.OleDbConnection
Dim kh_ds As DataSet
strsql = "select khmc,khsh,dzdh,khyh from dj_khxx where khdm = '" & ps_khdm & "'"
constr = New OleDb.OleDbConnection()
constr.ConnectionString = ConnectStr
dadapter = New OleDb.OleDbDataAdapter(strsql, constr)
kh_ds.Clear()
dadapter.Fill(kh_ds)
'kh_ds = GetDataFromDb(strsql)

If Not kh_ds Is Nothing Then
khmc = kh_ds.Tables(0).Rows(0)("khmc")
khsh = kh_ds.Tables(0).Rows(0)("khsh")
dzdh = kh_ds.Tables(0).Rows(0)("dzdh")
khyh = kh_ds.Tables(0).Rows(0)("khyh")
End If
'非空性检测
If khmc = "" Then
MsgBox("客户代码为" & ps_khdm & "的客户名称不存在！", MsgBoxStyle.Information, "提示")
Exit Sub
End If
If khsh = "" Then
MsgBox("客户代码为" & ps_khdm & "的客户税号不存在！", MsgBoxStyle.Information, "提示")
Exit Sub
End If
If dzdh = "" Then
MsgBox("客户代码为" & ps_khdm & "的客户地址电话不存在！", MsgBoxStyle.Information, "提示")
Exit Sub
End If
If khyh = "" Then
MsgBox("客户代码为" & ps_khdm & "的客户开户银行不存在！", MsgBoxStyle.Information, "提示")
Exit Sub
End If
'取出最大单据号
Dim max_djid As Integer
Dim djid_ds As DataSet
strsql = "select max(djid) as djid from dj_fp "
djid_ds.Clear()
djid_ds = GetDataFromDb(strsql)
If Not djid_ds Is Nothing Then
max_djid = djid_ds.Tables(0).Rows(0)("djid") + 1
Else
max_djid = 1
End If
'插入发票信息
Dim insstr As String
insstr = "insert into dj_fp(djid,fph,djh,gfsh,gfmc,gfdzdh,khyh,sfdc,djrq) values" & _
"(" & max_djid & ",'NULL','" & ps_djxx & "','" & khsh & "','" & khmc & "','" & dzdh & "','" & khyh & "','N'," & djrq & ")"
If UpdateDataBase(insstr) = False Then
MsgBox("插入DJ_FP表记录失败", MsgBoxStyle.Information, "提示")
Exit Sub
End If
'插入明细表记录
Dim mx_ds As DataSet
Dim i As Integer '循环变量
Dim count As Integer '记录数
Dim hwdm As String
Dim hwmc As String
Dim dw As String
Dim gg As String
Dim dj As Decimal
Dim sysl As Decimal
Dim sl As Decimal
Dim je As Decimal
Dim se As Decimal
Dim jshj As Decimal
strsql = "select hwdm,hwmc,dw,gg,dj,sysl,sum(sl) sl,sum(je) je,sum(se) se,sum(jshj) jshj from view_salemx where djh in (" & ps_djxx & ")"
mx_ds.Clear()
mx_ds = GetDataFromDb(strsql)
If Not mx_ds Is Nothing Then
count = mx_ds.Tables(0).Rows.Count
If count > 0 Then
For i = 0 To count - 1
'取出单据明细信息
hwdm = mx_ds.Tables(0).Rows(i)("hwdm")
hwmc = mx_ds.Tables(0).Rows(i)("hwmc")
dw = mx_ds.Tables(0).Rows(i)("dw")
gg = mx_ds.Tables(0).Rows(i)("gg")
dj = mx_ds.Tables(0).Rows(i)("dj")
sl = mx_ds.Tables(0).Rows(i)("sl")
je = mx_ds.Tables(0).Rows(i)("je")
se = mx_ds.Tables(0).Rows(i)("se")
jshj = mx_ds.Tables(0).Rows(i)("jshj")
'插入明细记录
insstr = "insert into dj_fpmx(djid,xh,hwdm,hwmc,dw,gg,sl,dj,sysl,se,jshj) values" & _
"(" & max_djid & "," & i & ",'" & hwdm & "','" & hwmc & "','" & dw & "','" & gg & "'," & sl & "," & dj & "," & sysl & "," & se & "," & jshj & ")"
If UpdateDataBase(insstr) = True Then
MsgBox("发票信息生成完毕！", MsgBoxStyle.Information, "提示")
Else
MsgBox("发票明细信息形成失败！", MsgBoxStyle.Information, "提示")
Exit For
End If
Next
End If
End If
End Sub

[hzj19800817]

说明：以上代码在执行时每次都要重新连数据库，实际上”load_djxx“数据库只要连一次就行了，这样程序执行效率肯定不好，大家帮忙看看怎么样优化一下？

[hzj19800817]

上面的有错，这个是标准
Public Sub load_djxx(ByVal ps_khdm As String, ByVal ps_djxx As String, ByVal djrq As Date)
'取出客户信息
Dim khmc As String '客户名称
Dim khsh As String '客户税号
Dim dzdh As String '地址电话
Dim khyh As String '开户银行
Dim strsql As String
Dim kh_ds As DataSet
strsql = "select khmc,khsh,dzdh,khyh from dj_khxx where khdm = '" & ps_khdm & "'"
kh_ds.Clear()
kh_ds = GetDataFromDb(strsql)

If Not kh_ds Is Nothing Then
khmc = kh_ds.Tables(0).Rows(0)("khmc")
khsh = kh_ds.Tables(0).Rows(0)("khsh")
dzdh = kh_ds.Tables(0).Rows(0)("dzdh")
khyh = kh_ds.Tables(0).Rows(0)("khyh")
End If
'非空性检测
If khmc = "" Then
MsgBox("客户代码为" & ps_khdm & "的客户名称不存在！", MsgBoxStyle.Information, "提示")
Exit Sub
End If
If khsh = "" Then
MsgBox("客户代码为" & ps_khdm & "的客户税号不存在！", MsgBoxStyle.Information, "提示")
Exit Sub
End If
If dzdh = "" Then
MsgBox("客户代码为" & ps_khdm & "的客户地址电话不存在！", MsgBoxStyle.Information, "提示")
Exit Sub
End If
If khyh = "" Then
MsgBox("客户代码为" & ps_khdm & "的客户开户银行不存在！", MsgBoxStyle.Information, "提示")
Exit Sub
End If
'取出最大单据号
Dim max_djid As Integer
Dim djid_ds As DataSet
strsql = "select max(djid) as djid from dj_fp "
djid_ds.Clear()
djid_ds = GetDataFromDb(strsql)
If Not djid_ds Is Nothing Then
max_djid = djid_ds.Tables(0).Rows(0)("djid") + 1
Else
max_djid = 1
End If
'插入发票信息
Dim insstr As String
insstr = "insert into dj_fp(djid,fph,djh,gfsh,gfmc,gfdzdh,khyh,sfdc,djrq) values" & _
"(" & max_djid & ",'NULL','" & ps_djxx & "','" & khsh & "','" & khmc & "','" & dzdh & "','" & khyh & "','N'," & djrq & ")"
If UpdateDataBase(insstr) = False Then
MsgBox("插入DJ_FP表记录失败", MsgBoxStyle.Information, "提示")
Exit Sub
End If
'插入明细表记录
Dim mx_ds As DataSet
Dim i As Integer '循环变量
Dim count As Integer '记录数
Dim hwdm As String
Dim hwmc As String
Dim dw As String
Dim gg As String
Dim dj As Decimal
Dim sysl As Decimal
Dim sl As Decimal
Dim je As Decimal
Dim se As Decimal
Dim jshj As Decimal
strsql = "select hwdm,hwmc,dw,gg,dj,sysl,sum(sl) sl,sum(je) je,sum(se) se,sum(jshj) jshj from view_salemx where djh in (" & ps_djxx & ")"
mx_ds.Clear()
mx_ds = GetDataFromDb(strsql)
If Not mx_ds Is Nothing Then
count = mx_ds.Tables(0).Rows.Count
If count > 0 Then
For i = 0 To count - 1
'取出单据明细信息
hwdm = mx_ds.Tables(0).Rows(i)("hwdm")
hwmc = mx_ds.Tables(0).Rows(i)("hwmc")
dw = mx_ds.Tables(0).Rows(i)("dw")
gg = mx_ds.Tables(0).Rows(i)("gg")
dj = mx_ds.Tables(0).Rows(i)("dj")
sl = mx_ds.Tables(0).Rows(i)("sl")
je = mx_ds.Tables(0).Rows(i)("je")
se = mx_ds.Tables(0).Rows(i)("se")
jshj = mx_ds.Tables(0).Rows(i)("jshj")
'插入明细记录
insstr = "insert into dj_fpmx(djid,xh,hwdm,hwmc,dw,gg,sl,dj,sysl,se,jshj) values" & _
"(" & max_djid & "," & i & ",'" & hwdm & "','" & hwmc & "','" & dw & "','" & gg & "'," & sl & "," & dj & "," & sysl & "," & se & "," & jshj & ")"
If UpdateDataBase(insstr) = True Then
MsgBox("发票信息生成完毕！", MsgBoxStyle.Information, "提示")
Else
MsgBox("发票明细信息形成失败！", MsgBoxStyle.Information, "提示")
Exit For
End If
Next
End If
End If
End Sub