VB.NET SQL procedures to return values
-
Hi, I have a problem with an sql procedure. What i want to do is build a procedure that will add a record to a database table and after the addition of the record will return the id of that record. So the procedure is: CREATE PROCEDURE AddListing @Name varchar(150), @Description varchar(1000), @Keywords varchar(50), @Address varchar(200), @Telephone varchar(50), @Fax varchar(50), @Mobile varchar(50), @Email varchar(50), @URL varchar(50), @Aproved bit, @Premium bit, @UserID int, @nextID int Output --
-
Hi, I have a problem with an sql procedure. What i want to do is build a procedure that will add a record to a database table and after the addition of the record will return the id of that record. So the procedure is: CREATE PROCEDURE AddListing @Name varchar(150), @Description varchar(1000), @Keywords varchar(50), @Address varchar(200), @Telephone varchar(50), @Fax varchar(50), @Mobile varchar(50), @Email varchar(50), @URL varchar(50), @Aproved bit, @Premium bit, @UserID int, @nextID int Output --
Check out this link http://aspnet.4guysfromrolla.com/articles/062905-1.aspx Navi
-
Hi, I have a problem with an sql procedure. What i want to do is build a procedure that will add a record to a database table and after the addition of the record will return the id of that record. So the procedure is: CREATE PROCEDURE AddListing @Name varchar(150), @Description varchar(1000), @Keywords varchar(50), @Address varchar(200), @Telephone varchar(50), @Fax varchar(50), @Mobile varchar(50), @Email varchar(50), @URL varchar(50), @Aproved bit, @Premium bit, @UserID int, @nextID int Output --
It is a bad practice to get your ID using MAX(). You should define the l_id column as an
IDENTITYcolumn, and allow SQL Server to create the ID for you. Then, make your sproc like this:CREATE PROCEDURE AddListing
@Name varchar(150),@Description varchar(1000),@Keywords varchar(50),
@Address varchar(200),@Telephone varchar(50),@Fax varchar(50),
@Mobile varchar(50),@Email varchar(50),@URL varchar(50),
@Aproved bit,@Premium bit,@UserID int,@nextID int Output --SET @nextID = SCOPE_IDENTITY()INSERT INTO usr_ls_line (usr_id, l_id)
VALUES (@UserID,@nextID)return @nextID
Then, in VB.NET do something like this:
Imports System.Data
Imports System.Data.SqlClient...
Dim conn As New SqlConnection
conn.ConnectionString = "Data Source=(local);" & _
"Initial Catalog=mytestdb;" & _
"Integrated Security=SSPI"Dim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "AddListing"Dim prm1 As New SqlParameter("@Name", SqlDbType.VarChar, 150)
prm1.Direction = ParameterDirection.Input
cmd.Parameters.Add(prm1)
prm1.Value = "MyStaffName"Dim prm2 As New SqlParameter("@Description", SqlDbType.VarChar, 1000)
prm2.Direction = ParameterDirection.Input
cmd.Parameters.Add(prm2)
prm2.Value = "Whatever"'...
Dim prm5 As New SqlParameter("@nextID", SqlDbType.Int) prm5.Direction = ParameterDirection.Output cmd.Parameters.Add(prm5)conn.Open()
cmd.ExecuteNonQuery()
conn.Close()MsgBox(
"Returned ID=" + prm5.Value)--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
-
It is a bad practice to get your ID using MAX(). You should define the l_id column as an
IDENTITYcolumn, and allow SQL Server to create the ID for you. Then, make your sproc like this:CREATE PROCEDURE AddListing
@Name varchar(150),@Description varchar(1000),@Keywords varchar(50),
@Address varchar(200),@Telephone varchar(50),@Fax varchar(50),
@Mobile varchar(50),@Email varchar(50),@URL varchar(50),
@Aproved bit,@Premium bit,@UserID int,@nextID int Output --SET @nextID = SCOPE_IDENTITY()INSERT INTO usr_ls_line (usr_id, l_id)
VALUES (@UserID,@nextID)return @nextID
Then, in VB.NET do something like this:
Imports System.Data
Imports System.Data.SqlClient...
Dim conn As New SqlConnection
conn.ConnectionString = "Data Source=(local);" & _
"Initial Catalog=mytestdb;" & _
"Integrated Security=SSPI"Dim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "AddListing"Dim prm1 As New SqlParameter("@Name", SqlDbType.VarChar, 150)
prm1.Direction = ParameterDirection.Input
cmd.Parameters.Add(prm1)
prm1.Value = "MyStaffName"Dim prm2 As New SqlParameter("@Description", SqlDbType.VarChar, 1000)
prm2.Direction = ParameterDirection.Input
cmd.Parameters.Add(prm2)
prm2.Value = "Whatever"'...
Dim prm5 As New SqlParameter("@nextID", SqlDbType.Int) prm5.Direction = ParameterDirection.Output cmd.Parameters.Add(prm5)conn.Open()
cmd.ExecuteNonQuery()
conn.Close()MsgBox(
"Returned ID=" + prm5.Value)--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
In this case yes, an IDENTITY column would work work, yet you can't make such a blanket statement that not using it is bad practice. Perhaps there is some special processing that must take place to assign an ID, or the ID field is not numeric.
only two letters away from being an asset
-
In this case yes, an IDENTITY column would work work, yet you can't make such a blanket statement that not using it is bad practice. Perhaps there is some special processing that must take place to assign an ID, or the ID field is not numeric.
only two letters away from being an asset
Mark Nischalke wrote:
you can't make such a blanket statement that not using it is bad practice
Good point Mark.
--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
-
Hi, I have a problem with an sql procedure. What i want to do is build a procedure that will add a record to a database table and after the addition of the record will return the id of that record. So the procedure is: CREATE PROCEDURE AddListing @Name varchar(150), @Description varchar(1000), @Keywords varchar(50), @Address varchar(200), @Telephone varchar(50), @Fax varchar(50), @Mobile varchar(50), @Email varchar(50), @URL varchar(50), @Aproved bit, @Premium bit, @UserID int, @nextID int Output --
-
Hi, I have a problem with an sql procedure. What i want to do is build a procedure that will add a record to a database table and after the addition of the record will return the id of that record. So the procedure is: CREATE PROCEDURE AddListing @Name varchar(150), @Description varchar(1000), @Keywords varchar(50), @Address varchar(200), @Telephone varchar(50), @Fax varchar(50), @Mobile varchar(50), @Email varchar(50), @URL varchar(50), @Aproved bit, @Premium bit, @UserID int, @nextID int Output --
dim comm as new sqlcommand("AddListing",connection) comm.commandtype=commandtype.storedprocedure 'For input parameters coom.parameters.add("@name","abc") comm.parameters.add("@nextid",sqldbtype.int).direction=parameterdirection.output comm.executenonquery comm.parameters("@nextid").value Thanks & Regards Kumar Prabhakar
abc
-
Hi, I have a problem with an sql procedure. What i want to do is build a procedure that will add a record to a database table and after the addition of the record will return the id of that record. So the procedure is: CREATE PROCEDURE AddListing @Name varchar(150), @Description varchar(1000), @Keywords varchar(50), @Address varchar(200), @Telephone varchar(50), @Fax varchar(50), @Mobile varchar(50), @Email varchar(50), @URL varchar(50), @Aproved bit, @Premium bit, @UserID int, @nextID int Output --