在Access数据库的更新数据行...(Access + DataGrid)
在Access数据库的添加数据行和删除行,都测试成功了。
可是更新数据行就是不行...
查了很多文章,可惜...
还希望那位高手指点...
下面为源码.........
<%@ Page Language= "VB " ContentType= "text/html " ResponseEncoding= "utf-8 " %>
<%@ Import Namespace= "System.Data " %>
<%@ Import NameSpace= "System.Data.OleDb " %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN " "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd ">
<html xmlns= "http://www.w3.org/1999/xhtml ">
<head>
<meta http-equiv= "Content-Type " content= "text/html; charset=utf-8 " />
<link media= "all " rel= "stylesheet " type= "text/css " href= "CSS/Style.css " />
<title> Update </title>
<script language= "VB " runat= "server ">
Dim MyConnection As OleDbConnection
Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)
MyConnection = New OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;DATA Source= " & Server.MapPath( "Database/933c1601697f3642.mdb "))
If Not (IsPostBack) Then
BindGrid()
End If
End Sub
Sub MyDataGrid_Edit(ByVal Sender As Object, ByVal E As DataGridCommandEventArgs)
MyDataGrid.EditItemIndex = CInt(E.Item.ItemIndex)
BindGrid()
End Sub
Sub MyDataGrid_Cancel(Sender As Object, E As DataGridCommandEventArgs)
MyDataGrid.EditItemIndex = -1
BindGrid()
End Sub
Sub MyDataGrid_Update(ByVal Sender As Object, ByVal E As DataGridCommandEventArgs)
Dim MyCommand As OleDbCommand
Dim UpdateCmd As String = "UPDATE RecordBase SET RECORD_DATE=@RECORD_DATE, ADDRESS=@ADDRESS, ACTION_CODE=@ACTION_CODE, POLICE_NAME=@POLICE_NAME, CAR_CODE=@CAR_CODE, CAR_TYPE=@CAR_TYPE WHERE (REC_ID = @Id) "
MyCommand = New OleDbCommand(UpdateCmd, MyConnection)
MyCommand.CommandTimeout = 10
MyCommand.Parameters.Add(New OleDbParameter( "@Id ", OleDbType.Integer))
MyCommand.Parameters.Add(New OleDbParameter( "@RECORD_DATE ", OleDbType.Date))
MyCommand.Parameters.Add(New OleDbParameter( "@ADDRESS ", OleDbType.VarChar))
MyCommand.Parameters.Add(New OleDbParameter( "@ACTION_CODE ", OleDbType.VarChar))
MyCommand.Parameters.Add(New OleDbParameter( "@POLICE_NAME ", OleDbType.VarChar))
MyCommand.Parameters.Add(New OleDbParameter( "@CAR_CODE ", OleDbType.VarChar))
MyCommand.Parameters.Add(New OleDbParameter( "@CAR_TYPE ", OleDbType.VarChar))
MyCommand.Parameters( "@Id ").Value = MyDataGrid.DataKeys(CInt(E.Item.ItemIndex))
Dim Cols As String() = { "@Id ", "@RECORD_DATE ", "@ADDRESS ", "@ACTION_CODE ", "@POLICE_NAME ", "@CAR_CODE ", "@CAR_TYPE "}
Dim NumCols As Integer = E.Item.Cells.Count
Dim I As Integer
For I = 2 To NumCols - 1
Dim CurrentTextBox As TextBox
CurrentTextBox = E.Item.Cells(I).Controls(0)
Dim ColValue As String = CurrentTextBox.Text
If I < 6 And ColValue = " " Then
Message.InnerHtml = " <ul> <li> Err : Empty! </li> </ul> "
Message.Style( "color ") = "red "
Return
End If
MyCommand.Parameters.Item(Cols(I - 1)).Value = ColValue
'Response.Write( " <ul> <li> - " & Cols(I - 1).ToString() & " </li> <li> - " & ColValue.ToString() & " </li> </ul> ")
Next
'MyCommand.Parameters.Item( "@Id ").Value = MyDataGrid.DataKeys(CInt(E.Item.ItemIndex))
Try
MyCommand.Connection.Open()
'MyCommand = New OleDbCommand(UpdateCmd, MyConnection)
MyCommand.ExecuteNonQuery()
Message.InnerHtml = " <ul> <li> " & MyCommand.Parameters( "@Id ").Value & " </li> " & _
" <li> " & E.Item.ItemIndex & " Suc! </li> " & _
" <li> " & Cols.ToString() & " </li> <li> " & NumCols.ToString() & " </li> </ul> " & _
" <blockquote> " & UpdateCmd.ToString() & " </blockquote> "
Message.Style( "color ") = "blue "
MyDataGrid.EditItemIndex = -1
Catch Exp As OleDbException
If Exp.ErrorCode = 2627 Then
Message.InnerHtml = " <ul> <li> Sam - " & MyCommand.Parameters( "@Id ").Value & " </li> " & _
" <li> " & E.Item.ItemIndex & " </li> " & _
" <li> " & Cols.ToString() & " </li> <li> " & NumCols.ToString() & " </li> </ul> " & _
" <blockquote> " & Exp.ToString() & " </blockquote> "
Else
Message.InnerHtml = " <ul> <li> Err - " & MyCommand.Parameters( "@Id ").Value & " </li> " & _
" <li> " & E.Item.ItemIndex & " </li> " & _
" <li> " & Cols.ToString() & " </li> <li> " & NumCols.ToString() & " </li> </ul> " & _
" <blockquote> " & Exp.ToString() & " </blockquote> "
End If
Message.Style( "color ") = "red "
Finally
MyCommand.Connection.Close()
End Try
BindGrid()
End Sub
Sub BindGrid()
Dim DS As DataSet
Dim MyCommand As OleDbDataAdapter
MyCommand = New OleDbDataAdapter( "Select Top 10 REC_ID,RECORD_DATE,ADDRESS,ACTION_CODE,POLICE_NAME,CAR_CODE,CAR_TYPE From RecordBase Order By REC_ID Desc ", MyConnection)
DS = New DataSet()
MyCommand.Fill(DS, "Table ")
MyDataGrid.DataSource = DS.Tables( "Table ").DefaultView
MyDataGrid.DataBind()
End Sub
</script>
</head>
<body>
<h5> <a href= "Insert.aspx " title= "CMD " target= "_self "> Insert </a> | <a href= "Update.aspx " title= "CMD " target= "_self "> Update </a> | <a href= "Delete.aspx " title= "CMD " target= "_self "> Delete </a> </h5>
<h5> 更新数据行 </h5>
<p> <span id= "ProInf " EnableViewState= "false " runat= "server "/> </p>
<p> <span id= "Message " EnableViewState= "false " runat= "server "/> </p>
<form runat= "server ">
<ASP:DataGrid id= "MyDataGrid " runat= "server "
Width= "100% "
HeaderStyle-CssClass= "HeadCSS "
AlternatingItemStyle-BackColor= "#F4F4F4 "
OnEditCommand= "MyDataGrid_Edit "
OnCancelCommand= "MyDataGrid_Cancel "
OnUpdateCommand= "MyDataGrid_Update "
DataKeyField= "REC_ID "
>
<Columns>
<asp:EditCommandColumn EditText= "编辑 " CancelText= "取消 " UpdateText= "更新 " ItemStyle-Wrap= "false "/>
</Columns>
</ASP:DataGrid>
</form>
</body>
</html>
[解决办法]
Dim UpdateCmd As String = "UPDATE RecordBase SET RECORD_DATE=@RECORD_DATE, ADDRESS=@ADDRESS, ACTION_CODE=@ACTION_CODE, POLICE_NAME=@POLICE_NAME, CAR_CODE=@CAR_CODE, CAR_TYPE=@CAR_TYPE WHERE (REC_ID = @Id) "
MyCommand = New OleDbCommand(UpdateCmd, MyConnection)
MyCommand.CommandTimeout = 10
MyCommand.Parameters.Add(New OleDbParameter( "@Id ", OleDbType.Integer))
MyCommand.Parameters.Add(New OleDbParameter( "@RECORD_DATE ", OleDbType.Date))
MyCommand.Parameters.Add(New OleDbParameter( "@ADDRESS ", OleDbType.VarChar))
MyCommand.Parameters.Add(New OleDbParameter( "@ACTION_CODE ", OleDbType.VarChar))
MyCommand.Parameters.Add(New OleDbParameter( "@POLICE_NAME ", OleDbType.VarChar))
MyCommand.Parameters.Add(New OleDbParameter( "@CAR_CODE ", OleDbType.VarChar))
MyCommand.Parameters.Add(New OleDbParameter( "@CAR_TYPE ", OleDbType.VarChar))
MyCommand.Parameters( "@Id ").Value = MyDataGrid.DataKeys(CInt(E.Item.ItemIndex))
=========================================================
这里应该有问题,ACCESS数据库的参数是按照顺序来的,所以你的代码应该这样写
Dim UpdateCmd As String = "UPDATE RecordBase SET RECORD_DATE=@RECORD_DATE, ADDRESS=@ADDRESS, ACTION_CODE=@ACTION_CODE, POLICE_NAME=@POLICE_NAME, CAR_CODE=@CAR_CODE, CAR_TYPE=@CAR_TYPE WHERE (REC_ID = @Id) "
MyCommand = New OleDbCommand(UpdateCmd, MyConnection)
MyCommand.CommandTimeout = 10
MyCommand.Parameters.Add(New OleDbParameter( "@RECORD_DATE ", OleDbType.Date))
MyCommand.Parameters.Add(New OleDbParameter( "@ADDRESS ", OleDbType.VarChar))
MyCommand.Parameters.Add(New OleDbParameter( "@ACTION_CODE ", OleDbType.VarChar))
MyCommand.Parameters.Add(New OleDbParameter( "@POLICE_NAME ", OleDbType.VarChar))
MyCommand.Parameters.Add(New OleDbParameter( "@CAR_CODE ", OleDbType.VarChar))
MyCommand.Parameters.Add(New OleDbParameter( "@CAR_TYPE ", OleDbType.VarChar))
MyCommand.Parameters.Add(New OleDbParameter( "@Id ", OleDbType.Integer))
MyCommand.Parameters( "@Id ").Value = MyDataGrid.DataKeys(CInt(E.Item.ItemIndex))