Last post Sep 20, 2011 09:58 AM by limno
Sep 13, 2011 08:17 PM|asplearning|LINK
Hello everyone, just one to have a question on how I can retreive a value from a table immediately after it is inserted. I got the error "Invalid attempt to read when no data is present" when I use simple select like this.
ALTER PROCEDURE usp_Procedure1 ( @FirstName nvarchar(30) ,@LastName nvarchar(30) ,@Email nvarchar (30) ) AS
Insert into Table1 (FirstName, LastName, Email)
Select FirstName from Table1 where Email=@Email
Any idea? Thank you.
Sep 13, 2011 09:37 PM|JVfromNY|LINK
This looks like a DataReader error message. What is the code you are calling this stored proc from?
Sep 14, 2011 04:01 AM|asplearning|LINK
Thanks for the reply. Here is the code I used to call this:
Dim con As SqlConnection
Dim com As SqlCommand
Dim rdr As SqlDataReader
Dim ConString As String = System.Configuration.ConfigurationManager.ConnectionStrings("ConString").ToString
con = New SqlConnection(ConString)
com = New SqlCommand("usp_Procedure1", con)
com.CommandType = CommandType.StoredProcedure
This doesn't work. Any advice? Thanks.
Sep 15, 2011 05:09 AM|asplearning|LINK
Anybody please? I just want to insert and retreive a non identity value in one stored procedure. Any advice please?
Sep 15, 2011 09:38 PM|Decker Dong - MSFT|LINK
Hello, try this sample:
Dim con As SqlConnection Dim com As SqlCommand Dim rdr As SqlDataReader Dim ConString As String = "server=.;database=dbcreator;integrated security=sspi" con = New SqlConnection(ConString) com = New SqlCommand("usp_Procedure1", con) com.CommandType = CommandType.StoredProcedure com.Parameters.AddWithValue("@FirstName", "Yang") com.Parameters.AddWithValue("@LastName", "jack") com.Parameters.AddWithValue("@Email", "firstname.lastname@example.org") con.Open() rdr = com.ExecuteReader() rdr.Read() Console.WriteLine(rdr("firstname").ToString())
Sep 19, 2011 08:35 AM|rakeshgali|LINK
While rdr.Read() = True
Sep 19, 2011 09:23 AM|limno|LINK
The stored procedure part should be fine (but why need to retrieve another parameter is another question). You may have problems with your code to retrieve records through this stored procedure. You need to show your code in question. Thanks.
Sep 19, 2011 09:00 PM|Decker Dong - MSFT|LINK
Have you tried mine? I did a sample for you and everything goes well with me....
Sep 19, 2011 09:17 PM|asplearning|LINK
Sorry for the late reply cause was busy working on other functionalities.
Thank you very much for the answer. I noticed that in my original code, if I take out the com.executenonquery() and just leave the rdr=com.executereader(), that will aso works. Don't really know why. But appreciate your help very much.
Sep 20, 2011 09:58 AM|limno|LINK
You can have only one method for your SqlCommand at one time.