SQL query from VB delivers no result

[sagn]

This is my dilemma:

I have the following: query

Private rs As ADODB.Recordset
Dim sql As String

sql = "Select * from database.dbo.sysobjects where
id = object_id(N'database.dbo.tablename')"

Set rs = sqlexec (dbc, sql)

where sqlexec is a function designed to carry out the query.
This function is tried and true (I didn't write it)

Anyway, with the above query rs is SOMETHING
But if I add
" and objectproperty(id,N'IsUserTable')=1"

Then rs comes back as NOTHING

I have even cut and pasted the query sent to sqlexec
and used it in Query analyzer and it works,
that is I get one row back...

Any thoughts?

 

[SkipVought]

Hi,

Looks to me like database.dbo.tablename is a variable and NOT a literal.

If that's the case...

sql = "Select * from database.dbo.sysobjects where
id = object_id(N'" & database.dbo.tablename & "')"

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[sagn]

Yes, it is a variable in the code...

but in my example I put in what the value of the variable would be, in hopes of simplifying.
The same way I use the term 'database' to denote the name of the database, when in reality I have no database
named database

So, I'm afraid my problem remains

diane

 

[SkipVought]

You need to post your CODE and NOT the results that you expect.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[sagn]

I'm sorry...

I thought if I actually took the results that my code actually produced, (not what I happened to expect my code WOULD do) that would be ok.

By the way I get no SQL errors when I run the query.

Private Function getDataBaseName() As String
getDataBaseName = "DataEntry"
End Function

Private Function getTableName() As String
Dim user As String
user = getUserLoginName()
getTableName = getDataBaseName() + "." + user + ".EMPTABLE_" + user
End Function
Public Function TableExists(dbc As ADODB.Connection) As Boolean
Dim Rows As Long
Dim sql As String
Dim OK As Boolean
sql = sqlTableExists()
Set rs = sqlexec(dbc, sql, True, log, False, True, Rows)
If dbc.errors.count Then
MsgBox sqlerrstr(dbc, sql, True), _
vbCritical+vbMsgBoxSetForeground, "SQL Query"
End If
If (rs Is Nothing) Then
OK = False
Else
OK = True
End If
TableExists = OK
End Function
Private Function sqlTableExists() As String

Dim sql As String

sql = "select 'TRUE' from " + getDataBaseName() + ".dbo.sysobjects "
sql = sql + "where id = object_id(N'" + getTableName() + "')"
sql = sql + " and objectproperty(id,N'IsUserTable')=1"

sqlTableExists = sql
End Function

 

[SkipVought]

What is IsUserTable? Is this a LITERAL?

Mebe it should be...

    Sql = "select 'TRUE' from " + getDataBaseName() + ".dbo.sysobjects "
    Sql = Sql + "where id = object_id(N'" + getTableName() + "')"
    Sql = Sql + " and objectproperty(id,N'" & IsUserTable & "')=1"

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244