calling an sql2000 stored procedure for one single value from asp.net - NEWBIE

[jason]

Hello - looking for an NEWBIE example/fix if someone could help me.

I've been using the following technique to pull and push sql2000 data
through my asp.net pages. It works fine and is nice and clean.

I know there is a cleaner way (while still using this technique) of
passing a single value back to asp.net without having to return a
dataset. Can someone point me to an example.

Many THANKS for any help or information.


CODE (may not be syntaxtically exact, but the idea has worked). Please
retain this same design of a compiled VB DLL and sql2000 stored
procedure in your solution):


IN THE VB DLL:
================

Public Function nextqna(ByVal currentq As String) As DataSet

Dim mydataseta As DataSet = New DataSet("MyDataSeta")
Dim da As New SqlDataAdapter
Dim con As SqlConnection = New
SqlConnection("server=XXX.XXX.XXX.XXX;uid=sa;pwd=XXXXXX;database=XXX")
con.Open()

Dim cmd As SqlCommand = new sqlcommand("dbo.[nextqna]", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@currentq",SqlDbType.varchar).Value = currentq
da.SelectCommand = cmd
da.Fill(mydataseta)
Return mydataseta

end function


IN THE SQL2000 STORED PROC:
==================

create procedure [nextqna]
@currentq varchar(120)AS
SELECT COUNT(q) from qna where q=@currentq


IN THE ASPX:
===============

Dim yy2 As class2
yy2 = new class2()
Dim mydatasetb2 As DataSet
mydatasetb2= yy2.nextqna("001")

 

[William \(Bill\) Vaughn]

Check out my article on handling OUTPUT parameters from SPs.
http://www.betav.com/msdn_magazine.htm
hth

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[JimM]

Look at the SqlCommand.ExecuteScalar method -- that should get you what you
want...

--- Jim ---

 

[jason]

Look at the SqlCommand.ExecuteScalar method -- that should get you what you

Hi thanks for help. Newbie here... I'd like to keep all the sql code
in the stored procedure. the SP is working fine and returns single
integer out. Only problem I'm having (and it's real newbie) is seting
the single ouput integer out through a compiled vb.net function.

(da is my sqladapter)
(con my connection)

Dim cmd As SqlCommand = new sqlcommand("dbo.[onevar]", con)
cmd.CommandType = CommandType.StoredProcedure
'cmd.execute
'myvar = da.getParameter(0).getValue();
'myvar=da.SqlCommand.ExecuteScalar
'da.SelectCommand = cmd
'da.Fill(mydataseta)
'da.Fill(myvar)
Return myvar

End Function


All the comments are confused and totally blind attempts at ado.net to
plug the integer into myvar. Do I need to set parms for output? Do I
need to execute the sqlcommand in place of the fill I had that was
working with the sp select into a dataset? All brilliant questions I'm
sure.

Thanks.

 

[JimM]

Hi Jason,

In your initial message you indicated your SP was:
-------------
create procedure [nextqna]
@currentq varchar(120)AS
SELECT COUNT(q) from qna where q=@currentq
-------------

If you want your function to return the COUNT(q) value, the simplest way
would be:

Public Function GetNextqna(ByVal currentq As String) As Integer
Dim cm As New SqlCommand("TestCommand", New SqlConnection(ConnectString))
cm.Parameters("@currentq").Value = currentq
Return CInt(cm.ExecuteScalar())
End Function

According to the docs, ExecuteScalar returns the value of the first column
of the first row. Of course you should do some error checking, but it's an
example of a simple lookup function. (I typed this off the top of my
head...haven't tested it, but I've used the technique before)

--- Jim ---

"JimM" <[email protected]> wrote in message

Look at the SqlCommand.ExecuteScalar method -- that should get you what

you

Hi thanks for help. Newbie here... I'd like to keep all the sql code
in the stored procedure. the SP is working fine and returns single
integer out. Only problem I'm having (and it's real newbie) is seting
the single ouput integer out through a compiled vb.net function.

(da is my sqladapter)
(con my connection)

Dim cmd As SqlCommand = new sqlcommand("dbo.[onevar]", con)
cmd.CommandType = CommandType.StoredProcedure
'cmd.execute
'myvar = da.getParameter(0).getValue();
'myvar=da.SqlCommand.ExecuteScalar
'da.SelectCommand = cmd
'da.Fill(mydataseta)
'da.Fill(myvar)
Return myvar

End Function


All the comments are confused and totally blind attempts at ado.net to
plug the integer into myvar. Do I need to set parms for output? Do I
need to execute the sqlcommand in place of the fill I had that was
working with the sp select into a dataset? All brilliant questions I'm
sure.

Thanks.