Need to get row id after insert. Can't get my syntax working.

 Hi,

I'm trying to get the identity of the row after an insert, but I can't get the syntax right.  Could someone with better eyeballs help me out?

 Here is my Insert stuff..

 

<asp:SqlDataSource ID="SqlDataSourceQueue" runat="server" 
ConnectionString="<%$ ConnectionStrings:ServiceQConnectionString %>"
SelectCommand="SELECT * FROM [queue] WHERE ([caseno] = @caseno)"
DeleteCommand="DELETE FROM [queue] WHERE [id] = @id"
InsertCommand="INSERT INTO queue (caseno, contact, customer, machine, problem, phone, serialno, down, csrep, entered, returned, answer_csrep) VALUES (@caseno,@contact,@customer,@machine,@problem,@phone,@serialno,@down,@csrep,@entered,@returned,@answer_csrep); SELECT @MyID = @@IDENTITY"
UpdateCommand="UPDATE [queue] SET [caseno] = @caseno, [contact] = @contact, [customer] = @customer, [machine] = @machine, [problem] = @problem, [phone] = @phone, [serialno] = @serialno, [down] = @down, [csrep] = @csrep, [entered] = @entered, [returned] = @returned, [answer_csrep] = @answer_csrep WHERE [id] = @id"
>
 
 

Here is the code that is trying to grab the id:

 

Private LastInsertID As Integer

Protected Sub
SqlDataSourceQueue_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSourceQueue.Inserted
Try
LastInsertID = e.Command.Parameters("@MyID").Value
Response.Write("Queue ID # " + LastInsertID.ToString + " has been entered")

Catch ex As Exception
Response.Write("Can't write @MyId")

End Try

End Sub

 

Please advise how to fix this.  Thanks!

 

Mark

 

 

 

0
aquinasadmirer
12/1/2008 4:17:46 PM
asp.net.web-forms 93655 articles. 6 followers. Follow

5 Replies
1072 Views

Similar Articles

[PageSpeed] 36

Hi there,

if you append

SELECT SCOPE_IDENTITY()

at the end of the insert statement, you should be able to get it. Also consider having a stored procedure. Consider the seperation of business logic layer and data access layer. That will surely make your life very easy.

 Thanks, Mal.

0
suhash
12/1/2008 4:25:20 PM

I would advise that you convert the SqlDataSource InsertCommand to a stored procedure as it need to execute two query to get the result

doing this you also need to set the SqlDataSource InsertCommandType="StoredProcedure"

Also set a parament in the SqlDataSource <InsertParameters>  that reference the id as an output parameter.




~ Remember to mark as Answer, if it solves your question ~
0
dj
12/1/2008 4:35:57 PM
suhash:

Hi there,

if you append

SELECT SCOPE_IDENTITY()

at the end of the insert statement, you should be able to get it. Also consider having a stored procedure. Consider the seperation of business logic layer and data access layer. That will surely make your life very easy.

 Thanks, Mal.

 

I tried adding it at the end like this:

   

InsertCommand="INSERT INTO queue (caseno, contact, customer, machine, problem, phone, serialno, down, csrep, entered, returned, answer_csrep) VALUES (@caseno,@contact,@customer,@machine,@problem,@phone,@serialno,@down,@csrep,@entered,@returned,@answer_csrep); SELECT SCOPE_IDENTITY();" 

 
But it still doesn't work.

 I changed the code so I can see the error....

 

Protected Sub SqlDataSourceQueue_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSourceQueue.Inserted


Try
LastInsertID = e.Command.Parameters("@MyID").Value
Response.Write("Queue ID # " + LastInsertID.ToString + " has been entered")

Catch ex As Exception
Response.Write("Can't write @MyId. The error is: " & ex.Message.ToString)

End Try
End Sub

 And I get this error:

 Can't write @MyId. The error is: Conversion from type 'DBNull' to type 'Integer' is not valid. 

Please advise...

 

PS Where can I read a tutorial about separating business logic and data access.  I found one, but I ran into a wall after getting into the tutorial.  Any suggestions?

 

PPS I made the suggestion to my boss about the stored procedures a month or so; I can't get write permission on the server *sigh* .

 

 

0
aquinasadmirer
12/1/2008 5:14:04 PM

hi there..

check out the following post

http://www.dreamincode.net/forums/showtopic32421.htm

very easy steps + written in vb.net.

 What I try to avoid is returning data tables to the presentation layer. if you adhere to this concept you will understand what BLL means. Put all the calculations etc etc on classes and use aspx to display only.

Thanks, Mal.

0
suhash
12/1/2008 5:31:45 PM

Hi All,

 I found the answer; here is what I did.  I appended

 SELECT @MyID = SCOPE_IDENTITY(); to the InsertCommand, and added an InsertParameter entry.
 The handling subroutine is included for completeness. 
 Thanks for help.
-Mark 

 Note: Changes are bold and underlined:

  

<asp:SqlDataSource ID="SqlDataSourceQueue" runat="server" 
ConnectionString="<%$ ConnectionStrings:ServiceQConnectionString %>"
SelectCommand="SELECT * FROM [queue] WHERE ([caseno] = @caseno)"
DeleteCommand="DELETE FROM [queue] WHERE [id] = @id"
InsertCommand="INSERT INTO queue (caseno, contact, customer, machine, problem, phone, serialno, down, csrep, entered, returned, answer_csrep) VALUES (@caseno,@contact,@customer,@machine,@problem,@phone,@serialno,@down,@csrep,@entered,@returned,@answer_csrep); SELECT @MyID = SCOPE_IDENTITY();"
UpdateCommand="UPDATE [queue] SET [caseno] = @caseno, [contact] = @contact, [customer] = @customer, [machine] = @machine, [problem] = @problem, [phone] = @phone, [serialno] = @serialno, [down] = @down, [csrep] = @csrep, [entered] = @entered, [returned] = @returned, [answer_csrep] = @answer_csrep WHERE [id] = @id"
>
  
<InsertParameters>
<asp:Parameter Name="caseno" Type="String" />
<asp:Parameter Name="contact" Type="String" />
<asp:Parameter Name="customer" Type="String" />
<asp:Parameter Name="machine" Type="String" />
<asp:Parameter Name="problem" Type="String" />
<asp:Parameter Name="phone" Type="String" />
<asp:Parameter Name="serialno" Type="String" />
<asp:Parameter Name="down" Type="String" />
<asp:Parameter Name="csrep" Type="String" />
<asp:Parameter Name="entered" Type="DateTime" />
<asp:Parameter Name="returned" Type="DateTime" />
<asp:Parameter Name="answer_csrep" Type="String" />
<asp:Parameter Name="MyID" Direction="Output" Type="Int32" />
</InsertParameters>
 
Private LastInsertID As Integer

Protected Sub SqlDataSourceQueue_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSourceQueue.Inserted


Try
LastInsertID = e.Command.Parameters("@MyID").Value
Response.Write("Queue ID # " + LastInsertID.ToString + " has been entered")

Catch ex As Exception
Response.Write("Can't write @MyId. The error is: " & ex.Message.ToString)

End Try
End Sub
 
 
 
 

 

0
aquinasadmirer
12/1/2008 7:47:02 PM
Reply:

Similar Artilces:

I can't get no cache, I can't get no cache. 'Cause I try and I try and I try and, I can't get no, I can't get no cache.
I have fiddled out for days tinkering with the setting in about:config trying to get FireFox 12 to use the disk cache. I have NOT found the trick. This One Trick Pony ain't doing it like it used to up until recently. Pray tell anyone, What information can I share that will point a knowledgeable person to aid me in getting FF to disk.cache? I have also tried restarting FF with add-ons disabled, (There were NOT too many to do this to, so it was quick and easy) Here is my stab at trying to convey the information that MIGHT govern matters Using about:cache Informatio...

Re: I can't get no cache, I can't get no cache. ' Cause I try and I try and I try and, I can't get no, I can't get no cache.
<div>please com e see me about htis<br /> <br /> ------- Original Message f= rom the Global Relay Archive -------<br /> From: Hp &lt;ferd@farkel.net&gt;= <br /> To: "support-firefox@lists.mozilla.org" &lt;support-firefox@lists.mo= zilla.org&gt;<br /> Sent: Sun, 27 May 2012 18:30:18 -0700<br /> Subject: I = can't get no cache, I can't get no cache. 'Cause I try and I try and I try = and, I can't get no, I can't get no cache.<br /> <br /></div> <pre class=3D"gr-maex-body-pre&qu...

Can not get Pickup Directory... .net can't access metabase maybe??? Need to get working ASAP
I am using 2.0 to try to send an email to the IisPickup directorySystem.Net.Mail.MailMessage message = new System.Net.Mail.MailMessage(test@testing.com, "test@test.com", "Testing " + DateTime.Now.ToString(), "Testing the Mail Drop Folder");System.Net.Mail.SmtpClient client = new System.Net.Mail.SmtpClient("127.0.0.1");client.DeliveryMethod = System.Net.Mail.SmtpDeliveryMethod.PickupDirectoryFromIis; client.Send(message);Example Site : www.andrewworral.com (hit the button)This code worked on our old deployment server but not on our new server.  I s...

Can't get it to work. Can't get help.
Name: Tony Clarke Email: minionataondotat Product: Thunderbird Summary: Can't get it to work. Can't get help. Comments: Installed T'bird. Had to change defunct server that T'bird selected by default. Kept asking for passwords associated with previous email (Outlook Express ), when passwords entered then got error messages saying aon responded with 're-authentification failure'. Searched your knowledge base & help forums to no avail. Found them very user-unfriendly & ended up more confused than before. Would like to try T'bird, but I think ...

Two things,Why do I keep getting emails that I don't want. can't get rid of them? Why can't I get my email when I am out of town? Or can I?
Name: Ed Leech Email: ELCraftatzoominternetdotnet Product: Thunderbird Summary: Two things,Why do I keep getting emails that I don't want. can't get rid of them? Why can't I get my email when I am out of town? Or can I? Comments: I am getting frusted with all the emails coming in that I do not want, I am using the tools to get thme out but they keep coming. They just use different names or whatever. I have been thinking of just switching to something else but my business intrusts know this email and it is tooo confusing to change. Every time I go out of town on bu...

Can't get the Web Controls to work.. The Build.Bat file doesn't work? HELP>> ARGGG>.
Hi.. I was wanting to use the IE Web Controls, the Tab Strip, etc, and play with it.. Since I have IIS 5, I thought I'd copy them there using the Read me.txt instructions and then copy to a dev server that I use at a web host.. Well, everything worked except the Build file doesn't build the Microsoft.UI.Webcontrols.DLL file? When I try to run it by double clicking on it, it runs really fast in  the DOS window, but I can't see what it says and it closes.. So I tried to do so manually and I received an error that the "cse.exe is not recognized as an internal or external command or batch...

Can't get an INSERT command to work with form parameters
So I am using a SqlDataSource to insert a record into an Access DB.  I am using text boxes as the insert parameters.  The code is included below.  I can't figure it out, please help!<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Investor DatabaseConnectionString %>"            ProviderName="<%$ ConnectionStrings:Investor DatabaseConnectionString.ProviderName %>"         &nbs...

update fails, can't get out of it and now can't get my email
Name: martha Email: mjhartleyatchampmaildotcom Product: Thunderbird Summary: update fails, can't get out of it and now can't get my email Comments: The directions about bugging aren't helpful to me because I don't know what you're talking about. You offered an update, I tried it, now it is in an endless loop of trying to install, failing, and trying again. It is very frustrating because I can't even get into my old email files to get addresses, photos, etc. I have turned off my firewall and have rebooted the computer, per some of the suggestions in the...

2010: Can't register - can't download the software - can't register until I have the software
I have active maintenance on Rad Studio: From: Vicky Rassmisaengthong [mailto:Vicky.Rassmisaengthong@EMBARCADERO.COM] Sent: Wednesday, February 25, 2009 1:58 PM To: rgrossman Cc: amer.supportadmin@codegear.com; Ashley Cosentino Subject: Software Assurance Support for Tech III Inc PO# Credit Card Dear Embarcadero Technologies Support Customer, Welcome! You have been registered as the primary contact on support account number AM####### Herewith we confirm your Embarcadero Technologies support agreement covering: Qty 1 RAD Studio Enterprise Named User licens...

It's all very fuzzy now...can make web request(?) but definately can't get web response from web service
Alas...trying to get a web service response from a web service that is part of the web site in Visual Studio.Net 3.5 appears to be impossible.  Infact, I've been ALL over the internet over the past couple of days looking at all types of fragmentory examples in all types of Microsoft languages to no avail. =========QUESTION:=========1.  How do I call a web service (the function to call is "WebService_BulkData") so that my aspx page will send it a SOAP request and print out the web service response data to the page?  The aspx page AND the web service are part of t...

Can't get through init 3 from init 5 and can't get through init 5 from init 3.
I tried opensuse 12.1 Gm today. Installation was perfect with xfce 4.8 until I tried installing the nvidia driver. In runlevel 5 as superuser I tried to shift to runlevel 3 and was welcome with a black screen, I have to use ctrl-alt-delete to reboot. During the reboot I used runlevel 3 and I was able to install the nvidia driver. After the nvidia driver installation I tried to shift to runlevel 5 and there, I was welcome with a black screen with a stationary blinking x cursor. I rebooted once again wiith runlevel 5 to get the gui. Any one had seen this happened? This installation is...

Desperate!!!! -- can't get DNN4 with sqlexpress2005 to work on winxp --- can't connect to DB!!!!!
HELP!!!!! HELP!!!!I followed the install guide for DNN4 (install version)...but it doesn't work with SQL 2005 express on Win XP...i have tried everything!! below is the error message and the connection strings...HELP!!!   Error Installing DotNetNukeCurrent Assembly Version: 04.00.00ERROR: Could not connect to database specified in connectionString for SqlDataProviderSystem.Data.SqlClient.SqlException: Unable to open the physical file "C:\dotnetnuke\App_Data\dotnetnuke.mdf". Operating system error 5: "5(Access is denied.)". An attempt to attach an auto-named database for file C:\dotne...

Debian Woody, two problems: can't get kppp to work and can't su in X
Hello, I have Debian Woody installed on this Dell i8100 laptop via Libranet 2.0. I have two hurdles left that I can't seem to overcome. 1. I can't get kppp to work. I always get the following error message: Apr 27 19:38:48 libranet pppd[622]: The remote system is required to authenticate itself Apr 27 19:38:48 libranet pppd[622]: but I couldn't find any suitable secret (password) for it to use to do so. Apr 27 19:38:48 libranet pppd[622]: (None of the available passwords would let it use an IP address.) Exit Status From 'man pppd': 1 An immediately f...

Can't get the source stepping to work with the System.web.pdb's
it used to worki'm running 3.5ps and vs2008 sp1...I've read thishttp://blogs.msdn.com/sburke/archive/2008/01/16/configuring-visual-studio-to-debug-net-framework-source-code.aspxI've read thishttp://www.microsoft.com/whdc/devtools/debugging/debugstart.mspxI've deleted/restarted/deleted so many times with no success..My  mscorlib.dll    is2.0.50727.3082 (QFE.050727-3000)    11/24/2008 9:47 PMWhich I realize is above 2.0.50727.1433So maybe it's just doomed to not work right now.... Maybe the pdb for it just isn't available..It downloads...

Web resources about - Need to get row id after insert. Can't get my syntax working. - asp.net.web-forms

Resources last updated: 12/17/2015 4:12:40 AM