Updating An SQL Server Database From An ASP.NET Page
Dec 14, 2006
Hello again!
I think what I need to do should be simple, but it is proving difficult.
I am displaying a page with data drawn from three different SQL Server tables. Most of the fields on the page correspond to fields in one of the three tables, and these fields can be updated by the user. I then want to propagate these updates to SQL Server, refill the DataSet, and rebind.
What I'm currently doing is basically this. I've set up a separate SqlCommand for this update. I have a ParameterCollection defined for this SqlCommand that includes the key field to be used in my WHERE clause and all the fields I want to update.
When the user clicks on the [Save Customer Review] button, I (1) set the values of the parameters to the values of the corresponding screen fields, in most instances using one of the Convert methods so the data being assigned to the parameter has the correct type; (2) open an SqlConnection; (3) do SqlCommand1.ExecuteNonQuery(); (4) close the SqlConnection; (5) fill the DataSet; (6) bind.
I've experienced multiple kinds of failure in this process, but the primary kinds of failure I've experienced are these:
- The one value I'm actually updating (a text field) doesn't get updated on the database. No other fields are touched.
- Two fields with Int values (SQL Server shows them as length 4; does that correspond to Int32?) are set to NULL.
- The ExecuteNonQuery complains that I'm not supplying a value for the one field that is type DateTime. (In the SQL Server database, this field is permitted to be NULL, and in the record I'm playing with, it is in fact NULL. However, since TextBox.Value is of type String, I'm having to set it to Nothing [Visual Basic] if the text box is blank.)
One thing I don't like about this is that I can't see what values the parameters are taking on as I do my processing, even in the debugger. At least I don't know of any way to examine or watch them. One reason this bothers me is that I have a rather strong suspicion that there's something wrong with the way I've set up the parameters, but I can't tell what's happening to them to confirm that, and I also can't see the SQL that's being generated (or can I?).
Any thoughts?
Thanks,
Tim
PS Be gentle, please -- this is my first Web application. :-)
I was hoping someone could tell me what my syntax error is in my code: (this is my first time creating one of these!)
<cfquery name="JobPost" datasource= "JobPost"> UPDATE Jobs (Position, Description, Requirements) VALUES ('#Form.Position#', '#Form.Description#' , '#Form.Requirements#') WHERE JobID=2 </cfquery>
I get the following diagnostic error in the browser: ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
SQL = "UPDATE Jobs (Position, Description, Requirements) VALUES ('Updated Receptionist', 'description updated You will take phone calls' , 'description updated Must be able to work a phone.') WHERE JobID=2"
Data Source = "JOBPOST"
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (129:2) to (130:23) in the template file
I have a web interface where i have listing of several data and check box for inserting data into SQL server 2005 database table, so I am able to inset data to sql tables using stored procedure. Now the question is i want to update these inserted records(agency approval column inserted as 1) in same table and assign value 1 fot the checked data to column finace approval as 1. Here is how ia have webclas library where i script for getting the insert parameterspublic void Process_Payment(ref DataTable TableWithPayments, string Payment) {SqlCommand InsertCommand = new SqlCommand(); SqlConnection AccessDatabase = new SqlConnection(FinanceSourceWrite.ConnectionString);int PaymentID = 0; AccessDatabase.Open(); InsertCommand.Connection = AccessDatabase;
//DataTable TemporaryTable = new DataTable(); //TemporaryTable = TableWithPayments;SqlTransaction TransactionProcess = null; SqlParameter InsertParameters;foreach (DataRow DataCommentInfo in TableWithPayments.Rows) {InsertCommand.CommandText = "InsertPaymentList"; //THIS IS my stored procedureInsertCommand.CommandType = CommandType.StoredProcedure; TransactionProcess = AccessDatabase.BeginTransaction(); // SET ALL THE VALUES FOR THE PARAMETERSInsertParameters = InsertCommand.Parameters.Add("@JC_ID", SqlDbType.Int); InsertParameters.Direction = ParameterDirection.Input;InsertParameters.Value = DataCommentInfo["JC_ID"]; InsertParameters = InsertCommand.Parameters.Add("@Payment_Type", SqlDbType.Int); InsertParameters.Direction = ParameterDirection.Input;InsertParameters.Value = DataCommentInfo["Payment_Type_ID"];
ALTER PROCEDURE [dbo].[InsertPaymentList]( @JC_ID int , @Payment_Type int, @Payment_Group int, @AGENCY_ID int, @Agency_approval int, @Agency_approval_date datetime, @Program_ID nvarchar(50), @Status bit, @Jobsite_code_ID int, @Date_Stamp datetime, @Provider nvarchar(50), @UserName nvarchar(256), @Activity_ID int, @Subproject_ID int, @Payment_Support_Retention_List_ID int, @WPR_ID int, @Placement_ID int, @Enrollment_ID int, @Satisfaction_ID int, @Enrollment_Bonus_ID int, @Re_Placement_Bonus_ID int )AS INSERT INTO Payment_LIST_AIMS (JC_ID, Payment_Type, Payment_Group, AGENCY_ID, Agency_approval, Agency_approval_date, Program_ID, status, Jobsite_code_ID, Provider, UserName, Placement_ACTV_ID, Placement_Sub_ID, Support_Retention_ID, WPR_ID, Placement_ID,Enrollment_ID,Satisfaction_ID,Enrollment_Bonus_ID,Re_Placement_Bonus_ID) VALUES @JC_ID, @Payment_Type, @Payment_Group, @AGENCY_ID, @Agency_approval,@Agency_approval_date, @Program_ID, @Status, @Jobsite_code_ID, @Provider, @UserName, @Activity_ID, @Subproject_ID, @Payment_Support_Retention_List_ID, @WPR_ID, @Placement_ID,@Enrollment_ID,@Satisfaction_ID,@Enrollment_Bonus_ID,@Re_Placement_Bonus_ID) SELECT CAST(scope_identity() as int) Here like you see agency approval column in SQL server table gets value assigned as 1 when Agency user clicks the confirm payment button and so all the values as above.... Now another user Finance user process the same records from the web UI and clicks the process payment button at this stage ..i need to update Finance approval column as 1 agains that particular record existing th the SQL table, there are two three coulmc to be updated , Finance approval(this is where i need help) , Finance approval date , and user Being a newbie please help me whith how i can fix this Thanks Santosh
I'm taking over an SQL Serverdatabase which is often updatedwith data from an Access database.Knowing little about either, thissimple task has become a challenge.I'm told that the previous personused to type some simple SQLcommands and this would updatethe SQL server database but thecommands have been lost and theprevious person is unavailable.The Access database has exactlythe same column names as the SQL-server databaseand it is something simplelike:Control ID Phone Name Address1 Address2Does anyone know the SQL commandsto do this kind of SQL server database update from theabove mentioned Access database?The previous person left an app which connectsthe two databases and leaves a placewhere the SQL commands are to be typed.ThanksTAK
I am having trouble updating my database. I have seen a couple of other people had the same problem. Two answers I had found and tried did not work. The updating if newer I had already came across in a step by step class room tutorial written by a college professor and to make sure the save procedure came after the update procedure. I did need to make this last change but its still not updating. Any further advice on this subject would be appreciated. .
I have a matrix table. These status can be changed by the user and I want to capture each change in database with out updating the earlier status
Pending Activated In PROGRESS Submitted Completed
Pending can be changed to submitted or completed. For one form there can be different status at different time. And each status must be saved in the database table. How can I design a table...
I have moved some DB's from a physical server to a virtual server while the physical server is being upgraded. This morning when I checked to make sure all the jobs are running fine I saw that a backup job failed for one of the databases. The error was :
[color=#ff0000]Executed as user: NT AUTHORITYSYSTEM. BACKUP 'database 1' detected an error on page (1:1400911) in file 'D:Datadatabase 1.mdf'. [SQLSTATE 42000] (Error 3043) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.[/color]
Then I ran a DBCC check db command and found the following errors:
Msg 8928, Level 16, State 1, Line 1 Msg 8939, Level 16, State 98, Line 1 Msg 8976, Level 16, State 1, Line 1 Msg 8978, Level 16, State 1, Line 1 Msg 8939, Level 16, State 98, Line 1 Msg 8978, Level 16, State 1, Line 1
The messages varies from data from one page cannot be referenced on another, data page cannot be processed, page was not seen in the scan although the parent was etc etc.
I have a report with tablix. when tablix returns no rows Footer is coming all the way up . How to display the footer at the bottom of the page all time.
I have created one reports but all the records are displaying on one page.find a solution to display the records page by page. I created the same report without group so the records are displaying in page by page.
I have my first small SQl Server 2005 database developed on my localserver and I have also its equivalent as an online database.I wish to update the local database (using and asp.net interface) andthen to upload the data (at least the amended data, but given thesmall size all data should be no trouble) to the online database.I think replication is the straight answer but I have no experience ofthis and I am wondering what else I might use which might be lesscomplicated. One solution is DTS (using SQL 2000 terms) but i am notsure if I can set this up (1) to overwrite existing tables and (2) notto seemingly remove identity attributes from fields set as identities.I know there are other possibilities but I would be glad of advice asto the likely best method for a small database updated perhaps onceweekly or at less frequent intervals,Best wishes, John Morgan
How do I insert data that I have collected in a local database onto a table on my online ie hosted database which is on a different server?
At the moment I am just uploading all the data to the hosted DB but this is wasting bandwith as only a small percentage of data is actually selected and used.
I thought that if i used a local DB and then update the table on my hosted DB this would be much more efficient, but I am not sure how to write the SQL code to do this!
I got thrown into a new project that is going to require me to update an SQL server database tables from an Access table on the backend of an Oracle database on another server. At the end of each day the Access dabase will be updated from the Oracle database.
What I need to do, is when the Access database is updated I need to have the table in the SQL database automaticaly updated. When a new record is added to the Access table I need the new record added to the SQL table. When a record is deleted in the Access table I need to keep that record in the SQL table and set a field to a value (such as 0). And when a record is updated in Access, have it updated in SQL.
Needless to say this is a bit out of my area and not sure how to accomplish this.
i have sql 2005 installed on my personal machine, but our server has sql 2000 on it. the structure of my database was made on the server, but i'm not sure how to update the server copy from my local copy. when i try to export my data from my local machine to the server (or import from my local machine to the server), i get pre-execute errors.
roughly every other week, i'll need to be able to update the server version from my local version, so i'm trying to find the most efficient method. what is the best way to update a 2000 database from a 2005 database? it doesn't matter if i append or overwrite, but i do use identity fields. the error i get when trying to use the import/export wizard is:
- Pre-execute (Error)
Messages
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". (SQL Server Import and Export Wizard)
Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify that the column metadata is valid. (SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Destination 3 - ReleaseNotes" (202) failed the pre-execute phase and returned error code 0xC0202025. (SQL Server Import and Export Wizard)
Appreciate your efforts in answering queries of so many newbees!I hope to find answering my query..I have created a logon screen to which i have also given the option of changing the password ... Now below is the code for updating the new password given by the user ....Imports System.Data.SqlClient Dim con As New SqlConnection("server=sys2;initial catalog=kris;integrated security=SSPI") Dim cmd As New SqlCommand("select * from u_login", con) Dim dr As SqlDataReader Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load End Sub Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click con.Open() dr = cmd.ExecuteReader While dr.Read() If dr(0) = txtEmail.Text And dr(1) = txtoldpwd.Text Then Dim NewPwdStr As String = txtnewpwd.Text Dim OldPwdStr As String = txtoldpwd.Text Dim sqlstr As String = "Update U_Login set pwd = ('" & NewPwdStr & "') Where pwd = '" & OldPwdStr & "'" Dim cmd1 As New SqlCommand("sqlstr", con) cmd1.ExecuteNonQuery() Response.Write(" Password Changed ... Please login again") End If End While dr.Close() con.Close() End Sub The above code although doesnt throw any error however it shows a blank screen and doesnt even update the new password. Can you plz help me understand what could possibly be wrong in my code n why is that am getting the blank screen. Your help will be highly appreciated!Thanks,Brandy
Im sorry for how simple this question is im.. brand new at this.I am trying to write a program that will take information entered in 4 txt fields on a webform and populate a sql database with them with a submit button.. Eventually its going to be a updateable phone list for my department but for now im just trying to get the submit button to work. More then anything i just need a good resourse to read up on it. Thankyou Adam
At present I am working on a school project. the project was installed few months back. database is MSsql server. Because of some new things i had to change few tables, add few columns and add few tables also. Now My problem is that the school is already having the data which they don't want to be disturbed . So I want My structure and their data.
I'm having a difficult time calling UPDATE when my 'WHERE" clause calls ID,which is numeric and the primary key. I found that I can do a SELECT justfine with "WHERE ID".. Is there something special about UPDATE that i'm notgetting? I've searched the internet but it appears that what I'm doingshould work.Any help would be greatly appreciated.if (modcustomer == "relate"){var modid1 = Request.Form ("modid1");var modid2 = Request.Form ("modid2");var relationship = Request.Form ("relationship");var comments = Request.Form("comments");SqlString = "UPDATE Customer SET Relationship = ' " + relationship + " 'WHERE ID = " + modid1;connection.Execute (SqlString);}thanks,Debbie EhrlichReliable Software - Developers of Code Co-op®, Server-less version controlfor distributed teams www.relisoft.com
Can someone please tell me what I am missing... I am trying to update a record with information a user types into a textbox. I am not getting any errors even when in DEBUG mode. But yet the Data is not getting updated.
Any Help would be very much appreciated.
T
CODE:
Protected Sub bUpdateSlsNum_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles bUpdateSlsNum.Click
Dim myCommand As SqlCommand
Dim myConnection As SqlConnection
myConnection = New SqlConnection("Server=.SQLEXPRESS;AttachDbFilename=|DataDirectory|CustomerInfo.mdf;Integrated Security=True;User Instance=True")
Dim UpdateCmd As String = "UPDATE CustomerSalesNum SET WorkSheetID=@WorkSheetID, Price=@Price, Freight=@Freight, Setup=@Setup, " _
This is a weird problem. I added a database to the app_data folder, but when I make updates and then run some reports the updates aren't shown. The database is updated, cause queries show the updates and I inspected the database. The updates however, aren't being reflected when I run the reports.
I have a web project created with VS2005 that is connected to a database on an SQL Server. I've created a data diagram on the SQL Server and created a matching dataset in my project. The data diagram has the 'enforce relationship...' and cascade textboxes checked for the table relationships. My dataset has 'both relation and foreign key constraint' checked with the rules all set to cascade. The code creates a table adapter then does the tableadapter.Update. The data gets added to my main table, but not to any of the child tables. Any ideas on what could be causing this? Thanks,Rhonda
I am wondering how some of you update your database once it is already been in use. So take for example you have a few tables and users can enter data into the site. Now suppose you either add tables to the db or you add fields to an existing table. I am using sql express 2005 and just want to copy the db over with the copy website but it would overwrite any data users entered.Is there any easy solutions or suggestions for this.Thanks
I am quite new to ASP.net 2.0. I have had plenty of experience using ADO.net in standard windows applications. In my app I am opening a connection to an SQL database and I am creating a DataTable without a DataSet: Shared m_cnADONetConnection As New System.Data.SqlClient.SqlConnection Shared m_daDataAdapter As System.Data.SqlClient.SqlDataAdapter Shared m_rowPosition As Integer Shared m_dtContacts As New System.Data.DataTable I am initializing everything and filling my DataTable when the Page first Loads if it isnt a postback.Protected Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Form1.Load If Not Page.IsPostBack Then m_rowPosition = 0 m_cnADONetConnection.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=C:First ASP DatabaseApp_DataMyFirstDatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True" m_cnADONetConnection.Open()m_daDataAdapter = New System.Data.SqlClient.SqlDataAdapter("Select * From Books", m_cnADONetConnection)m_cbRefillCommand = New System.Data.SqlClient.SqlCommand m_daDataAdapter.Fill(m_dtContacts) Me.ShowCurrentRecord() End IfEnd Sub The Me.ShowCurrentRecord Sub assigns the values of the current record(row) in the DataTable via (m_rowPosition) to TextBox controls on the form: I also have record navigation buttons on my form: << < > >> Moving me from record to record (row to row) by incrementing or decrementing m_rowPosition All is good! I am able navigate the DataTable and the textboxes change their text properties accordingly from record to record. The only other control on my form is a button which I'm coding its click event to save changes that I made to the current row (record) by changing the values in the textboxes then clicking save. Here is my code:Protected Sub ButtonSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ButtonSave.Click If m_dtContacts.Rows.Count <> 0 Then m_dtContacts.Rows(m_rowPosition).BeginEdit()m_dtContacts.Rows(m_rowPosition)("Title") = TBTitle.Text m_dtContacts.Rows(m_rowPosition)("Author") = TBAuthor.Textm_dtContacts.Rows(m_rowPosition)("YearPublished") = TBYearPublished.Text m_dtContacts.Rows(m_rowPosition)("Price") = TBPrice.Textm_dtContacts.Rows(m_rowPosition)("LastReadOn") = TBLastReadOn.Textm_dtContacts.Rows(m_rowPosition)("PageCount") = TBPageCount.Text m_dtContacts.Rows(m_rowPosition).AcceptChanges() m_dtContacts.Rows(m_rowPosition).EndEdit() m_dtContacts.AcceptChanges() m_daDataAdapter.Update(m_dtContacts) End Sub After I click save I can navigate through my records and back to the one I just changed and updated and all is well. The changes were made in the table. However, when I close the page and exit out of Visual Web Developer and reopen the database: THE CHANGES WERENT UPDATED!!! This worked all the time in VB2005.net when developing a standard windows app. Can I use the same approach I was using in my code above or am I missing something.
I have read and searched all over and what I'm thinking is that my UpdateCommand, InsertCommand, DeleteCommand, SelectCommand are empty. Do I have to do it this way?
Hello all Can any one help me in updateing the dataBase with the updated datset through the DataGrid i am able to get the updated dataset from the data Grid but i dont know how to update the database with that kindly help
Hi all, I'm getting an error when updating my database. The connection is fine and the dropdown menu is good also so i think it's the way i've formed my sql string. Here's the line i think i'm having probs with:
Sub UpdateRecord (Src As Object, Args As EventArgs)
'-- CHECK FOR VALID RECORD --- Dim ValidRecord As Boolean = True
'-- Check for missing Project Title If ProjectTitle.Text = "" Then ProjectTitleMessage.Text = "Missing Project Title" ValidRecord = False End If
'-- Check for missing Lecturer Name If LecturerName.Text = "" Then LecturerNameMessage.Text = "Missing Lecturer Name" ValidRecord = False End If
'-- Check for missing Project Description If ProjectDescription.Text = "" Then ProjectDescriptionMessage.Text = "Missing Project Description" ValidRecord = False End If
If ValidRecord = True Then
'-- UPDATE A RECORD -- Try DBConnection = New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:finalyearproject2projectallocation.mdb") DBConnection.Open()
I'm trying to build a record update form and I'm going off the "Build Your Own ASP.NET Website" book. I'm not getting any compilation errors; the database just doesn't update.
I am happy with my mySQL install. I can quite happily created tables and fill them with initial data using mySQL Client
What I want to do now is via a web browser be able to add data to various tables etc. I need to make the web entering part of it as simple as possible for the users.
Although the codes get correctly compiled and executed the tables on the database are not getting updated. I also used the execute sql command manually but it also doesnt work. :-( What to do?
I am preparing a desktop application in Visual Basic 2005 with SQL Server 2005(working with Local Database). Regarding application, my client wants to install the same application in office and also in his laptop to work at home. It means the database is installed into two different locations. If any new records are inserted from multiple tables in desktop, then how can i update the data in the laptop, and vice-versa. My client wants if he work in his laptop at home and bring back the database in his USB. then how can i update the data in the desktop. Even i want to check whether new records has been added in USB database.
Please give me solution for my problem. If possible, please write a code for me.
Is there any possible solution directly with SQL Server 2005 alone?
I recently converted a program I made in C# from Access to Sql Server Express and I noticed something odd.
Anytime I update or insert data into the database, it only sticks around until the program closes. I can insert new records but as soon as the program quits, the inserts are gone.
Is this a bug or is there something else I need to do?
I am having the oddest problem. I can update the data in the dataset, but when I use the update command, or try to do a update manually it will not save the values back to the database.
tbl_strt_tmesTableAdapter.Update(lesson_plannerDataSet.tbl_strt_tmes);//this should save the values
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
the table adapter update, should send the values to the database, but for some reason, it is not. i can see the existing values from the database, along with my new values that i added in my loop, but those values are not being sent to the database when I invoke the update command on the data adapter.
Any help will be greatly appreciated. I am making this program for my wife who is a school teacher.
