update datarow problem

P

Pascal

I try to update a datarow as you can see below but it does'nt work and I
don't know why !
Public Sub EnregistrerResultats()
' On vide le dataset (Est-ce utile?)
MonDataSet.Clear()
'on paramètre la chaîne de connexion pour la base de donnée
Connection.ConnectionString = "provider=microsoft.jet.oledb.4.0;" &
"data source= " & _
Application.StartupPath & "\" & "calcul_mental.mdb;"
'On rempli le DataSet:
MonAdapter.Fill(MonDataSet, "Resultats")
'Connection.Close()
Dim dt As New DataTable()
dt = MonDataSet.Tables("Resultats")
'dt.DefaultView.RowFilter = String.Format("Nom='{0}' AND
Prenom='{1}' AND Ddn='{2}'", MonNOM, MonPrenom, MaDdn)

Dim drCurrent As DataRow()
Dim strExpr As String
strExpr = "N_Eleve = '" & NumEleve & "'"
drCurrent = dt.Select(strExpr)
'ajouter les infos : notes, temps etc;
Public DatePassage As Date = Now 'Date à laquelle l'exercice fut fait
drCurrent("Date") = DatePassage 'Error here conversion of date
impossible into system.data.datarow

'Connection.Close()

End Sub

thanks for your help
p ascal
 
R

Rich P

Hi Pascal,

Here is a code sample you can try out. Create a new application for
testing it out. In the form -- add a datagridview control. Add the
Imports Statements at the top of the Form1 class module.

Imports System.Data
Imports System.Data.OleDb

Then after the Form1 Class declaration add the dim statement before

Dim da As OleDbDataAdapter, conn, conn2 As OleDbConnection, ds As
DataSet

--and here is the code sample - will look something like this:

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Imports System.Data
Imports System.Data.OleDb

Public Class Form1

Dim da As OleDbDataAdapter, conn, conn2 As OleDbConnection, ds As
DataSet

Private Sub Form1_Load(...) Handles MyBase.Load

conn.ConnectionString = "provider=microsoft.jet.oledb.4.0; Data Source =
C:\testFolder\dbTest.mdb;Jet OLEDB:database Locking Mode=0;Mode=Share
Deny None"
da = New OleDbDataAdapter
ds = New DataSet
da.SelectCommand = New OleDbCommand
da.SelectCommand.Connection = conn
da.SelectCommand.CommandType = CommandType.Text
da.SelectCommand.CommandText = "Select * from Table1"
da.Fill(ds, "tbl1")
datagridview1.DataSource = ds.Tables(0)

End Sub
'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Create a test mdb and add a table and fill it with test data. Note:
this will work for Access97-2003. For Access 2007 the connection string
is little bit different

conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False;"

HTH

Rich
 
C

Cor Ligthert[MVP]

Pascal,

I don't see anything that looks like an update. (I assume that you mean with
that update from the database)

The easiest way is to use a commandbuilder and an update but probably it is
better that you answer the question from Armin first.

Cor
 
P

Pascal

My first message was too laconic, and I apologize. I will try to be more
precise and especially to use the correct vocabulary.
The purpose of the program is to keep track of the results of my students
when they use my mental calculation software (called Mathadore:
http://www.scalpa.info/new_logiciels.php). So I created a database,
"calcul_mental.mdb" with Access which here is a screenshot
(http://www.scalpa.info/mabase.jpg). I created a dataset from the database
in "VB2008 express" called calcul_mentalDataSet. (I followed the explanation
described in section C. (Here is the translation of this page :
http://translate.google.fr/translate?u=http% 3A% 2F%
2Fplasserre.developpez.com% 2Fv6-6.htm & sl tl = en & hl = en & = en & ie =
UTF-8).
1° When the program starts, the child must either identify (
frmListEleves.vb) or register (frmNewEleve.vb) if this is his first visit.
2° The student, once identified or registered, can choose an activity.
3° I want to keep track of these activities in the table "Resultats". So I
store in this table all the informations regarding the student
authenticated.
4° This is where my trouble begins ... For each activity in "Mathadore"
(Frm_decomposer.vb, Frm_encadrer.vb, etc.). I must record the results
obtained by the student in the table "Resultats".
a) I find it hard to retrieve, by programming, "DataRow" which
corresponds to the student currently authenticated. I tried with a dt.find,
but without success (probably because of the structure of my database that
is not well thought at the primary keys?)
So I tried to find the DataRow in the table "Results" which corresponds to
the current student like this:
Dim drCurrent As DataRow()
Dim strExpr As String
strExpr = "N_Eleve = '" & NumEleve & "'"
drCurrent = dt.Select(strExpr)
Click to expand...
without being sure it works, because then I could not change the content of
the Table "Results". An error occur here in the code:
'Add infos : notes, temps etc;
drCurrent("Date") = DatePassage ' Error 1 A value of type 'Date'
can not be converted into 'System.Data.DataRow'
Click to expand...

I hope this help to understand my goal and my problem.

Thanks to all the people spending their time on this.... "newbee-mess"
Merry Xmas
Pascal

Below the code of the module:
###########################
Imports System.Data

Module ModStats
Public NumEleve As Integer 'n° de l'élève
Public MonNOM As String 'Nom de l'élève
Public MonPrenom As String 'Prénom de l'élève
Public MaDdn As Date 'Date de naissance de l'élève
Public MonExo As String 'Titre de l'exercice comme dans la barre de
titre
Public MonTemps As Date 'Durée passée à faire l'exercice
Public Debut As Date
Public Fin As Date
Public MaNote As Single 'Note sur 20 obtenue
Public MaFrequence As Short 'Nombre de fois où l'exercice fut tenté.
Public DatePassage As Date = Now 'Date à laquelle l'exercice fut fait
Public Consigne As String = "" 'Consigne de l'exercice

'http://plasserre.developpez.com/v6-5.htm
'Déclaration de la connexion
Public Connection As New OleDb.OleDbConnection
'Déclaration du DataSet
Public MonDataSet As New DataSet
'Déclaration du dataAdapter ("Resultats" est la table access que j'ai
crée dans le fichier mabase.mdb)
Public MonAdapter As New OleDb.OleDbDataAdapter("select * from
Resultats", Connection)

Public Sub EnregistrerResultats()
' On vide le dataset (Is it necessary?)
MonDataSet.Clear()
'on paramètre la chaîne de connexion pour la base de donnée
Connection.ConnectionString = "provider=microsoft.jet.oledb.4.0;" &
"data source= " & _
Application.StartupPath & "\" & "calcul_mental.mdb;"
'On rempli le DataSet:
MonAdapter.Fill(MonDataSet, "Resultats")
'Connection.Close()
Dim dt As New DataTable()
dt = MonDataSet.Tables("Resultats")
'dt.DefaultView.RowFilter = String.Format("Nom='{0}' AND
Prenom='{1}' AND Ddn='{2}'", MonNOM, MonPrenom, MaDdn)
Dim drCurrent As DataRow()
Dim strExpr As String
strExpr = "N_Eleve = '" & NumEleve & "'"
drCurrent = dt.Select(strExpr)
'ajouter les infos : notes, temps etc;
'drCurrent("Date") = DatePassage

'Connection.Close()

End Sub




Public Function FirstLetterToUpper(ByRef str As String)
str = UCase(Microsoft.VisualBasic.Left(str, 1)) & Mid(str, 2)
Return str
End Function


End Module
 
A

Armin Zingler

Pascal said:
Dim drCurrent As DataRow()
Click to expand...
Dim strExpr As String
strExpr = "N_Eleve = '" & NumEleve & "'"
drCurrent = dt.Select(strExpr)
'ajouter les infos : notes, temps etc;
'drCurrent("Date") = DatePassage
Click to expand...

drCurrent is an array of DataRows. Which of the DataRows do you want to
change? If there can be only one DataRow in the array due to the filter
expression, you must write

drCurrent(0)("Date") = DatePassage


BTW, it's strongly recommended to switch Option Strict On.


Armin
 
P

Pascal

Yes you did it, thanks. That makes me discover another problem.... I
imagined when the update comes on the table Eleves, the field N_Eleve in
Eleves table and in Resultats table are updated simultaneously
(automatically)? Isn't it true ?
I discovered that there weren't numbers in N_Eleve field of the Resultats
table as expected !
I simulated 5 inscriptions in the first Wndows and discovered that only
Eleves tables was updated!
Do i have to change the code to update all the tables which are containing
the same kind of data?

For example in frmNewEleve :
Private Sub UpdateDatabase()
Try
Me.ElevesTableAdapter.Update(Me.Calcul_mentalDataSet.Eleves)
Catch ex As Exception
MessageBox.Show(ex.Message, "Liste des éleves mise à jour",
MessageBoxButtons.OK, MessageBoxIcon.Stop)
End Try
End Sub

should be

Private Sub UpdateDatabase()
Try
Me.ElevesTableAdapter.Update(Me.Calcul_mentalDataSet.Eleves)
Me.ResultatsTableAdapter.Update(Me.Calcul_mentalDataSet.Resultats)

Catch ex As Exception
MessageBox.Show(ex.Message, "Liste des éleves mise à jour",
MessageBoxButtons.OK, MessageBoxIcon.Stop)
End Try
End Sub

Thanks
pascal

--
 
Top