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?
VB Code:
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




Reply With Quote