[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

find what newID() was created in vb.net and sql server 2005

Posted on 2007-08-02
13
Medium Priority
?
1,211 Views
Last Modified: 2013-11-26
Hi,

I have the following code in vb.net 2005: -

sql = "insert into dbo.Products values (newid(),'" & _
                                    txtProductName.Text & "', '" & _
                                    txtStockCode.Text & "', '" & _
                                    txtPrice.Text & "', '" & _
                                    cmbStockLocation.SelectedValue.ToString & "', '" & _
                                    cmbSupplier.SelectedValue.ToString & "')"

                Dim command As New SqlClient.SqlCommand(sql, oConn)
                command.ExecuteNonQuery()
                dbClose()

In the first column it creates a newid via the newID() command. That new Id I need to use again in anothe sql statement in the same procedure. What I am trying to find out is how do I find what ID was created please so I can place it into another sql command after this insert.

Many Thanks
Lee
0
Comment
Question by:ljhodgett
  • 5
  • 3
  • 2
  • +2
13 Comments
 
LVL 15

Assisted Solution

by:derekkromm
derekkromm earned 800 total points
ID: 19616614
The best way to do this is to have your insert statement as a stored procedure so you can return the value after the insert:

create procedure ProductsInsert(parameters) as

declare @id uniqueidentifier
select @id=newid()

insert into products values(@id, parameters)

select @id



Then you can do command.ExecuteScalar to retrieve the returned id.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 19616627
You need to store the value of the NEWID() in a local variable and then insert that value
0
 
LVL 10

Expert Comment

by:lahousden
ID: 19616684
Best bet is to get the new id into a local variable and pass that to the insert statement.  In T-SQL this would be:

declare @ui_newid uniqueidentifier
set @ui_newid = NEWID()
insert into dbo.Products values (@ui_newid,...)

I am not familiar enough with VB.NET to say how you would get the @ui_newid value into a VB variable, but I am sure that it will be straight -forward...
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19616695
you can do without a procedure, but a procedure is recommended, though:


sql = "declare @id uniqueidentifier  
        set nocount on  
        set @id = newid()
        insert into dbo.Products values (@id,'" & _
                                    txtProductName.Text & "', '" & _
                                    txtStockCode.Text & "', '" & _
                                    txtPrice.Text & "', '" & _
                                    cmbStockLocation.SelectedValue.ToString & "', '" & _
                                    cmbSupplier.SelectedValue.ToString & "')
                                     select @id new_id "

                Dim command As New SqlClient.SqlCommand(sql, oConn)
                Dim new_id Ss String = command.ExecuteScalar().ToString()
                dbClose()

0
 

Author Comment

by:ljhodgett
ID: 19616805
Hi,

Unfortunatly I am unfamiliar with stored procedures at the moment as I am currently teaching myself (We'll trying!!!!) vb.net but would be great if someone can show how to create the stored procedure and interact through vb.net for this please. The current sql statment I am using is: -

sql = "insert into dbo.Products values (newid(),'" & _
                                    txtProductName.Text & "', '" & _
                                    txtStockCode.Text & "', '" & _
                                    txtPrice.Text & "', '" & _
                                    cmbStockLocation.SelectedValue.ToString & "', '" & _
                                    cmbSupplier.SelectedValue.ToString & "')"

Many Thanks
Lee
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 19616847
The stored procedure has to be created on the SQL Server.

If you want to do it directly through .NET, you could run the following query after your insert:
(change productname, stockcode, etc to the proper column names)

sql = "select id from Products where productname='" & _
                                    txtProductName.Text & "' and stockcode= '" & _
                                    txtStockCode.Text & "' and price= '" & _
                                    txtPrice.Text & "' and stocklocation= '" & _
                                    cmbStockLocation.SelectedValue.ToString & "' and supplier= '" & _
                                    cmbSupplier.SelectedValue.ToString & "'"

Then you do

dim x as string = command.executescalar

0
 

Author Comment

by:ljhodgett
ID: 19616894
Hi,

It would be great to know how to do it as a stored procedure.

Many Thanks
Lee
0
 

Author Comment

by:ljhodgett
ID: 19618283
Hi,

I've managed to create the procedure and compile is sucessfully using: -

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            LH
-- Create date: 02/08/02007
-- Description:      Insert product information into "dbo.Products"
-- =============================================
ALTER procedure [dbo].[InsertProducts](
@id uniqueidentifier,
@ProdName varchar(100),
@StockCode varchar(100),
@TextPrice  varchar(100),
@StockLocation varchar(100),
@Supplier varchar(100))
AS
BEGIN
   SET NOCOUNT ON
      select @id=newid()
      insert into dbo.products values(@id, @ProdName, @StockCode, @TextPrice, @StockLocation,@Supplier)
      select @id
END

Can you please check it's correct for me and how would I interact with it via vb.net please?

Many Thanks
Lee
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19618307
0
 

Author Comment

by:ljhodgett
ID: 19623668
hi,

I've tried the following code: -

                Dim command As New SqlClient.SqlCommand(sql, oConn)
                command.ExecuteNonQuery()

                Dim myCommand As New SqlCommand("InsertProducts", oConn)
                myCommand.CommandType = CommandType.StoredProcedure

                myCommand.Parameters.Add("@ProdName", 5) 'this one is optional
                myCommand.Parameters.Add("@Stockcode", 5)
                myCommand.Parameters.Add("@TextPrice", 5)
                myCommand.Parameters.Add("@StockLocation", 5)
                myCommand.Parameters.Add("@Supplier", 5)

                Dim myAnswer As New SqlParameter("@id", SqlDbType.VarChar, 30)
                myAnswer.Direction = ParameterDirection.Output

                myCommand.Parameters.Add(myAnswer)

                myCommand.ExecuteNonQuery()

but it comes up: -

The formal parameter "@id" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.

What does this mean please and how do I resolve it.

Many Thanks
Lee
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1200 total points
ID: 19623683
change the proc slightly


ALTER procedure [dbo].[InsertProducts](
@ProdName varchar(100),
@StockCode varchar(100),
@TextPrice  varchar(100),
@StockLocation varchar(100),
@Supplier varchar(100),
@id uniqueidentifier OUTPUT

)
AS
BEGIN
   SET NOCOUNT ON
      select @id=newid()
      insert into dbo.products values(@id, @ProdName, @StockCode, @TextPrice, @StockLocation,@Supplier)
      select @id
END
0
 

Author Comment

by:ljhodgett
ID: 19623739
Hi,

I've changed the stored procedure but unfortunately it comes up Error converting data type uniqueidentifier to varchar. when I run myCommand.ExecuteNonQuery()

Best Regards
Lee
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 19623752
Change this "@id uniqueidentifier OUTPUT " to
@ID varchar(40) OUTPUT
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question