Calling a Sql Server function in VB.NET 2005

I have a function in my SQL Server database called GetCfgValue. It receives a string parameter and returns a string value. I want to call that function from within a VB.NET 2005 app. How do I call that function from inside my application

Answer this question

Calling a Sql Server function in VB.NET 2005

Confused999
Hi there, I'm new to SQL Server and I'm trying to learn about functions. Could you please post your function. I cant figure out how to use my function. It also receives a string as input and returns a string. But I get the following error:

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: Parameter 1: '@Out' of type: String, the property Size has an invalid size: 0

Robert Burger

use a SQLConnection object
SQLCommand Object and 2
SQLParameters objects

Dim MySQLCommand as new SQLCommand("GetCfgValue", MySQLConn)
MYSQLCommand.CommandType = CommandType.StoredProcedure
Dim MyPara1 as New SQLParameter
MyParam1.ParameterName ="@ME"
MyParam1.Direction = ParameterDirection.Input
MyParam1.SqlDbType=SqlDbType.VarChar
MyParam1.Value = "TEST"
MyParam2.ParameterName ="@Out"
MyParam2.Direction = ParameterDirection.output
MyParam2.SqlDbType=SqlDbType.VarChar

MySQLCommand.Parameters.Add(MyParam1)
MySQLCommand.Parameters.Add(MyParam2)

Recordseffected = MySQLCommand.ExecuteNonQuery
Dim MyReturnVal as String = MyParam2.value

Raidx
I'm going with your code (DMan1) because when you look at the underlying code that VS writes for the function, it is virtually the same. I did change one line (MyParam2.Direction = ParameterDirection.ReturnValue). It didn't seem to like it when I had .output as the paramterdirection. Anyway, thanks for the help. It works great. I'm still curious as to why I can't get the "new" version to work, but I'll worry about that some other time.
Sjako
I guess I should have been more specific. That's the "old" way. I was looking for some clues about the new VS 2005 way. I have a QueriesTableAdapter of which my function is a member. I'm able to preview the function in the Data Designer and it returns the correct result there. However, my implementation in code must somehow be flawed.

Dim Clientname As String = Me.QueriesTableAdapter1.GetCfgValue("clientname").ToString

I would expect this to return a string value. Instead it returns nothing.

I'll use the "old" way if necessary, but I'd prefer to take advantage of the new Data Designer and let VS do most of the work.
TACOBOB
Hi Tjam,

I tried with ur above mentioned code.
But in Visual Studio 2005, i cud not get the "Add option" for Sql Command. There is only AddRange Option and AddwithValue.

Plz Help.
Adremoshier
Hmmmm. That's very strange. 'Add' appears as the first method in the list that displays through intellisense. What version of VS 2005 are you using I'm using the July CTP (8.0.50630.0). It seems unlikely, but perhaps there is a bug in an earlier beta. Otherwise, I don't know what to tell you.
vinaya

Function GetUserName(ByVal _ConnectionString As String, ByVal UserID As Integer) As String

   Dim conn As SqlConnection = New SqlConnection
   conn.ConnectionString = _ConnectionString
   conn.Open()
   Dim MySQLCommand As New SqlCommand("fTS_GetUserNameByUserID", conn)
   MySQLCommand.CommandType = CommandType.StoredProcedure
   Dim MyParam1 As New SqlParameter
   MyParam1.ParameterName = "@UserID"
   MyParam1.Direction = ParameterDirection.Input
   MyParam1.SqlDbType = SqlDbType.VarChar
   MyParam1.Value = UserID
   Dim MyParam2 As New SqlParameter
   MyParam2.ParameterName = "@Out"
   MyParam2.Direction = ParameterDirection.ReturnValue
   MyParam2.SqlDbType = SqlDbType.VarChar

   MySQLCommand.Parameters.Add(MyParam1)
   MySQLCommand.Parameters.Add(MyParam2)

   Dim Recordsaffected As Integer = MySQLCommand.ExecuteNonQuery
   Dim MyReturnVal As String = MyParam2.Value
   conn.Close()

Return MyReturnVal

End Function
Calling a Sql Server function in VB.NET 2005

Answer this question