Solved

SqlException (VB .NET) with Access 2000

Posted on 2003-10-30
6
394 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
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:…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

774 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