Using MS SQL Function in VB.Net 2003

[Alex]

Hi...

I'm rather new to VB.Net, so please forgive the elementary question. I'm
trying to use an MS SQL 2005 Function from within my VB.Net 2003 code, and
as simple as might be, I'm unable to find any snippet of code that shows me
how to do this.

The function is this 'RetrieveGroupID(@UserID)' and if for example someone
uses 'RetrieveGroupID(44)' it will return the GroupID for User Number 44.

This application is using web forms, and the UserID is stored in the
session, so I have this already. My problem is I'm not sure how to actually
run the Function then store the output as a variable.

Can someone point me to examples or a how-to showing this?

Thanks --

Alex

 

[Nick Chan]

Hi alex, i do not know the 'expert' way
i call the function using "SELECT dbo.RetrieveGroupID(44)"
im sure u know the way to retrieve Selects

 

[Alex]

Hi Nick,

Thanks for the reply, but my question was exactly what you assume I already
know... I'm new to Visual Basic coding, and though I have a number of books
and resources at hand, often times a simple problem like this eludes me.

What I'm looking for is a snippet or example of code showing how to execute
a SQL command in a class, then store the output in a variable. In this
instance I am using a function which returns a single integer, but you're
correct in staying the same snippet would work with any SQL statement which
only returned one value. This is the missing piece I need... I don't know
how to do this, and any assistance from you or anyone else would be much
appriciated.

Thanks again, and take care --

Alex

 

[Mark Rae [MVP]]

Thanks for the reply, but my question was exactly what you assume I
already know... I'm new to Visual Basic coding, and though I have a
number of books and resources at hand, often times a simple problem like
this eludes me.

What I'm looking for is a snippet or example of code showing how to
execute a SQL command in a class, then store the output in a variable. In
this instance I am using a function which returns a single integer, but
you're correct in staying the same snippet would work with any SQL
statement which only returned one value. This is the missing piece I
need... I don't know how to do this, and any assistance from you or anyone
else would be much appriciated.

How is your ASP.NET app currently configured to interface with the
database...?

 

[Alex]

How is your ASP.NET app currently configured to interface with the
database...?

Hi Mark,

The application is a monolithic program written in VB.Net 2003 by a coder
who's no longer here, and I'm coming in on his coat tails to maintain the
code, learning VB along the way. To the best I can determine the
application creates a session with all the general information, like the
UserID, UserName, Groups, and so forth. It then uses datasets, setup both
in the code and via the IDE, to retrieve information from the MS SQL 2005
database.

What I need now is to edit a class so it pulls in a value from an MS SQL
2005 function and stores this value in a variable to use with in the class.
Here's what I've pieced together thus far:

Private Function GetManagerID() As Integer
Dim cn As New
SqlClient.SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("MyConnectionString"))
Dim cm As New SqlClient.SqlCommand("select dbo.ManagerFunction(" +
Me.UserID + ")", cn)
cm.ExecuteNonQuery()
End Function

But how do I get the value from dbo.ManagerFunction(#) into a variable to
use within my application after this function is called? Also note,
MyConnectionString is already defined in the application and stores our
connection information.

Thanks ..

Alex

 

[Mark Rae [MVP]]

The application is a monolithic program written in VB.Net 2003 by a coder
who's no longer here, and I'm coming in on his coat tails to maintain the
code, learning VB along the way. To the best I can determine the
application creates a session with all the general information, like the
UserID, UserName, Groups, and so forth. It then uses datasets, setup both
in the code and via the IDE, to retrieve information from the MS SQL 2005
database.

I've been there, and you have my sympathy...

Change your Function as follows:

' cm.ExecuteNonQuery()
cm.Connection.Open()
Dim ReturnValue As Integer = cm.ExecuteScalar()

I should point out that the above may not be 100% correct as I never go
anywhere near VB.NET, but I think that's more or less it...