0

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

2
Contributors
10
Replies
11
Views
8 Years
Discussion Span
Last Post by bajanpoet
Featured Replies
  • If you traced the SQL Command sent, you'll find it doesn't surround the state within single quotes [B]'[/B] What you need is to modify it [icode]Dim SQLComm = New SqlCommand("SELECT * FROM ViewCustomers('" & vRegion & "')", conn)[/icode] Read More

1

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)

Votes + Comments
Was a great help... two pairs of eyes are better than one for seeing minute differences in code!
0

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

0

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

0

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?

0

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.