error msg help needed on stored procedure

[TJS]

trying to get a record count from a stored procedure using a supplied SQL
statement

Error msg:
===========
"The SqlParameterCollection only accepts non-null SqlParameter type
objects,not String objects."

SPROC:
========
CREATE PROCEDURE [dbo].[RecordCount]

@SQLstring varchar(2000)

AS

DECLARE @SQL varchar(2000)
Set @SQL = "Return("+ @SQLstring +")"
EXEC (@SQL)

GO
Calling vb function:
=================

Public Function RecordCount(ByVal vSQL As String) As Integer

' Create Instance of Connection and Command Object
Dim myConnection As New
SqlConnection(ConfigurationSettings.AppSettings("c onnectionString"))
Dim myCommand As New SqlCommand("NCL_RecordCount", myConnection)

' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure

' Add Parameters to SPROC
Dim parameterSQL As New SqlParameter("@SQLstring", SqlDbType.NVarChar,
2000)
parameterSQL.Value = vSQL
myCommand.Parameters.Add(parameterSQL)

'record count from table as OUTPUT
Dim parameterRecordCount As New SqlParameter("ReturnValue",SqlDbType.Int)
myCommand.Parameters.Add("parameterRecordCount")
parameterRecordCount.Direction = ParameterDirection.ReturnValue

' Open the database connection and execute the command
myConnection.Open()
Dim temp as Integer
myCommand.ExecuteNonQuery()
temp = myCommand.Parameters("ReturnValue").Value
myConnection.Close()
return Temp

End Function
vSQL:
========

vSQL = "Select count(*) from tableA where tableA .LName = '"+
<txtValueSupplied> + "'"

[S. Justin Gengo]

TJS,

Maybe I'm missing something, but why are you passing the actual sql
statement into your procedure?

Try this:

CREATE PROCEDURE [dbo.[RecordCount]
@LName varchar(255)
AS
SELECT COUNT(tableA.LName) AS LastNameCount
FROM tableA
WHERE tableA .LName = @LName
RETURN
GO
Dim parameterSQL As New SqlParameter("@SQLstring", SqlDbType.NVarChar, 255)
parameterSQL.Value = "Gengo"
myCommand.Parameters.Add(parameterSQL)
'---Your code might work, but it's not very efficient. However it certainly
won't work as is:
' You've used "ExecuteNonQuery" to execute the command. ExecuteNoneQuery
means that
' nothing will be returned. You want to use "ExecuteScalar" (single item
returned).

Dim temp as Integer
temp = myCommand.ExecuteScalar()

Sincerely,

--
S. Justin Gengo, MCP
Web Developer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche
"TJS" <no****@here.com> wrote in message
news:uZ***************@TK2MSFTNGP11.phx.gbl...

trying to get a record count from a stored procedure using a supplied SQL
statement

Error msg:
===========
"The SqlParameterCollection only accepts non-null SqlParameter type
objects,not String objects."

SPROC:
========
CREATE PROCEDURE [dbo].[RecordCount]

@SQLstring varchar(2000)

AS

DECLARE @SQL varchar(2000)
Set @SQL = "Return("+ @SQLstring +")"
EXEC (@SQL)

GO
Calling vb function:
=================

Public Function RecordCount(ByVal vSQL As String) As Integer

' Create Instance of Connection and Command Object
Dim myConnection As New
SqlConnection(ConfigurationSettings.AppSettings("c onnectionString"))
Dim myCommand As New SqlCommand("NCL_RecordCount", myConnection)

' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure

' Add Parameters to SPROC
Dim parameterSQL As New SqlParameter("@SQLstring", SqlDbType.NVarChar,
2000)
parameterSQL.Value = vSQL
myCommand.Parameters.Add(parameterSQL)

'record count from table as OUTPUT
Dim parameterRecordCount As New SqlParameter("ReturnValue",SqlDbType.Int) myCommand.Parameters.Add("parameterRecordCount")
parameterRecordCount.Direction = ParameterDirection.ReturnValue

' Open the database connection and execute the command
myConnection.Open()
Dim temp as Integer
myCommand.ExecuteNonQuery()
temp = myCommand.Parameters("ReturnValue").Value
myConnection.Close()
return Temp

End Function
vSQL:
========

vSQL = "Select count(*) from tableA where tableA .LName = '"+
<txtValueSupplied> + "'"

[TJS]

trying to make it a generic procedure for getting record count
"S. Justin Gengo" <sjgengo@[Remove_To_Email]aboutfortunate.com> wrote in
message news:eh**************@TK2MSFTNGP11.phx.gbl...

TJS,

Maybe I'm missing something, but why are you passing the actual sql
statement into your procedure?

Try this:

CREATE PROCEDURE [dbo.[RecordCount]
@LName varchar(255)
AS
SELECT COUNT(tableA.LName) AS LastNameCount
FROM tableA
WHERE tableA .LName = @LName
RETURN
GO
Dim parameterSQL As New SqlParameter("@SQLstring", SqlDbType.NVarChar, 255) parameterSQL.Value = "Gengo"
myCommand.Parameters.Add(parameterSQL)
'---Your code might work, but it's not very efficient. However it certainly won't work as is:
' You've used "ExecuteNonQuery" to execute the command. ExecuteNoneQuery means that
' nothing will be returned. You want to use "ExecuteScalar" (single item returned).

Dim temp as Integer
temp = myCommand.ExecuteScalar()

Sincerely,

--
S. Justin Gengo, MCP
Web Developer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche
"TJS" <no****@here.com> wrote in message
news:uZ***************@TK2MSFTNGP11.phx.gbl...

trying to get a record count from a stored procedure using a supplied SQL statement

Error msg:
===========
"The SqlParameterCollection only accepts non-null SqlParameter type
objects,not String objects."

SPROC:
========
CREATE PROCEDURE [dbo].[RecordCount]

@SQLstring varchar(2000)

AS

DECLARE @SQL varchar(2000)
Set @SQL = "Return("+ @SQLstring +")"
EXEC (@SQL)

GO
Calling vb function:
=================

Public Function RecordCount(ByVal vSQL As String) As Integer

' Create Instance of Connection and Command Object
Dim myConnection As New
SqlConnection(ConfigurationSettings.AppSettings("c onnectionString"))
Dim myCommand As New SqlCommand("NCL_RecordCount", myConnection)

' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure

' Add Parameters to SPROC
Dim parameterSQL As New SqlParameter("@SQLstring", SqlDbType.NVarChar, 2000)
parameterSQL.Value = vSQL
myCommand.Parameters.Add(parameterSQL)

'record count from table as OUTPUT
Dim parameterRecordCount As New

SqlParameter("ReturnValue",SqlDbType.Int)

myCommand.Parameters.Add("parameterRecordCount")
parameterRecordCount.Direction = ParameterDirection.ReturnValue

' Open the database connection and execute the command
myConnection.Open()
Dim temp as Integer
myCommand.ExecuteNonQuery()
temp = myCommand.Parameters("ReturnValue").Value
myConnection.Close()
return Temp

End Function
vSQL:
========

vSQL = "Select count(*) from tableA where tableA .LName = '"+
<txtValueSupplied> + "'"

[S. Justin Gengo]

Ahhh, in that case this (and using ExecuteScalar instead of ExecuteNonQuery)
should do the trick:

http://www.devarticles.com/art/1/4/2

Sincerely,

--
S. Justin Gengo, MCP
Web Developer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche
"TJS" <no****@here.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

trying to make it a generic procedure for getting record count
"S. Justin Gengo" <sjgengo@[Remove_To_Email]aboutfortunate.com> wrote in
message news:eh**************@TK2MSFTNGP11.phx.gbl...

TJS,

Maybe I'm missing something, but why are you passing the actual sql
statement into your procedure?

Try this:

CREATE PROCEDURE [dbo.[RecordCount]
@LName varchar(255)
AS
SELECT COUNT(tableA.LName) AS LastNameCount
FROM tableA
WHERE tableA .LName = @LName
RETURN
GO
Dim parameterSQL As New SqlParameter("@SQLstring", SqlDbType.NVarChar,

255)

parameterSQL.Value = "Gengo"
myCommand.Parameters.Add(parameterSQL)
'---Your code might work, but it's not very efficient. However it

certainly

won't work as is:
' You've used "ExecuteNonQuery" to execute the command.

ExecuteNoneQuery

means that
' nothing will be returned. You want to use "ExecuteScalar" (single

item

returned).

Dim temp as Integer
temp = myCommand.ExecuteScalar()

Sincerely,

--
S. Justin Gengo, MCP
Web Developer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche
"TJS" <no****@here.com> wrote in message
news:uZ***************@TK2MSFTNGP11.phx.gbl...

trying to get a record count from a stored procedure using a supplied SQL statement

Error msg:
===========
"The SqlParameterCollection only accepts non-null SqlParameter type
objects,not String objects."

SPROC:
========
CREATE PROCEDURE [dbo].[RecordCount]

@SQLstring varchar(2000)

AS

DECLARE @SQL varchar(2000)
Set @SQL = "Return("+ @SQLstring +")"
EXEC (@SQL)

GO
Calling vb function:
=================

Public Function RecordCount(ByVal vSQL As String) As Integer

' Create Instance of Connection and Command Object
Dim myConnection As New
SqlConnection(ConfigurationSettings.AppSettings("c onnectionString"))
Dim myCommand As New SqlCommand("NCL_RecordCount", myConnection)

' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure

' Add Parameters to SPROC
Dim parameterSQL As New SqlParameter("@SQLstring", SqlDbType.NVarChar, 2000)
parameterSQL.Value = vSQL
myCommand.Parameters.Add(parameterSQL)

'record count from table as OUTPUT
Dim parameterRecordCount As New

SqlParameter("ReturnValue",SqlDbType.Int)

myCommand.Parameters.Add("parameterRecordCount")
parameterRecordCount.Direction = ParameterDirection.ReturnValue

' Open the database connection and execute the command
myConnection.Open()
Dim temp as Integer
myCommand.ExecuteNonQuery()
temp = myCommand.Parameters("ReturnValue").Value
myConnection.Close()
return Temp

End Function
vSQL:
========

vSQL = "Select count(*) from tableA where tableA .LName = '"+
<txtValueSupplied> + "'"

[TJS]

I'm not seeing any difference from that link. I'm also passing in a string
?
"S. Justin Gengo" <sj*****@aboutfortunate.com> wrote in message
news:eo**************@TK2MSFTNGP11.phx.gbl...

Ahhh, in that case this (and using ExecuteScalar instead of ExecuteNonQuery) should do the trick:

http://www.devarticles.com/art/1/4/2

Sincerely,

--
S. Justin Gengo, MCP
Web Developer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche
"TJS" <no****@here.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

trying to make it a generic procedure for getting record count
"S. Justin Gengo" <sjgengo@[Remove_To_Email]aboutfortunate.com> wrote in
message news:eh**************@TK2MSFTNGP11.phx.gbl...

TJS,

Maybe I'm missing something, but why are you passing the actual sql
statement into your procedure?

Try this:

CREATE PROCEDURE [dbo.[RecordCount]
@LName varchar(255)
AS
SELECT COUNT(tableA.LName) AS LastNameCount
FROM tableA
WHERE tableA .LName = @LName
RETURN
GO
Dim parameterSQL As New SqlParameter("@SQLstring", SqlDbType.NVarChar,

255)

parameterSQL.Value = "Gengo"
myCommand.Parameters.Add(parameterSQL)
'---Your code might work, but it's not very efficient. However it

certainly

won't work as is:
' You've used "ExecuteNonQuery" to execute the command.

ExecuteNoneQuery

means that
' nothing will be returned. You want to use "ExecuteScalar" (single

item

returned).

Dim temp as Integer
temp = myCommand.ExecuteScalar()

Sincerely,

--
S. Justin Gengo, MCP
Web Developer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche
"TJS" <no****@here.com> wrote in message
news:uZ***************@TK2MSFTNGP11.phx.gbl...
> trying to get a record count from a stored procedure using a

supplied SQL

> statement
>
> Error msg:
> ===========
> "The SqlParameterCollection only accepts non-null SqlParameter type
> objects,not String objects."
>
> SPROC:
> ========
> CREATE PROCEDURE [dbo].[RecordCount]
>
> @SQLstring varchar(2000)
>
> AS
>
> DECLARE @SQL varchar(2000)
> Set @SQL = "Return("+ @SQLstring +")"
> EXEC (@SQL)
>
> GO
>
>
> Calling vb function:
> =================
>
> Public Function RecordCount(ByVal vSQL As String) As Integer
>
> ' Create Instance of Connection and Command Object
> Dim myConnection As New
> SqlConnection(ConfigurationSettings.AppSettings("c onnectionString"))
> Dim myCommand As New SqlCommand("NCL_RecordCount", myConnection)
>
> ' Mark the Command as a SPROC
> myCommand.CommandType = CommandType.StoredProcedure
>
> ' Add Parameters to SPROC
> Dim parameterSQL As New SqlParameter("@SQLstring",

SqlDbType.NVarChar,

> 2000)
> parameterSQL.Value = vSQL
> myCommand.Parameters.Add(parameterSQL)
>
> 'record count from table as OUTPUT
> Dim parameterRecordCount As New
SqlParameter("ReturnValue",SqlDbType.Int)
> myCommand.Parameters.Add("parameterRecordCount")
> parameterRecordCount.Direction = ParameterDirection.ReturnValue
>
> ' Open the database connection and execute the command
> myConnection.Open()
> Dim temp as Integer
> myCommand.ExecuteNonQuery()
> temp = myCommand.Parameters("ReturnValue").Value
> myConnection.Close()
> return Temp
>
> End Function
>
>
> vSQL:
> ========
>
> vSQL = "Select count(*) from tableA where tableA .LName = '"+
> <txtValueSupplied> + "'"
>
>
>
>
>
>
>
>
>
>
>
>