Use ODBC connection to a sybase database

[asheffer]

I have been working on trying to query data out of a sybase database and I keep getting a syntax error. If I try and change the syntax to match what is used in the ODBC example on this site it returns that the file can not be found.

Does anyone have any Ideas

Thanks
Al

[Bob Hansen]

How about providing the script that you are using, and the changes you have made to match the sample?

[asheffer]

SQLString = SELECT first_name FROM "DBA"."spr_klient" WHERE last_name='Sheffer'
set rsCustomers = MyDB.Execute(SQLString)

This is the select statement. I have tested the syntax with sybase and it works but not from within MS.

Thanks
Al Sheffer

[Bob Hansen]

You provided:

SQLString = SELECT first_name FROM "DBA"."spr_klient" WHERE last_name='Sheffer'

Try putting double quotes around the SQL string, like:

SQLString = "SELECT first_name FROM "DBA"."spr_klient" WHERE last_name='Sheffer'"

Note: This suggestion is untested, just an observation.

Hope it was helpful................good luck,
Bob

[support]

Hi,

You need to format the string correctly with VBScript syntax. Like this:

SQLString = "SELECT first_name FROM DBA.spr_klient WHERE last_name='Sheffer'"

You will need to remove the quotes from around the table name, which shouldn't be necessary anyway, otherwise you'll get a syntax error because double quotes are string delimiters in VBscript.

[asheffer]

I am now getting a error that states VBScript Runtimes Are Not Properly Installed. Do you have any suggestions

Thanks

[support]

Hi,

Odd. The necessary VBScript run times are installed when you install Macro Scheduler. However, if you need to reinstall/install VBScript run times you can get them from:

http://msdn.microsoft.com/library/defau ... webdev.asp

[asheffer]

I am now getting a syntax error that States "Syntax error near '0' Line 11, Column 1

This is the statement I amcurrently using on Line 10 and 11

Line 10=SQLString = SQLString = "SELECT first_name FROM DBA.spr_klient WHERE last_name='Sheffer'"
Line 11=set rsCustomers = MyDB.Execute(SQLString)

Got any Ideas

Thanks

[support]

Hi,

You have SQLString = SQLString = ....

You should have only one SQLString =

Should be something like:

SQLString = "SELECT first_name FROM DBA.spr_klient WHERE last_name='Sheffer'"

Remove the first SQLString =

[asheffer]

Thanks I think that I am losing it.

I am now getting a new error "Item cannot be found in the collection corresponding to the requested name or ordinal.

Line 14, Column 2

Here is the hole script
VBSTART

Function GetCustomerName(MemberName)

Dim SQLString

set MyDB = CreateObject("ADODB.Connection")
MyDB.Open "clubsys"

SQLString = "SELECT first_name FROM DBA.spr_klient WHERE last_name='Sheffer'"
set rsCustomers = MyDB.Execute(SQLString)

If Not rsCustomers.EOF then
GetMemberName = rsCustomers.Fields("MemberName")
Else
GetMemberName = "Not Found"
End if

MyDB.Close

End Function

VBEND

Input>MemberName,Enter Member Last Name:
VBEval>GetCustomerName("%MemberName%"),CustName
MessageModal>The Member Name Is : %CRLF% %CRLF% %MemberName%


Thanks

[support]

It is saying MemberName is not a valid field name.

What are the field names in your sql table?

[asheffer]

I am trying to get "first_name" If I plug that in instead of Membername it runs through but only shows the data that I typed in at the prompt no first name.

Thanks

[support]

Hi,

That's what is supposed to happen according to your code:

Input>MemberName,Enter Member Last Name:
VBEval>GetCustomerName("%MemberName%"),CustName
MessageModal>The Member Name Is : %CRLF% %CRLF% %MemberName%

You are outputting the value the user entered.

Surely you mean:

Input>MemberName,Enter Member Last Name:
VBEval>GetCustomerName("%MemberName%"),CustName
MessageModal>The Member Name Is : %CRLF% %CRLF% %CustName%

Don't you want to output the value returned by the function, not the value the user enters?

[asheffer]

Thatt did it I guess I didnt truly understand the VBEval function now I understand.

Thanks
for all your help your support is great.

[asheffer]

I have got it working but I cant seam to pass the input Variable to the statement in the where clause here is the code. I always get a not found
VBSTART

Function GetMemberName(MemberLName)

Dim SQLString

set MyDB = CreateObject("ADODB.Connection")
MyDB.Open "clubsys"

SQLString = "SELECT first_name FROM DBA.spr_klient WHERE last_name='" & MemberLName & "'"
set rsspr_klient = MyDB.Execute(SQLString)

If Not rsspr_klient.EOF then
GetMemberName = rsspr_klient.Fields("MemberLName")
Else
GetMemberName = "Not Found"
End if

MyDB.Close

End Function

VBEND

Input>MemberLName,Enter Member Last Name:
VBEval>GetMemberName("%MemberName%"),MemberName
MessageModal>The Member Name Is : %CRLF% %CRLF% %MemberName% %MemberLName%

Thanks