Working with an Access database in ADO.NET (using ASP.NET)
Dim strSQL As String
Dim strSearchFor As String
Dim arrWords As String()
Dim arrWord As String
Dim RecordCount As Integer
Dim dcJMS As New OleDb.OleDbCommand
strSearchFor = Request("txtClient")
arrWords = (strSearchFor.Split(" "))
'cnJMS.Open()
'dcJMS.CommandText = "DELETE * FROM ClientSearch WHERE UserID=" & Session("UserID")
'dcJMS.ExecuteScalar()
'cnJMS.Close()
'RecordCount = dcJMS.ExecuteNonQuery()
For Each arrWord In arrWords
dcJMS.CommandText = "INSERT INTO ClientSearch SELECT CLID As ClientID, '" & arrWord & "' AS Word, '" & strSearchFor & "' AS SearchedFor, " & Session("UserID") & " AS UserID FROM tblClientData WHERE Company like '*" & arrWord & "*'"
dcJMS.Connection = cnJMS
cnJMS.Open()
dcJMS.ExecuteNonQuery()
dcJMS.Connection.Close()
Next
now this runs and doesn't cause any exceptions and appears to have worked fine but nothing gets inserted into the table.
This is some example data captured in my Autos
dcJMS.CommandText "INSERT INTO ClientSearch SELECT CLID As ClientID, 'test' AS Word, 'this is a test' AS SearchedFor, 1 AS UserID FROM tblClientData WHERE Company like '*test*'" String
arrWord "test" String
strSearchFor "this is a test" String
If I run "SELECT CLID As ClientID, 'test' AS Word, 'this is a test' AS SearchedFor, 1 AS UserID FROM tblClientData WHERE Company like '*test*'" in a test query inside M$ Access it displays 11 records, why won't the INSERT INTO <> SELECT <> work
Your help is muchly apprecaited
Start Free Trial
