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

You need to store the value of the NEWID() in a local variable and then insert that value

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...

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()

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

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

Hi,

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

Many Thanks
Lee

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

see here a tutorial:
http://www.vbdotnetheaven.com/UploadFile/dclark/StoredProceduresinVBdotNET04082005072716AM/StoredProceduresinVBdotNET.aspx

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

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

Change this "@id uniqueidentifier OUTPUT " to
@ID varchar(40) OUTPUT