executenonquery returns null

[JB]

Hello Everyone,
I am at my wits end with this problem, and any help would be
appreciated. I have a web app which contains a keywords textbox that
the user enters terms into. I am trying to validate that the terms
entered are in a thesaurus I have stored in a SQLServer 2000 database.
What I have done is gather the terms entered in the textbox into an
array. I split the array and use a procedure to check each term
against the thesaurus database. My problem is he stored
procedure is executed, and it works for the first term I check (i.e.
the first element in the array). I get the correct data back. It's the
second term, third term, foruth term, etc. that returns a null
value.that I've checked and the new term is going into the input
parameter each time.
Any ideas????

The procedure in SQLServer 2000:

CREATE PROCEDURE thes_check
@STHES varchar(50),
@RESULT varchar(50) OUTPUT
AS
set nocount on
SELECT @RESULT = (select distinct term FROM term
WHERE term = @STHES)
GO


The VB.NET code:

Dim i As String = txtSubject.Text
Dim a() As String
Dim j As Integer

Dim subject As String

i.ToLower()
i.Trim()
a = i.Split(",")

For j = 0 To a.GetUpperBound(0)
'Response.Write(a(1))
'create the connection for a datareader
Dim strconn As String = "blahblahblah"
Dim cnSqlServer As New
SqlClient.SqlConnection(strconn)
Dim cmdSqlServer As New SqlClient.SqlCommand()
Dim prmSQLPar As SqlClient.SqlParameter

Dim thesterm As String

With cmdSqlServer
.Connection = cnSqlServer
.CommandText = "thes_check"
.CommandType = CommandType.StoredProcedure
End With

prmSQLPar = cmdSqlServer.Parameters.Add("@STHES",
a(j))
prmSQLPar.Direction = ParameterDirection.Input
prmSQLPar.SqlDbType = SqlDbType.VarChar

prmSQLPar = cmdSqlServer.Parameters.Add("@RESULT",
Nothing)
prmSQLPar.Direction = ParameterDirection.Output
prmSQLPar.SqlDbType = SqlDbType.VarChar
prmSQLPar.Size = 250

cnSqlServer.Open()
cmdSqlServer.ExecuteNonQuery()

cmdSqlServer.Cancel()
cnSqlServer.Close()


Response.Write("Input: " &
CheckNull(cmdSqlServer.Parameters("@STHES").Value))
Response.Write("Output: " &
CheckNull(cmdSqlServer.Parameters("@RESULT").Value))

Next

 

[David Browne]

Hello Everyone,
I am at my wits end with this problem, and any help would be
appreciated. I have a web app which contains a keywords textbox that
the user enters terms into. I am trying to validate that the terms
entered are in a thesaurus I have stored in a SQLServer 2000 database.
What I have done is gather the terms entered in the textbox into an
array. I split the array and use a procedure to check each term
against the thesaurus database. My problem is he stored
procedure is executed, and it works for the first term I check (i.e.
the first element in the array). I get the correct data back. It's the
second term, third term, foruth term, etc. that returns a null
value.that I've checked and the new term is going into the input
parameter each time.
Any ideas????

The procedure in SQLServer 2000:

CREATE PROCEDURE thes_check
@STHES varchar(50),
@RESULT varchar(50) OUTPUT
AS
set nocount on
SELECT @RESULT = (select distinct term FROM term
WHERE term = @STHES)
GO


The VB.NET code:

Dim i As String = txtSubject.Text
Dim a() As String
Dim j As Integer

Dim subject As String

i.ToLower()
i.Trim()

'strings are immutable
'shoule be
i = i.ToLower()
i = i.Trim()

a = i.Split(",")

For j = 0 To a.GetUpperBound(0)
'Response.Write(a(1))
'create the connection for a datareader
Dim strconn As String = "blahblahblah"
Dim cnSqlServer As New
SqlClient.SqlConnection(strconn)
Dim cmdSqlServer As New SqlClient.SqlCommand()
Dim prmSQLPar As SqlClient.SqlParameter

Dim thesterm As String

With cmdSqlServer
.Connection = cnSqlServer
.CommandText = "thes_check"
.CommandType = CommandType.StoredProcedure
End With

prmSQLPar = cmdSqlServer.Parameters.Add("@STHES",
a(j))

"a, b, c" > {"a"," b", " c"}
Probably you have a loose space in a(j)
try to send a(j).Trim()


If this doesn't work, turn on the sql profiler and see what you're sending
to the db.

David