Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SqlException (VB .NET) with Access 2000

Posted on 2003-10-30
6
Medium Priority
?
417 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 500 total points
ID: 9656556
I would recommend

SELECT * FROM [Transaction]

as transaction is a keyword within SQL.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

885 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