Hi,

I am new to SQL Server. I am attempting to execute an insert query,
but I keep getting an entire range of errors depending on which way I
choose to do it. Can anybody tell me what is wrong with my code below?

"txtkeyword" is a text box on my form.

Thanks in advance,


Daniel McDonald


Dim mySQL As String
Dim myRecSet As ADODB.Recordset
Dim myConnection As New ADODB.Connection


mySQL = "INSERT INTO tblcustomerfind ( CompanyName )SELECT
tblcustomer.CompanyName"
mySQL = mySQL & " From tblcustomer, tblcustomerfind WHERE
(((tblcustomer.CompanyName) Like '" & txtkeyword & "'%))"

'myConnection.Open "Provider=sqloledb;Data Source=Server;Initial
Catalog=ConSoft;User Id=Administrator;P***word=janis01;Integrated
Security=SSPI;"
myConnection.Open "Provider=MSDASQL;Driver={SQL
Server};Server=Server;Initial Catalog=ConSoft;User
Id=Administrator;P***word=janis01;Integrated Security=SSPI;"

Set myRecSet = myConnection.Execute(mySQL)

Have you checked if you can do a simple SELECT yet? Not sure how the
MSDASQL driver handles an INSERT INTO followed by a SELECT command.

You might also look into your 'Like' verb in the query text. I know that
certain drivers such as MSAccess can't understand that in earlier versions.

Hope this helps.

--
Ananda Chakravarty
www.cvtrack.com
CVTracker for Job Seekers, Net: Work


"Daniel McDonald" <EMAIL REMOVED> wrote in message
news:EMAIL REMOVED...
> Hi,
>
> I am new to SQL Server. I am attempting to execute an insert query,
> but I keep getting an entire range of errors depending on which way I
> choose to do it.
....
> mySQL = mySQL & " From tblcustomer, tblcustomerfind WHERE
> (((tblcustomer.CompanyName) Like '" & txtkeyword & "'%))"
>
> 'myConnection.Open "Provider=sqloledb;Data Source=Server;Initial
> Catalog=ConSoft;User Id=Administrator;P***word=janis01;Integrated
> Security=SSPI;"
> myConnection.Open "Provider=MSDASQL;Driver={SQL
> Server};Server=Server;Initial Catalog=ConSoft;User
> Id=Administrator;P***word=janis01;Integrated Security=SSPI;"
>
> Set myRecSet = myConnection.Execute(mySQL)

Hi Dan,
Couple things. 1) Your "From" and Where clause is wrong. You want
to use a Join statement in the From and in the Where you want to move
the apostrophe to the other side of the % symbol:

mySQL = "INSERT INTO tblcustomerfind ( CompanyName )SELECT
tblcustomer.CompanyName"
mySQL = mySQL & " From tblcustomer INNER JOIN tblcustomerfind ON
tblcustomer.CustID = tblcustomer.CustID WHERE
(((tblcustomer.CompanyName) Like '" & txtkeyword & "%'))"

and 2) You don't want to set a recordset, just execute the command and
don't use parenthesis':
myConnection.Execute mySQL

David

Daniel McDonald <EMAIL REMOVED> wrote in message news:<EMAIL REMOVED>. ..
> Hi,
>
> I am new to SQL Server. I am attempting to execute an insert query,
> but I keep getting an entire range of errors depending on which way I
> choose to do it. Can anybody tell me what is wrong with my code below?
>
> "txtkeyword" is a text box on my form.
>
> Thanks in advance,
>
>
> Daniel McDonald
>
>
> Dim mySQL As String
> Dim myRecSet As ADODB.Recordset
> Dim myConnection As New ADODB.Connection
>
>
> mySQL = "INSERT INTO tblcustomerfind ( CompanyName )SELECT
> tblcustomer.CompanyName"
> mySQL = mySQL & " From tblcustomer, tblcustomerfind WHERE
> (((tblcustomer.CompanyName) Like '" & txtkeyword & "'%))"
>
> 'myConnection.Open "Provider=sqloledb;Data Source=Server;Initial
> Catalog=ConSoft;User Id=Administrator;P***word=janis01;Integrated
> Security=SSPI;"
> myConnection.Open "Provider=MSDASQL;Driver={SQL
> Server};Server=Server;Initial Catalog=ConSoft;User
> Id=Administrator;P***word=janis01;Integrated Security=SSPI;"
>
> Set myRecSet = myConnection.Execute(mySQL)