Desperately require help with Visual Basic / SQL Server 2000

[Daniel McDonald]

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.Connectio n


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

'myConnection.O pen "Provider=sqlol edb;Data Source=Server;I nitial
Catalog=ConSoft ;User Id=Administrato r;Password=jani s01;Integrated
Security=SSPI;"
myConnection.Op en "Provider=MSDAS QL;Driver={SQL
Server};Server= Server;Initial Catalog=ConSoft ;User
Id=Administrato r;Password=jani s01;Integrated Security=SSPI;"

Set myRecSet = myConnection.Ex ecute(mySQL)

[Ananda C.]

Re: Desperately require help with Visual Basic / SQL Server 2000

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

https://www.cvtrack.com

CVTracker for Job Seekers, Net: Work


"Daniel McDonald" <mcdonald.danie l@bigpond.com> wrote in message
news:6a7lsvk5nb lpphvbq21dkonac h4q76ju4m@4ax.c om...[color=blue]
> 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.[/color]
....[color=blue]
> mySQL = mySQL & " From tblcustomer, tblcustomerfind WHERE
> (((tblcustomer. CompanyName) Like '" & txtkeyword & "'%))"
>
> 'myConnection.O pen "Provider=sqlol edb;Data Source=Server;I nitial
> Catalog=ConSoft ;User Id=Administrato r;Password=jani s01;Integrated
> Security=SSPI;"
> myConnection.Op en "Provider=MSDAS QL;Driver={SQL
> Server};Server= Server;Initial Catalog=ConSoft ;User
> Id=Administrato r;Password=jani s01;Integrated Security=SSPI;"
>
> Set myRecSet = myConnection.Ex ecute(mySQL)[/color]

[David]

Re: Desperately require help with Visual Basic / SQL Server 2000

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.Com panyName"
mySQL = mySQL & " From tblcustomer INNER JOIN tblcustomerfind ON
tblcustomer.Cus tID = tblcustomer.Cus tID 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.Ex ecute mySQL

David

Daniel McDonald <mcdonald.danie l@bigpond.com> wrote in message news:<6a7lsvk5n blpphvbq21dkona ch4q76ju4m@4ax. com>...[color=blue]
> 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.Connectio n
>
>
> mySQL = "INSERT INTO tblcustomerfind ( CompanyName )SELECT
> tblcustomer.Com panyName"
> mySQL = mySQL & " From tblcustomer, tblcustomerfind WHERE
> (((tblcustomer. CompanyName) Like '" & txtkeyword & "'%))"
>
> 'myConnection.O pen "Provider=sqlol edb;Data Source=Server;I nitial
> Catalog=ConSoft ;User Id=Administrato r;Password=jani s01;Integrated
> Security=SSPI;"
> myConnection.Op en "Provider=MSDAS QL;Driver={SQL
> Server};Server= Server;Initial Catalog=ConSoft ;User
> Id=Administrato r;Password=jani s01;Integrated Security=SSPI;"
>
> Set myRecSet = myConnection.Ex ecute(mySQL)[/color]