Error: 'CITY' is not a recognized optimizer lock hints option

[bajanpoet]

Hi guys!
I have created two table valued multi-statement functions using SQL Server 2000 and am now trying to develop a VB.NET front end application where I run the functions and pass string parameters to them.

When I run the function in SQL Server Query Analyzer, it works, but when I run the function through VB.NET using this code

Private Sub ViewCustomers()
        conn.Open()
        Dim SQLComm = New SqlCommand("SELECT * FROM ViewCustomers(" & vRegion & ")", conn)
        Dim r As SqlDataReader()
        r = SQLComm.ExecuteReader()
        dgCustomers.DataSource = r
        conn.Close()
    End Sub

So when I'm trying to run the solution to test the code, the error pointer points to the " r = SQLComm.ExecuteReader()" line and I get the error ''CITY' is not a recognized optimizer lock hints option.

'CITY' is one of the strings that would be assigned to the variable vRegion.

How do I get rid of this Optimizer lock hints option error? :icon_confused:

[rapture]

Maybe the others will have more info

1. Please use code tags when supplying code (it's easier to read)
2. Appears to be a SQL problem - try searching "is not a recognized optimizer lock hints option" and there is a ton out there

[bajanpoet]

Sorry about the code tags... I tried using them, but I guess they didn't come out as I expected....

I'll do some more digging on this error. Thanks.

[rapture]

let us know if you get stuck, you might also try the SQL thread.

If you fix it let us know that as well and mark the thread as solved when you do get it.

to do code tags in the future its [] with code in the middle at the start and /code in the middle at the end

[bajanpoet]

Thanks. I'll check out the SQL thread too. Thanks a lot

[bajanpoet]

Thanks. I'll check out the SQL thread too. Thanks a lot

Could someone help me find where to place the SQL question? I don't see a specific SQL forum...

[Ramy Mahrous]

If you traced the SQL Command sent, you'll find it doesn't surround the state within single quotes '
What you need is to modify it Dim SQLComm = New SqlCommand("SELECT * FROM ViewCustomers('" & vRegion & "')", conn)

[Salem]

Asked here - http://www.daniweb.com/forums/thread171525.html

[bajanpoet]

Thanks, Salem...
I was encouraged to ask it in the SQL forum... didn't remember to update this thread. Sorry!!! *embarrassed*

[rapture]

Ramy's code fixed the problem?

[bajanpoet]

sorta... now I got another one. The original problem is fixed, but now I'm getting another... here's a quote...

I seem to have gotten past that problem... now it says

InvalidCastException
Unable to cast object of type 'System.Data.SqlClient.SqlDataReader' to type 'System.Data.SqlClient.SqlDataReader[]'.'

looking at the same ExecuteReader command r = SQLComm.ExecuteReader()

[Ramy Mahrous]

It works my friend, I just put the answer here too because it's also related to VB.NET

Dim r As SqlClient.SqlDataReader
r = SQLComm.ExecuteReader()
dgCustomers.DataSource = r

[bajanpoet]

It works!

Thanks!

But why???? lol I already have the Imports System.Data.SqlClient at the top of my code... so isn't using the Dim r As SqlClient.SqlDataReader redundant?

I'll mark it as solved now, but I'm asking for future reference

[Ramy Mahrous]

As you like, I don't import SqlClient in my code :) so I used fullname

[bajanpoet]

cool. So, I'll send this request on VB.NET.

Thanks again.