By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,491 Members | 3,132 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,491 IT Pros & Developers. It's quick & easy.

Update records using datagrids

P: n/a
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 mu***************@yahoo.com.sg
Thanks again!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 21 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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/de...eterstopic.asp

I hope however this helps a little bit.

Cor
Nov 21 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.