Using @@IDENTITY In The Sqldatasource Insert Command
Jan 11, 2008
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.
Hello all, I am having problem to insert the record into database from sqldatasource control. my code is listed below, and i can't find anything why it cause the exception... <asp:SqlDataSource ID="SqlDataSource1" runat="server" DataSourceMode="DataSet" ConnectionString="<%$ ConnectionStrings:WebsiteDataConnection %>" SelectCommand="SELECT * FROM [ServiceAgents]" InsertCommand="INSERT INTO ServiceAgents VALUES(@Ser_STATE, @Ser_CITY, @Ser_AGENT, @Ser_PHONE, @Ser_EQUIPMENT)"> <InsertParameters> <asp:FormParameter Name="Ser_STATE" FormField="TextBox_state"/> <asp:FormParameter Name="Ser_CITY" FormField="TextBox_city"/> <asp:FormParameter Name="Ser_AGENT" FormField="TextBox_agent"/> <asp:FormParameter Name="Ser_PHONE" FormField="TextBox_phone"/> <asp:FormParameter Name="Ser_EQUIPMENT" FormField="TextBox_equip" /> </InsertParameters> </asp:SqlDataSource> The table has got the fields that needed for insert command, for example: <table> <tr> <td>State:</td> <td> <asp:TextBox ID="TextBox_state" runat="server"></asp:TextBox> <asp:RequiredFieldValidator id="RequiredFieldValidator1" runat="server" ControlToValidate="TextBox_state" Display="Static" ErrorMessage="Please enter a state." /> </td> </tr> <tr> <td>City:</td> <td> <asp:TextBox ID="TextBox_city" runat="server"></asp:TextBox> <asp:RequiredFieldValidator id="RequiredFieldValidator2" runat="server" ControlToValidate="TextBox_city" Display="Static" ErrorMessage="Please enter a city." /> </td> </tr> <tr> <td>Agent:</td> <td> <asp:TextBox ID="TextBox_agent" runat="server"></asp:TextBox> <asp:RequiredFieldValidator id="RequiredFieldValidator3" runat="server" ControlToValidate="TextBox_agent" Display="Static" ErrorMessage="Please enter a agent." /> </td> </tr> <tr> <td>Phone:</td> <td> <asp:TextBox ID="TextBox_phone" runat="server"></asp:TextBox> <asp:RequiredFieldValidator id="RequiredFieldValidator4" runat="server" ControlToValidate="TextBox_phone" Display="Static" ErrorMessage="Please enter a phone No." /> </td> </tr> <tr> <td>Equipment:</td> <td> <asp:TextBox ID="TextBox_equip" runat="server"></asp:TextBox> <asp:RequiredFieldValidator id="RequiredFieldValidator5" runat="server" ControlToValidate="TextBox_equip" Display="Static" ErrorMessage="Please enter a equipment." /> </td> </tr> <tr> <td></td> <td> <asp:Button ID="Button2" runat="server" Text="Add" OnClick="addEntry" BackColor="#FFFBFF" BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" Font-Names="Verdana" Font-Size="1.0em" ForeColor="#284775"/> </td> </tr> </table> And in the code i just called: SqlDataSource1.Insert(); Then the page gives me the exception like: Cannot insert the value NULL into column 'STATE', table 'WebsiteData.dbo.ServiceAgents'; column does not allow nulls. INSERT fails. The statement has been terminated. But i actually input all the required text in the textbox.... Any idea guys? Thanks
hi i have an sqldatasource which has an insert command - a stored procedue is used.I have a text box with a button next to it . it is not in a datagrid.on the onclick event I would like to pass the value of the text box to the sqldatasource insert parameter ( it only expects this one parameter , and use the sqldatasource to do the insert basically doing a manual insert using the sqldatasource.does anyone know if this is possible thanks
hello, I'm new to store procedure in slq 2000. I want to create a sproc in my sql 2000 sevrer,I have a table named "Supplier", now I want to return my identiy field value when I use insert into sql command (insert into "Supplier") successful. So how to create this store procedure ? much thanks
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 am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me
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?
I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).
What is the most direct way to do this in SSIS?
TIA,
barkingdog
P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?
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.
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. <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:onepduConnectionString %>" InsertCommand="INSERT INTO [tblaccounts] ([fname],[lname], [address], [city], [state], [zip], ,[phone],[credits]) VALUES (@fName,@lname, @address, @city, @state, @zip, @email, @phone, @credits)> <InsertParameters> <asp:FormParameter Name="fname" FormField="FirstNameTextBox" Type="String" /> <asp:FormParameter Name="lname" FormField="LastNameTextBox" Type="String" /> <asp:FormParameter Name="address" FormField="AddressTextBox" Type="String" /> <asp:FormParameter FormField="cityTextBox" Name="city" Type="String" /> <asp:FormParameter FormField="stateTextBox" Name="state" Type="String" /> <asp:FormParameter FormField="zipTextBox" Name="zip" Type="String" /> <asp:FormParameter FormField="emailTextBox" Name="email" Type="string" /> <asp:FormParameter FormField="phoneTextBox" Name="phone" Type="string" /> <asp:Parameter Name="credits" DefaultValue=0 /> </InsertParameters> </asp:SqlDataSource>
I am using <asp:SqlDataSource ID and for the Select Command, the following, where the WHERE clause ... for an exact match (=) works correctly: SelectCommand="SELECT [PatientID], [MedRecord] , [Accession], [FirstName], [LastName], [Address1] FROM [ClinicalPatient] WHERE (LastName = @LastName) ORDER BY [LastName]DESC"> I would like to do a "LIKE" search where the LastName Parameter is matched using "LIKE". In this situation how would the syntax be written.... I tried: LastName LIKE '%" & LastName & "%'" But I get an error???? Any suggestions, please... Thanks !!
I have a SqlDataSource object that is bound to a GridView control. I have configured the SqlDataSource with a default select command. Under certain values of query strings on the URL for this page (Default.aspx), I want to change the select command. So I put the statements in the Page_Load method for Default.aspx to define SqlDataSource1.SelectCommand. The changed SelectCommand works fine for the first page of GridView data and shows 5 GridView pages, but if I switch to one of the other pages, it seems to revert to the default SelectCommand (which generates 19 GridView pages). I assume I should put my code to change the SelectCommand somewhere else. Can someone help me with where to put it? Thanks!
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 need to dyanamically generate my SQL commands so to do that i am generating sqldatasource commands programmatically rather declaratively. SELECT commands seems to work fine but DELETE isnt doing anything, here is my code:
SqlDataSource sdsConsultant = new SqlDataSource(); protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { loadDataSet(); //it just loads dataset used by formview initializeSDS(); }
//SELECT sdsConsultant.SelectCommand = "SELECT * FROM Consultants WHERE (id=@id)"; QueryStringParameter id = new QueryStringParameter("id", "id"); sdsConsultant.SelectParameters.Add(id);
//DELETE sdsConsultant.DeleteCommand = "DELETE * FROM Consultants WHERE (id=@id2)"; QueryStringParameter id2 = new QueryStringParameter("id2", "id"); sdsConsultant.DeleteParameters.Add(id2);
hello everybody, i have a question to ask, suppose i have a sqldatasource, can i use it in a method??this is my case, i need to make a new method to count the rows in a datagrid, so i will have to read the sqldatasource. the problem is, how to retrieve it?? usually i use the built in sqldatasource_selected to count the rows.... is there any other way??
My compiler says that the line in bold below is illegal. The error msg I'm getting is: No overload for method 'select' takes '0' arguments. How can I correct this error and execute a SELECT? protected void Button1_Click(object sender, EventArgs e) { SqlDataSource2.Select (); } protected void SqlDataSource2_Selected(object sender, SqlDataSourceStatusEventArgs e) {string strReadyFirstName = e.Command.Parameters["@FirstName"].Value.ToString();string strReadyLastName = e.Command.Parameters["@LastName"].Value.ToString(); } <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT [User_ID], [User_Name], [FirstName], [LastName], [Company_Name], [Department_Name] FROM [CompanyDepartment] WHERE ([User_Name] = @User_Name)" OnSelected="SqlDataSource2_Selected"> <selectparameters> <asp:sessionparameter DefaultValue="TheirUserName" Name="User_Name" SessionField="TheirUserName" Type="String" /> </selectparameters> </asp:SqlDataSource>
I have a GridView (that uses SqlDataSource1) and a Dropdownlist. Depending upon the value selected on the DropDownList I need to select different stored procedures for the gridview. The problem is that I can do it without taking SqlDataSource1 by using DataSet or DataTable. But, I need to Use SQLDataSource1 for easy way of Header SORTING. So, is there any way to change the SQLDatasource1.SELECT Command dynamically. So that, I can use different queries for the Single DataGrid. I have attached the sample code of the SqlDataSource1 I'm using. I need to change the Command i.e. SelectCommand="usp_reports_shortages" to "usp_reports_shortagesbyID" and "usp_reports_shortagesbyDate" depending on the value selected in the dropdownlist. So, is there any way to do this????<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TESTDrivercommunication %>"
I have an SQLDataSource. The SQL is SELECT UserName, Category, ItemDescription, Size, Price, Reduce, Donate, Sold, ItemNumber, SoldDate, SoldPrice, PrintedFROM Tags WHERE (Printed = @Printed1 OR Printed = @Printed2 OR Printed = @Printed3) ORDER BY ItemNumber DESCThe bit field "printed" can be NULL, True or False.In the Selecting event of the SQLDataSource I have the following to show ALL records. But it does not work. If I remove these parameters it show ALL records. e.Command.Parameters("@Printed1").Value = Nothing 'ASP.NET 2.0 using Visual basice.Command.Parameters("@Printed2").Value = Truee.Command.Parameters("@Printed3").Value = False What am I doing wrong??? Thanks Craig
I am trying to implement an "advanced search" feature on my ASP.NET 2.0 web form. I have a GridView control and a SqlDataSource. The SqlDataSource control successfully retrieves data when the SelectCommand attribute is set in the aspx page. I need to make it so when a user clicks on a button, it can take a value from a text box and use it in the WHERE clause. I have tried setting the SelectCommand programmatically and then DataBinding but it never accepts the new SelectCommand. What can I do to fix this?
Hi - I'm using .net2, and have a gridview, populated by a SQL Datasource (Edit, Insert, Delete, Select). Like we all used to do with the datagrid, I've added text boxes into the footer, and a link button, which I'd like to use to fire the Update command. How do I get the link button to trigger the update command? Thanks, Mark
Is there a way to drop a column identity using TSQL?
Also, does this code look correct?
declare @max1 int declare @max2 int select @max1 = max(acid) from cms_2a alter table cms_3a add cwid int identity(@max1, 1) <------ Error select @max2 = max(cwid) from cms_3a alter table cms_4a add cwtid int identity(@max2, 1) <------ Error
I do not have identities on tables cms_3a and cms_4a, but I do have an identity on cms_2a.
Hello, I'm new to the forum and new to SQL, ASP.NET, etc. I am creating an intranet site for my company in VS 2005 and have run into a very annoying problem that I can't seem to solve. I have tried Googling it and came up empty. I have a database in SQL Express 2005 and my website will be accessing several tables within the database. I can retrieve info just fine and I can update, delete, etc just fine using gridview or other prebuilt tools, but when I add a few text boxes and wire a button to the SqlDataSource.Insert() command, I get a new record that is full of null values except for the identity key I have set. The kicker is that I am also using a master page and when I duplicate the web page without the master page link, everything works just fine. The following snippets show what I'm doing:<InsertParameters><asp:FormParameter Name="Name" Type="String" FormField="txtName" /><asp:FormParameter Name="Location" Type="String" FormField="ddlLocation" /><asp:FormParameter Name="Issue" Type="String" FormField="txtProblem" /></InsertParameters>Of course I match the formfields to the text boxes, create an onclick event for my button, the sqldatasource is configured correctly, it just doesn't work with the master page no matter what I do. Any help would be appreciated. Thanks
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
Hello all,I'm writing a site with one page that uses the session variable (User ID) to pick one user ID out of a comma separated list in the field Faculty. The default parameterized query designed in the SqlDataSource wizard only returns lines that contain an exact match:SELECT * FROM tStudents WHERE ([faculty] = @faculty) The query: SELECT * FROM tStudents WHERE ([faculty] LIKE '%userID%') works as I need when I hard code the query with a specific user ID into the SqlDataSource in the aspx page. It will not work if I leave the @faculty parameter in it:SELECT * FROM tStudents WHERE ([faculty] LIKE '%@faculty%') e.Command.Parameters works to replace the @Faculty with a user ID, but again, adding the single quote and percentage sign either causes errors or returns no results. I've tried several variations of: string strEraiderID = "'%" + Session["eRaiderID"].ToString() + "%'"; e.Command.Parameters["@faculty"].Value = strEraiderID;no results are returned, not even the lines returned with the default select query.How do generate the equivalent of SELECT * FROM tStudents WHERE ([faculty] LIKE '%userID%') into the SqlDataSource? Thanks much!
Is there any way I can use a variable from my code behind file in the UpdateCommand of a sqlDataSource? I have tried <%$ strUserGuid %>and<% strUserGuid %> any help appreciated.Thanks Dave
I have a gridview with a sqlDataSource with the SelectCommand as "SELECT Movie.Title, Movie.Category, Movie.ReleaseDate, ItemForSale.Quantity, ItemForSale.HasUnLimitedQuantity FROM ItemForSale INNER JOIN Movie ON ItemForSale.ID = Movie.ID"
what kinda 'UpdateCommand' do I set so that ItemForSale is also updated from the grid? I tried two update statements seperated with a semicolon but that wouldn't work, any suggestions...
Hi, I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time. thanks. varun
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
