DataSource Controls :: How To Insert SQL Record From C# Code Behind
May 31, 2010
I converted code from VB that works to c# to insert a record in an SQL daTA BASE.
The VB code is as follows:
Insert a new record into Contact
Dim connectionString As String = ConfigurationManager.ConnectionStrings("aspnetdbConnectionString").ConnectionString
Dim insertSql As String = "INSERT INTO Contact(UserId, Notes) VALUES(@UserId, @Notes)"
Using myConnection As New SqlConnection(connectionString)
Conseptually what I'm doing is simple, and technically it may even be simple, but I'm still rusty. Here's what I'm trying to do:
I have a webform that allows the user to enter a new record, and in there I have also included a couple FileUpload controls. Upon clicking the "Save" button, I'm saving the details entered into one table (ITEMS), and also inserting the FileUpload details (path, id, upload_user) into another table ATTACHMNTS). Because one ITEM can have many attachments, I needed to split the tables up. So on save, I need to save the ITEM, save the ATTACHMENT, and then also put the ITEM_ID in the ATTACHMENT table. I'm second guessing myself because I think all I'd have to do is after I do the ITEM insert, do a SELECT MAX on the ITEM table and hold the newest (MAX) id in a variable and then use it in the ATTACHMENTS update. I'm not 100% on this because there IS a chance that someone else could save a few milliseconds later and I'll link the wrong ITEM. See my delima?
I'm very new to this area and am using ASP.NET 3.5 with WMD 2008 Express Edition and VB code.
How do I write records to the SQL database using VB? I want to use the connection string, etc used in the ASP.NET controls if possible.
My website is a Rugby Club site and I've an SQL database of match fixtures which include TeamId(1XV, 2XV, etc.), Season(2008, 2009, etc) and match date.
I allow some users to update the fixture details but want to insert a series of new fixture records for the neext season which contain just TeamId Season and Match Dates. I therefore want to take the season and Team Id from label fields and then create match dates by adding seven days to a start date.
I use the following code to insert recode to a sql 2008 table, the field ID of DBProgram is IDENTITY, so SQL 2008 SERVER will pass a value to it automatically. How can I get the ID value of the record I just insert ?
DBProgram dbProgram = new DBProgram(); db.DBPrograms.InsertOnSubmit(dbProgram); db.SubmitChanges();
I am going to insert Multiple record IN sql using storedProcedure from gridview...
for this purpose i am using cell concatinating method and send whole gridview cell data in string variable.
i have tried xml document method or sending data in table through looping as well but didn't satisfy from both of them... b/c concatinating method required a delimeter for seperation(for which i have restrict the user from that sepereator ) and xml method required an extra file which is not a efficient approach .
how can i insert a gridview(multiple) record using store procedure.if record exist then it first Delete then Insert in a particular CRS_CODE.my table is below.
I'm using the linq to entity command to perform a record update. I want to set one of the fields back to NULL but I'm not sure how do it. I have a query such as
Dim record = From r in db.LISTS where r.ID = refID Select r record.Title = txtTitle.text if txtDetail.text = "" then record.Detail = end if
I have an orders table with a column names orderID. What I want to do is insert a new record and get the value from the new orderID column. The orderID is my Primary key. The Insert SQL I have now is -
INSERT INTO orders (creditAutorizarionNumber, OrderName, OrderPersonPhone, OrderPersonEmail) VALUES (@creditAutorizarionNumber,@OrderName,@OrderPersonPhone,@OrderPersonEmail)
How do I write the query to get the newly entered orderID ?
After this and in the same procedure i want to update the inserted or the updated record which occared in the above code with the follwing update statment :
[Code]....
updating mark is completed successfully if the record is existed (Update Case) but if the record does not exist (new record) ... the update of the mark is not occured ..
i Am using Special character while insert in the database. When i user single Quotes('), it shows an error that "Unclosed quotation mark after the character string" How can i over come this issue. But i want to insert the special characters"
How do I set my insert identity return value parameter in codebehind? I have working code now but i have all of my parameters set in codebehindexcept the following:
when need to insert into Text.boxes details from user (name,e-mail, e.tc.) and to get record of it into DB on other page. I try to get after pressing Submit btn:
I have a page called Article.aspx that takes content from the database. I would like to use the title of the content as the title of the page for a little SEO I am doing. I setup a literal on the aspx page that must take the value of the content title from the code behind, what is the best way about doing this? I'm guessing I need to open a data base connection to read the record from the table but I am still learning asp.net and not sure how to write it
when i want insert a record i see an error like this Can't perform Create, Update, or Delete operations on 'Table(MainMenuLink)' because it has no primary key. with this codes:
MyLinqDataClassesDataContext db = new MyLinqDataClassesDataContext(); MainMenuLink li = new MainMenuLink { Link = "www.tprogrammer.com", LinkSubject = "subject" }; db.MainMenuLinks.InsertOnSubmit(li); [code]....
Consider I am using 3 fields staff id, staff name and phone no. Staff id is a primary key. I want to get a error "the record already exists" if there is already a record in the same id when i am clicking add record button. (Plz give the full coding including stored procedure for insert).
I need to insert a record in table which is in remote server....I am using two connection strings for local and remote....both the servers are in same workgroup only...so am able to connect the both.The problem is am filling data of local table in on dataset1 and remote server table data in another dataset dataset2..Now whil inserting a record local an error occured like "This row already belongs to another table" after googlingI found like this like "Dataset.importrow()" instead of "dataset.add()" method...then there is no errors but the inserted record is not inserting in remote server database...
I have a scenario in which the user can add a new option to appear in the list of available choices in a DDL contained in a detailsview when they select an option <Add New>. This displays an AJAX model popup which then allows the user to enter the new option text. Then they hit an Insert button which inserts that new option into the DDL's datasource and I call ddl.DataBind. Subsequently I want to close the modal popup and then display the newly created DDL option as selected.
When I step through the code, everything appears to be working as expected...i.e. the DDL's datasource now has 1 more record than before and the ddl.SelectedValue is the newly created record for its datasource. I can also query ddl.items(99) and see the new option is part of the ddl's items collection and the new record is Selected.
HOWEVER, once the screen comes back from the debugger, the DDL is now showing the newly created option, but still the prior selected option of "<Add New>". And if you pull the DDL down the newly created option is not in the list?
I cannot figure out how that happens...though I suspect it has something to do with the AJAX/Update panel. Here's some code:
I use GridView & FormView to update a record. but when I click the "save" button, throw an except : FormView must be in the insert mode to insert a record. The record has been updated success, and I didn't change the mode of the FormView, how this error comes?
I am counting from a table for that i have written code as below
protected void get_Id_Afterpoint() { int counter = 0; string strSql = "select count(*) as ID from tblEnergy where ID=?"; OdbcCommand com = new OdbcCommand(strSql, con); com.Parameters.AddWithValue("ID", DropDownList1.SelectedValue); OdbcDataAdapter oda = new OdbcDataAdapter(com); DataTable dt = new DataTable(); oda.Fill(dt); if (dt.Rows.Count == 0) { lblID2.Text = "1"; } else { counter = dt.Rows.Count; counter = counter + 1; lblID2.Text = Convert.ToString(counter); } }
there is no record related to DropDownList1.SelectedValue. but as i am counting if(dt.rows.count) and i put break point on the bolded part it shows 1 record. how it can be possible?
I am creating an album using the following stored procedure, which returns the primary key for the newly created record:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[fpa_sp_albums_update_insert] @album_id int, @album_name nvarchar (50), @album_descr nvarchar(250), @album_img_cover_id_FK int, @album_creation_date date AS If @album_id > 0 UPDATE [fpa_albums] SET album_name=@album_name, album_descr= @album_descr, album_img_cover_id_FK = @album_img_cover_id_FK, album_creation_date = @album_creation_date Where ((album_id = @album_id)) Else INSERT INTO [fpa_albums] ( album_name, album_descr, album_img_cover_id_FK, album_creation_date) VALUES ( @album_name, @album_descr, @album_img_cover_id_FK, @album_creation_date) Return SCOPE_IDENTITY();
I execute the above stored procedure using the SQLHELPER. The VB.NET code for the page is as follows:
[Code]....
However, the createdAlbumID always shows -1 instead of the id for the newly created album.
I'm very new on VWD, but I got the hang of it for the past 2 weeks(thank you for all the videos posted here in ASP.NET),but I'm really weak on the coding part(vb & c#),except for all the sample shown on the tutorials. Anyways, I have a simple project that does insert, update & delete to SQL express using the details view. But to complete my project, I need to add a button that will create an event to restore the very last record inserted in SQL and display it back into the details view for minor modification and nserted again as the newest record with the mod. I'm doing this so user, doesn't need to re-key-in redundant information, like names, original date & time etc, and just make modification on the description of the last record.....
I am trying to pass in as an insert parameter for SQLds2 the scope_identity of the insert of SQLds1.
Exception Details: System.InvalidOperationException: Error executing 'InsertCommand' in SqlDataSource 'SqlDataSource1'. Ensure the command accepts the following parameters: @Name1, @NewId
