error msg help needed on stored procedure

  • Thread starter Thread starter TJS
  • Start date Start date
T

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("connectionString"))
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,

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
 
trying to make it a generic procedure for getting record count


S. Justin Gengo said:
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 said:
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("connectionString"))
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> + "'"
Click to expand...
Click to expand...
 
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 said:
trying to make it a generic procedure for getting record count


S. Justin Gengo said:
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 said:
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("connectionString"))
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> + "'"
Click to expand...
Click to expand...
Click to expand...
 
I'm not seeing any difference from that link. I'm also passing in a string
?


S. Justin Gengo said:
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 said:
trying to make it a generic procedure for getting record count


S. Justin Gengo said:
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


trying to get a record count from a stored procedure using a
Click to expand...
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("connectionString"))
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> + "'"
Click to expand...
Click to expand...
Click to expand...
 
Back
Top