在vb.net里的SQL数据无法现时更新
小的是新手, 还有很多东西不清楚. 请原谅我问些愚蠢的问题. 谢谢.
在clsConnectDB.vb里的"Add Project Record"代码:
Public Sub AddProjectRecord(ByVal cJobNo As String, ByVal cName As String, ByVal cDescp As String, _
ByVal nRevNo As Integer, ByVal cPrjmgr As String, ByVal cMD As String, _
ByVal cRegDate As String, ByVal cStartDate As String, ByVal cCompleteDate As String)
Dim loDataAdpr As OleDbDataAdapter
Dim lnRowAffectResult As Integer
isConnectDB()
Try
loDataAdpr = New OleDbDataAdapter()
loDataAdpr.InsertCommand = New OleDbCommand()
loDataAdpr.InsertCommand.Connection = oConn_ProjMgt 'oConn_ProjectMgt
loDataAdpr. = CommandType.Text
loDataAdpr. = " INSERT INTO ProjReg " & _
" (jobno, name, descp, revno, prjmgr, md,regdate, startdate, completedate) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)"
loDataAdpr.InsertCommand.Parameters.Add("@jobno", OleDbType.VarChar).Value = cJobNo
loDataAdpr.InsertCommand.Parameters.Add("@name", OleDbType.VarChar).Value = cName
loDataAdpr.InsertCommand.Parameters.Add("@descp", OleDbType.VarChar).Value = cDescp
loDataAdpr.InsertCommand.Parameters.Add("@revno", OleDbType.Integer).Value = nRevNo
loDataAdpr.InsertCommand.Parameters.Add("@prjmgr", OleDbType.VarChar).Value = cPrjmgr
loDataAdpr.InsertCommand.Parameters.Add("@md", OleDbType.VarChar).Value = cMD
loDataAdpr.InsertCommand.Parameters.Add("@regdate", OleDbType.VarChar).Value = cRegDate
loDataAdpr.InsertCommand.Parameters.Add("@startdate", OleDbType.VarChar).Value = cStartDate
loDataAdpr.InsertCommand.Parameters.Add("@completedate", OleDbType.VarChar).Value = cCompleteDate
lnRowAffectResult = loDataAdpr.InsertCommand.ExecuteNonQuery()
cMsgStatus = "Added record"
lnRowAffectResult = loDataAdpr.InsertCommand.UpdatedRowSource
Catch OleDBExceptionErr As OleDbException
cMsgStatus = OleDBExceptionErr.Message
End Try
End Sub
**************************************************************************
在clsConnectDB.vb里的"Update Project Record"代码:
Public Sub UpdateProjectRecord(ByVal nID As Integer, ByVal cName As String, ByVal cDescp As String, _
ByVal nRevNo As Integer, ByVal cPrjmgr As String, ByVal cMD As String, _
ByVal cRegDate As String, ByVal cStartDate As String, ByVal cCompleteDate As String) Dim lnRowAffectResult As Integer
Dim loDataAdpr As OleDbDataAdapter
isConnectDB()
Try
loDataAdpr = New OleDbDataAdapter()
loDataAdpr.UpdateCommand = New OleDbCommand()
loDataAdpr.UpdateCommand.Connection = oConn_ProjMgt
loDataAdpr. = CommandType.Text
loDataAdpr. = " UPDATE ProjReg SET " & _
" name = ?, descp = ?, revno = ?, prjmgr = ?, md = ?, regdate = ?, startdate = ?, completedate = ? WHERE id = ?"
loDataAdpr.UpdateCommand.Parameters.Add("@name", OleDbType.VarChar).Value = cName
loDataAdpr.UpdateCommand.Parameters.Add("@descp", OleDbType.VarChar).Value = cDescp
loDataAdpr.UpdateCommand.Parameters.Add("@revno", OleDbType.Integer).Value = nRevNo
loDataAdpr.UpdateCommand.Parameters.Add("@prjmgr", OleDbType.VarChar).Value = cPrjmgr
loDataAdpr.UpdateCommand.Parameters.Add("@md", OleDbType.VarChar).Value = cMD
loDataAdpr.UpdateCommand.Parameters.Add("@regdate", OleDbType.VarChar).Value = cRegDate
loDataAdpr.UpdateCommand.Parameters.Add("@startdate", OleDbType.VarChar).Value = cStartDate
loDataAdpr.UpdateCommand.Parameters.Add("@completedate", OleDbType.VarChar).Value = cCompleteDate
loDataAdpr.UpdateCommand.Parameters.Add("@id", OleDbType.Integer).Value = nID
lnRowAffectResult = loDataAdpr.UpdateCommand.ExecuteNonQuery()
cMsgStatus = "Updated record"
Catch OleDBExceptionErr As OleDbException
cMsgStatus = OleDBExceptionErr.Message
End Try
End Sub
**************************************************************************
在frmProjectRegistration.vb里"Print"的代码:
Private Sub cmdPrint_Click(sender As System.Object, e As System.EventArgs) Handles cmdPrint.Click
oDSreport = New dsrptDataset()
oGenRpt = New clsGenReportData(frmMain.objDB.oConn_ProjMgt)
cSQLstring = " SELECT jobno, name, descp, revno, regdate, md, prjmgr, " & _
" startdate, completedate"
" FROM ProjReg where id = " & CStr(nID)
cRptFile = "ProjectManagement_2012.rptProjectRegForm.rdlc"
oGenRpt.PopulateData(cSQLstring)
oDSreport.dtProject.Load(oGenRpt.oDrReport)
If oDSreport.dtProject.Count > 0 Then
ViewReport()
Else
MsgBox("No record display", MsgBoxStyle.Information, "Message")
End If
oDSreport.Dispose()
oDSreport = Nothing
End Sub
**************************************************************************
在frmProjectRegistration.vb里"Save"的代码:
Private Sub btnSaveRegistration_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveRegistration.Click
If nDataFlag = 1 Then
frmMain.objDB.AddProjectRecord(txtJobNo.Text, txtName.Text, txtDescp.Text, CInt(nudRevno.Value), cboPrjmgr.Text, cboMD.Text, _
dtpRegdate.Text.Trim(), dtpStartdate.Text.Trim(), dtpCompletedate.Text.Trim())
Else
frmMain.objDB.UpdateProjectRecord(nID, txtName.Text, txtDescp.Text, CInt(nudRevno.Value), cboPrjmgr.Text, cboMD.Text, _
dtpRegdate.Text.Trim(), dtpStartdate.Text.Trim(), dtpCompletedate.Text.Trim())
End If
MsgBox(frmMain.objDB.cMsgStatus, MsgBoxStyle.Information, "Message")
End Sub
**************************************************************************
在frmProjectRegistration.vb里"Load Project"的代码:
Private Sub Load_Project(ByVal nID As Integer)
Dim lcSQL As String
Dim lnComboboxIndex As Integer
lcSQL = " SELECT * FROM projreg WHERE id = " & nID
frmMain.objDB.QueryRecord(lcSQL)
While frmMain.objDB.oQuery.Read()
If frmMain.objDB.oQuery.HasRows And Not frmMain.objDB.oQuery.IsDBNull(0) Then
txtJobNo.Text = frmMain.objDB.oQuery.GetString(1)
txtName.Text = frmMain.objDB.oQuery.GetString(2)
txtDescp.Text = frmMain.objDB.oQuery.GetString(3)
nudRevno.Value = frmMain.objDB.oQuery.GetInt32(91)
lnComboboxIndex = cboPrjmgr.FindStringExact(frmMain.objDB.oQuery.GetString(29))
cboPrjmgr.SelectedIndex = lnComboboxIndex
lnComboboxIndex = cboMD.FindStringExact(frmMain.objDB.oQuery.GetString(30))
cboMD.SelectedIndex = lnComboboxIndex
If Not String.IsNullOrWhiteSpace(frmMain.objDB.oQuery.GetString(31)) Then
dtpRegdate.Text = frmMain.objDB.oQuery.GetString(31)
Else
dtpRegdate.CustomFormat = " "
dtpRegdate.Text = ""
End If
If Not String.IsNullOrWhiteSpace(frmMain.objDB.oQuery.GetString(32)) Then
dtpStartdate.Text = frmMain.objDB.oQuery.GetString(32)
Else
dtpStartdate.CustomFormat = " "
dtpStartdate.Text = ""
End If
If Not String.IsNullOrWhiteSpace(frmMain.objDB.oQuery.GetString(33)) Then
dtpCompletedate.Text = frmMain.objDB.oQuery.GetString(33)
Else
dtpCompletedate.CustomFormat = " "
dtpCompletedate.Text = ""
End If
End If
End While
End Sub
**************************************************************************
以上的代码皆可正常操作, 唯一的问题就是它不能立即更新. 需要出去"Add New Project"再进回去才可以看得到之前已经加进去record...
请问大虾们, 我应该怎么样才能让它自动更新SQL里面的record呢?
谢谢.
