none
Problem using Tableadapters to insert records

    Question

  • I recently was tasked with converting a ADO program over to a SQL Server environment. I think I have it pretty close by I am receiving error when I try to update statement. Error Message : Incorrect syntax near '`'. I assume it has something do with the fact the server has changed. I have searched but cant find a good example of how to code the insert using SQL server.   

           Private Sub Parse_ChargeBack_File(ByVal InFileName As String)
    
                '***********************************
                '**** Define Database Conection 
                '***********************************
                Dim strSource As String = "O2WJBURKE\SQL2008R2"
                Dim Catalog As String = "ImportACS300SQL"
                conString = "Provider=SQLOLEDB;data source=" & strSource & ";initial catalog=" & Catalog & ";integrated security=SSPI"
    
                Dim strMessage As String = ""
                Dim lngTotalACS As Long
                Dim strFileName As String
                Dim totalread As Integer = 0
    
                ' Define table adapters
                Dim dtaHeader As New ImportACS300DataSetTableAdapters.ACS_ChgBck_DataHeaderTableAdapter
                Dim dtaData As New ImportACS300DataSetTableAdapters.ACS_ChgBck_DataTableAdapter
    
                ' Define the default dataset
                Dim ds As New ImportACS300DataSet
    
                dtaHeader.Connection = New OleDb.OleDbConnection(conString)
                dtaData.Connection = New OleDb.OleDbConnection(conString)
    
                '***********************************************
                '****         Import from Text               ***
                '***********************************************
                strFileName = Mid(InFileName, 1 + InStrRev(InFileName, "\"))
    
                Dim reader = File.OpenText(InFileName)
                Dim strread As String = Nothing
                Dim lines As Integer = 0
    
                While (reader.Peek() <> -1)
                    strread = reader.ReadLine()
                    lines = lines + 1
    
                    If Not (Trim(strread) = "") Then 'Ignore blank line
                            Dim dtACS_Date As Date = CDate(Mid(strread, 10, 10))
    
                            Dim dsNewRow As ImportACS300DataSet.ACS_ChgBck_DataHeaderRow
                            dsNewRow = ds.ACS_ChgBck_DataHeader.NewACS_ChgBck_DataHeaderRow
    
                            dsNewRow.FileName = strFileName 'Forgin Key to ACS Data
                            dsNewRow.EntryType = "S"
                            dsNewRow.Accumdate = Now
    
                            ds.ACS_ChgBck_DataHeader.AddACS_ChgBck_DataHeaderRow(dsNewRow)
    
                        ElseIf "D" = Mid(strread, 1, 1) Then
                            totalread = totalread + 1
    
                            Dim dsNewRow As ImportACS300DataSet.ACS_ChgBck_DataRow
                            dsNewRow = ds.ACS_ChgBck_Data.NewACS_ChgBck_DataRow
    
                            dsNewRow.FileName = strFileName 'Forgin Key to ACS Data
                            dsNewRow.Batch_Id = Batch_Id
                            dsNewRow.ChargeBack_Date = CDate(Trim(Mid(strread, 42, 10)))
                            dsNewRow.ShipmentNumber = CStr(Trim(Mid(strread, 53, 10)))
                            dsNewRow.ShipmentDate = CDate(Trim(Mid(strread, 64, 10)))
                            dsNewRow.ChargeBack_Reason = CStr(Trim(Mid(strread, 75, 1)))
                            dsNewRow.ChargeBack_Amount = CDbl(Trim(Mid(strread, 77, 6)))
                            dsNewRow.Product_Code = CStr(Trim(Mid(strread, 84, 6)))
                            dsNewRow.Product_Desc = CStr(Trim(Mid(strread, 92, 45)))
    
                            ds.ACS_ChgBck_Data.AddACS_ChgBck_DataRow(dsNewRow)
    
                        ElseIf "" = Mid(strread, 1, 1) Then
                            'MsgBox("EOF Reached")
                        Else
                            strMessage = "302 - This is not a valid record type"
                        End If
                    End If
    
                End While
    
                Try
                    If totalread <> lngTotalACS Then
                        strMessage = "Canceled: 303 - Import not sussessful Input file counts do not match Header"
                    Else
                        dtaHeader.Update(ds.ACS_ChgBck_DataHeader)
                        dtaData.Update(ds.ACS_ChgBck_Data)
                    End If
                Catch ex As Exception
                    Dim str As String = ex.Message
                    blnStatusGood = False
                    strMessage = "Error Message : " & ex.Message
                End Try
    
    
    Parse_ChargeBack_File_exit:
    
                reader.Close()
                dtaHeader.Connection.Close()
                dtaData.Connection.Close()
                ds.Dispose()
    
                Exit Sub
    
            End Sub


    Tuesday, April 04, 2017 2:23 PM

Answers

  • Thank you for looking at this. The error occurs on the "dtaHeader.Update(ds.ACS_ChgBck_DataHeader)" statement. Since I don't write a Insert statement, I don't know how to check that. I know the Datatableadapter generates a Select,insert,update and delete statements just not sure where how to look at them. 

    You can open the DataSet in the Data Designer, select the TableAdapter and then view the associated SQL statements in the Properties windows. Depending on your version of Visual Studio it will look something like this:


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, April 04, 2017 6:35 PM

All replies

  • Do you know which line of code is generating the exception (error)? The incorrect syntax error would appear to be an issue with your SQL statement or the data it is attempting to update.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, April 04, 2017 2:38 PM
  • Change   ` to '

    Most likely you do a copy and paste and did not get a normal apostrophe but instead a tilde.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, April 04, 2017 2:41 PM
    Moderator
  • Thank you for looking at this. The error occurs on the "dtaHeader.Update(ds.ACS_ChgBck_DataHeader)" statement. Since I don't write a Insert statement, I don't know how to check that. I know the Datatableadapter generates a Select,insert,update and delete statements just not sure where how to look at them. 

    "Change   ` to ' Most likely you do a copy and paste and did not get a normal apostrophe but instead a tilde."

    I looked at the code I don't see a tilde in the code.  

    Perhaps this code is just wrong for a SQL insert records as opposed to a Access database insert. I have search what seems to be hundreds of posts on inserting records using dataTableadapters. Not one has looked like this code. The closest example I have found is below. Either the guy that wrote this code added a bunch of extra code or something. Don't misunderstand this works fine in a program that insert records to the Access Database. Its been in production for a couple of years, just not working here. 

    Dim newCustomersRow As NorthwindDataSet.CustomersRow
    newCustomersRow = NorthwindDataSet1.Customers.NewCustomersRow()
    
    newCustomersRow.CustomerID = "ALFKI"
    newCustomersRow.CompanyName = "Alfreds Futterkiste"
    
    NorthwindDataSet1.Customers.Rows.Add(newCustomersRow)


    Tuesday, April 04, 2017 5:22 PM
  • Thank you for looking at this. The error occurs on the "dtaHeader.Update(ds.ACS_ChgBck_DataHeader)" statement. Since I don't write a Insert statement, I don't know how to check that. I know the Datatableadapter generates a Select,insert,update and delete statements just not sure where how to look at them. 

    You can open the DataSet in the Data Designer, select the TableAdapter and then view the associated SQL statements in the Properties windows. Depending on your version of Visual Studio it will look something like this:


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, April 04, 2017 6:35 PM
  • Thank you Paul, If you can't tell I am really new to this. That reveled a key error. The connection string still points to the Access Database. So the real problem is the statement I believed changed the connection string did not work as expected. Is there a way to set\change the connection string at runtime? This eventually will run via SSIS and I will need to pass it the connection proprieties.

                dtaHeader.Connection = New OleDb.OleDbConnection(conString)
                dtaData.Connection = New OleDb.OleDbConnection(conString)

    Tuesday, April 04, 2017 7:08 PM
  • For TableAdapters changing the connection string at run time can be awkward because Microsoft didn't provide an easy way to do it. But I usually refer people to the below article:

    http://thecodemonk.com/2008/02/18/tableadapter-connection-strings/


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, April 05, 2017 12:46 PM
  • Thanks Paul, as a matter of fact I found a post yesterday where you had referred someone to that site. I downloaded the example but haven't been able to get it to work for me. As this eventually will be will be running in SSIS. In multi server development environment it would be nice to get that working.  
    Wednesday, April 05, 2017 2:55 PM