November 27, 2007 at 7:49 am
hi i need a help
i have a procedure which return a string
i had call this procedure in vb.net but not able to fetch that return string.
can anyone tell me how to return a string in procedure and how to access that string in vb.net
My procedure is
create procedure pro_funcall
(@latitude nvarchar(20),
@longitude nvarchar(20),
@state nvarchar(20) output)
as
begin
set @state= select state from tbl_state where lat=@latitude and long = @longitude
end
in vb.net the calling statements are:
cn = New SqlConnection("Data Source=SPEED\SQLEXPRESS;Initial catalog=project;Integrated Security=True")
ds = New DataSet
cn.Open()
Dim cmd As New SqlClient.SqlCommand("pro_funcall ", cn)
Dim para As New SqlClient.SqlParameter
para = cmd.Parameters.Add("@lat", SqlDbType.VarChar)
Dim para1 As New SqlClient.SqlParameter
para1 = cmd.Parameters.Add("@longi", SqlDbType.VarChar)
para.Value = lat
para1.Value = longi
Dim para2 As New SqlClient.SqlParameter
para2.Value = cmd.Parameters.Add("@state", SqlDbType.VarChar)
para.Direction = ParameterDirection.Output
Dim dr As SqlClient.SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
Debug.WriteLine(para2.Value)
dr.Close()
plz help me!!!!!!!!!!
November 27, 2007 at 10:53 am
Looks to me like you're almost there, although you have a few problems.
First, the "ExecuteReader" call isn't necessary. Your stored proc is not returning a result set, you're using an output parameter. So instead of "ExecuteReader", try using "ExecuteNonQuery" instead.
Second, assuming the execution is successful, simply read the value of the output parameter (assign Para2.Value into a string). It look slike you're trying to do that, but your code has a bug ("para.Direction = ParameterDirection.Output" should be "para2.Direction = ParameterDirection.Output"
I'm not a VB.NET expert (I use C#) but I think your final code should look something more like this:
[font="Courier New"]Dim cn As SqlConnection
cn = New SqlConnection("Data Source=SPEED\SQLEXPRESS;Initial catalog=project;Integrated Security=True")
cn.Open()
Dim cmd As New SqlClient.SqlCommand("pro_funcall", cn)
Dim para As New SqlClient.SqlParameter
para = cmd.Parameters.Add("@lat", SqlDbType.VarChar)
para.Value = lat
Dim para1 As New SqlClient.SqlParameter
para1 = cmd.Parameters.Add("@longi", SqlDbType.VarChar)
para1.Value = longi
Dim para2 As New SqlClient.SqlParameter
para2 = cmd.Parameters.Add("@state", SqlDbType.VarChar)
para2.Direction = ParameterDirection.Output
Dim affectedRows As Long = cmd.ExecuteNonQuery(CommandBehavior.CloseConnection)
Debug.WriteLine(para2.Value)[/font]
November 27, 2007 at 11:15 am
This is what I would do...
Execute the stored procedure with known input parameters and verify the expected output value manually in Query Analyzer or similar.
Unless I am missing something, "para.Direction" should be "para2.Direction"
It is possible that the output value is NULL which is why you don't see anything in the debug output.
As a side note; consider changing the lat/lon values to decimal datatypes. NVARCHAR() is rather wasteful in a scenario where unicode is not actually being used. Additionally, any calculation (e.g. distance between two points, does point exist within a specified area) will be easier with the conversion of what I am assuming are Degrees Minutes Seconds values into decimal Degrees (e.g. 48° 15' 23" = 48.25639 degrees).
HTH,
Art
November 29, 2007 at 6:59 am
Thanks a lot for your help 🙂
But it shows an error at:
Dim affectedRows As Long = cmd.ExecuteNonQuery(CommandBehavior.CloseConnection)
The error is executenonquery cannot accept this argument.
November 29, 2007 at 7:46 am
Yeah, that would probably be because ExecuteNonQuery doesn't take any parameters, sorry (you can double-check that by checking on-line help).
So take out the "CommandBehavior.CloseConnection", and then be sure to add a new line to close your database connection (add a cn.Close() line), so that you properly close your database connection (or wrap your database stuff in a try/catch/finally and put the database close in the finally section -- not sure but I think you can do that in VB about the same as you can in c#).
November 30, 2007 at 12:14 am
Hi,
I think you need to insert the following lines as well
Dim tables As New DataTable("inuse")
dim adap as new sqldataadapter
*comm is sqlcommand
comm.Connection = con
comm.CommandType = CommandType.StoredProcedure
comm.CommandText = "NameOfYourSP"
adap.SelectCommand = comm
adap.Fill(tables)
Regards,
Avaneesh.
November 30, 2007 at 9:05 am
A SqlDataAdapter isn't needed if all you want is to get back the output parameter value, but specifying the command type is definitely a good idea. I bit the bullet and wrote some code that actually works, here it is (in VB.NET 2.0):
[font="Courier New"]Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim lat As String
Dim longi As String
lat = "E"
longi = "A"
Dim cn As SqlConnection
cn = New SqlConnection("[your database connection string here]")
cn.Open()
Dim cmd As New SqlCommand("pro_funcall", cn)
cmd.CommandType = CommandType.StoredProcedure
Dim para As New SqlParameter
para = cmd.Parameters.Add("@lat", SqlDbType.VarChar, 20)
para.Value = lat
Dim para1 As New SqlParameter
para1 = cmd.Parameters.Add("@longi", SqlDbType.VarChar, 20)
para1.Value = longi
Dim para2 As New SqlParameter
para2 = cmd.Parameters.Add("@state", SqlDbType.VarChar, 20)
para2.Direction = ParameterDirection.Output
Dim affectedRows As Long = cmd.ExecuteNonQuery()
Debug.WriteLine(para2.Value)
End Sub
End Module[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply