Update records using datagrids

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">
You
are now in the
doctor profile. Please select what you would like to do with the
information.
<table cellSpacing="0" cellPadding="0" width="151" border="0">
<tr>
<td width="151" background="images/fon_menu.gif" height="30"><A href="doctoradd.aspx">Add</A>
</td>
</tr>
<tr>
<td width="151" background="images/fon_menu.gif" height="30">Update
</td>
</tr>
<tr>
<td width="151" background="images/fon_menu.gif" height="30"><A href="doctordelete.aspx">Delete</A>
</td>
</tr>
</table>
<IMG height="3" src="../doctoradd_files/image001.gif"
width="681" v:shapes="_x0000_s1025">
<IMG height="12" alt=""
src="images2/icon01.gif" width="11" align="absMiddle"
border="0">  <A href="adminhome.html">HOME</A>
Please choose
the record of the doctor's data to be updated:
<form name="search_form" runat="server" ID="Form1">

<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>
</form>
</body>
</HTML>
--------------------------------------------------------
updatedoctor.aspx:
(da datagrid part)

<form name="search_form" runat="server" ID="Form1">

<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>
</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

Subscribe Post Reply

1952

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/de...eterstopic.asp

I hope however this helps a little bit.

Cor

Nov 21 '05 #2

by: Reply via newsgroup | last post by:

Folks, When performing an update in mysql (using PHP), can I find out how many records were matched? mysql_affected_rows() won't work... and I have the following problem that I thought I...

PHP

How to do an update on existing records?

by: Google Mike | last post by:

I have one table of new records (tableA) that may already exist in tableB. I want to insert these records into tableB with insert if they don't already exist, or update any existing ones with new...

Microsoft SQL Server

Have a better UPDATE statement than what I wrote?

by: serge | last post by:

/* This is a long post. You can paste the whole message in the SQL Query Analyzer. I have a scenario where there are records with values pointing to wrong records and I need to fix them using an...

Microsoft SQL Server

Bulk Insert / Update / Delete

by: Philip Boonzaaier | last post by:

I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums...

PostgreSQL Database

question on copying records between 2 datagrids

by: TM | last post by:

I have two datagrids in my application where one grid is for the master item list and the second grid is for a shopping list. I would like to click on a button and copy items from the master list...

Visual Basic .NET

Help - Problem Getting DataAdapter to update

by: Siv | last post by:

Hi, I seem to be having a problem with a DataAdapter against an Access database. My app deletes 3 records runs a da.update(dt) where dt is a data.Datatable. I then proceed to update a list to...

Visual Basic .NET

Using arrays to take records from a html table and update database

by: Ian Davies | last post by:

Hello Needing help with a suitable solution. I have extracted records into a table under three columns 'category', 'comment' and share (the category column also holds the index no of the record...

PHP

duplicates query help & strategy for update queries with SetWarnings = False

by: ARC | last post by:

Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for...

Microsoft Access / VBA

how to update record based on checkbox selection

by: mukeshrasm | last post by:

Hi I want to update records in database based on selected records using checkbox. if user does not select a record and clicks update button it should show the message please select record to be...

PHP

Speedup your spring application

by: linyimin | last post by:

Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...

General

Development of Funny H5 Game - Toy Claw

by: kcodez | last post by:

As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...

HTML / CSS

Access Europe meeting on Wed 6 Sept - Database Analyzer and other tools

by: isladogs | last post by:

The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...

Microsoft Access / VBA

How to insert a new record in a database

by: Taofi | last post by:

I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...

DB2 Database

Python code for finding the longest common subsequence

by: Rina0 | last post by:

I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...

Python

To verify entry of a dropdown

by: DJRhino | last post by:

Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...

Microsoft Access / VBA

React to native button blinking effect

by: lllomh | last post by:

Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{

Software Development

How does React native implement an English player?

by: lllomh | last post by:

How does React native implement an English player?

Mobile Development

How to calculate date of retirement from date of birth

by: Mushico | last post by:

How to calculate date of retirement from date of birth

Data Management

Update records using datagrids

Similar topics