"Greg Gailer" <g_gailer@[EMAIL PROTECTED]
> wrote in message
news:12kjkbjp6q4ne30@[EMAIL PROTECTED]
> Dodgy wrote:
>> On Fri, 27 Oct 2006 13:13:50 +1000, Greg Gailer
>> <g_gailer@[EMAIL PROTECTED]
> waffled on about something:
>>
>>> Dodgy wrote:
>>>> On Thu, 26 Oct 2006 19:56:48 +1000, Greg Gailer
>>>> <g_gailer@[EMAIL PROTECTED]
> waffled on about something:
>>>>
>>>>> Dodgy wrote:
>>>>>> On Thu, 26 Oct 2006 16:44:52 +1000, Greg Gailer
>>>>>> <g_gailer@[EMAIL PROTECTED]
> waffled on about something:
>>>>>>
>>>>>>> Greetings all,
>>>>>>> I have a MySQL database running on a Linux server and wanting to
>>>>>>> access it from Windows. I have the database connection all working
>>>>>>> correctly. I am using the tables and the query from the BDE
>>>>>>> components and can edit the database perfectly through the tables
>>>>>>> but am having no success in the code for the query. The query
>>>>>>> component has a basic query that works well in the component
itself.
>>>>>>>
>>>>>>> select * from Service
>>>>>>> order by CustNum,Invoice;
>>>>>>>
>>>>>>> In code, I execute another query to isolate just one person as
>>>>>>> follows:
>>>>>>>
>>>>>>> with Query do begin
>>>>>>> if Active then Close;
>>>>>>> if Prepared then Unprepare;
>>>>>>> SQL.Clear;
>>>>>>> SQL.Add('select * from Service');
>>>>>>> SQL.Add('where CustNum=9');
>>>>>>> SQL.Add('order by Invoice;');
>>>>>>> Prepare;
>>>>>>> Open;
>>>>>>> end;
>>>>>>>
>>>>>>> I was hoping to get all the records for customer number 9 from
this
>>>>>>> query but I get the records of all customers ordered as before.
This
>>>>>>> seems to suggest that the query is not working at all. I have used
>>>>>>> this sort of query before for local DBase files with no problems.
>>>>>>> What am I doing wrong now? Can anyone please help?
>>>>>>> Greg
>>>>>>> g_gailer@[EMAIL PROTECTED]
>>>>>> I must confess to never having used the Tstring methods of the SQL
>>>>>> property, and as your query seems to be acting as if only the first
>>>>>> line is being executed, that's the first thing that comes to my
>>>>>> attention.
>>>>>>
>>>>>> Have you tried...
>>>>>>
>>>>>> with Query do begin
>>>>>> if Active then Close;
>>>>>> SQL.text:='select * from Service '
>>>>>> +'where CustNum=9 '
>>>>>> +'order by Invoice;');
>>>>>> Open;
>>>>>> end;
>>>>>>
>>>>>> Dodgy.
>>>>> Thanks for the reply,
>>>>> I tried what you suggested and it still doesn't work. It should, as
>>>>> far as I know, send the query when I close and then open the query
but
>>>>> it doesn't seem to be doing it from the code. It does , however, do
it
>>>>> from the actual component, so I must be missing something in my
code.
>>>>> I am very confused!
>>>>>
>>>>> Greg
>>>> You don't say what query component you are using.
>>>>
>>>> Oh hang on, I just saw an acronym I missed earlier.
>>>>
>>>> You said "BDE"
>>>>
>>>> I should have run away screaming at that point...
>>>>
>>>> Dump it... Loose it... It never caused me anything but trouble.
>>>>
>>>> Use standard TADOConnection and TADOQuery (Came in D5 and later I
>>>> believe).
>>>>
>>>> TADOConnection will happily connect to the MySQL ODBC connector.
(IIRC
>>>> Just don't give it an initial catalogue).
>>>>
>>>> Dodgy.
>>> Well, that was a good thought. I changed the query as you said. I
>>> dropped a TADOConnection, got it to connect, used a TADOQuery,
>>> DatasetProvider, ClientDataset and then a DataSource on the form.
>>> Connected them all together and checked that my basic SQL was working
>>> again and then tried the code again. Still no success I'm afraid. Is
>>> there anything else that can stop the query from accessing the
database?
>>> It seems like the basic component SQL statement is working but the
code
>>> isn't. There must be something disabling the query.
>>> Greg
>>
>> Did you see pr's comment about you having a semi-colon on the end of
>> your sql query? I didn't even see it at first, so I even ended up with
>> it pasted into my code!
>>
>> Try
>> SQL.text:='select * from Service '
>> +'where CustNum=9 '
>> +'order by Invoice');
>>
>> Dodgy.
>
> I have tried it with and without the semicolon but neither works. I have
> tried both SQL.Text and SQL.Add. I have tried BDE, dbExpress, dbGo
> components and the trial version of mySQLDAC and they all do the same
> thing. It looks to me now like nothing can access the database correctly
> so I will try in Visual Basic and see how that goes. Thanks for your
help
> and your time.
I you have tried all of these then it is unlikely that VB will make it
work.
You need to look at some of the more esoteric things:
-Are field and table names case sensitive? This may depend on the tool
used.
-Field types, maybe the values are cast and then don't work properly.
-Maybe you are working on the wrong query, the wrong datamodule?
-Maybe you reassign datasources in code?
-What events are involved when opening the query?
-Filters?
etc.
PR
|
