While there may be other ways to accomplish this task, I am interested in making it work with dynamic SQL. In production, there will be over 20 parameters coming from the vb.net to the SQL, being driven from user input. Then those same variables will be used to actually retrieve the records to a datagrid.
So with a tip of the cap to Rod Serling, I submit this small code and SQL for your consideration from my Twilight Zone:
Public Function totalrecordsbysql(list as arraylist) as integer
dim RetVal as new integer
dim querystring as stringDim cn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("Indiafriend"))
Dim cmd As SqlCommand = New SqlCommand("SimpleDynProfileCount", cn)
cmd.commandtype = commandtype.storedproceduredim mydr as SqlDataReader
cmd.Parameters.add("@.TotalRecords",SqlDbType.int).direction=ParameterDirection.Output
cmd.Parameters.add("@.age",sqldbtype.int).value = 18cn.Open()
try
mydr=cmd.executereader()
catch e as sqlexception
dim err as sqlerror
dim strErrorString as stringfor each err in e.Errors
strErrorString += "SqlError: #" & err.Number.ToString () & vbCRLF + err.Message
trace.write("sqlexception",strErrorString)
Nextfinally
RetVal = cmd.parameters("@.TotalRecords").value
end try
Return RetVal
cn.close()
End Function
Now here is the stored procedure:
CREATE PROCEDURE SimpleDynProfileCount@.age int,
@.TotalRecords int outputAS
Declare @.sql nvarchar(4000),
@.paramlist nvarchar(4000)select @.sql = 'select @.xTotalRecords = count(*) from profile where 1 = 1 '
// RAISERROR(@.sql, 16, 1)
IF @.age > 0
Select @.sql = @.sql + ' AND age > @.xage 'Select @.paramlist = '@.xage int, @.xTotalRecords int output'
Execute sp_executesql @.sql,@.paramlist,@.age,@.xTotalRecords = @.TotalRecords output
select @.TotalRecords
GO
Please note the commented RAISERROR statement. If I uncomment this statement, I will get a return value of 11 records. If I leave it out, I get zero records.
The data is the database should return 11 records, based on the criteria of age > 11Your code works fine for me in Query Analyzer -- with one exception. I needed to change the double forward slashes (//) to double dashes (--) in front of the RAISERROR.
You might also place a SET NOCOUNT ON at the top of your stored procedure. This will suppress the information "xx items selected" messages and will avoid having them returned inadvertently as a resultset.
Terri|||That's what is so strange: I don't receive error messages, just the wrong answer (zero). The double dashes are just artistic license: I didn't remember the comment tag. I just eliminate the line, altogether, in production.|||Try adding SET NOCOUNT ON after your AS at the top of the stored procedure.
Terri|||I have added the statement, but the results are the same. Works fine with the RAISERROR statement, but returns zero without the statement.
I don't want to just leave the RAISERROR statement active. It will come back to haunt me later.
rod|||I have resolved the syntax.
I made a mistake in the vb.net when I used a datareader to retrieve the output value!
Instead of:
mydr=cmd.executereader()
the syntax should be:
cmd.executeNonQuery()
Using this syntax I removed from the Stored Procedure, the RAISERROR statement and the last SELECT @.totalrecords.
After doing all that, everything worked as expected.
-rod
