'CITY' is not a recognized optimizer lock hints option

[bajanpoet]

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.

Below is a code fragment for one of the functions.

CREATE FUNCTION ViewCustomers(@Region varchar(6))
returns @CustTable TABLE (CustomerID varchar(20), [Customer Name] varchar(50))
AS
BEGIN
	IF @Region = 'West' 
	 BEGIN
		INSERT @CustTable
		SELECT rtrim(idcust)[Customer ID], 
                rtrim(namecust)[Customer Name] 
                FROM arcus 
                WHERE idcust IN 
                ('4000229', '4002100', '60000', '41700', '98850', 'T00038',
		'7422', 'S00046', '0092375', '0109550', '94770', '0032250',
		'S00049',	'4001118', 'H06830', 'P00060', '5258', '3655',
		'3745', 'SS1107', '5127', '4001820',	'4000926', 'SS1108',
		'R02430',	'P00048',	'4002171', '4001506', 'ZF0017',
                'E00038',	'J00017',	'R06004',	'4000500', 'ZC0060',  
                'C15093',	'ZS0076')
	 END
	ELSE IF @Region = 'South'
     BEGIN
		INSERT @CustTable
		SELECT rtrim(idcust)[Customer ID], 
                rtrim(namecust)[Customer Name] 
                FROM arcus 
                WHERE idcust IN 
                ('G00088' ,'4000360', '4008005', '4001892', 'D04905',  
                 'ZA0009',   '4000310', 'D04895', 'E00041', 'E00014',  
                 'E00024', '7643',  '4000850', '73500', '4000935',    
                 '4000936', '4001300', '86100',  'P00059', 'P00031', '4665', 
                 '4002043', '4001444', '0098690', '1082', '98900', 
                 'S00051', '4342', 'SS1104', '17800', '110400',  '1094',  
                 '4002041', '0092000', '4470', 'ZI0001', '0025300',  
                 'ZC0100',  'ZF0035', 'R06010')
    END
ELSEIF.....
....
END

When I run the function in SQL Server Query Analyzer, using SELECT * FROM ViewCustomers('City') it works and displays the customerID and name of the customers associated with the region passed to the function as a parameter.

My next step is to 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". I'm trying to populate a datagrid with data in the function's @CustTable.

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

(BTW - the original request was placed in the VB.NET forum because the error was in the r = SQLComm.ExecuteReader() line, but I was advised to try here as well.)

[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)

[bajanpoet]

wow... I didn't see that... you're right! I'll modify it and get back to you... give me a sec...

[bajanpoet]

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 fine

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

[Ramy Mahrous]

Please mark both threads as solved, if it's :)

[bajanpoet]

I will... thanks!

Should I post another question concerning why the datagrid isn't showing up the data from the function, or should I ask it here?

[Ramy Mahrous]

Another thread because we can made a reference to this if anyone has a problem calling UDF in VB.NET

[bajanpoet]

ok thanks .... here we go...

[Ramy Mahrous]

DataGrid and such programming related, should be asked in VB.NET forum, friend.

[bajanpoet]

Yeah I recognize that. Thanks! I was just opening the VB.NET to send it there