Hello all,
I have a sqldatasource that inserts data to the database but I want to get the ID from an Identity field after I am done inserting the data. How can I best do that? I'm not using a stored procedure to do this.
I'm trying to return the identity from a stored procedure but am getting the error - "specified cast is invalid" when calling my function. Here is my stored procedure - ALTER Procedure TM_addTalentInvite @TalentID INT AS Insert INTO TM_TalentInvites ( TalentID ) Values ( @TalentID ) SELECT @@IDENTITY AS TalentInviteID RETURN @@IDENTITY
And here is my function - public static int addTalentInvite(int talentID) { // Initialize SPROCstring connectString = "Data Source=bla bla"; SqlConnection conn = new SqlConnection(connectString);SqlCommand cmd = new SqlCommand("TM_addTalentInvite", conn); cmd.CommandType = CommandType.StoredProcedure; // Update Parameterscmd.Parameters.AddWithValue("@TalentID", talentID); conn.Open();int talentUnique = (int)cmd.ExecuteScalar(); conn.Close();return talentUnique; } Any help you can give me will be greatly appreciated thanks!
I have a stored procedure that inserts a record. I call the @@Identity variable and assign that to a variable in my SQL statement in my asp.net page.
That all worked fine when i did it just like that. Now I'm using a new stored procedure that inserts records into 3 tables successively, and the value of the @@Identity field is no longer being returned.
As you can see below, since I don't want the identity field value of the 2 latter records, I call for that value immediately after the first insert. I then use the value to populate the other 2 tables. I just can't figure out why the value is not being returned to my asp.net application. Think there's something wrong with the SP or no?
When I pass the value of the TicketID variable to a text field after the insert, it gives me "@TicketID".
I am creating an ad-hoc reporting module for my website. The user may select a list of columns and specify the WHERE for the report. I build the select statement and the where statement and pass the info to a stored procedure. The stored procedure accepts 3 select parameters and a 3 wherestmt parameters. I finally got the SQLDatasource to work by setting the parameter values in the Selecting method of the SQLDatasource. Binding it to a gridview shows me all my values. Now, I am trying to programmatically call the select method of the SQLDatasource so I can get a dataview or datareader to manipulate further. I am getting an null back when I call the select method. Any ideas why? or a workaround? <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AssetMgmtRWConnectionString %>"SelectCommand="getAdhocRptData" SelectCommandType="StoredProcedure" DataSourceMode="DataReader"> <SelectParameters> <asp:Parameter Name="strSelect" Type="String" /> <asp:Parameter Name="strSelect2" Type="String" /> <asp:Parameter Name="strSelect3" Type="String" /> <asp:Parameter Name="strWhere" Type="String" /> <asp:Parameter Name="strWhere2" Type="String" /> <asp:Parameter Name="strWhere3" Type="String" /> <asp:Parameter Name="bitDebug" Type="Boolean" /> </SelectParameters> </asp:SqlDataSource>
<VB Code>Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 'Getting an error that the object is null? why is the above code not returning any data? 'Tried using a datareader and datasource.Dim rptview As IDataReader = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), IDataReader) If rptview.Read Then lblMsg.Text = "Got Rows" End If End Sub Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting 'For this sp, need to set each paramater even if it is not being used. Setting it in the wizard 'does not work. Need to be set programmatically. e.Command.Parameters("@strSelect").Value = "p.field1, p.field2 " e.Command.Parameters("@strSelect2").Value = "s.field1, s.field2" e.Command.Parameters("@strSelect3").Value = "a.field1, a.field2, a.field4, a.field5" e.Command.Parameters("@strWhere").Value = "p.field3 is not null and a.field5 is null" e.Command.Parameters("@strWhere2").Value = "'" e.Command.Parameters("@strWhere").Value = "" e.Command.Parameters("@bitdebug").Value = False End Sub
TotalSelected.Value = SqlDataSource1.SelectCommand = "SELECT COUNT(*) FROM tblNews";
the reason i am tring to do this is so if i can find out the amount of rows before sqldatasource selects for details view then i can make the sqldataesource select depends on total minus 5 so e.g. if total 200 then - 5 so i can select bottom 195 so it misses top 5 for details view any1 any ideas? Thanks Andy,
Hi All: I have what I'm sure is a common scenario...I have a table to track pageviews of a form, and which also tracks when a person viewing the form submits it. The table has three fields: an INT identity/PK field, a DATETIME (default getdate()) field, and a BIT field with default "false". When the page is viewed, I insert a record into the dB: Protected Sub Page_load(ByVal src As Object, ByVal e As EventArgs)
conn = New SqlConnection("Server=myserver;Database=mydb;User ID=user;Password=password;Trusted_Connection=false;") If Not IsPostBack Then AddTrack() End If End Sub Sub AddTrack()
Dim myCommand As SqlCommand Dim insertTrack As String insertTrack = "Insert PageTracker (submitted) Values (0)"myCommand = New SqlCommand(insertTrack, conn) myCommand.Connection.Open() Try myCommand.ExecuteNonQuery()tempTxt.Text = "<br>Ticked</b><br>" & insertTrack Catch ex As SqlException tempTxt.Text = ex.Number.ToString()
End Try myCommand.Connection.Close() End Sub And if I view the page, the record is inserted into the table. But now I need to know the value of the identity field, so when the form is submitted, I can update the field "submitted" from "0" to "1". The way I would do it in ASP is to add a "SELECT @@identity" to the query, and get the value using RS.nextrecordset. How would I do this in .NET? or is there a better way for me to do this?
I am currently using IDENT_CURRENT to return the Id of a new row in SQL 2000, but I am looking for a similar way to do this in SQL 7. Ihave no experience with SQL 7
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 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...
Hi all i'm trying to get the identity field after inserting into db, what am i doing wrong? thanks a lot my sproc: CREATE PROCEDURE ng_AddCotacao(...@Codigo_cotacao int OUTPUT)ASBEGINSET NOCOUNT ONINSERT INTONegocios_cotacoes(...)VALUES(...)SELECT @Codigo_cotacao=SCOPE_IDENTITY()SET NOCOUNT OFFENDGO
class file public class Cotacoes { public int codigoCotacao; } public class CotacaoAtualiza { public Cotacoes cotacoes = new Cotacoes(); public CotacaoAtualiza() { } public void AdicionarCotacao( ... ) { SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["stringConexao"]); SqlCommand myCommand = new SqlCommand("ng_AddCotacao", myConnection); myCommand.CommandType = CommandType.StoredProcedure; ... SqlParameter paramCodigo_cotacao = new SqlParameter("@Codigo_cotacao", SqlDbType.Int, 4); paramCodigo_cotacao.Direction = ParameterDirection.Output; myCommand.Parameters.Add(paramCodigo_cotacao); ... myConnection.Open(); SqlDataReader result = myCommand.ExecuteReader(); while(result.Read()) { this.cotacoes.codigoCotacao = (int) result["@Codigo_cotacao"]; } myConnection.Close(); }
calling into code-behind file: CotacaoAtualiza ca = new CotacaoAtualiza(); Cotacoes cotacoes = ca.cotacoes; Response.Redirect("Cotacao_confirma.aspx?cotacao=" + cotacoes.codigoCotacao);
I have a table tblnetwork on which identity property is defined on column networkid.
When I issue the following command new record is getting inserted into tblnetwork and identity column is getting incremented properly. But, I am not able to get the @@identity value It is returned as NULL.
I have a createStudent SProc. When this is called it calls a createContact SProc. This in turn calls a Create Address Stored procedure. Now when I create the address, an Identity column automatically creates a new Identity for me. I need to return this value back to the calling stored procedure. I know how to return a value and all, however, how to I get the identity just entered (the corresponding identity) reliably? I know that select MAX <Identity> could suffice in this case, but is flawed as if someone was to add another object near the same time we could get the wrong identity returned...
I am storing product information in a SQL Server database table; the product information has no unique fields so I have created an Identity field called ‘uid’. Is there a way of querying the table to find out what value will be given to the next ‘uid’ field before the next record is written to the table? I need to use this as a FK in other tables.
Hello, I have a C# application that adds records to a SQL Server database using a query something like this one:
INSERT INTO table_name (first_name, last_name, date_added) ('john', 'smith', '1/1/2005 12:00:00pm') ; SELECT SCOPE_IDENTITY() AS [Scope_Identity]
This works fine unless there's already a John Smith in the database. When that happens, Scope_Identity is null even though the date_added is different. About half the time the record is added even though Scope_Identity is null. I've added code to notify me when this happens, but it's a pain in the neck to re-run my import utility for individual records.
(The table I'm adding to does have a autonumbered key field)
Hi, Hope you guys won't mind this rather newbie question. I'm writing a simple blog page for my website and have created a SqlDataSource which queries the database for a list of blog post titles (from the web.Blog table) and the number of comments (from the web.BlogComments table). The SqlDataSource control is: <asp:SqlDataSource ID="sourceBlogArticles" ProviderName="System.Data.SqlClient" connectionString="<%$ ConnectionStrings:myDatabase %>" runat="server" SelectCommand="SELECT gb.blogID, gb.title, gb.description, gb.tags, gb.dateAdded, COUNT(gbc.blogID) AS noOfComments FROM web.Blog gb LEFT OUTER JOIN web.BlogComments gbc ON gb.blogID = gbc.blogID GROUP BY gb.blogID, gb.title, gb.description, gb.tags, gb.dateAdded ORDER BY gb.dateAdded"></asp:SqlDataSource> This works perfectly well if each blog entry in the web.Blog table has associated comments in the web.BlogComments table. However, if there are no comments yet defined in the web.BlogComments table for that blogID then no row is returned in ASP.Net (as checked with a GridView control or similar linked to the data source to view what I get) HOWEVER, I think the SELECT command IS correct: if I use the select command as a query in SQL Server Managment Studio Express, I do get the rows returned, with 0 for the number of comments which is what I would expect for that query: blogID, title, description, tags, dateAdded, noOfComments 1, title 1, description for title 1, tag1, 2007-09-27 06:49:03.810, 32, title 2, description for title 2, tag2, 2007-09-27 06:49:37.513, 03, title 3, description for title3, tag3, 2007-10-02 18:21:30.467, 0 Can anyone help? The result from the SSMSE query is what I want, yet when I use the very same SELECT statement in my SqlDataSource I don't get any rows returned if the BlogComment count is zero (in the above example I get only the first row). Many thanks for any suggestions!
Hi, and thanks in advance. I have VWD 2005 Express and SQL 2005 Express running. I have a SqlDastasource wired to the stored procedure. When I only include one Control parameter I get results from my Stored procedure, when I inclube both Control Parameters I get no results. I manually remove the second control parameter from the sqldatasource by deleting... <asp:ControlParameter ControlID="ddlSClosed" DefaultValue="" Name="SClosed" PropertyName="SelectedValue" Type="String" /> I have one Radio Group and one dropdownlist box that supplies the parameters. The dropdownlist parameter is Null or "" when the page is first loaded. Below is my SQLDatasource and Stored procedure. I am new to Stored Procedures, so be gentle. Any help would be appreciated!
ALTER PROCEDURE [dbo].[spDisplayServiceOrders] ( @SDate_Entered SmallDateTime, @SClosed nvarchar(50)= NULL ) AS If @SClosed IS NULL BEGIN
SELECT Service_Orders.SStore_Assigned_Number, Store_Info.Store_Other, Service_Orders.PO_Number, Service_Orders.SWorkType, Service_Orders.Service_Order_Number, Service_Orders.SDate_Entered, Service_Orders.SContact, Service_Orders.SClosed FROM Service_Orders INNER JOIN Store_Info ON Service_Orders.Store_ID = Store_Info.Store_ID and SDate_Entered >= @SDate_Entered Order by SDate_Entered DESC END ELSE BEGIN
SELECT Service_Orders.SStore_Assigned_Number, Store_Info.Store_Other, Service_Orders.PO_Number, Service_Orders.SWorkType, Service_Orders.Service_Order_Number, Service_Orders.SDate_Entered, Service_Orders.SContact, Service_Orders.SClosed FROM Service_Orders INNER JOIN Store_Info ON Service_Orders.Store_ID = Store_Info.Store_ID and SDate_Entered >= @SDate_Entered and SClosed = @SClosed Order by SDate_Entered DESC
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
I'm trying to update my e-commerce approach from ADO-heavy code to a more modern approach based on the SqlData Source. I also want to move away from Stored Procedures for the moment, if I can, though I may return to them later (mainly for educational purposes at the moment). In the past I used @@Identity in a stored procedure to return an ID# which I passed on to the end user as their "order number". Unfortunately this approach seems to only apply to SPs. In short, what's the best way to handle this using SqlDataSource and minimal ADO code? Just to add a bit more detail, I've got basically three tables, in a fairly obvious relationship. Orders stores the main order info (Customer's name and address, total, etc), OrderDetails lists the line items, and Products contains detail on the items. (Put another way, the auto-generated tags in the SqlDataSource object either don't include the OrderID param because it's toggled for Identity in the database, or if I toggle Identity off then I don't know how to trigger it to toggle the next number in sequence. And either way I don't know how to feed that information back to the program.) Thanks!
Is it possible to write the InsertCommand for a SqlDataSource to return the value of the AUTONumber of a field generated when a new record is added to a database table and display that value on label1.Text after the postback of ItemInsersted event? For example, <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NearMissConnectionString >" InsertCommand="INSERT INTO [NearMiss] ([Branch], [Division] VALUES (@Branch, @Division); SELECT SCOPE_IDENTITY()" SelectCommand="SELECT [NearMissID], [Branch], [Division], FROM [NearMiss]" <InsertParameters> <asp parameter Name="Branch" Type="Int32" /> <asp parameter Name="Division" Type="String" /> </InsertParameters> </asp:SqlDataSource>
NearMissID would be the value of the autonumber generated by the SQL Server
I am trying to retrieve the value of the autonumber generated when a new record is inserted into a SQL 2000 Database using a SqlDataSource attached to a FormView. Is this possible? Can some give me a short example of how to retrieve that value and display it in a textbox? Thanks for any help with this. It seems like it would be a common issue for .net developers.
is there a way that I can use the sqldatasource with a form view where my sqldatasource select statement is like this select * from tblUsers where vcUserName=@vcUserName <selectparameters> <asp:Parameter Name="user.identity.name" Type="String /> </selectParameters>
Is there no way to pass identity info back through SqlDataSource? You can only do that with ADO.NET code? In other words, if I want to run a complex INSERT statement to a table that uses Identity, I can't take that key value back through something like SCOPE_IDENTITY() and use it? I know how to do this with ADO code, but I can't figure out how to do it purely with SqlDataSource. I was hoping to do this without having to write a new Insert statement -- just using the one that's already in the SqlDataSource control. But there doesn't seem to be any facility for Identity in there. I tried embedding a separate select statement after the insert statement and a semi-colon, but that didn't seem to do anything. Thanks!
I apologise if i have not posted this in the correct Topic before i start. But was uncertain where to post this query. This is my first project in ASP.NET, MS Visual Web Developer 2005 Express and SQL Server 2005 Express. I have relatively little experience, so please bare with me. I have managed to create a form that inserts data into a table and then inserts the Automatically Created Primary Key(as a foreign key) in another table. I have done this by inserting what is highlighted in red in the code of my InsertCommand below (Please scroll across to the end of the code):-InsertCommand="INSERT INTO [PrinterModel] ([Model], [PrinterMakeID], [CartridgeCode], [PartCode], [Duplex], [NIC], [Wireless], [Parallel], [USB], [Colour], [PrinterTypeID]) VALUES (@Model, @PrinterMakeID, @CartridgeCode, @PartCode, @Duplex, @NIC, @Wireless, @Parallel, @USB, @Colour, @PrinterTypeID) INSERT INTO [Model] ([PrinterModelID],[TypeID]) VALUES (@@IDENTITY, 3)" Can you see any problems that may arise from using this method. This project is an Asset Management System and will be used by no more than a handful of users. My Concern is the use of the @@IDENTITY (As it only stores the last Key used). Should I be using it here? If there is more than one user inserting into tables (Chances of this happening are very low), will the correct Primary key be insert to the table in the above code?Thank you for your comments.
Ok, So I am trying to do 1 insert into a table and then insert more information into another table but I need to get the [TKTNUM] (the column with the idenity increment on it) value that was auto-generated when the insert was done. I have done a search and I am well aware that this are tons of posts on this subject already that is how I found out to use the SCOPE IDENTITY(). But nobody is using the insert syntax that I am using and since I am new, I don't understand any of it. I think they are using ADO.NET. I am extremely new to ASP.NET so take it easy on me ok guys... But can someone take a look at my code and tell me what the problem is. Again, I'm new so I am learning as I go so I might be missing something really stupid. Any help would be greatly appreciated. O yeah, I am running this code in a button click event NOT in a stored procedure (dont really know now to use those anyway).Here is my code:Protected Sub submitBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs)Dim newTktNumIf Page.IsValid ThenDim it3Conn As SqlDataSource = New SqlDataSource()it3Conn.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString1").ToStringit3Conn.InsertCommand = "INSERT INTO [IT_TICKET] ([REQUEST_DATE], [LOGGED_BY], [REQUESTED_BY], [TKTSTATUS],[NEEDED_DATE]) VALUES (@REQUEST_DATE, @LOGGED_BY, @REQUESTED_BY, @TKTSTATUS,@NEEDED_DATE); SELECT @newTktNum = SCOPE_IDENTITY()"it3Conn.InsertParameters.Add("REQUEST_DATE", DateTime.Now.ToString)it3Conn.InsertParameters.Add("LOGGED_BY", User.Identity.Name)it3Conn.InsertParameters.Add("REQUESTED_BY", User.Identity.Name)it3Conn.InsertParameters.Add("TKTSTATUS", "NEW")it3Conn.InsertParameters.Add("NEEDED_DATE", needByTB.Text.ToString)it3Conn.Insert()End IfMsgBox("Ticket Number: " & newTktNum & " has been created", MsgBoxStyle.OkOnly, "Ticket Created")End Sub
Hi,How do I get hold of the HttpContext.Current.User.Identity.Name from within an sqldatasource? I want to create a WHERE clause that says WHERE UserName = @UserName and the @UserName parameter be filled with the current user name. I would be able to do this in code, but I don't know how to get at this information from within a datasource. At a guess I tried to use the Cookie option from the drop down list, and use the name of the FormsAuthentication Cookie, but this didn't work. Is there a way I can do this? If so how? I don't want to resort to using a hidden field or anything like that if I can help it. Many thanks, Steve
I am opening a simple command against a view which joins 2 tables, so that I can return a column which is defined as a tinyint in one of the tables. The SELECT looks like this: SELECT TreatmentStatus FROM vwReferralWithAdmissionDischarge WHERE ClientNumber = 138238 AND CaseNumber = 1 AND ProviderNumber = 89 The TreatmentStatus column is a tinyint. When I execute that above SQL SELECT statement in SQL Server Management Studio (I am using SQL Server 2005) I get a value of 2. But when I execute the same SQL SELECT statement as a part of a SqlDataReader and SqlCommand, I get a return data type of integer and a value of 1. Why?
What is the C# code I use to do this? I'm guessing it should be fairly simple, as there is only one row selected. I just need to pull out a specific field from that row and then insert that value into a different SqlDataSource.
I hvae a stored procedure that has this at the end of it: BEGIN EXEC @ActionID = ActionInsert '', @PackageID, @AnotherID, 0, '' END SET NOCOUNT OFF
SELECT Something FROM Something Joins….. Where Something = Something now, ActionInsert returns a Value, and has a SELECT @ActionID at the end of the stored procedure. What's happening, if that 2nd line that I pasted gets called, 2 result sets are being returned. How can I modify this SToredProcedure to stop returning the result set from ActionINsert?
While I have learned a lot from this thread I am still basically confused about the issues involved.
.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.
To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.
1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.
2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!
3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?
