[RESOLVED] SQL Check!.

[maps]

This is part of my other thread from Updating a table with records in an XML dataset.
Here my problem is, i want to INSERT records with a SELECTION Criteria of all users but not the user logged in.
In the code below, i get an SQL Exception. "missing Semicolon at the end of the SQL Statement".
When i put the semicolon before the SELECT statement, it tells me
"Character Found After the End Of the SQl statement"
Is this the correct SQL statement for this?

Private Sub update_tblPopulatedPlaces_fromXML()
        Dim dsXMLImport As New DataSet
        myConn = New OleDbConnection(strAccessConn)
        Dim strSQL As String = "INSERT INTO tblPopulatedPlaces(AdminBdry1_ID,AdminBdry2_ID,AdminBdry3_ID,AdminBdry4_ID,PPname,uid_Created,date_Created,uid_Modified,date_Modified)" & _
        " VALUES(@AdminBdry1_ID,@AdminBdry2_ID,@AdminBdry3_ID,@AdminBdry4_ID,@PPname,@uid_Created,@date_Created,@uid_Modified,@date_Modified)" & _
        " SELECT * FROM tblPopulatedPlaces WHERE([uid_Created] <> '" & user.userName.ToString & "')"
        Dim da As New OleDbDataAdapter()
        da.AcceptChangesDuringFill = False
        da.InsertCommand = New OleDbCommand(strSQL, myConn)
        Try
            myConn.Open()
            dsXMLImport.ReadXml(Me.tbFilePath.Text)
            With da.InsertCommand.Parameters
                For i As Integer = 0 To dsXMLImport.Tables(1).Rows.Count - 1
                    drPublic = dsXMLImport.Tables(1).Rows(i)
                    .AddWithValue("@AdminBdry1_ID", drPublic(1))
                    .AddWithValue("@AdminBdry2_ID", drPublic(2))
                    .AddWithValue("@AdminBdry3_ID", drPublic(3))
                    .AddWithValue("@AdminBdry4_ID", drPublic(4))
                    .AddWithValue("@PPname", drPublic(5))
                    .AddWithValue("@uid_Created", drPublic(6))
                    .AddWithValue("@date_Created", drPublic(7))
                    .AddWithValue("@uid_Modified", drPublic(8))
                    .AddWithValue("@date_Modified", drPublic(9))
                Next i
            End With
            da.Update(dsXMLImport, dsXMLImport.Tables(1).TableName)
        Catch exSQL As OleDbException
            ShowErrorBoxOK("update_tblPopulatedPlaces_fromXML():", exSQL.Message, My.Resources.myStrings.capSQLError)
        Catch ex As Exception
            ShowErrorBoxOK("update_tblPopulatedPlaces_fromXML():", ex.Message, My.Resources.myStrings.capGeneralError)
        Finally
            myConn.Close()
            myConn.Dispose()
            da.Dispose()
        End Try
    End Sub

[josep]

Hi,

you must put the semicolon after the select statment (in red)

Dim strSQL As String = "INSERT INTO tblPopulatedPlaces(AdminBdry1_ID,AdminBdry2_ID,AdminBdry3_ID,AdminBdry4_ID,PPname,uid_Created,date_C  reated,uid_Modified,date_Modified)" & _
        " VALUES(@AdminBdry1_ID,@AdminBdry2_ID,@AdminBdry3_ID,@AdminBdry4_ID,@PPname,@uid_Created,@date_Create  d,@uid_Modified,@date_Modified)" & _
        " SELECT * FROM tblPopulatedPlaces WHERE([uid_Created] <> '" & user.userName.ToString & "')[COLOR=Red];[/COLOR]"

Hope this helps

[maps]

I get the same Error message.

Dim strSQL As String = "INSERT INTO tblPopulatedPlaces(AdminBdry1_ID,AdminBdry2_ID,AdminBdry3_ID,AdminBdry4_ID,PPname,uid_Created,date_Created,uid_Modified,date_Modified)" & _
        " VALUES(@AdminBdry1_ID,@AdminBdry2_ID,@AdminBdry3_ID,@AdminBdry4_ID,@PPname,@uid_Created,@date_Created,@uid_Modified,@date_Modified);" & _
        " SELECT * FROM tblPopulatedPlaces WHERE([uid_Created] <> '" & user.userName.ToString & "');"

[josep]

Did you tried without the semicolon after the insert statment, a I typed or with it as you typed, or both?

[maps]

Yes i did.
when i have this below, i get the exception
"Missing Semiclon at the end of SQL statement"

Dim strSQL As String = "INSERT INTO tblPopulatedPlaces(AdminBdry1_ID,AdminBdry2_ID,AdminBdry3_ID,AdminBdry4_ID,PPname,uid_Created,date_Created,uid_Modified,date_Modified)" & _
        " VALUES(@AdminBdry1_ID,@AdminBdry2_ID,@AdminBdry3_ID,@AdminBdry4_ID,@PPname,@uid_Created,@date_Created,@uid_Modified,@date_Modified)" & _
        " SELECT * FROM tblPopulatedPlaces WHERE([uid_Created] <> '" & user.userName.ToString & "');"

When i dont have any semicolon anywhere, i get the same error!

When i have two semicolons, i get the Exception
"Characters Found After the End Of the SQl statement"

[josep]

You can try to add a carriage return between both queries,

Dim strSQL As String = "INSERT INTO tblPopulatedPlaces(AdminBdry1_ID,AdminBdry2_ID,AdminBdry3_ID,AdminBdry4_ID,PPname,uid_Created,date_C  reated,uid_Modified,date_Modified)" & _
        " VALUES(@AdminBdry1_ID,@AdminBdry2_ID,@AdminBdry3_ID,@AdminBdry4_ID,@PPname,@uid_Created,@date_Create  d,@uid_Modified,@date_Modified);" & cbcrlf & _
        "SELECT * FROM tblPopulatedPlaces WHERE([uid_Created] <> '" & user.userName.ToString & "');"

if it did not work you can launch it separately; first the insert statment probably using the method executenonquery and after that launch the select

[maps]

It has Jammed. Let me try launching them separately.