Getting Identity Of Inserted Record Using Inserted Event????
is there any way of getting the identity without using the "@@idemtity" in sql??? I'm trying to use the inserted event of ObjectDataSource, with Outputparameters, can anybody help me???
View Complete Forum Thread with Replies
Related Forum Messages:
Get @@IDENTITY Of New Inserted Record,
I am using VS.net (2003) SQLcommand TEXT. with input params. SQL server 2000 Can anyone tell me how to get the Identity value (field value, Idenity col) when you do an insert? I want to get the Identity value, then redirect the user to another page and use this identity value so they can update more (other) fields that are on that page. My code so far that works... but Where do I put @@IDENTITY ? How do I call or assign the @@IDENTITY value to a value in my aspx.vb code page? Question: how do I get the Identity value from the ID column. Question: How do I assign that value to some variable in code, say, assign it to (Session("App_ID")) = IdentityValueOrSomething?Help... --------------------- INSERT INTO App (AppName, Acronym, Description,bla bla bla bla........) VALUES (@AppName, @Acronym, @Description, bla bla bla bla........) ----------------------------- Private Sub btnAddApp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddApp.Click With cmdAddApp '.Parameters("@app_id").Value = Session("App_ID") .Parameters("@AppName").Value = txtAppName.Text .Parameters( bla bla bla............. .Parameters( bla bla bla............. .Parameters( bla bla bla............. End With Try cnAppKBdata.Open() cmdAddApp.ExecuteNonQuery() cnAppKBdata.Close() ''''''''''''''Session("App_ID") = whatever the @@IDENTITY is....'''''''''''''''''''''''''''?? Response.Redirect("AppUpdate.asp") Catch ex As Exception End Try End Sub Anyone have the lines of code that does this? Any advise or examples :) thanks you.
View Replies !
How To Retrieve Identity Of Just Inserted Record???
Ok I've been researching this for a day now and I'm not coming up with much. I want to store the auto-incrementing ID of the last inserted record in a session variable, so that I may put it in a foreign key column in another table, if the user wishes to fill out a form on another page. I think my stored procedure is correct. But don't know what code to add to my aspx page. Any help will be greatly appreciated. Here is my VB ScriptProtected Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim personalContactDataSource As New SqlDataSource()personalContactDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("DataConnectionString1").ToString() personalContactDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure personalContactDataSource.InsertCommand = "PersonalContactInsert" personalContactDataSource.InsertParameters.Add("FirstName", FirstName.Text) personalContactDataSource.InsertParameters.Add("LastName", LastName.Text)personalContactDataSource.InsertParameters.Add("KeyPerson", KeyPerson.Checked) personalContactDataSource.InsertParameters.Add("DayPhone", DayPhone.Text)personalContactDataSource.InsertParameters.Add("EveningPhone", EveningPhone.Text) personalContactDataSource.InsertParameters.Add("Fax", Fax.Text)personalContactDataSource.InsertParameters.Add("Email", Email.Text) personalContactDataSource.InsertParameters.Add("HomeAddress", HomeAddress.Text)personalContactDataSource.InsertParameters.Add("City", City.Text) personalContactDataSource.InsertParameters.Add("State", State.Text)personalContactDataSource.InsertParameters.Add("Zip", Zip.Text) personalContactDataSource.InsertParameters.Add("ReqEffectDate", ReqEffectDate.Text)personalContactDataSource.InsertParameters.Add("MRID", MRID.Text) personalContactDataSource.InsertParameters.Add("CurrentPremium", CurrentPremium.Text)personalContactDataSource.InsertParameters.Add("CurrentCarrier", CurrentCarrier.Text) personalContactDataSource.InsertParameters.Add("CurrentDeductible", CurrentDeductible.Text)personalContactDataSource.InsertParameters.Add("CurrentCoins", CurrentCoins.Text)personalContactDataSource.InsertParameters.Add("ReasonForQuote", ReasonForQuote.Text) End Sub And here is my Stored ProcALTER PROCEDURE dbo.PersonalContactInsert @FirstName varchar(30),@LastName varchar(30), @DayPhone varchar(14),@EveningPhone varchar(14), @Fax varchar(14),@Email varchar(60), @HomeAddress varchar(80),@City varchar(30), @State char(2),@Zip char(5), @KeyPerson bit,@ReqEffectDate smalldatetime, @CurrentCarrier varchar(30),@CurrentPremium smallmoney, @CurrentDeductible smallmoney,@CurrentCoins smallmoney, @ReasonForQuote varchar(150),@MRID int, @ClientNumber int OUT AS INSERT INTO PersonalContact(FirstName, LastName, DayPhone, EveningPhone, Fax, Email, HomeAddress, City, State, Zip, KeyPerson, ReqEffectDate, CurrentCarrier, CurrentPremium, CurrentDeductible, CurrentCoins, ReasonForQuote, MRID, DateTimeStamp) VALUES(@FirstName,@LastName,@DayPhone,@EveningPhone,@Fax,@Email,@HomeAddress,@City,@State,@Zip,@KeyPerson,@ReqEffectDate,@CurrentCarrier,@CurrentPremium,@CurrentDeductible,@CurrentCoins,@ReasonForQuote,@MRID, GetDate()) SET @ClientNumber = SCOPE_IDENTITY() RETURN
View Replies !
Dataset And Identity Of New Record Inserted
Hi, I have 2 tables in my database PrescriptionHeader and PrescriptionDetails.My PrescriptionHeader table has the following fields:PrescriptionID -identity fieldPatientID PatientfNamePatientlname PrescriptionDetails table has the following fields:PrescriptionDetailID -identityPrescriptionID -from PrescriptionHeader table MedicineDosage The function InsertPrescription inserts values into the table PrescriptionHeader. I want the same function to then insert the value of MedicineDosage into PrescriptionDetails with the same PrescriptionID inserted into PrescriptionHeader. How do I tell the function to insert the PrescriptionID that was automatically inserted into PrescriptionHeader also into table PrescriptionDetails . How do I return the identity before proceeding to insert into PrescriptionDetails table?ThanksFunction InsertPrescription(ByVal PatientID As String, _ByVal PatientFname As String, _ByVal Patientlname As String, ByVal MedicineDosage as String)Dim DBAdapter As SqlDataAdapterDim DBDataSet As DataSetDim SQLString As StringDim DBCommandBuilder As SqlCommandBuilderSQLString = "SELECT * FROM PrescriptionHeader WHERE PrescriptionId = ''"DBAdapter = New SqlDataAdapter(SQLString, DBConnection)DBDataSet = New DataSetDBAdapter.Fill(DBDataSet)Dim AddedRow As DataRow = DBDataSet.Tables(0).NewRow()AddedRow("PatientID") = PatientIDAddedRow("PatientfName") = PatientFnameAddedRow("Patientlname") = PatientlnameDBDataSet.Tables(0).Rows.Add(AddedRow)DBCommandBuilder = New SqlCommandBuilder(DBAdapter)DBAdapter.Update(DBDataSet) End Function
View Replies !
Returning Timestamp From Inserted Record Using @@Identity
i recently found a little error in a stored procedure that was included in a project handed over to me.... the sp was rather simple. it just inserted a record into a table and returned the identity and the timestamp as follows IF @@ERROR>0 BEGIN SELECT @int_InterventionID = 0 RETURN @@ERROR END ELSE BEGIN SELECT @int_InterventionIDReturned = MAX(InterventionID) FROM tblIntervention SELECT @ts_TimestampReturned = [Timestamp] FROM tblIntervention WHERE InterventionID = @int_InterventionIDReturned SELECT @int_InterventionID = @int_InterventionIDReturned, @ts_Timestamp = @ts_TimestampReturned RETURN 0 END i figured that it should be using @@Identity for the interventionIdentity rather than max(InterventionID) so i changed to... IF @@ERROR>0 BEGIN SELECT @int_InterventionID = 0 RETURN @@ERROR END ELSE BEGIN SELECT @int_InterventionIDReturned = @@IDENTITY SELECT @ts_TimestampReturned = [Timestamp] FROM tblIntervention WHERE InterventionID = @int_InterventionIDReturned SELECT @int_InterventionID = @int_InterventionIDReturned, @ts_Timestamp = @ts_TimestampReturned RETURN 0 END it returns the @int_InterventionIDReturned but the timestamp now comes back as null??? why?? how can i ensure that i always get the timestamp of the record it has just inserted any help greatly appreciated, Cheers, Craig
View Replies !
SqlDataSource Inserted Event Appears To Execute Twice!
Hello I have a piece of VB.NET code that generates an email on the SqlDataSource Inserted event. It appears to be executing twice because it is sending two emails. If I place the code on any other event, it just sends the one email. Does any have a suggestion on how to handle this? Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted Dim MailServerName As String = "alvexch01"Dim Message As MailMessage = New MailMessage Message.From = New MailAddress("sender@email.com")Message.To.Add("receiver@email.com") Message.Subject = "Near Miss" Message.Body = "Test" Message.IsBodyHtml = True Message.Priority = MailPriority.NormalDim MailClient As SmtpClient = New SmtpClient MailClient.Host = MailServerName MailClient.Send(Message) Message.IsBodyHtml = True Message.Dispose() End Sub
View Replies !
DLink -get Last Row Inserted Identity
hello everyone, I am using DLink (Link for Database) in visual studio 2008.does anyone knows how to get the identity of an inserted row?in sql server in stored procedure we used to write @@identitybut how do we do it in DLink.??? if we don't want to call a stored procedure
View Replies !
Getting Identity/Serial Of Row Just Inserted?
This isn't so much purely a SQL Server question as a question on ASP.NET VB technique. In particular, I have a situation where I am either inserting a NEW row for a "profile table" (name, email, etc.) or Updating an existing one. In both cases, I need to create a new row in a related table which has the identity/serial column of the parent table as the primary key for the data to be inserted into this subsidiary table (for which there may be many rows inserted, all tying back to the parent). At the time I do the update, of course, I have the identity/serial of the "parent" so it's easy to update/insert. However, if the profile is NEW, I need to capture the identity/serial which was inserted so as to use it for the child table insert. (I remember a call to an obscure function which was -- essentially -- "give me the identity/serial of that which was just INSERTed" but I am unable to locate equivalent functionality. (I have searched various online help files for "Insert serial", "Insert identity" and the like with no results. Hints? Mahalos in advance ... :) KevInKauai
View Replies !
Getting Last Inserted Identity Values
i have a website that accepts users on it. first the user will apply to make use of my site and the data that he supplied will be put to account table. my problem is how can i get the last inserted identity value lets say id, to create the id of the person applied by simply incrementing it... i dont want to use the built in function of the sql server. can anybody help me of this process. asap...
View Replies !
Returning Inserted Row Identity ID
I am inserting a record by calling a stored procedure in my asp.net code. I need to return the identity field from the insert to use elsewhere in my code. I have found many things regarding this but nothing has worked. Here is my code that works for the insert.... conClasf.Open() strSQLInsert = "Exec spInsNewCAR '" _ & calDateInitiatedPopup.SelectedDate _ & "', '" & calResponseDatePopup.SelectedDate _ & "', '" & elbPartName.Selectedtext _ & "', '" & elbPartName.Item(elbPartName.SelectedValue).Text2 _ & "', '" & calSupplyDatePopup.SelectedDate _ & "', '" & elbMRBApproval3.Selectedtext _ & "'" cmdAd = New OleDbCommand(strSQLInsert, conClasf) cmdAd.ExecuteNonQuery( ) conClasf.Close Here is the last part of my stored procedure. BTW, I have added Scope_Identity to the end of my sp and when I open my sp up, it is not there...not sure what that is about. I guess my main problem is what command do I use in ASP.net to execute the sp AND hold my returned value... Thanks, JOE
View Replies !
Getting Last Identity Value Inserted In A Table
Suppose we have a table with an identity field. Someone adds a record and wants to retrieve the value of the identity field from the just added record. I have seen code like this: BEGIN TRAN .... INSERT data select newlyAddedID from table END TRANS In other words, they think that surrounding the INSERT and SELECT in a transaction insures they will get the correct value (even when other apps are updating this table). Is this approach sound? TIA, barkingdog
View Replies !
Need To Get The ID Of The Last Record That Was Inserted
I've seen a lot of info on how to do this with @@IDENTITY and SCOPE_IDENTITY(), but can't get this to work in my situation. I am inserting a record into a table. The first field is a GUID (UNIQUEIDENTIFIER) that uses newid() to generate a unique GUID. Since I am not using an int, I can't set the IsIdentity property of the field. Without IsIdentity set, @@IDENTITY and SCOPE_IDENTITY() do not work. How can I get the ID (or whole record) of the last record that I inserted into a SQL database? Note that I am doing this in C#. As a last resort, I could generatate the GUID myself before the insert, but I can't find C# code on how to to this.
View Replies !
Getting An Int Id From An Inserted Record
Hello,I would like do an insert into a table. The table has an autoincrimenting unique int id. After I do the insert how do i get theunique int id of the record that I just inserted? Is there a straightforward way of accomplishing this?Thanks,Billy
View Replies !
Records Being Inserted Into Gaps Of Identity Col
I have a table with the following specifications: [FutureArticleId] [bigint] IDENTITY(1,1) NOT NULL, [cFutureArticleTitle] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [cDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [gCategoryId] [bigint] NOT NULL, [cKeyword] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [bIsDeleted] [bit] NOT NULL CONSTRAINT [DF_tbl_FutureArticle_bIsDeleted] DEFAULT ((0)), [tOnCreated] [datetime] NOT NULL, [tOnUpdated] [datetime] NULL, [gCreatedBy] [bigint] NOT NULL, [gUpdatedBy] [bigint] NULL, After some insertions and deletion from the UI, obviously there are gaps in the FutureArticleId column which is an identity column. However, sometimes while inserting the records the records are being inserted into the gap and not in the next available Identity value. Is there any table setting which I need to do, to stop this. Please let me know Thanks Ankit
View Replies !
Get Primary Key Of Last Inserted Record
Ok I know this might not be the most accurate place to post this but I know someone here has an answer for me on it.I need to get the product_ID of the new record that is created by this insert statement INSERTINTO products ( class_ID,category_ID,product_name,product_desc,product_image,product_dimension,product_o1,product_o2,product_o3,product_ac,product_ph,product_photo ) SELECT class_ID,category_ID,product_name,product_desc,product_image,product_dimension,product_o1,product_o2,product_o3,product_ac,product_ph,product_photo FROM productsWHERE product_ID = @productID
View Replies !
Duplicate Inserted Record
Hi EverybodyThis Code duplicate the record in the database, can somebody help me understand why that happen. Thanks a LOT CompanyName: <asp:textbox id="txtCompanyName" runat="server" /><br />Phone:<asp:textbox id="txtPhone" runat="server" /><br /><br /><asp:button id="btnSubmit" runat="server" text="Submit" onclick="btnSubmit_Click" /><asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:dsn %>" insertcommand="INSERT INTO [items] ([smId], [iTitleSP]) VALUES (@CompanyName, @Phone)" selectcommand="SELECT * FROM [items]"> <insertparameters> <asp:controlparameter controlid="txtCompanyName" name="CompanyName" /> <asp:controlparameter controlid="txtPhone" name="Phone" /> </insertparameters></asp:sqldatasource> VBPartial Class Default2 Inherits System.Web.UI.Page Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click SqlDataSource1.Insert() End SubEnd Class ----------------------------------------------Yes is an Identity the Primary Key of the Table items
View Replies !
Retriving The ID Of The Last Record Inserted
I would appreciate help with retriving the ID of the last record inserted. Have spent considerable time in forums and google but can't find anything that works for me. Here is my VB Code Dim queryString As String = "INSERT INTO [DUALML] ([UseriD], [Company]) VALUES (@UseriD, @Company)" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection Dim dbParam_useriD As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_useriD.ParameterName = "@UseriD" dbParam_useriD.Value = useriD dbParam_useriD.DbType = System.Data.DbType.Int32 dbCommand.Parameters.Add(dbParam_useriD) Dim dbParam_company As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_company.ParameterName = "@Company" dbParam_company.Value = company dbParam_company.DbType = System.Data.DbType.[String] dbCommand.Parameters.Add(dbParam_company) Dim rowsAffected As Integer = 0 dbConnection.Open Try rowsAffected = dbCommand.ExecuteNonQuery Finally dbConnection.Close End Try Return rowsAffected End Function
View Replies !
Get PK For Inserted Record In SQLdatasource
I have a table named invoice that contains the following columns-invoiceno - Primary key and is set to autonumber-customerno-incoicedateand on my VB code i did the following InsertCommandSqlDataSource1.InsertCommand = "INSERT INTO invoice(customerno, invoicedate) VALUES('" & Session("UID") & "', GetDate()) "SqlDataSource1.Insert()My Question is how do i get the Primary Key Value it generated during the insert operation(invoice['incoiceno'])? Besides the creationg a stored procedere like the one in the MSDN Library
View Replies !
Get Last 7 Days Inserted Record
Hi All, i have a database, and every time when any row enter , it capture the date and time of insertation, then does any function or query available, which help to investigate me, today inserted record,yesterday inserted record,last 7 days inserted record,last 30 days inserted record and last year inserted record.. i am using asp.net and MS Access... Any idea thanx in advance... sajjad
View Replies !
How Do I Trace Who Inserted A Particular Record?
Is it possible to retrieve the user name or host name of the user that inserted or updated a particular record? Is this information stored in the database's log file, in hidden fields, or anywhere else? I know I can easily add a user_name field and use triggers to add this information for new records, but I need this information for records already existing in the table. Thanks. Jason
View Replies !
Cannot Read A Record Just Inserted
Hi, I have a small app which has the following statements: Insert into tableA (ID, Name) Values (iID, vName) Select * from tableA where ID = iID where iID and vName are variables. If I run 100 instances of this app, each inseting mutually exclusive IDs, the second statement may occasionally return no record. Why ? By the way, I use OLEDB command object and didn't specify any transaction isolation level (use the default in OLEDB). Any feedback would be greatly appreciated. Thanks. Ray
View Replies !
Cannot Read A Record Just Inserted
Hi, I have a small app which has the following statements: Insert into tableA (ID, Name) Values (iID, vName) Select * from tableA where ID = iID where iID and vName are variables. If I run 100 instances of this app, each inseting mutually exclusive IDs, the second statement may occasionally return no record. Why ? By the way, I use OLEDB command object and didn't specify any transaction isolation level (use the default in OLEDB). Any feedback would be greatly appreciated. Thanks. Ray
View Replies !
Insert Row, Then Update Using Inserted Identity Issues..
hey everyone, I have the following SQL: CREATE PROCEDURE [dbo].[sp_InsertItem] @item_channel_id INT, @item_title VARCHAR(75), @item_link VARCHAR(75), @item_description VARCHAR(150), @item_long_description VARCHAR(1000), @item_date DATETIME, @item_type VARCHAR(20) AS IF (@item_type = 'article') BEGIN INSERT INTO items ( item_channel_id, item_title, item_link, item_description, item_long_description, item_date, item_type ) VALUES ( @item_channel_id, @item_title, @item_link, @item_description, @item_long_description, @item_date, @item_type ) END IF (@item_type = 'mediaItem') BEGIN DECLARE @new_identity INT DECLARE @new_link VARCHAR(100) INSERT INTO items ( item_channel_id, item_title, item_link, item_description, item_long_description, item_date, item_type ) VALUES ( @item_channel_id, @item_title, @item_link, @item_description, @item_long_description, @item_date, @item_type ) SET @new_identity = @@IDENTITY SET @new_link = @item_link + @new_identity UPDATE items SET item_link = @new_link WHERE item_id = @new_identity END GO Basically, what I am trying to do is this... IF the item type is article, insert normally... which works fine... however, if the item time is mediaItem, insert part of the item_link... (everything minus id.. eg: site.com/items.aspx?item_id=)... then once the row has been inserted, update that row, to make the link site.com/items.aspx?item_id=<new id> however, when the sql runs the mediaItem code, it leaves the item_link field blank. Why is this doing this? Thanks all!
View Replies !
Retrieve Last Inserted Or Updated Record
Hi I have an application which get any change from database using sql dependency. When a record is inserted or updated it will fire an event and my application get that event and perform required operation. On the event handler I am usin select ID,Name from my [table]; this will return all record from database. I just want to get the record which is inserted or updated. Can u help me in that. Take care Bye
View Replies !
Duplicate Record Inserted. Weird.........
1 Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click 2 Dim sqlStr As String 3 Dim sqlStr2 As String 4 Dim myConnection As MySqlConnection = Nothing 5 Dim myCommand As MySqlCommand = Nothing 6 Dim myConnection2 As MySqlConnection = Nothing 7 Dim myCommand2 As MySqlCommand = Nothing 8 Dim myReader As MySqlDataReader = Nothing 9 Dim IC As String 10 11 IC = txtIC1.Text + "-" + txtIC2.Text + "-" + txtIC3.Text 12 13 14 Try 15 sqlStr2 = "SELECT * FROM User WHERE uLogin='" & txtUserName.Text.Trim() & "'" 16 17 ' Connection 18 myConnection2 = New MySqlConnection(ConfigurationManager.ConnectionStrings("dbConnection").ToString()) 19 myConnection2.Open() 20 ' Command 21 myCommand2 = New MySqlCommand(sqlStr2, myConnection2) 22 ' Reader 23 myReader = myCommand2.ExecuteReader() 24 Catch ex As Exception 25 ' Exception Error Here 26 Response.Write(Err.Number & " - " & Err.Description) 27 28 End Try 29 ' Checking 30 If myReader.Read() Then 31 32 33 Label2.Text = "Username already exist. Please choose another username" 34 Label3.Text = "*" 35 36 Else 37 38 Try 39 40 41 sqlStr = "INSERT INTO userapplication(uaName,uaIC,) VALUE (?uName,?uIC )" 42 43 44 45 ' Connection 46 myConnection = New MySqlConnection(ConfigurationManager.ConnectionStrings("dbConnection").ToString()) 47 myConnection.Open() 48 'Command 49 myCommand = New MySqlCommand(sqlStr, myConnection) 50 51 myCommand.Parameters.AddWithValue("?uName", txtName.Text) 52 myCommand.Parameters.AddWithValue("?uIC", IC) 53 54 55 myCommand.ExecuteNonQuery() 56 myConnection.Close() 57 Response.Redirect("Register.aspx", False) 58 59 Catch ex As Exception 60 ' Exception Error Here 61 Response.Write(Err.Number & " - " & Err.Description) 62 Finally 63 ' Clean Up 64 If Not IsNothing(myCommand) Then 65 myCommand.Dispose() 66 End If 67 ' 68 If Not IsNothing(myConnection) Then 69 If myConnection.State = Data.ConnectionState.Open Then myConnection.Close() 70 myConnection.Dispose() 71 End If 72 End Try 73 74 75 End If 76 77 End Sub 78 79 above is my code for the user registration page.the code that i bold,which with number 55,56 and 57,are where the problem occur. when it run,it run 55, then 57,then back to 55, then 57 again means that my db hav duplicate record being insert anyone know how to solve this problem?
View Replies !
Get The Number Of Days It Has Been Since A Record Was Inserted
Hiwhen inserting records into a table one of the fields is a date field. I am using the GETDATE() function to insert the date as the record is being inserted.when i retrieve an entire record from the table i want to be able to select this date, but also to get the number of days it has been since that record was inserted.eg: 3 daysif the record was inserted less than one day ago (<24 hrs ago) i would like it to return the number of hours. e.g. 22 hrsi dont want hours to be displayed if the days is >= 1.please can anyone guide me with this?thanks!
View Replies !
Problems Viewing Last Inserted Record
Basically, I'm creating a blog for fun. I have a formview, and I have several sqlDataSource objects inside the formview's ItemTemplate. Due to peculiarities with the way I plan to implement my blog, I have to programmatically update those sqlDataSource objects. But I've run into a problem: after I insert a record and change my formview's mode to ReadOnly, I can get the last inserted record, but I can't update any of the sqlDataSource objects inside my ItemTemplate, those sqlDataSource objects are simply invisible. Here is my markup:<%@ Page Language="VB" MasterPageFile="~/layout/mysite.master" AutoEventWireup="false" CodeFile="folder.aspx.vb" Inherits="mysite_blog_folder" title="Untitled Page" %> <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <asp:FormView ID="FormView1" runat="server" DataKeyNames="ID_Global" DataSourceID="sqlFolder"> <InsertItemTemplate> Title:<br /> <asp:TextBox ID="TitleTextBox" runat="server" Text="<%# Bind('Title') %>"></asp:TextBox><br /> <br /> Parent:<br /> <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="sqlAllFolders" DataTextField="Title" DataValueField="ID_Global" SelectedValue='<%# Bind("ID_Parent", "{0:N}") %>'> </asp:DropDownList><br /> <br /> Keywords:<br /> <asp:TextBox ID="TextBox2" runat="server" Text="<%# Bind('Keywords') %>"></asp:TextBox><br /> <br /> <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert" Text="Insert"></asp:LinkButton> <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel"></asp:LinkButton> </InsertItemTemplate> <ItemTemplate> <asp:Label ID="TitleLabel" runat="server" Text='<%# Bind("Title") %>' Font-Bold="True"></asp:Label><br /> <br /> <asp:Repeater ID="repeatBreadcrumb" runat="server" DataSourceID="sqlBreadCrumb"> <HeaderTemplate> <asp:Repeater ID="repeatBlogInfo" runat="server" DataSourceID="sqlBlogInfo"> <ItemTemplate> <asp:HyperLink runat="server" Text='<%# Eval("Title") %>' NavigateUrl='<%# String.Format("~/mysite/blog/blog.aspx?b={0}", Eval("id_blog")) %>' /> </ItemTemplate> <FooterTemplate> > </FooterTemplate> </asp:Repeater> </HeaderTemplate> <SeparatorTemplate> > </SeparatorTemplate> <ItemTemplate> <asp:HyperLink runat="server" Text='<%# Eval("Title") %>' NavigateUrl='<%# String.Format("~/mysite/blog/folder.aspx?f={0}", Eval("id_folder")) %>' /> </ItemTemplate> </asp:Repeater> <asp:SqlDataSource ID="sqlBreadCrumb" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNetConnectionString %>" SelectCommand="blog_Get_Folder_Breadcrumb" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:Parameter DefaultValue="0" Name="id_global" Type="Int32" /> </SelectParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="sqlBlogInfo" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNetConnectionString %>" SelectCommand="Select ID_Global, ID_Blog, Title FROM blog_blogs WHERE id_global = (select id_blog from blog_folders where id_global = @id_global)"> <SelectParameters> <asp:Parameter DefaultValue="0" Name="id_global" /> </SelectParameters> </asp:SqlDataSource> <br /> <asp:GridView ID="gridSubFolders" runat="server" AutoGenerateColumns="False" DataKeyNames="ID_Global" DataSourceID="sqlFolderChildren" Width="100%" AllowPaging="True" AllowSorting="True" CellPadding="4" ForeColor="#333333" GridLines="None"> <Columns> <asp:BoundField DataField="ID_Folder" HeaderText="Folder #" SortExpression="ID_Folder"> <ItemStyle Width="10%" /> </asp:BoundField> <asp:HyperLinkField DataNavigateUrlFields="ID_Folder" DataNavigateUrlFormatString="~/mysite/blog/folder.aspx?f={0}" DataTextField="Title" DataTextFormatString="{0}" HeaderText="Subfolders" SortExpression="Title"> <ItemStyle Width="90%" /> </asp:HyperLinkField> </Columns> <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> <EditRowStyle BackColor="#999999" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> </asp:GridView> <asp:SqlDataSource ID="sqlFolderChildren" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNetConnectionString %>" SelectCommand="blog_Get_Folder_Children" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:Parameter DefaultValue="0" Name="id_global" Type="Int32" /> </SelectParameters> </asp:SqlDataSource> <br /> <br /> <asp:GridView ID="gridArticles" runat="server" AutoGenerateColumns="False" CellPadding="4" DataKeyNames="id_global" DataSourceID="sqlFolderArticles" ForeColor="#333333" GridLines="None" AllowPaging="True" AllowSorting="True"> <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <Columns> <asp:BoundField DataField="id_Article" HeaderText="Article #" SortExpression="id_Article"> <ItemStyle Width="10%" /> </asp:BoundField> <asp:HyperLinkField DataNavigateUrlFields="id_Article" DataNavigateUrlFormatString="~/mysite/blog/article.aspx?a={0}&do=edit" DataTextField="Title" DataTextFormatString="{0}" HeaderText="Article" SortExpression="Title" /> </Columns> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> <EditRowStyle BackColor="#999999" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> <EmptyDataTemplate> There are no articles in this folder. </EmptyDataTemplate> </asp:GridView> <asp:SqlDataSource ID="sqlFolderArticles" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNetConnectionString %>" SelectCommand="blog_get_folder_articles" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:Parameter DefaultValue="0" Name="id_global" Type="Int32" /> </SelectParameters> </asp:SqlDataSource> <br /> </ItemTemplate> </asp:FormView> <asp:SqlDataSource ID="sqlFolder" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNetConnectionString %>" InsertCommand="blog_new_folder" InsertCommandType="StoredProcedure" SelectCommand="select ID_Global, ID_Folder, Title FROM blog_folders where ID_Global = @id_global" UpdateCommand="blog_edit_folder" UpdateCommandType="StoredProcedure"> <UpdateParameters> <asp:Parameter Name="id_global" Type="Int32" /> <asp:Parameter Name="status" Type="Byte" /> <asp:Parameter Name="Title" Type="String" /> <asp:Parameter Name="article" Type="String" /> </UpdateParameters> <SelectParameters> <asp:Parameter DefaultValue="0" Name="id_global" /> </SelectParameters> <InsertParameters> <asp:ProfileParameter DefaultValue="0" Name="id_blog" PropertyName="editingblogid" Type="Int32" /> <asp:Parameter Name="id_parent" Type="Int32" /> <asp:Parameter Name="Title" Type="String" /> <asp:Parameter Name="Keywords" Type="String" /> <asp:Parameter Direction="Output" Name="RETURN_VALUE" Type="Int32" /> </InsertParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="sqlAllFolders" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNetConnectionString %>" SelectCommand="blog_Get_Folder_Tree_ALL_Condensed" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:Parameter DefaultValue="0" Name="id_global" Type="Int32" /> </SelectParameters> </asp:SqlDataSource> <br /> </asp:Content> Here is my code behind:1 Imports System.Data.SqlClient 2 3 Partial Class mysite_blog_folder 4 Inherits System.Web.UI.Page 5 6 Dim ID_Global_Folder As Integer = 0 7 8 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 9 If Not Page.IsPostBack Then 10 If Request.QueryString("f") IsNot Nothing Then 11 ID_Global_Folder = New globalid("blog_Get_GlobalID_Folder", _ 12 "id_blog", _ 13 Profile.editingblogID, _ 14 "id_folder", _ 15 Request.QueryString("f")).ReturnValue 16 Else 17 ID_Global_Folder = 0 18 End If 19 20 If Request.QueryString("do") IsNot Nothing Then 21 Select Case LCase(Request.QueryString("do")) 22 Case "new" 23 FormView1.ChangeMode(FormViewMode.Insert) 24 End Select 25 End If 26 End If 27 End Sub 28 29 Protected Sub sqlFolder_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles sqlFolder.Inserted 30 ID_Global_Folder = Convert.ToInt32(e.Command.Parameters("@RETURN_VALUE").Value) 31 FormView1.ChangeMode(FormViewMode.ReadOnly) 32 End Sub 33 34 Private Sub Reset_Default_Parameters() 35 sqlFolder.UpdateParameters.Item("id_global").DefaultValue = ID_Global_Folder 36 sqlFolder.SelectParameters.Item("id_global").DefaultValue = ID_Global_Folder 37 38 Select Case FormView1.CurrentMode 39 Case FormViewMode.ReadOnly 40 InitializeSQLControl("sqlFolderChildren", ID_Global_Folder) 41 InitializeSQLControl("sqlFolderArticles", ID_Global_Folder) 42 InitializeSQLControl("sqlBreadCrumb", ID_Global_Folder) 43 InitializeSQLControl("sqlBlogInfo", ID_Global_Folder) 44 Case FormViewMode.Insert 45 sqlAllFolders.SelectParameters.Item("id_global").DefaultValue = Profile.editingblogID 46 Case FormViewMode.Edit 47 End Select 48 End Sub 49 50 Private Sub InitializeSQLControl(ByVal strName As String, ByVal iValue As Integer) 51 Dim mySQLControl As SqlDataSource 52 mySQLControl = FormView1.FindControl(strName) 53 If mySQLControl IsNot Nothing Then 54 mySQLControl.SelectParameters.Item("id_global").DefaultValue = iValue 55 End If 56 End Sub 57 58 Protected Sub Page_LoadComplete(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.LoadComplete 59 Call Reset_Default_Parameters() 60 End Sub 61 End Class Here is whats happening: 1) After inserting a new record, the event sqlFolder_Inserted gets called. I get the uniqueID of the last inserted folder and save that for later use.2) The page finishes loading up, then the event Page_LoadComplete gets called. I call Reset_Default_Parameters to update my sqlDataSource objects.2.1) sqlDataSource sqlFolder, which is datasource used by the formview, is updated just fine.2.2) I update my formview's child sqlDataSource objects by calling InitializeSQLControl.2.2.1) I use Formview1.Findcontrol to reference the sqlDatasource in my formview's ItemTemplate, I store the referenced control in a variable called mySQLControl.2.2.2) However, mySQLControl is Nothing. ASP.net simply doesn't "see" that control on the page, even though the formview is in ReadOnly mode. I've tried calling InitializeSQLControl in different places in my code, including the Page_Load, sqlFolder_Inserted, but it doesn't make a difference. That problem *only* happens after I insert a new record. Otherwise, I can browse through my folder hierarchy without a problem, and all of my child sqlDataSource objects update just fine. So, how do I update my child sqlDataSource controls in my ItemTemplate after I insert a new record?
View Replies !
SCOPE_IDENTITY() Returning The ID Value Of An Inserted Record.
There are loads of postings on the net about this problem but none I have found explain the cause. Whenever returning a value from a TableAdapter.Insert method followed by a SELECT SCOPE_IDENTITY() , the value returned is always 1. I have run the same select in SQL management studion and the correct value is returned but with a 1 showing in the column selector (just to the left of the first column. The column selector column is not data column. This must be the reason that issuing a SELECT after an INSERT does not work when using a TableAdapter isert method. Has anyone come across the solution for this issue? Thanks
View Replies !
Retrieve The GUID For Inserted Record
I am using this code to insert a record in my table where i have assigned a guid datatype field to generate an automatic guid for each record. but now i need to retrieve the guid to use it to send a confirmation email to the user. SqlConnection sql_connection = new SqlConnection("Server=xxx.xxx.xx.xxx;uid=xxxxxxxx;password=xxxxxxx;database=xxxxxxx;");SqlCommand sql_command = new SqlCommand("INSERT INTO members (member_sex, member_cpr, member_nationality, member_block, member_gov, member_daaera, member_email, member_mobile, member_created_ip) Values (@member_sex, @member_cpr, @member_nationality, @member_block, @member_gov, @member_daaera, @member_email, @member_mobile, @member_created_ip)", sql_connection); sql_command.Parameters.Add(new SqlParameter("@member_sex", Session["member_sex"].ToString()));sql_command.Parameters.Add(new SqlParameter("@member_cpr", Session["member_cpr"]));sql_command.Parameters.Add(new SqlParameter("@member_nationality", Session["member_nationality"].ToString()));sql_command.Parameters.Add(new SqlParameter("@member_block", Session["member_block"].ToString()));sql_command.Parameters.Add(new SqlParameter("@member_gov", "GOV"));sql_command.Parameters.Add(new SqlParameter("@member_daaera", 6));sql_command.Parameters.Add(new SqlParameter("@member_email", Session["member_email"].ToString().ToLower()));sql_command.Parameters.Add(new SqlParameter("@member_mobile", Session["member_mobile"].ToString()));sql_command.Parameters.Add(new SqlParameter("@member_created_ip", Request.UserHostAddress.ToString())); sql_connection.Open();sql_command.ExecuteNonQuery();sql_connection.Close();
View Replies !
Problem With Stored Procedure And Retrieving Inserted Identity Value
Hello!I use a procedure to insert a new row into a table with an identitycolumn. The procedure has an output parameter which gives me theinserted identity value. This worked well for a long time. Now theidentity value is over 700.000 and I get errors whiles retrieving theinserted identitiy value. If I delete rows and reset the identityeverything works well again. So I think it is a data type problem.My Procedure:create procedure InsertProduct@NEWID int outputasbeginset nocount oninsert into PRODUCT(D_CREATED)values(getdate()+'')set nocount offselect @NEWID = @@IDENTITYendMy C# code:SqlCommand comm = new SqlCommand("InsertProduct", sqlCon);comm.CommandType = CommandType.StoredProcedure;comm.Parameters.Add(new SqlParameter("@NEWID",System.Data.SqlDbType.Int)).Direction =System.Data.ParameterDirection.Output;try{SqlDataReader sqlRead = comm.ExecuteReader();object o = comm.Parameters["@NEWID"].Value;//...}catch ( Exception ex ){throw ex;}The object o is alwaya System.DbNull. I also tried to use bigint.Any hints are welcomeCiaoSusanne
View Replies !
CE 3.5, VS 2008, Typed Dataset: Get The Updated Identity Of Inserted Row
Hello, Using VS 2008 Beta 2, SQL CE 3.5, on desktop, and Typed Datasets: The INSERT command of dataset table adapter does not return the updated identity of inserted row. Why? also every time I want to modify the insert command to return the updated identity of inserted row, i get the error: "Unable to parse query text." (Should I post this in Orcas forum?!) Regards, Parham.
View Replies !
Help Getting An ID, Back From A Record, That Has Just Been Inserted With A Stored Procedure
Hi, I was wondering if anyone could offer me some advice. I am currently using a stored procedure to insert records into a database. I want to be able to retrieve the ID (primar key) from the item that has just been inserted using the stored procedure. The ID I want to get back is Meter_ID This is my stored procedure:ALTER PROCEDURE dbo.quote (@Business_Name nvarchar(50), @Business_Type nvarchar(50),@Contact_Title nchar(10), @Contact_First_Name nvarchar(50),@Contact_Last_Name nvarchar(50), @Address_Line_1 nvarchar(MAX),@Address_Line_2 nvarchar(MAX), @City nvarchar(MAX),@Postcode nchar(7), @Tel_No nchar(11),@E_mail_Address nvarchar(50), @Distributor_ID int,@Profile_Class int, @Meter_Time_Code int,@Line_Loss_Factor int, @Unique_Identifier1 int,@Unique_Identifier2 int, @Check_Digit int,@Tariff nchar(20), @UnitRate1AnnualUsage nchar(10),@UnitRate2AnnualUsage nchar(10), @UnitRate1SubTotal money,@UnitRate2SubTotal money, @QuoteTotal money ) ASINSERT INTO client_details (Business_Name, Business_Type, Contact_Title, Contact_First_Name, Contact_Last_Name, Address_Line_1, Address_Line_2, City, Postcode, Tel_No, email_Address)VALUES (@Business_Name, @Business_Type,@Contact_Title, @Contact_First_Name, @Contact_Last_Name, @Address_Line_1, @Address_Line_2, @City, @Postcode, @Tel_No, @E_mail_Address) DECLARE @Client_ID INTSET @Client_ID = scope_identity() INSERT INTO meter_quote (Client_ID, Tariff, Meter_Distributor_ID, Meter_Profile_Class, Meter_Time_Code, Meter_Line_Loss_Factor, Unique_Identifier1, Unique_Identifier2, Check_Digit, UnitRate1AnnualUsage, UnitRate2AnnualUsage, UnitRate1SubTotal, UnitRate2SubTotal, QuoteTotal)VALUES (@Client_ID, @Tariff, @Distributor_ID, @Profile_Class, @Meter_Time_Code, @Line_Loss_Factor, @Unique_Identifier1, @Unique_Identifier2, @Check_Digit, @UnitRate1AnnualUsage, @UnitRate2AnnualUsage, @UnitRate1SubTotal, @UnitRate2SubTotal, @QuoteTotal) RETURN And this is the code I have in my asp page:<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="quote" InsertCommandType="StoredProcedure"> <InsertParameters><asp:ControlParameter ControlID="TextBoxBusinessName" DefaultValue="" Name="Business_Name" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="DropDownBusinessType" Name="Business_Type" PropertyName="SelectedValue" Type="String" /><asp:ControlParameter ControlID="DropDownListTitle" Name="Contact_Title" PropertyName="SelectedValue" Type="String" /><asp:ControlParameter ControlID="TextBoxFirstName" Name="Contact_First_Name" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxLastName" Name="Contact_Last_Name" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxAddressLine1" Name="Address_Line_1" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxAddressLine2" Name="Address_Line_2" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxAddressLine3" Name="City" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxPostcode" Name="Postcode" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxTelNo" Name="Tel_No" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxEmail" Name="E_mail_Address" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxDistributorID" Name="Distributor_ID" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxProfileClass" Name="Profile_Class" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxMeterTimeCode" Name="Meter_Time_Code" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxLineLossFactor" Name="Line_Loss_Factor" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxUniqueIdentifier1" Name="Unique_Identifier1" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="TextBoxUniqueIdentifier2" Name="Unique_Identifier2" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxCheckDigit" Name="Check_Digit" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="LabelTariff2" Name="Tariff" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="TextBoxUnitRate1Usage" Name="UnitRate1AnnualUsage" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="LabelUnitRate2Usage" Name="UnitRate2AnnualUsage" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="LabelUnitRate1Total" Name="UnitRate1SubTotal" PropertyName="Text" Type="Decimal" /><asp:ControlParameter ControlID="LabelUnitRate2Total" Name="UnitRate2SubTotal" PropertyName="Text" Type="Decimal" /><asp:ControlParameter ControlID="LabelQuoteTotal" Name="QuoteTotal" PropertyName="Text" Type="Decimal" /> </InsertParameters> </asp:SqlDataSource> And the following in the C# code: try { SqlDataSource3.Insert();//Insert quote details into the database using a stored procedure }catch (Exception ex) {LabelInsertException.Text = "Failed" + ex.Message; } Any help would be much appreciated Thanks, Hayley
View Replies !
Duplicate Record Inserted With Stored Procedure
I'm calling the stored procedure below to insert a record but every record is inserted into my table twice. I can't figure out why. I'm using Sql Server 2000. Thanks.CREATE PROCEDURE sp_AddUserLog(@Username varchar(100),@IP varchar(50))AS SET NOCOUNT ONINSERT INTO TUserLogs (Username, IP) VALUES (@Username, @IP)GO Sub AddUserLog(ByVal Username As String) Dim SqlText As String Dim cmd As SqlCommand Dim strIPAddress As String 'Get the users IP address strIPAddress = Request.UserHostAddress Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString) SqlText = "sp_AddUserLog" cmd = New SqlCommand(SqlText) cmd.CommandType = CommandType.StoredProcedure cmd.Connection = con cmd.Parameters.Add("@Username", SqlDbType.VarChar, 100).Value = Username cmd.Parameters.Add("@IP", SqlDbType.VarChar, 100).Value = strIPAddress Try con.Open() cmd.ExecuteNonQuery() Finally con.Close() End Try End Sub
View Replies !
Can Insert Trigger Update Record Being Inserted
I think a trigger is the way to go on this but let me try to explain the confusion. I have a table with an id field. Based upon this ID field I need to have a character stripped out of the ID and placed in another column on the same table/same record(Sorry, because of the development env I can't use a computer column). The question is how to do this with an insert trigger. My understanding is the trigger will fire on the insert, but the record isn't there yet. Is there a way to handle this? My code below does not error but also does not update? create TRIGGER test_Ins_tr ON table1 FOR update AS DECLARE @oid char(1) declare @actid char(10) SELECT @oid = SUBSTRING(right(col1,2),1,1), @actid = col1 FROM inserted update table1 set col2 = @oid where table1.col1 = @actid
View Replies !
Newly Inserted Record RowID To A Label Control
Greetings all, I am tring to capture the ID of a newly inserted record from a form to a label that I will reference in a reciept page. I intend to pass the rowid to retrieve record information on other pages. The insert suceeds... I just need to capture the auto generated ID for the new row to a label on the page post onclick. Any thoughts? Dim MySQL As String = "Insert into dropkick (name, status, payroll, unit, contactnumber, email, equipment, issue, timein) values (@name, @status, @payroll, @unit, @contactnumber, @email, @equipment, @issue, @timein)"Dim myConn As SqlConnection = New SqlConnection(SqlDataSource1.ConnectionString) Dim Cmd As New SqlCommand(MySQL, myConn)Cmd.Parameters.Add(New SqlParameter("@payroll", txt_payroll.Text)) Cmd.Parameters.Add(New SqlParameter("@name", txt_name.Text))Cmd.Parameters.Add(New SqlParameter("@status", "W")) Cmd.Parameters.Add(New SqlParameter("@unit", txt_dept.Text))Cmd.Parameters.Add(New SqlParameter("@contactnumber", txt_cell.Text)) Cmd.Parameters.Add(New SqlParameter("@email", txt_email.Text))Cmd.Parameters.Add(New SqlParameter("@equipment", txt_equipment.Text)) Cmd.Parameters.Add(New SqlParameter("@issue", txt_issue.Text))Cmd.Parameters.Add(New SqlParameter("@timein", lbl_datetime.Text)) myConn.Open() Cmd.ExecuteNonQuery() Label1.Visible = "true" Page.DataBind() myConn.Close() Label1.Text = "Your data has been received!" ''LABEL TO BE POPULATED WITH ID OF NEW RECORD lbl_id.Text = ID
View Replies !
Orw Is Not Inserted
while executing this command locally, ita working fine Insert into tblorderDetails (OrderID,ProductID,SofaPackageID,Quantity,UnitPrice,TotalOrd,ItemStatus ) values(1, 3915, 0, 1, 2049.00, 2049.00, 'PO') but when executing online, giving me following msg: (1 row(s) affected) Msg 207, Level 16, State 1, Line 1 Invalid column name 'Location16FreeStock'. and also data is not inserted in table.
View Replies !
Nothing Is Being Inserted
I will paste my code below. Â I inserted a breakpoint but nothing is being sent to the database and nothing came up when I ran it with the breakpoint. Â Can anyone tell me how to fix this?Protected Sub btn_addfriend_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_addfriend.Click Dim add_friend_source As New SqlDataSource add_friend_source.InsertCommand = "INSERT INTO [Friends] ([UserName], [UserID], [IP], [AddedOn], [FriendName]) VALUES (@UserName, @UserID, @IP, @AddedOn, @FriendName)" detailsview_addfriend.DataSource = add_friend_source add_friend_source.DataBind() add_friend_complete.Text = "Success!" End SubThe "Success!" text is the only thing that seems to work properly...
View Replies !
Why Are "&" Inserted As "_"
I have a program to insert rows into a SQL Server table from an ASCII file. There appears to be some data mapping going on. For example: "J & J Smith" is imported as "J _J Smith". This doesn't happen on all servers. I can run it against my database and get the desired results. I looked into the Server Configuation. Under the Security tab there are Mapping options. It appears that you can map _ to something else, but I don't see where you can map & to anything.
View Replies !
Getting Last Inserted Value
hi, this is my spc: select top 1 u.userid, u.user_name, u.password, c.code_description as role_code, u.expiry_date, u.created_date, u.active, convert(varchar,v. user_date, 103) + ' ' + right('0' + stuff(right(convert(varchar,v.user_date, 109), 14), 8, 4, ''), 10) as user_date, v.operation,h.user_name as updatedby from [usermaster] u inner join [codeMaster] c on 'sp'=c.code inner join [HRUser_developerlog] v on u.userid=v.inserted_id and v.operation='update' inner join [usermaster] h on v.userid=h.userid where u.userid = '3' order by v. user_date if i use it gives the v.user_date as fist modified date it is not giving last modified date. select * from HRUser_developerlog user_date operat userid ion 2007-01-25 14:28:17.000insert1 2007-01-24 13:02:18.093insert4 2007-03-03 11:30:29.310update2 2007-03-03 11:30:55.373insert3 2007-03-03 11:31:31.717insert26 2007-01-25 14:28:17.000insert3 2007-03-03 11:43:39.733update26 2007-03-03 11:48:04.543delete3 2007-03-03 14:26:22.420update3 2007-03-03 14:27:00.280update3 2007-03-03 14:27:12.013update2 2007-03-03 14:27:35.763update1 2007-02-08 14:28:17.030update2 2007-03-03 14:27:55.967update3 2007-03-03 14:29:18.827update3 2007-03-03 14:30:52.983update3 so it has to show 2007-03-03 14:30:52.983 but it shows the first updated id only 2007-03-03 14:26:22.420 please help me to get my need
View Replies !
How Return Inserted ID
Hi! I would like to get the last inserted ID in a sql 2005 table to a variable.My code is: SqlDataSource ds = new SqlDataSource();ds.ConnectionString = ConfigurationManager.ConnectionStrings["ConnString"].ToString();ds.InsertCommandType = SqlDataSourceCommandType.Text;ds.InsertCommand = "INSERT INTO Products(SellerID, Price) OUTPUT INSERTED.ProductID VALUES(@SellerID,@Price)"; ds.InsertParameters.Add("SellerID", "Sony"); ds.InsertParameters.Add("Price", "123");string output = ds.Insert().ToString(); // it's return rowsAffected but I need productID (SCOPE_IDENTITY or @@IDENTITY )Thank you for your time and help :)
View Replies !
How Do I Retreive Id Of Just Inserted Row?
I'm creating a web application that has user input on 3 seperate pages. Each page prompts the user for specific information and at the bottom of the page the user will click on the "Submit" button to post the info from the form to the database table. Once the user has submitted the first page of information how do I retrieve the ID from the CustID column of that row so I can use the Update function to add additional info to that row when the user clicks on the submit button on page 2 & 3. I don't want to hold all the information in variables until the end in case they bail out of the form. TIA Steve
View Replies !
Getting The Id Of The Row That I Inserted With SQL 2005
Hi,I use a Stored Procedure who works very well....INSERT INTO Computers (CategoryID, SubCategoryID, ......VALUES (@CategoryID, @SubCategoryID, .........But as soon as it creates the new row, i want to be able to get the Id (ComputerId) of this row. I use ComputerId as the primary key.How can i do that? I code in VB.Thanks
View Replies !
Return Inserted Row
how do i insert a record, then return the inserted record back to VS (ASP + VB) to display? maybe just the ID will be enough. then i will do a select
View Replies !
DataSet - Inserted Row ID
I have a dataset that uses generated stored procedures to do its select, insert, update, delete. I am inserting a row to that dataset, and after the update, using the ID of newly created row. This worked just fine until I added triggers to some of the tables on my DB, and now, when I insert a row, the row's ID is not available after the update (it's 0) Any idea what happened / what I have to do to fix this? Thnx!
View Replies !
|
TRACKER
