Returning a Scalar Identity from DataSource

[Guest]

Hi Everybody,
I'd Appreciate anybody who can give me a lead here.
I'm trying to run an "Insert" command from my ASP page. The command takes as
values some on-page textboxes as well some other paremeters such as user info
etc.
I tried using a DataSet method, but could not figure out how to insert
custom parameters (the user info ones), so i switched to inline SQL this is
my code:

Dim dtsRequest As New SqlDataSource
dtsRequest.ConnectionString =
ConfigurationManager.ConnectionStrings("IODatabaseConnectionString1").ToString
dtsRequest.InsertCommandType = SqlDataSourceCommandType.Text
dtsRequest.InsertCommand = "insert into requests (requestedby,
checkdate,CheckAmountNIS,payeename,shortdescription,notes,autoconfirm,rate)
values (@requestedby, @checkdate,
@CheckAmountNIS,@payeename,@shortdescription,@notes,@autoconfirm,@rate);
select @@identity"
dtsRequest.InsertParameters.Add("RequestedBy", curPersonID)
dtsRequest.InsertParameters.Add("CheckDate", CheckDateTextBox.Text)
dtsRequest.InsertParameters.Add("CheckAmountNIS",
CheckAmountNISTextBox.Text)
dtsRequest.InsertParameters.Add("payeename", PayeeNameTextBox.Text)
dtsRequest.InsertParameters.Add("shortdescription",
ShortDescriptionTextBox.Text)
dtsRequest.InsertParameters.Add("notes", NotesTextBox.Text)
dtsRequest.InsertParameters.Add("autoconfirm",
AutoConfirmCheckBox.Checked)
dtsRequest.InsertParameters.Add("rate", RateTextBox.Text)

'Now I dimension an integer and set it to the DataSource's Insert Method:
Dim inNewRequestID As Integer
inNewRequestID = dtsRequest.Insert
'---------END CODE

Now I wanted that the inNewRequestID should be the identity field. isn't
that what it is supposed to return? now it returns "1" as number of affected
rows, which is not what I need.

Any lead would be appreciated. I'm after a whole night of Googling on this
one <yawn>.

 

[Eliyahu Goldin]

Run a batch:

insert ...;select scope_identity()

Make a SQLCommand object for this batch and run it with an ExecuteScalar
call.

 

[Guest]

Yasher Koach.
I was aware that there was the possibilty of a direct SQL Command. For
simplicity, though, I thought of using built-in ASP.Net contols/objects.
In the end, I could not figure out how to make a scalar execution with the
SQLDataSource object, so I resorted to a TableAdapter, and thank Heaven,
after some tinkering, I got it to work A-OK.
Your help and willingness to do so are greatly appreciated.
--
Thankfully, YisMan

Run a batch:

insert ...;select scope_identity()

Make a SQLCommand object for this batch and run it with an ExecuteScalar
call.

--
Eliyahu Goldin,
Software Developer & Consultant
Microsoft MVP [ASP.NET]

Hi Everybody,
I'd Appreciate anybody who can give me a lead here.
I'm trying to run an "Insert" command from my ASP page. The command takes
as
values some on-page textboxes as well some other paremeters such as user
info
etc.
I tried using a DataSet method, but could not figure out how to insert
custom parameters (the user info ones), so i switched to inline SQL this
is
my code:

Dim dtsRequest As New SqlDataSource
dtsRequest.ConnectionString =
ConfigurationManager.ConnectionStrings("IODatabaseConnectionString1").ToString
dtsRequest.InsertCommandType = SqlDataSourceCommandType.Text
dtsRequest.InsertCommand = "insert into requests (requestedby,
checkdate,CheckAmountNIS,payeename,shortdescription,notes,autoconfirm,rate)
values (@requestedby, @checkdate,
@CheckAmountNIS,@payeename,@shortdescription,@notes,@autoconfirm,@rate);
select @@identity"
dtsRequest.InsertParameters.Add("RequestedBy", curPersonID)
dtsRequest.InsertParameters.Add("CheckDate", CheckDateTextBox.Text)
dtsRequest.InsertParameters.Add("CheckAmountNIS",
CheckAmountNISTextBox.Text)
dtsRequest.InsertParameters.Add("payeename", PayeeNameTextBox.Text)
dtsRequest.InsertParameters.Add("shortdescription",
ShortDescriptionTextBox.Text)
dtsRequest.InsertParameters.Add("notes", NotesTextBox.Text)
dtsRequest.InsertParameters.Add("autoconfirm",
AutoConfirmCheckBox.Checked)
dtsRequest.InsertParameters.Add("rate", RateTextBox.Text)

'Now I dimension an integer and set it to the DataSource's Insert Method:
Dim inNewRequestID As Integer
inNewRequestID = dtsRequest.Insert
'---------END CODE

Now I wanted that the inNewRequestID should be the identity field. isn't
that what it is supposed to return? now it returns "1" as number of
affected
rows, which is not what I need.

Any lead would be appreciated. I'm after a whole night of Googling on this
one <yawn>.