Urgent: VB runtime error SQL 3

[RubenV]

Hi everyone,

when I launch my app I get this runtime error:

runtime error '-2147217900 (80040e14)'
ODBC syntax error

This is the select command:
SELECT re.desc, c.Name as Company
FROM Client c, Related_Entries re
WHERE re.Related_From_Id = '" & strID & "'
ORDER BY c.Name;

If I leave re.desc out of it, it's ok.
Yet, the name of the field re.desc is correct.

Does anyone have some clues.
It's very urgent...

here is the code:
dcRltdCompanies.RecordSource = strSearchRltdCompanies
dcRltdCompanies.CursorLocation = adUseClient
dcRltdCompanies.LockType = adLockOptimistic
dcRltdCompanies.Refresh

It happens when doing the .Refresh command.
So it happens when executing the query...

I really hope someone can be of any help to me.

Regards,
Ruben

 

[HarleyQuinn]

It won't be causing the error but you don't anywhere in the statement link the two tables you are using.

Harleyquinn

---------------------------------
For tsunami relief donations

 

[HarleyQuinn]

And the fieldname desc is a reserved word (for descending). That might be your problem.

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[RubenV]

I did link the tables but I didn't include it to make the statement more easy.

This is the full statement:
SELECT re.desc, c.Name as Company, c.Address_Line_1 as Address, c.City as City, c.Zip_Code as ZipCode, c.State_Province as State, c.Country as Country, c.Client_Id, re.Related_To_Number
FROM Client c, Related_Entries re
WHERE re.Related_From_Id = '" & strID & "'
AND (re.Related_To_Id = c.Client_Id)
AND (c.Contact_Number = 0)
ORDER BY c.Name

I believe it might be related to the reserved word 'desc'.
Do you have an idea on how to solve this?
Because this is a table used by a CRM application and I can't rename the tables...

Thx a lot for your time already.

 

[HarleyQuinn]

You could try [re].[desc]

That might solve it for you.

Harleyquinn

---------------------------------
For tsunami relief donations

 

[HarleyQuinn]

But it depends what type of DB your are querying. It won't work for an Oracle DB.

Harleyquinn

---------------------------------
For tsunami relief donations

 

[RubenV]

I'm still having the same problem, even with [re].[desc]

 

[RubenV]

Oh and it's a pervasive database.

 

[strongm]

Pervasive? Use quotes rather than square brackets as a delimiter, i.e

re."desc"

 

[HarleyQuinn]

Enclose the filed name in "'s rather than the ['s

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[HarleyQuinn]

Sorry strongm, I've never used pervasive DB's and have just found the answer via google. Forgot to refresh my page before posting though...

Sorry about that.

Harleyquinn

---------------------------------
For tsunami relief donations

 

[strongm]

No need for apologies.

 

[RubenV]

Sorry,

I think I misunderstand...

do you mean like this:
SELECT re.'desc'
or
SELECT re.'" & "desc" & "'

Neither work... but I probably misunderstand.

Thanks for your patience.

 

[RubenV]

Oh... in case I did get it right, re."desc" does not work as a quote initiates or ends a string.

 

[RubenV]

Sorry, I post way too much but that's because I'm a bit stressed so instead of posting all in one I post several times.

Sorry again.

The reason I can't use double quotes is the following:
Look at my select statement:

strSearchRltdCompanies = "SELECT re.desc as Type, c.Name as Company, c.Address_Line_1 as Address, c.City as City, c.Zip_Code as ZipCode, c.State_Province as State, c.Country as Country, c.Client_Id, re.Related_To_Number " & _
"FROM Client c, Related_Entries re " & _
"WHERE ........... " & _
"AND.............. " & _
...
...

As a double quote would mean that the string would end, or split up, I can't just put a double quote around the reserved word: desc.

Thanks again for your patience.

 

[tsdragon]

Use double double-quotes inside the string (i.e. str = "this string has ""quotes"" inside it.")

Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[RubenV]

Thanks to all of you.

The problem is solved !!!

Really, you all saved pretty much my life

Thx again.

 

[tsdragon]

You're welcome, that's what we're here for.

And that's probably the easiest star I ever got.


Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[RubenV]

Well,

you put your time into it so it's the least I could do

I just found out about the double double quotes when you posted it but anyway, thx a lot for your time.

 

[RubenV]

I now get the same runtime error,

but now even when just doing this:

strQuery = "select * from client;"
dcContacts.RecordSource = strQuery & adOpenDynamic & adLockOptimistic & adAsyncConnect & adAsyncExecute & adAsyncFetchNonBlocking
dcContacts.Refresh
Set dgContacts.DataSource = dcContacts

I don't know why.
My connectionstring is like this but it's correct:
Provider=MSDASQL.1;Password=cheers;Persist Security Info=True;User ID=master;Data Source=DemeLocal;Mode=ReadWrite

I also think that if this was wrong, it would give me a different error.

Hmm, is this another pervasive db issue?
I checked google but I don't find that much.