Update records using datagrids

  • Thread starter mursyidatun ismail
  • Start date
M

mursyidatun ismail

Dear all,

database use: Ms Access.
platform: .Net

i'm trying to update a record/records in a table called t_doctors by
clicking da edit link provided in the database.

when i ran through da browsers and click update it gave me this error:
Specified argument was out of the range of valid values. Parameter name:
index


I'm sorry, but i really couldn't think clearly right now, can someone
please help me out with this?


My updatedoctor.aspx:
(the whole coding)

<%@ Page Language="vb" AutoEventWireup="false"
Codebehind="doctorupdate.aspx.vb" Inherits="Clinic.doctorupdate" %>
<%@ import Namespace="system.data.oledb" %>
<%@ import Namespace="system.data" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>Edit your data</title>
</HEAD>
<body bgColor="#99ccff">
<p><font style="FONT-WEIGHT: 700; FONT-SIZE: 9pt" face="Tahoma">You
are now in the
doctor profile. Please select what you would like to do with the
information.</font></p>
<table cellSpacing="0" cellPadding="0" width="151" border="0">
<tr>
<td width="151" background="images/fon_menu.gif" height="30"><p
class="menu01" align="center"><b><font style="FONT-SIZE: 9pt"
face="Tahoma"><A href="doctoradd.aspx">Add</A></font></b></p>
</td>
</tr>
<tr>
<td width="151" background="images/fon_menu.gif" height="30"><p
class="menu01" align="center"><b><font style="FONT-SIZE: 9pt"
face="Tahoma">Update</font></b></p>
</td>
</tr>
<tr>
<td width="151" background="images/fon_menu.gif" height="30"><p
class="menu01" align="center"><b><font style="FONT-SIZE: 9pt"
face="Tahoma"><A href="doctordelete.aspx">Delete</A></font></b></p>
</td>
</tr>
</table>
<p class="menu01" align="center"><span style="Z-INDEX: 1; LEFT: 10px;
WIDTH: 681px; POSITION: absolute; TOP: 180px; HEIGHT: 3px; mso-ignore:
vglayout"><IMG height="3" src="../doctoradd_files/image001.gif"
width="681" v:shapes="_x0000_s1025"></span></p>
<p class="menu01" align="center"><IMG height="12" alt=""
src="images2/icon01.gif" width="11" align="absMiddle"
border="0">&nbsp;&nbsp;<font style="FONT-WEIGHT: 700; FONT-SIZE: 9pt"
face="Tahoma"><A href="adminhome.html"><font
color="#000000">HOME</font></A></font></p>
<p align="left"><font style="FONT-WEIGHT: 700; FONT-SIZE: 9pt"
face="Tahoma">Please&nbsp;choose
the&nbsp;record of the doctor's data to be updated:</font></p>
<form name="search_form" runat="server" ID="Form1">
<P align="left">
<asp:datagrid id="dgdoctor" runat="server" Font-Names="Tahoma"
Font-Size="Smaller" Font-Name="Tahoma"
autogeneratecolumns="False" DataKeyField="doctor_id"
OnEditCommand="SetEditMode" oncancelcommand="CancelEdit"
OnUpdateCommand="UpdateDatabase">
<ItemStyle Font-Size="Smaller" Font-Names="Tahoma"
BackColor="#99ccFF"></ItemStyle>
<HeaderStyle Font-Bold="True" BackColor="#3300FF"></HeaderStyle>
<Columns>
<asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Update"
CancelText="Cancel" EditText="Edit"></asp:EditCommandColumn>
<asp:BoundColumn DataField="doctor_id" ReadOnly="True"
HeaderText="ID"></asp:BoundColumn>
<asp:BoundColumn DataField="fullname"
HeaderText="Name"></asp:BoundColumn>
<asp:BoundColumn DataField="nric" ReadOnly="True"
HeaderText="NRIC"></asp:BoundColumn>
<asp:BoundColumn DataField="date_of_birth" readonly="True"
HeaderText="Date of Birth"></asp:BoundColumn>
<asp:boundcolumn datafield="address"
HeaderText="Address"></asp:boundcolumn>
<asp:boundcolumn datafield="nationality"
HeaderText="Nationality"></asp:boundcolumn>
<asp:BoundColumn DataField="race" ReadOnly="True"
HeaderText="Race"></asp:BoundColumn>
<asp:BoundColumn DataField="qualification"
HeaderText="Qualification"></asp:BoundColumn>
</Columns>
</asp:datagrid></P>
</form>
</body>
</HTML>
--------------------------------------------------------
updatedoctor.aspx:
(da datagrid part)

<form name="search_form" runat="server" ID="Form1">
<P align="left">
<asp:datagrid id="dgdoctor" runat="server" Font-Names="Tahoma"
Font-Size="Smaller" Font-Name="Tahoma"
autogeneratecolumns="False" DataKeyField="doctor_id"
OnEditCommand="SetEditMode" oncancelcommand="CancelEdit"
OnUpdateCommand="UpdateDatabase">
<ItemStyle Font-Size="Smaller" Font-Names="Tahoma"
BackColor="#99ccFF"></ItemStyle>
<HeaderStyle Font-Bold="True" BackColor="#3300FF"></HeaderStyle>
<Columns>
<asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Update"
CancelText="Cancel" EditText="Edit"></asp:EditCommandColumn>
<asp:BoundColumn DataField="doctor_id" ReadOnly="True"
HeaderText="ID"></asp:BoundColumn>
<asp:BoundColumn DataField="fullname"
HeaderText="Name"></asp:BoundColumn>
<asp:BoundColumn DataField="nric" ReadOnly="True"
HeaderText="NRIC"></asp:BoundColumn>
<asp:BoundColumn DataField="date_of_birth" readonly="True"
HeaderText="Date of Birth"></asp:BoundColumn>
<asp:boundcolumn datafield="address"
HeaderText="Address"></asp:boundcolumn>
<asp:boundcolumn datafield="nationality"
HeaderText="Nationality"></asp:boundcolumn>
<asp:BoundColumn DataField="race" ReadOnly="True"
HeaderText="Race"></asp:BoundColumn>
<asp:BoundColumn DataField="qualification"
HeaderText="Qualification"></asp:BoundColumn>
</Columns>
</asp:datagrid></P>
</form>
---------------------------------------------------------
Enclosed is the code behind for updatedoctor.aspx from page_load
downwards:
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
If Not IsPostBack Then
BindDataGrid()
End If
End Sub

Sub BindDataGrid()
Dim myConn As OleDbConnection
Dim myOleDbAdapter As OleDbDataAdapter
Dim connStr, sqlStr As String
Dim myDataSet As New DataSet

connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " &
Server.MapPath("clinic.mdb")
sqlStr = "SELECT * FROM t_doctors ORDER BY doctor_id"
myConn = New OleDbConnection(connStr)
myConn.Open()
myOleDbAdapter = New OleDbDataAdapter(sqlStr, myConn)
myOleDbAdapter.Fill(myDataSet, "t_doctors")
dgdoctor.DataSource = myDataSet.Tables("t_doctors")
dgdoctor.DataBind()
myConn.Close()
End Sub

Sub SetEditMode(ByVal s As Object, ByVal e As
DataGridCommandEventArgs) Handles dgdoctor.EditCommand
dgdoctor.EditItemIndex = e.Item.ItemIndex
BindDataGrid()
End Sub

Sub CancelEdit(ByVal s As Object, ByVal e As
DataGridCommandEventArgs) Handles dgdoctor.CancelCommand
dgdoctor.EditItemIndex = -1
BindDataGrid()
End Sub

Sub UpdateDatabase(ByVal s As Object, ByVal e As
DataGridCommandEventArgs) Handles dgdoctor.UpdateCommand
Dim myConn As OleDbConnection
Dim connStr, sqlStr, strFullname, strNric, strDob, strAddress,
strNationality, strRace, _
strQualification As String
Dim myUpdateCommand As OleDbCommand
Dim intdoctorId As Integer

'get the key value of the row, here its da record id of the
database clinic.mdb of table t_doctors
intdoctorId = dgdoctor.DataKeys.Item(e.Item.ItemIndex)

'get the new value for the respective fields
strFullname = (CType(e.Item.Cells(1).Controls(0), TextBox)).Text
strNric = (CType(e.Item.Cells(2).Controls(0), TextBox)).Text
strDob = (CType(e.Item.Cells(3).Controls(0), TextBox)).Text
strAddress = (CType(e.Item.Cells(4).Controls(0), TextBox)).Text
strNationality = (CType(e.Item.Cells(5).Controls(0),
TextBox)).Text
strRace = (CType(e.Item.Cells(6).Controls(0), TextBox)).Text
strQualification = (CType(e.Item.Cells(7).Controls(0),
TextBox)).Text

sqlStr = "UPDATE t_doctors SET fullname='" + strFullname + "',
nric='" + strNric + "', date_of_birth = '" + strDob _
+ "', address = '" + strAddress + "', nationality='" +
strNationality + "', race='" + strRace _
+ "', qualification='" + strQualification + "' WHERE
doctor_id=intdoctorId.ToString"

connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " &
Server.MapPath("clinic.mdb")
myConn = New OleDbConnection(connStr)
myConn.Open()
myUpdateCommand = New OleDbCommand(sqlStr, myConn)
myUpdateCommand.ExecuteNonQuery()
myConn.Close()
dgdoctor.EditItemIndex = -1
BindDataGrid()

End Sub

----------------------------------------------------------

This part is da update part only:

Sub UpdateDatabase(ByVal s As Object, ByVal e As
DataGridCommandEventArgs) Handles dgdoctor.UpdateCommand
Dim myConn As OleDbConnection
Dim connStr, sqlStr, strFullname, strNric, strDob, strAddress,
strNationality, strRace, _
strQualification As String
Dim myUpdateCommand As OleDbCommand
Dim intdoctorId As Integer

'get the key value of the row, here its da record id of the
database clinic.mdb of table t_doctors
intdoctorId = dgdoctor.DataKeys.Item(e.Item.ItemIndex)

'get the new value for the respective fields
strFullname = (CType(e.Item.Cells(1).Controls(0), TextBox)).Text
strNric = (CType(e.Item.Cells(2).Controls(0), TextBox)).Text
strDob = (CType(e.Item.Cells(3).Controls(0), TextBox)).Text
strAddress = (CType(e.Item.Cells(4).Controls(0), TextBox)).Text
strNationality = (CType(e.Item.Cells(5).Controls(0),
TextBox)).Text
strRace = (CType(e.Item.Cells(6).Controls(0), TextBox)).Text
strQualification = (CType(e.Item.Cells(7).Controls(0),
TextBox)).Text

sqlStr = "UPDATE t_doctors SET fullname='" + strFullname + "',
nric='" + strNric + "', date_of_birth = '" + strDob _
+ "', address = '" + strAddress + "', nationality='" +
strNationality + "', race='" + strRace _
+ "', qualification='" + strQualification + "' WHERE
doctor_id=intdoctorId.ToString"

connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " &
Server.MapPath("clinic.mdb")
myConn = New OleDbConnection(connStr)
myConn.Open()
myUpdateCommand = New OleDbCommand(sqlStr, myConn)
myUpdateCommand.ExecuteNonQuery()
myConn.Close()
dgdoctor.EditItemIndex = -1
BindDataGrid()

End Sub


******************************************************

can someone please help me figure this out.
I'm really out of time right now... and i just can't think anything.

Your help is greatly appreciated!

Best Regards,
mursyidah


p/s: you can send da solution to my email as above
or just in case its (e-mail address removed)
Thanks again!
 
C

Cor Ligthert

Mursyidatum,

Why don't you make for yourself a simple program to do this with one
keyfield and a normal field.

When it than is not working, we can probably help you.
Now it is more unpaid consultancy work because ou are giving a probably
complete program.

One thing you surely would not do it using the + in VBNet where the & should
be used, althoug mostly it goes well, can that give a lot of problems.

Or even better use the OleDBCommandParameters for the update.

http://msdn.microsoft.com/library/d...dataoledboledbcommandclassparameterstopic.asp

I hope however this helps a little bit.

Cor
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

[Newbie Problem]DataGrid OnUpdateCommand "InvalidCastException" problem 2
DataGrid - Weird Update Command Problem 1
DataGrid ButtonColumn - Please help !!! 4
In-place Updating in Datagrid not working 6
Handling not visible DataGrid objects 2
Text Value in a TextBox inside Datagrid 2
positioning of html and aspx elements 1
Find Control in a nested DataGrid 2

Top