Hi,
I need to insert a new row in a table and get the newly created rowid at the same time.
I have this SPROC:
==============
USE [dnn520]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Questions_Add]
(@Q_Text ntext)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [dbo].[Questions]
(Q_Text)
VALUES
(@Q_Text)
 

-- Get the newly created rowID

SELECT cast(SCOPE_IDENTITY() as int) AS newQID
END
==============

newQID is the new rowID.
This works in mssql studio and when I run the SPROC it inserts the new row and displays the new rowID.

Now I need to set the procedure that handles this in DataProvider.vb, AddQController.vb and SqlDataProvider.vb to do the insert and getting the rowID at the same query to the DB.
I use these code snippets:
in  DataProvider.vb:
...
 

Public MustOverride Sub Questions_Add(ByVal Q_Text As String) 

...
In SqlDataProvider.vb :
...
 

Public Overrides Sub Questions_Add(ByVal Q_Text As String)

SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("Questions_Add"), Q_Text)

End Sub

...
 

in PsychoAddQController.vb:

Public Sub Questions_Add(ByVal objQuestions_Add As AddQInfo)

DataProvider.Instance().Questions_Add(objQuestions_Add.Q_Text)

End Sub

...

How do I do this?
Thanks,
Yehuda

Hello Yen,

Thank you for taking the time to make it so clear.

I will try that but have just one note. The code supposed to insert data to the DB and uses the regullar procedure to do that task. The need to mix insert and select commands in the same query and how it works is not clear to me.

I'll appreciate if you could elaborate about how it works.

Anyway, I'll put the changed code and will inform you.

Thanks,

Yehuda

Hi Yehuda,

I'll explain it using 1 argument and then you can expand on the functions and stored procedure to accept 12 arguments.

Firstly, you need further modify your SqlDataProvider and DataProvider classes.

SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("Questions_Add"), Q_Text) becomes

Return ctype(SqlHelper.ExecuteScalar(ConnectionString, GetFullyQualifiedName("Questions_Add"), Q_Text)
,string)

Execute Scalar will accept the integer from SELECT cast(SCOPE_IDENTITY() as int) AS newQID in your stored proc
And the Return will pass it back to the calling function
Hope that makes sense
regards