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
