executenonquery error

[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 that it works for the
first term I check (i.e. the first element in the array) but doesnt
for the second, third, etc. term. I've checked and the new term is
going into the input parameter each time, but beyond that...nothing.

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

 

[JB]

Thanks for getting back to me. And that's exactly right. The stored
procedure is executed, and for the first term, I get the correct data
back. It's the second term, third term, foruth term, etc. that returns
a null value. Since it works for you, I wonder if maybe it could be
something on my SQLServer that needs to be tweaked (grasping
straws....)?

 

[sheril1981]

http://vb.net-informations.com/dataadapter/dataAdapter-dataset.htm

thanks.