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
Tha_BossAsked:
Who is Participating?
 
_ys_Commented:
I would recommend

SELECT * FROM [Transaction]

as transaction is a keyword within SQL.
0
 
fantasy1001Commented:
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 ~
0
 
mufaddalCommented:
Hi
try to debug application and find the exact line no for error.

mufaddal
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Tha_BossAuthor Commented:
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
0
 
Tha_BossAuthor Commented:
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")
0
 
Tha_BossAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.