How do I get the Identity field value after a sql insert so the value can be included in the email sent upon submit?

I have  a form that gathers a bunch of data, inserts into a sql database via a stored procedure, and then emails a user some details.  How can I include the value of the unique identifier for the row just inserted into sql in the email?    Here's my code behind.. and I don't know how/where to query the db to get the value and insert it...

Protected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.Click

InsertConflictSearch.InsertParameters("SearchStatusID").DefaultValue = "1"
InsertConflictSearch.Insert()
FormPlaceHolder.Visible =
False
ThankYouPlaceHolder.Visible = True

Const ToAddress As String = foo@foo.com
Dim
mm As New MailMessage(foo@foo.com, ToAddress)
Dim sBody As String

sBody = "Type of search:&nbsp;&nbsp;&nbsp;" & SearchTypeID.SelectedItem.Text & "<BR/> ***INSERT RETURNED SQL UNIQUE FIELD HERE***
mm.Subject = "New Conflict Search"
mm.Body = sBody
mm.IsBodyHtml =
True
Dim smtp As New SmtpClient
smtp.Send(mm)
End Sub


Never make important decisions on a Monday!
0
funluckykitty
5/1/2008 3:06:33 PM
asp.net.visual-basic.net 3721 articles. 0 followers. Follow

4 Replies
1174 Views

Similar Articles

[PageSpeed] 13

I'm a bit confused by your problem.

The email code really has very little to do with this.  All you have to do is concatenate the returned sql unique id in the right space in the message.

So, I'm guessing the problem is you don't know how to get the unique id value back from the database?  If so, it would be best to show the code that inserts the record into the database (both the VB and the database side of the code).

Without knowing what database you are using (Oracle, Sql Server), we really can't tell you how to get the unique id value back. 

 

0
david
5/1/2008 7:08:01 PM

You're right... email works fine.  The part I can't figure out is how to set the returned sql id back from the database. For now, I'd like to figrue out how to set that returned value (@SearchID) equal to the text for a label named ConfirmationID, which will display on the page after a user clicks sbumit.  

My search page where the sqldatasource is established...
Search.aspx  

 <asp:SqlDataSource ID="InsertConflictSearch" runat="server" ConnectionString="<%$ ConnectionStrings:NewEmploymentConnectionString %>" InsertCommand="sp_InsertConflictSearch" InsertCommandType="StoredProcedure" >

Code Behind
Search.aspx.vb
Protected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.Click

InsertConflictSearch.InsertParameters("SearchStatusID").DefaultValue = "1"
InsertConflictSearch.Insert()
FormPlaceHolder.Visible =
False
ThankYouPlaceHolder.Visible = True
ConfirmationLabel.Text = ??????

Stored Procedure

CREATE PROCEDURE sp_InsertConflictSearch

(
@SearchTypeID int,
@RequestingAttorney int ,
@PotentialClient varchar (2000),
@OtherParties varchar(2000),
@NamesToSearch varchar(2000),
@OtherRelatedToSearch varchar(2000),
@ClientToSearch varchar(100),
@EntitiesToSearch varchar(2000),
@ExistingBatchNumber varchar(100),
@AdditionalNames varchar(2000),
@SearchStatusID int,
@SubmittedBy  varchar(50)
)

AS
BEGIN

INSERT INTO.tConflictSearches (SearchTypeID, RequestingAttorney, PotentialClient, OtherParties, NamesToSearch, OtherRelatedToSearch, ClientToSearch, EntitiesToSearch, ExistingBatchNumber, AdditionalNames, SearchStatusID, SubmittedBy)

VALUES (@SearchTypeID, @RequestingAttorney, @PotentialClient,@OtherParties, @NamesToSearch, @OtherRelatedToSearch, @ClientToSearch, @EntitiesToSearch, @ExistingBatchNumber, @AdditionalNames, @SearchStatusID, @SubmittedBy)

DECLARE @SearchID INT
SET @SearchID = scope_identity()

END
GO
 


Never make important decisions on a Monday!
0
funluckykitty
5/1/2008 8:12:18 PM

 

This maybe a bit off on the syntax of how an output parameter is declared, but it's close.  It's bedtime where I'm at! 

 

CREATE PROCEDURE sp_InsertConflictSearch

(@SearchTypeID int
,@RequestingAttorney int
,@PotentialClient varchar (2000)
,@OtherParties varchar(2000)
,@NamesToSearch varchar(2000)
,@OtherRelatedToSearch varchar(2000)
,@ClientToSearch varchar(100)
,@EntitiesToSearch varchar(2000)
,@ExistingBatchNumber varchar(100)
,@AdditionalNames varchar(2000)
,@SearchStatusID int
,@SubmittedBy  varchar(50)

,@SearchID int output -- syntax maybe wrong, You want this to be an output parameter.
)

AS
BEGIN

INSERT INTO.tConflictSearches (SearchTypeID, RequestingAttorney, PotentialClient, OtherParties, NamesToSearch, OtherRelatedToSearch, ClientToSearch, EntitiesToSearch, ExistingBatchNumber, AdditionalNames, SearchStatusID, SubmittedBy)

VALUES (@SearchTypeID, @RequestingAttorney, @PotentialClient,@OtherParties, @NamesToSearch, @OtherRelatedToSearch, @ClientToSearch, @EntitiesToSearch, @ExistingBatchNumber, @AdditionalNames, @SearchStatusID, @SubmittedBy)

SET @SearchID = scope_identity()

END
GO


0
david
5/1/2008 8:53:35 PM

So, once I've declared the output parameter, how do I set it's value equal to a label in my search.aspx.vb? 


Never make important decisions on a Monday!
0
funluckykitty
5/2/2008 1:07:33 PM
Reply:

Similar Artilces:

How to add several form field values to email being sent in vb.net?
I have a form that sends an email when the submit button is clicked.  I'd like to include the values from the form into the text of the email, but I don't know how to do that.  Below is the code I have to send the email... How do I add all the form values to the message text  (mm.Body) and include line breaks or spacing to layout the form values for easy reading?  Protected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.ClickConst ToAddress As String = "foo@foo.com"Dim mm As New MailMessage("foo@foo.com&q...

Visual Studio .Net? Visual Basic .Net?
Okay, I admit it -- I know nothing about .Net. I'm a Visual Basic and ASP developer. DotNetNuke is my first tentative step into the whole ".Net Thing", and I'm looking for some advice... What do I really need to be able to make some modifications, or new modules for DotNetNuke? Do I need the full, THOUSAND DOLLAR Visual Studio .Net 2003 package, or can I get away with the HUNDRED DOLLAR Visual Basic .Net Standard package?Chameleon Systems, Inc. You can use webmatrix, available here for free. One of the drawbacks of webmatrix however is that it does not support code-be...

Visual Basic.NET or Visual Studio.NET?
OK, this has probably been asked before... I'm a student looking to cut my costs, and I can do this by just purchasing Visual Basic.NET as opposed to the fully-fledged Visual Studio.NET application. Of course, I'll only be able to program in VB, but that's fine with me. I wanted to know if all of the ASP.NET specific options are available, such as using a WYSIWYG editor? I'd also like to know if VB.NET has IntelliSense, or that's a specific option only available to Visual Studio.NET users. Thanks in advance. I'd have to believe that VB.Net has intellisense. What is the price d...

very basic question: how can I insert a Null value for a DateTime value? Thanks
 Hello,I declare a DateTime value in my code which I want to insert into a DateTime field in the SQL Server 2005 database.But I am not able to insert the NULL in the datetime field. Everytime I initialize a DateTime filed it automatically initializes it with some value like '1/1/0001' which I do not want. I want to insert NULL into the database.How can i achieve it?Thanks.   How about making the variable nullable so it will allow null values.C# <---> VB.Net Translator how do you do that...do you have an example?  What is your VB.NET or C# code. You may...

Can I go from VS .NET 2003 to Visual Basic .NET 2003?
Hi, I just developed a commercial application using the trial version of Visual Studio .NET 2003.  My trial is about to expire.  I was hoping .NET 2.0 would be released by now, but that is obviously months away so I'm wondering if I could save a few hundred bucks by buying Visual Basic .NET 2003(around $110 i think) instead of having to buy Visual Studio .NET 2003, which I believe is up around $600 or so.   All my code is Visual Basic, so that's why I'm thinking that's all I need.  And it's mostly web development that I do if that matters.  T...

Visual Basic.NET or Visual Studio.NET? #2
I am currently using the Web Matrix to develop ASP.NET applications, but I am keep to upgrade to VS.NET. However, I can't justify spending $1,000 at the moment. I then found the MUCH cheaper "Visual Basic .NET 2003 Standard Edition" which is only $100. I have very little C# knowledge, and use Basic almost all the time. Would this be a good tool to use for web development? Does it support all the code behind stuff that VS users love so much? Thanks!! I use "VB.NET Standard Edition" and I'm happy with it. It has exactly the same features as Visual Studio.NET. Th...

how can i caonvert positive value (in integer) to a negative value?
for example i have the number 4 i want to get the number -4 ? this wayint a = 1; a = -a;MAKMark as Answer if this reply helps youMVP ASP/ASP.NetASP.Net Hosting : Host DepotMy Site : ASPSnippets int a = 4 a= a - a*2 ie. a=4 a*2 = 8 4-8 = - 4  Now a becomes -4ByPT.SivakumarIndia...

Get Scope Identity Value using ObjectDataSource and Vb.Net
Hi, I have been trying to get the scope Identity after inserting a record using an ObjectDataSource. I can't find what event, or how to get the value that the scope identity returns. Here is my Sproc. ALTER PROCEDURE dbo.[YourCompany_LanCustomer_Insert]     (     @DNNUserID int,     @FirstName nvarchar(50),     @LastName nvarchar(50),     @Address nvarchar(50),     @Address2 nvarchar(50),     @City nvarchar(50),     @State nvarchar(50),    ...

Can I install Visual Basic 6 on my machine which has VS.NET 2003 & VS.NET 2005?
I may have a need to use Visual Basic 6 for a legacy project in the near future.  I was wondering if I can install VB6 on my machine which already has VS.NET 2003 & VS.NET 2005 installed on it? I did not know if doing so would cause issues or not so thought I would ask before just leaping and doing.   Ira Richard Smith At one time i had to install Visual Basic 6 on my machine with VS.NET 2003 and had "NO" issues. I would suggest take a backup of your machine or a restore point before you proceed. Please post your experience. I am sure some one would appreci...

Insert NULL value into datetime field of Oracle database
 Hi all,I have a form which has a textbox where a date is entered by the user - this field is not compulsory so if it is left blank I want to store it in the table as nothing. However, when I use the code below it enters 01/01/0001 instead of nothing. I have tried various other options Endate.MinValue and EndDate = System.DBNull.Value (this throws an error)Im stumped. Anyone have any ideas as to how I can enter a blank value into a date field in my oracle table? Would prefer to do it before I go near the sql Insert (so in my vb.net code) because thats done using a stored procedure and w...

sql stored procedure
I have stored procedure which contains follwing part of it. it says syntax when i worte line to get @@identity valuewhen delete that  line command succesful. but i need to get @@identity from the insert statement and assign it to a variable and use it after any body pls tell me how to get this within a stored prosedure or what is the error of the following code bit.  (#tblSalesOrders is a temporary table which containsset of  records from orginal table )DECLARE @soNo1 INT  DECLARE @CursorOrders CURSOR SET @CursorOrders = CURSOR FAST_FORWARD...

How to get the value of a textbox that has been added to a template field in a gridview. VB.Net
I have a gridview that shows products, description, price. I have added a Buttonfield and a TemplateField that has a Textbox with the id QuantityTextBox.What I am trying to do is get the value of the Quantity TextBox in the RowCommand event of the gridview. I can get the price but need to get the quantity also. To get the price I used ItemPrice = Server.HtmlDecode(row.Cells(5).Text) My template field is cell 6 but the above routine does'nt work. Any help would be greatly appreciated.Dan5150www.5150creations.com Hiuse this: VB:  Protected Sub GridView1_RowCommand(ByVal ...

Visual Basic .NET 2003 vs Visual Studio .NET 2003
Hi, If im going to be coding my ASP.NET in just VB.NET then would I be able to go for the cheaper option of buying Visual Basic .NET 2003 ?? - Carl S Yes.Starting with ASP.NET 2.0? Look at:Programming Microsoft Web Forms My Blog Are there any limitations if I was doing my ASP.NET with VB.NET in Visual Basic .NET 2003 than if I was using the full Visual Studio .NET 2003 ?? Or are the only advantages of Visual Studio .NET 2003 in that it supports more programming languages ?? - Carl S You should check the MS Web site, but I believe that, given an edition (Standard, Professional...

How can I add 2 fields value to 1 fields value in a GridView?
Hi all!I have one access database named=Medlemmar.mdb and Tablename=TidBokning and with some columns ex. "Bokad_For" and "Plats".Let say that "Bokad_For" contains value "Group2" and "Plats" contains value "Stockholm".Now I want to make this 2 value to 1 fileds like this: "Bokad_For" contains value "Group2Stockholm"How can I do that?I use asp.net 2.0 and C#.I tried with Eval("Bokad_For") and somthing, but Eval works only in Templates (I guess) I cant get Templates to work in my GridView.i'm newbee in this and think I need some help here :-) ?Thanks for all help!Micael Roxenhall You can sol...

Web resources about - How do I get the Identity field value after a sql insert so the value can be included in the email sent upon submit? - asp.net.visual-basic.net

Identity of the first male Muslim - Wikipedia, the free encyclopedia
There is some disagreement among Muslims , and among historians of Islam , as to the identity of the first male convert to Islam after Muhammad ...

Birth certificate mix-up sparks identity fraud fears
Parents of newborn children have been mailed birth certificates for other peoples' babies in a mix-up described as a major privacy breach.

Birth certificate mix-up sparks identity fraud fears
Human error has led to nine parents being sent&nbsp;birth certificates for other peoples' babies, in what has been described&nbsp;as a major ...

'It's time for Aboriginal identity and story to be embraced by all Australians'
Some places in Perth were named after governors or other European elites, wives and family members or places in Britain. Frankly, many names ...

Case of mistaken identity: No, Iyad El-Baghdadi is not the leader of Daesh
Case of mistaken identity: No, Iyad El-Baghdadi is not the leader of Daesh

These simple mistakes could compromise your identity on Facebook
... Facebook account. However, Swiped author Adam Levin says there’s one simple mistake many Facebook users make that might compromise their identity ...

How an IRS agent discovered the identity of the Silk Road kingpin using Google searches
... high-tech wizardry to catch a cyber criminal. At least that’s the lesson from a young IRS agent, Gary L. Alford, who first uncovered the identity ...

NYPD: 4 tips to keep your money and identity safe
Worried about financial fraud and ID theft? Follow these guidelines courtesy of New York's finest

Lucasfilm Confirms Star Wars: The Force Awakens Stormtrooper TR-8R's Identity
Lucasfilm teased that despite the shot from Chewbacca's almost impossibly powerful bowcaster [...]

Beware Of The Santa Scam: There’s Nothing Holly Jolly About Identity Theft
... best-case scenario involves a scammy site simply taking your money without providing the promised letter, while the worst-case includes identity ...

Resources last updated: 1/13/2016 12:53:05 AM