Solved

SqlException (VB .NET) with Access 2000

Posted on 2003-10-30
6
396 Views
Last Modified: 2011-10-03
(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
0
Comment
Question by:Tha_Boss
6 Comments
 
LVL 5

Expert Comment

by:fantasy1001
ID: 9655785
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
 
LVL 2

Expert Comment

by:mufaddal
ID: 9656125
Hi
try to debug application and find the exact line no for error.

mufaddal
0
 
LVL 9

Accepted Solution

by:
_ys_ earned 125 total points
ID: 9656556
I would recommend

SELECT * FROM [Transaction]

as transaction is a keyword within SQL.
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

Author Comment

by:Tha_Boss
ID: 9657983
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
 

Author Comment

by:Tha_Boss
ID: 9658002
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
 

Author Comment

by:Tha_Boss
ID: 9659536
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Upgrading my SSIS package in VS 2012 6 62
SSIS Feed MS SQL Server Stored procedure with loop 4 38
Where is this file? 3 26
IDE for Python 5 27
A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question