Returning a value from a stored proc

D

David Lozzi

Here is the proc:

CREATE PROCEDURE [dbo].[sp_CheckNetName]
@CID as int,
@Netname as nvarchar(25),
@Return as int OUTPUT
AS


IF EXISTS (SELECT DISTINCT netname FROM computers WHERE CompanyID = @CID AND UPPER(netname) = UPPER(@Netname))
SELECT @Return = 1
ELSE
SELECT @Return = 0
GO

Which this works great in query analyzer.... Now here is the ASP.NET script to load this proc:

Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyDataAdapter As SqlDataAdapter

MyConnection = New SqlConnection(sqlconn)
MyDataAdapter = New SqlDataAdapter("sp_CheckNetName", MyConnection)
MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@CID", SqlDbType.int, 4))
MyDataAdapter.SelectCommand.Parameters("@CID").Value = Trim(request("COMPID"))

MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Netname", SqlDbType.VarChar, 25))
MyDataAdapter.SelectCommand.Parameters("@Netname").Value = Trim(netname.text)

MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Return", SqlDbType.Int, 4))
MyDataAdapter.SelectCommand.Parameters("@Return").Direction = ParameterDirection.Output

DS = New DataSet()
MyDataAdapter.Fill(DS, "Net")

results.Text = MyDataAdapter.SelectCommand.Parameters(2).Value

MyDataAdapter.Dispose()
MyConnection.Close()



The results always displays 0, however in queryanalyzer, it will display the appropriate response.

Thanks!
 
E

Edd Connolly

David,

This might help...


CREATE PROCEDURE [dbo].[sp_CheckNetName]
(
@CID int,
@Netname nvarchar(25)
)
AS


IF EXISTS (SELECT DISTINCT netname FROM computers WHERE CompanyID = @CID AND UPPER(netname) = UPPER(@Netname))
RETURN 1
ELSE
RETURN 0
GO

--------------

Dim MyConnection As SqlConnection
Dim MyConnection As SqlCommand

MyConnection = New SqlConnection(sqlconn)
MyCommand = new SqlCommand("sp_CheckNetName", MyConnection)
MyCommand.CommandType = CommandType.StoredProcedure

MyCommand.Parameters.Add("@CID", SqlDbType.int, 4)
MyCommand.Parameters("@CID").Value = Trim(request("COMPID"))

MyCommand.Parameters.Add("@Netname", SqlDbType.VarChar, 25)
MyCommand.Parameters("@Netname").Value = Trim(netname.text)

MyCommand.Parameters.Add("@Return", SqlDbType.Int, 4)
MyCommand.Parameters("@Return").Direction = ParameterDirection.ReturnValue;


sqlconn.Open();
MyCommand.ExecuteNonQuery();
sqlconn.Dispose();
results.Text = MyDataAdapter.SelectCommand.Parameters(2).Value;




There is no need to use a DataAdapter here. Just execute the command directly. Also, although I have not changed it in the code above, refrain from using "sp_" as a prefix for stored procedures as SQL Server will look for the procedure in the Master DB before looking in your database, causing a slight degredation in performance.


--
Edd Connolly

MCP 70-315





Here is the proc:

CREATE PROCEDURE [dbo].[sp_CheckNetName]
@CID as int,
@Netname as nvarchar(25),
@Return as int OUTPUT
AS


IF EXISTS (SELECT DISTINCT netname FROM computers WHERE CompanyID = @CID AND UPPER(netname) = UPPER(@Netname))
SELECT @Return = 1
ELSE
SELECT @Return = 0
GO

Which this works great in query analyzer.... Now here is the ASP.NET script to load this proc:

Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyDataAdapter As SqlDataAdapter

MyConnection = New SqlConnection(sqlconn)
MyDataAdapter = New SqlDataAdapter("sp_CheckNetName", MyConnection)
MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@CID", SqlDbType.int, 4))
MyDataAdapter.SelectCommand.Parameters("@CID").Value = Trim(request("COMPID"))

MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Netname", SqlDbType.VarChar, 25))
MyDataAdapter.SelectCommand.Parameters("@Netname").Value = Trim(netname.text)

MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Return", SqlDbType.Int, 4))
MyDataAdapter.SelectCommand.Parameters("@Return").Direction = ParameterDirection.Output

DS = New DataSet()
MyDataAdapter.Fill(DS, "Net")

results.Text = MyDataAdapter.SelectCommand.Parameters(2).Value

MyDataAdapter.Dispose()
MyConnection.Close()



The results always displays 0, however in queryanalyzer, it will display the appropriate response.

Thanks!
 
E

Edd Connolly

David,

The last line should have read:

results.Text = MyCommand.Parameters(2).Value;


--
Edd Connolly

MCP 70-315



David,

This might help...


CREATE PROCEDURE [dbo].[sp_CheckNetName]
(
@CID int,
@Netname nvarchar(25)
)
AS


IF EXISTS (SELECT DISTINCT netname FROM computers WHERE CompanyID = @CID AND UPPER(netname) = UPPER(@Netname))
RETURN 1
ELSE
RETURN 0
GO

--------------

Dim MyConnection As SqlConnection
Dim MyConnection As SqlCommand

MyConnection = New SqlConnection(sqlconn)
MyCommand = new SqlCommand("sp_CheckNetName", MyConnection)
MyCommand.CommandType = CommandType.StoredProcedure

MyCommand.Parameters.Add("@CID", SqlDbType.int, 4)
MyCommand.Parameters("@CID").Value = Trim(request("COMPID"))

MyCommand.Parameters.Add("@Netname", SqlDbType.VarChar, 25)
MyCommand.Parameters("@Netname").Value = Trim(netname.text)

MyCommand.Parameters.Add("@Return", SqlDbType.Int, 4)
MyCommand.Parameters("@Return").Direction = ParameterDirection.ReturnValue;


sqlconn.Open();
MyCommand.ExecuteNonQuery();
sqlconn.Dispose();
results.Text = MyDataAdapter.SelectCommand.Parameters(2).Value;




There is no need to use a DataAdapter here. Just execute the command directly. Also, although I have not changed it in the code above, refrain from using "sp_" as a prefix for stored procedures as SQL Server will look for the procedure in the Master DB before looking in your database, causing a slight degredation in performance.


--
Edd Connolly

MCP 70-315





Here is the proc:

CREATE PROCEDURE [dbo].[sp_CheckNetName]
@CID as int,
@Netname as nvarchar(25),
@Return as int OUTPUT
AS


IF EXISTS (SELECT DISTINCT netname FROM computers WHERE CompanyID = @CID AND UPPER(netname) = UPPER(@Netname))
SELECT @Return = 1
ELSE
SELECT @Return = 0
GO

Which this works great in query analyzer.... Now here is the ASP.NET script to load this proc:

Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyDataAdapter As SqlDataAdapter

MyConnection = New SqlConnection(sqlconn)
MyDataAdapter = New SqlDataAdapter("sp_CheckNetName", MyConnection)
MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@CID", SqlDbType.int, 4))
MyDataAdapter.SelectCommand.Parameters("@CID").Value = Trim(request("COMPID"))

MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Netname", SqlDbType.VarChar, 25))
MyDataAdapter.SelectCommand.Parameters("@Netname").Value = Trim(netname.text)

MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Return", SqlDbType.Int, 4))
MyDataAdapter.SelectCommand.Parameters("@Return").Direction = ParameterDirection.Output

DS = New DataSet()
MyDataAdapter.Fill(DS, "Net")

results.Text = MyDataAdapter.SelectCommand.Parameters(2).Value

MyDataAdapter.Dispose()
MyConnection.Close()



The results always displays 0, however in queryanalyzer, it will display the appropriate response.

Thanks!
 
D

David Lozzi

Edd,

what language is this in?

--
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@delphi-ts.com


David,

The last line should have read:

results.Text = MyCommand.Parameters(2).Value;


--
Edd Connolly

MCP 70-315



David,

This might help...


CREATE PROCEDURE [dbo].[sp_CheckNetName]
(
@CID int,
@Netname nvarchar(25)
)
AS


IF EXISTS (SELECT DISTINCT netname FROM computers WHERE CompanyID = @CID AND UPPER(netname) = UPPER(@Netname))
RETURN 1
ELSE
RETURN 0
GO

--------------

Dim MyConnection As SqlConnection
Dim MyConnection As SqlCommand

MyConnection = New SqlConnection(sqlconn)
MyCommand = new SqlCommand("sp_CheckNetName", MyConnection)
MyCommand.CommandType = CommandType.StoredProcedure

MyCommand.Parameters.Add("@CID", SqlDbType.int, 4)
MyCommand.Parameters("@CID").Value = Trim(request("COMPID"))

MyCommand.Parameters.Add("@Netname", SqlDbType.VarChar, 25)
MyCommand.Parameters("@Netname").Value = Trim(netname.text)

MyCommand.Parameters.Add("@Return", SqlDbType.Int, 4)
MyCommand.Parameters("@Return").Direction = ParameterDirection.ReturnValue;


sqlconn.Open();
MyCommand.ExecuteNonQuery();
sqlconn.Dispose();
results.Text = MyDataAdapter.SelectCommand.Parameters(2).Value;




There is no need to use a DataAdapter here. Just execute the command directly. Also, although I have not changed it in the code above, refrain from using "sp_" as a prefix for stored procedures as SQL Server will look for the procedure in the Master DB before looking in your database, causing a slight degredation in performance.


--
Edd Connolly

MCP 70-315





Here is the proc:

CREATE PROCEDURE [dbo].[sp_CheckNetName]
@CID as int,
@Netname as nvarchar(25),
@Return as int OUTPUT
AS


IF EXISTS (SELECT DISTINCT netname FROM computers WHERE CompanyID = @CID AND UPPER(netname) = UPPER(@Netname))
SELECT @Return = 1
ELSE
SELECT @Return = 0
GO

Which this works great in query analyzer.... Now here is the ASP.NET script to load this proc:

Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyDataAdapter As SqlDataAdapter

MyConnection = New SqlConnection(sqlconn)
MyDataAdapter = New SqlDataAdapter("sp_CheckNetName", MyConnection)
MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@CID", SqlDbType.int, 4))
MyDataAdapter.SelectCommand.Parameters("@CID").Value = Trim(request("COMPID"))

MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Netname", SqlDbType.VarChar, 25))
MyDataAdapter.SelectCommand.Parameters("@Netname").Value = Trim(netname.text)

MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Return", SqlDbType.Int, 4))
MyDataAdapter.SelectCommand.Parameters("@Return").Direction = ParameterDirection.Output

DS = New DataSet()
MyDataAdapter.Fill(DS, "Net")

results.Text = MyDataAdapter.SelectCommand.Parameters(2).Value

MyDataAdapter.Dispose()
MyConnection.Close()



The results always displays 0, however in queryanalyzer, it will display the appropriate response.

Thanks!
 
D

David Lozzi

No such luck. First off, I believe you were writing in jscript? I had to remove the ;. I am getting the same results, it is only returning 0. Here's the new proc:

CREATE PROCEDURE [dbo].[sp_CheckNetName]
@CID as int,
@Netname as nvarchar(25)
AS

IF EXISTS (SELECT DISTINCT netname FROM computers WHERE CompanyID = @CID AND UPPER(netname) = UPPER(@Netname))
Return 1
ELSE
Return 0
GO

And here is what my VB looks like now:

sub checkNetName(sender as object, e as eventargs)
Dim MyConnection As SqlConnection
Dim MyCommand As SqlCommand

MyConnection = New SqlConnection(sqlconn)
MyCommand = new SqlCommand("sp_CheckNetName", MyConnection)
MyCommand.CommandType = CommandType.StoredProcedure

MyCommand.Parameters.Add("@CID", SqlDbType.int, 4)
MyCommand.Parameters("@CID").Value = Trim(request("COMPID"))

MyCommand.Parameters.Add("@Netname", SqlDbType.VarChar, 25)
MyCommand.Parameters("@Netname").Value = Trim(netname.text)

MyCommand.Parameters.Add("@Return", SqlDbType.Int, 4)
MyCommand.Parameters("@Return").Direction = ParameterDirection.ReturnValue

myconnection.Open()
MyCommand.ExecuteNonQuery()
myconnection.Dispose()
results.Text = MyCommand.Parameters(2).Value
end sub


Could it be the EXISTS? Is there another way of checking to see if a value is contained in a table?

THANKS!


--
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@delphi-ts.com


David,

The last line should have read:

results.Text = MyCommand.Parameters(2).Value;


--
Edd Connolly

MCP 70-315



David,

This might help...


CREATE PROCEDURE [dbo].[sp_CheckNetName]
(
@CID int,
@Netname nvarchar(25)
)
AS


IF EXISTS (SELECT DISTINCT netname FROM computers WHERE CompanyID = @CID AND UPPER(netname) = UPPER(@Netname))
RETURN 1
ELSE
RETURN 0
GO

--------------

Dim MyConnection As SqlConnection
Dim MyConnection As SqlCommand

MyConnection = New SqlConnection(sqlconn)
MyCommand = new SqlCommand("sp_CheckNetName", MyConnection)
MyCommand.CommandType = CommandType.StoredProcedure

MyCommand.Parameters.Add("@CID", SqlDbType.int, 4)
MyCommand.Parameters("@CID").Value = Trim(request("COMPID"))

MyCommand.Parameters.Add("@Netname", SqlDbType.VarChar, 25)
MyCommand.Parameters("@Netname").Value = Trim(netname.text)

MyCommand.Parameters.Add("@Return", SqlDbType.Int, 4)
MyCommand.Parameters("@Return").Direction = ParameterDirection.ReturnValue;


sqlconn.Open();
MyCommand.ExecuteNonQuery();
sqlconn.Dispose();
results.Text = MyDataAdapter.SelectCommand.Parameters(2).Value;




There is no need to use a DataAdapter here. Just execute the command directly. Also, although I have not changed it in the code above, refrain from using "sp_" as a prefix for stored procedures as SQL Server will look for the procedure in the Master DB before looking in your database, causing a slight degredation in performance.


--
Edd Connolly

MCP 70-315





Here is the proc:

CREATE PROCEDURE [dbo].[sp_CheckNetName]
@CID as int,
@Netname as nvarchar(25),
@Return as int OUTPUT
AS


IF EXISTS (SELECT DISTINCT netname FROM computers WHERE CompanyID = @CID AND UPPER(netname) = UPPER(@Netname))
SELECT @Return = 1
ELSE
SELECT @Return = 0
GO

Which this works great in query analyzer.... Now here is the ASP.NET script to load this proc:

Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyDataAdapter As SqlDataAdapter

MyConnection = New SqlConnection(sqlconn)
MyDataAdapter = New SqlDataAdapter("sp_CheckNetName", MyConnection)
MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@CID", SqlDbType.int, 4))
MyDataAdapter.SelectCommand.Parameters("@CID").Value = Trim(request("COMPID"))

MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Netname", SqlDbType.VarChar, 25))
MyDataAdapter.SelectCommand.Parameters("@Netname").Value = Trim(netname.text)

MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Return", SqlDbType.Int, 4))
MyDataAdapter.SelectCommand.Parameters("@Return").Direction = ParameterDirection.Output

DS = New DataSet()
MyDataAdapter.Fill(DS, "Net")

results.Text = MyDataAdapter.SelectCommand.Parameters(2).Value

MyDataAdapter.Dispose()
MyConnection.Close()



The results always displays 0, however in queryanalyzer, it will display the appropriate response.

Thanks!
 
D

David Lozzi

Just shoot me now!!! I was sending the wrong variable to the CID...

thanks for your help!!

--
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@delphi-ts.com


No such luck. First off, I believe you were writing in jscript? I had to remove the ;. I am getting the same results, it is only returning 0. Here's the new proc:

CREATE PROCEDURE [dbo].[sp_CheckNetName]
@CID as int,
@Netname as nvarchar(25)
AS

IF EXISTS (SELECT DISTINCT netname FROM computers WHERE CompanyID = @CID AND UPPER(netname) = UPPER(@Netname))
Return 1
ELSE
Return 0
GO

And here is what my VB looks like now:

sub checkNetName(sender as object, e as eventargs)
Dim MyConnection As SqlConnection
Dim MyCommand As SqlCommand

MyConnection = New SqlConnection(sqlconn)
MyCommand = new SqlCommand("sp_CheckNetName", MyConnection)
MyCommand.CommandType = CommandType.StoredProcedure

MyCommand.Parameters.Add("@CID", SqlDbType.int, 4)
MyCommand.Parameters("@CID").Value = Trim(request("COMPID"))

MyCommand.Parameters.Add("@Netname", SqlDbType.VarChar, 25)
MyCommand.Parameters("@Netname").Value = Trim(netname.text)

MyCommand.Parameters.Add("@Return", SqlDbType.Int, 4)
MyCommand.Parameters("@Return").Direction = ParameterDirection.ReturnValue

myconnection.Open()
MyCommand.ExecuteNonQuery()
myconnection.Dispose()
results.Text = MyCommand.Parameters(2).Value
end sub


Could it be the EXISTS? Is there another way of checking to see if a value is contained in a table?

THANKS!


--
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@delphi-ts.com


David,

The last line should have read:

results.Text = MyCommand.Parameters(2).Value;


--
Edd Connolly

MCP 70-315



David,

This might help...


CREATE PROCEDURE [dbo].[sp_CheckNetName]
(
@CID int,
@Netname nvarchar(25)
)
AS


IF EXISTS (SELECT DISTINCT netname FROM computers WHERE CompanyID = @CID AND UPPER(netname) = UPPER(@Netname))
RETURN 1
ELSE
RETURN 0
GO

--------------

Dim MyConnection As SqlConnection
Dim MyConnection As SqlCommand

MyConnection = New SqlConnection(sqlconn)
MyCommand = new SqlCommand("sp_CheckNetName", MyConnection)
MyCommand.CommandType = CommandType.StoredProcedure

MyCommand.Parameters.Add("@CID", SqlDbType.int, 4)
MyCommand.Parameters("@CID").Value = Trim(request("COMPID"))

MyCommand.Parameters.Add("@Netname", SqlDbType.VarChar, 25)
MyCommand.Parameters("@Netname").Value = Trim(netname.text)

MyCommand.Parameters.Add("@Return", SqlDbType.Int, 4)
MyCommand.Parameters("@Return").Direction = ParameterDirection.ReturnValue;


sqlconn.Open();
MyCommand.ExecuteNonQuery();
sqlconn.Dispose();
results.Text = MyDataAdapter.SelectCommand.Parameters(2).Value;




There is no need to use a DataAdapter here. Just execute the command directly. Also, although I have not changed it in the code above, refrain from using "sp_" as a prefix for stored procedures as SQL Server will look for the procedure in the Master DB before looking in your database, causing a slight degredation in performance.


--
Edd Connolly

MCP 70-315





Here is the proc:

CREATE PROCEDURE [dbo].[sp_CheckNetName]
@CID as int,
@Netname as nvarchar(25),
@Return as int OUTPUT
AS


IF EXISTS (SELECT DISTINCT netname FROM computers WHERE CompanyID = @CID AND UPPER(netname) = UPPER(@Netname))
SELECT @Return = 1
ELSE
SELECT @Return = 0
GO

Which this works great in query analyzer.... Now here is the ASP.NET script to load this proc:

Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyDataAdapter As SqlDataAdapter

MyConnection = New SqlConnection(sqlconn)
MyDataAdapter = New SqlDataAdapter("sp_CheckNetName", MyConnection)
MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@CID", SqlDbType.int, 4))
MyDataAdapter.SelectCommand.Parameters("@CID").Value = Trim(request("COMPID"))

MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Netname", SqlDbType.VarChar, 25))
MyDataAdapter.SelectCommand.Parameters("@Netname").Value = Trim(netname.text)

MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Return", SqlDbType.Int, 4))
MyDataAdapter.SelectCommand.Parameters("@Return").Direction = ParameterDirection.Output

DS = New DataSet()
MyDataAdapter.Fill(DS, "Net")

results.Text = MyDataAdapter.SelectCommand.Parameters(2).Value

MyDataAdapter.Dispose()
MyConnection.Close()



The results always displays 0, however in queryanalyzer, it will display the appropriate response.

Thanks!
 
E

Edd Connolly

David,

Glad you sorted your problem. Apologies for putting a semicolon after my line of VB code. I develop in C# mostly so it's just habit.


--
Edd Connolly

MCP 70-315



Just shoot me now!!! I was sending the wrong variable to the CID...

thanks for your help!!

--
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@delphi-ts.com


No such luck. First off, I believe you were writing in jscript? I had to remove the ;. I am getting the same results, it is only returning 0. Here's the new proc:

CREATE PROCEDURE [dbo].[sp_CheckNetName]
@CID as int,
@Netname as nvarchar(25)
AS

IF EXISTS (SELECT DISTINCT netname FROM computers WHERE CompanyID = @CID AND UPPER(netname) = UPPER(@Netname))
Return 1
ELSE
Return 0
GO

And here is what my VB looks like now:

sub checkNetName(sender as object, e as eventargs)
Dim MyConnection As SqlConnection
Dim MyCommand As SqlCommand

MyConnection = New SqlConnection(sqlconn)
MyCommand = new SqlCommand("sp_CheckNetName", MyConnection)
MyCommand.CommandType = CommandType.StoredProcedure

MyCommand.Parameters.Add("@CID", SqlDbType.int, 4)
MyCommand.Parameters("@CID").Value = Trim(request("COMPID"))

MyCommand.Parameters.Add("@Netname", SqlDbType.VarChar, 25)
MyCommand.Parameters("@Netname").Value = Trim(netname.text)

MyCommand.Parameters.Add("@Return", SqlDbType.Int, 4)
MyCommand.Parameters("@Return").Direction = ParameterDirection.ReturnValue

myconnection.Open()
MyCommand.ExecuteNonQuery()
myconnection.Dispose()
results.Text = MyCommand.Parameters(2).Value
end sub


Could it be the EXISTS? Is there another way of checking to see if a value is contained in a table?

THANKS!


--
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@delphi-ts.com


David,

The last line should have read:

results.Text = MyCommand.Parameters(2).Value;


--
Edd Connolly

MCP 70-315



David,

This might help...


CREATE PROCEDURE [dbo].[sp_CheckNetName]
(
@CID int,
@Netname nvarchar(25)
)
AS


IF EXISTS (SELECT DISTINCT netname FROM computers WHERE CompanyID = @CID AND UPPER(netname) = UPPER(@Netname))
RETURN 1
ELSE
RETURN 0
GO

--------------

Dim MyConnection As SqlConnection
Dim MyConnection As SqlCommand

MyConnection = New SqlConnection(sqlconn)
MyCommand = new SqlCommand("sp_CheckNetName", MyConnection)
MyCommand.CommandType = CommandType.StoredProcedure

MyCommand.Parameters.Add("@CID", SqlDbType.int, 4)
MyCommand.Parameters("@CID").Value = Trim(request("COMPID"))

MyCommand.Parameters.Add("@Netname", SqlDbType.VarChar, 25)
MyCommand.Parameters("@Netname").Value = Trim(netname.text)

MyCommand.Parameters.Add("@Return", SqlDbType.Int, 4)
MyCommand.Parameters("@Return").Direction = ParameterDirection.ReturnValue;


sqlconn.Open();
MyCommand.ExecuteNonQuery();
sqlconn.Dispose();
results.Text = MyDataAdapter.SelectCommand.Parameters(2).Value;




There is no need to use a DataAdapter here. Just execute the command directly. Also, although I have not changed it in the code above, refrain from using "sp_" as a prefix for stored procedures as SQL Server will look for the procedure in the Master DB before looking in your database, causing a slight degredation in performance.


--
Edd Connolly

MCP 70-315





Here is the proc:

CREATE PROCEDURE [dbo].[sp_CheckNetName]
@CID as int,
@Netname as nvarchar(25),
@Return as int OUTPUT
AS


IF EXISTS (SELECT DISTINCT netname FROM computers WHERE CompanyID = @CID AND UPPER(netname) = UPPER(@Netname))
SELECT @Return = 1
ELSE
SELECT @Return = 0
GO

Which this works great in query analyzer.... Now here is the ASP.NET script to load this proc:

Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyDataAdapter As SqlDataAdapter

MyConnection = New SqlConnection(sqlconn)
MyDataAdapter = New SqlDataAdapter("sp_CheckNetName", MyConnection)
MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@CID", SqlDbType.int, 4))
MyDataAdapter.SelectCommand.Parameters("@CID").Value = Trim(request("COMPID"))

MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Netname", SqlDbType.VarChar, 25))
MyDataAdapter.SelectCommand.Parameters("@Netname").Value = Trim(netname.text)

MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@Return", SqlDbType.Int, 4))
MyDataAdapter.SelectCommand.Parameters("@Return").Direction = ParameterDirection.Output

DS = New DataSet()
MyDataAdapter.Fill(DS, "Net")

results.Text = MyDataAdapter.SelectCommand.Parameters(2).Value

MyDataAdapter.Dispose()
MyConnection.Close()



The results always displays 0, however in queryanalyzer, it will display the appropriate response.

Thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

problem with sending and returning value in/from stored proc 2
TableAdapter returns null for stored proc returning a single value 3
returning output vars from nested stored procedure 1
Stored Proc with parameters 2
Using layers won't return value from stored procedure 0
How get value from stored proc 2
Output value stored procedure dotnet 1
return value from stored proc using MS Data Access Application Block 0

Members online

Total: 42 (members: 2, guests: 40)
Robots: 396

Forum statistics

Threads
473,969
Messages
2,570,161
Members
46,708
Latest member
SherleneF1

Latest Threads

Top