asked on

SqlException (VB .NET) with Access 2000

(My english skill is bad, so sorry for the quality of the text)

I have about 8 Access table that I join with my DataTable in VB .NET and it works perfectly. But when I want to do the DataSet for the Transaction table an SqlException is trhow :

Private Function monDatasetTransaction() As Boolean
Dim OK As Boolean = True

Try
'Cree un lien entre la table clients et la table du dataset en mémoire
DATransaction = New SqlDataAdapter( _
"SELECT * FROM Transaction", Connection)
CBTransaction = New SqlCommandBuilder(DATransaction)
CBTransaction.GetUpdateCommand()

DATransaction.Fill(DSClubVideo, "Transaction")
DTTransaction = DSClubVideo.Tables("Transaction")

Catch e As SqlException
OK = False
MsgBox(e.ToString) 'The error comes from here....................
Catch e As Exception
OK = False
MsgBox(e.Message, MsgBoxStyle.Critical, "Erreur Général")
End Try
Return OK
End Function

The error: System.Data.SqlClient.SqlException: Incorrect Syntax Near the Keyword 'Transaction'...........................................................

Please help me, because I really don't know what is the problem with this one, all others DataTable works perfectly.

Thanx,
Mr. Tha_Boss

fantasy1001

Try this:
DATransaction = New SqlDataAdapter()
DATransaction.SelectCommand = New SqlCommand("SELECT * FROM Transaction", Connection)
CBTransaction = New SqlCommandBuilder(DATransaction)
DATransaction.UpdateCommand = CBTransaction.GetUpdateCommand()
' open the conn if close

Regards,
~ fantasy ~

mufaddal

Hi
try to debug application and find the exact line no for error.

mufaddal

ASKER CERTIFIED SOLUTION

_ys_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tha_Boss

ASKER

Ok,

here's the complete exception message that I get now:
Error on update... System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'Transaction'.
at System.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Common.DbDataAdapter.Update(DataTable datatable)
at ClubVideo.Transaction.commitChangementsTransaction() in ...

And I'll give you the rest of the code:

Private Sub BtnAjouterLocation_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAjouterLocation.Click

If monDatasetLocation() Then
AjouterLocationTransaction()

Dim Reponse As MsgBoxResult
Reponse = MsgBox("Voulez-vous ajoute", vbOKCancel, "Titre")
Select Case Reponse
Case vbOK
If monDatasetTransaction() Then
Dim Etat As String
Etat = "Location"
AjouterTransaction(Etat)
End If
Case vbCancel
End Select
End If
End Sub

'The code of this function is in my first message above.
Private Function monDatasetTransaction() As Boolean

Private Sub AjouterTransaction(ByVal transEtat As String)
Try
Dim drRows As DataRowCollection
drRows = DTTransaction.Rows
Dim objNewRow = DTTransaction.NewRow()

If transEtat = "Location" Then
objNewRow("NO_TRANSACTION") = txtNoTransaction.Text
objNewRow("DATE_TRANSACTION") = txtDateLocation.Text
objNewRow("DATE_RETOUR") = txtDateRetour.Text
objNewRow("ID_CLIENT") = msktxtIdClient.Text
Dim vraiIdItem As TexteValide = New TexteValide(msktxtIdItemLocation.Text)
objNewRow("ID_ITEM") = vraiIdItem.ToString
objNewRow("DUREE") = cboDuree.Text
End If

drRows.Add(objNewRow)
commitChangementsTransaction()

Catch sql As SqlException
MsgBox("Erreur")
End Try
End Sub

Private Sub commitChangementsTransaction()
Try
DATransaction.Update(DTTransaction)
Catch e As SqlException
MsgBox("qqqqqqqqqqqqqqqqqqqqqqqqqqqqq..." & e.ToString)
Catch e As Exception
MsgBox(e.Message, MsgBoxStyle.Critical, "Erreur Général.")
End Try
End Sub

Thanx all of you,
Tha_Boss

Tha_Boss

ASKER

I modify it like that and it still don't work:

DATransaction = New SqlDataAdapter( _
"SELECT * FROM [Transaction]", Connection)
CBTransaction = New SqlCommandBuilder(DATransaction)
DATransaction.UpdateCommand = CBTransaction.GetUpdateCommand()

DATransaction.Fill(DSClubVideo, "Transaction")
DTTransaction = DSClubVideo.Tables("Transaction")

Tha_Boss

ASKER

Thanx all of you,

I just change the name of my table I replace Transaction by AllTransaction and it works. It was reallly because of the keyword Transaction within SQl.

Thanx all,
Tha_Boss