By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,638 Members | 2,241 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,638 IT Pros & Developers. It's quick & easy.

Returning a value from a stored proc

P: n/a
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").Val ue = 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!

--
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@delphi-ts.com
Nov 18 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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

"David Lozzi" <dlozzi(remove-this)@delphi-ts.com> wrote in message news:eE**************@TK2MSFTNGP10.phx.gbl...
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").Val ue = 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!

--
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@delphi-ts.com
Nov 18 '05 #2

P: n/a
David,

The last line should have read:

results.Text = MyCommand.Parameters(2).Value;
--
Edd Connolly

MCP 70-315

"Edd Connolly" <ed******************@hotmail.com> wrote in message news:Je*****************@newsfe3-gui.ntli.net...
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

"David Lozzi" <dlozzi(remove-this)@delphi-ts.com> wrote in message news:eE**************@TK2MSFTNGP10.phx.gbl...
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").Val ue = 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!

--
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@delphi-ts.com
Nov 18 '05 #3

P: n/a
Edd,

what language is this in?

--
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@delphi-ts.com
"Edd Connolly" <ed******************@hotmail.com> wrote in message news:si****************@newsfe3-gui.ntli.net...
David,

The last line should have read:

results.Text = MyCommand.Parameters(2).Value;
--
Edd Connolly

MCP 70-315

"Edd Connolly" <ed******************@hotmail.com> wrote in message news:Je*****************@newsfe3-gui.ntli.net...
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

"David Lozzi" <dlozzi(remove-this)@delphi-ts.com> wrote in message news:eE**************@TK2MSFTNGP10.phx.gbl...
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").Val ue = 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!

--
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@delphi-ts.com
Nov 18 '05 #4

P: n/a
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
"Edd Connolly" <ed******************@hotmail.com> wrote in message news:si****************@newsfe3-gui.ntli.net...
David,

The last line should have read:

results.Text = MyCommand.Parameters(2).Value;
--
Edd Connolly

MCP 70-315

"Edd Connolly" <ed******************@hotmail.com> wrote in message news:Je*****************@newsfe3-gui.ntli.net...
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

"David Lozzi" <dlozzi(remove-this)@delphi-ts.com> wrote in message news:eE**************@TK2MSFTNGP10.phx.gbl...
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").Val ue = 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!

--
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@delphi-ts.com
Nov 18 '05 #5

P: n/a
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
"David Lozzi" <dlozzi(remove-this)@delphi-ts.com> wrote in message news:ub**************@TK2MSFTNGP12.phx.gbl...
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
"Edd Connolly" <ed******************@hotmail.com> wrote in message news:si****************@newsfe3-gui.ntli.net...
David,

The last line should have read:

results.Text = MyCommand.Parameters(2).Value;
--
Edd Connolly

MCP 70-315

"Edd Connolly" <ed******************@hotmail.com> wrote in message news:Je*****************@newsfe3-gui.ntli.net...
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

"David Lozzi" <dlozzi(remove-this)@delphi-ts.com> wrote in message news:eE**************@TK2MSFTNGP10.phx.gbl...
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").Val ue = 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!

--
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@delphi-ts.com
Nov 18 '05 #6

P: n/a
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

"David Lozzi" <dlozzi(remove-this)@delphi-ts.com> wrote in message news:%2****************@TK2MSFTNGP11.phx.gbl...
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
"David Lozzi" <dlozzi(remove-this)@delphi-ts.com> wrote in message news:ub**************@TK2MSFTNGP12.phx.gbl...
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
"Edd Connolly" <ed******************@hotmail.com> wrote in message news:si****************@newsfe3-gui.ntli.net...
David,

The last line should have read:

results.Text = MyCommand.Parameters(2).Value;
--
Edd Connolly

MCP 70-315

"Edd Connolly" <ed******************@hotmail.com> wrote in message news:Je*****************@newsfe3-gui.ntli.net...
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

"David Lozzi" <dlozzi(remove-this)@delphi-ts.com> wrote in message news:eE**************@TK2MSFTNGP10.phx.gbl...
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").Val ue = 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!

--
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@delphi-ts.com
Nov 18 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.