Solved

SqlException (VB .NET) with Access 2000

Posted on 2003-10-30
6
392 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Flash (http://en.wikipedia.org/wiki/Adobe_Flash) has evolved over the years to where it has become a masterful tool for displaying content screen.  It has excellent layout placement, UI precision as well as rendering capabilities. This, along with t…
Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now