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
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.
Hi GuysI am facing a problem while updating a DataTable to database using sqldataadapter.In my application I am fetching a dataTable from Database and filling values into textboxes on the UI.User has given facility to change or add new texboxes (new row) on the fly .(Textboxes on the UI are like in a row(tr) having two textboxes in each row.)I am again then converting a new (empty) datatable from scrap and filling its rows with the value of textboxes on submit button event.The datatable which I have created has the same schema as the database table.Now what I want here is that changed value should be reflected to the already existed rows in database and only new rows should be inserted.I am using a SQLDataAdapter having two sqlcommands , one for update with update procedure name and parameter mapping and another for insert with parameter mapping.But SqlDataAdapter is inserting fresh new rows all the time in the database table not updating the older one.Please help me in the matterThanks & RegardsVishal Sharma
Filling a DataTable from SqlQuery : If SqlQuery returns some null values problem ocurrs with DataTable. Is it possible using DataTable with some null values in it? Thanks
Hi, I got a problem regarding autoincremental Database Fields and DataTables. Using the technique to map the DataBase to an *.xsd file and trying to insert a Row into the table always results in this kind of error:Cannot insert explicit value for identity column in table 'Cars' when IDENTITY_INSERT is set to OFF. This only happens when I set CarIDs properties AutoIncrement = true, AutoIncrementSeed = -1, AutoIncrementStep = -1. Having AutoIncrement = false, results in this error:Column 'CarID' does not allow nulls. Do I missunderstand something, or do I need to change some parts? I would be glad if someone could help me and figure out what I am doing wrong.Thanks a lot have a nice weekend.Regards Johannes
Hi everybody, I am using VB Webforms as my front end and MSDE 2000 as my back end. I am not able to drop a database after executing the following vb code on that database: Dim vConnection As New SqlConnection(vConnectionString) Dim vAdapter As New SqlDataAdapter("SELECT party_type_code, party_type_name, party_type_imported" & _ " FROM Party_Type" & _ " ORDER BY party_type_name", vConnection) Dim vDataTable As New DataTable() vAdapter.Fill(vDataTable) vAdapter.Dispose() vConnection.Dispose() On trying to drop the database using the SQL Command "DROP DATABASE PPCABCD2005", I get the error message:Cannot drop the database 'PPCABCD2005' because it is currently in use ...However, if I don't execute the above code, I am able to drop it. Why is it so? What is the solution to this problem?
Hi All!! I need to export a whole datatable to the database, through bulk insert or any other method..Can any body help me in that??Yes, col's in the database table and datatable are same!!Thx,Regards,Nilee..
I'm working with a legacy database whose structure I cannot change. I'm building a web-based editor for one of the DB's tables. This table has a Primary Key called "Master_Idx" that is an Identity (autonumber) field.To start, I query the DB and populate a data table which is cached. This is used to "feed" the web form. Any additions, changes, and deletions are reflected in this cached table.The user then has the flexibility to press Save at any time, which ensures that the database is modified accordingly.Here's my question:When new records are added, I assign them a Master_Idx value, the first of which was calculated to be 1 + the Max(Master_Idx) value when the editing session started. But when I insert these records back into the database it's possible that another user might have also been doing similar editing (of different records from the same table), so there's no guarantee that the Master_Idx values in the data table will be identical to those in the DB. When I insert a record from the cached table back into the DB, what technique can I use to check what value was assigned to the Master_Idx? If it's not the same as in the cached table then I need to update it locally in a few places.I hope all of this makes sense but if it doesn't then let me know and I'll explain it in another way.Robert W.
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
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.
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,TimPS Be gentle, please -- this is my first Web application. :-)
hi, I have a problem with updating a row in my database. I don't know how to update and i need help. I have a form where the users select a category and a sub category to enter a message. I have two dropdown lists, one is for selecting the category and the other is for the sub-category. In my database for the categories and the sub-categories, i have a variable (Quantity) where i keep the quantity for each table. I just want to be able to increment this variable by 1 (for the selected category and sub-category) when a user enter a new message... The insert procedure is working well and have no problem with it. I use VWD 2005 Express with SQL 2005 express and i code in VB.HELP!!!!!
Hello, i currently have a gridview component displaying data from a SQLSERVER2005 database. I have created an 'edit' hyperlink on each record so that when pressed, the primary key of a record is sent in the querystring to another page when pressed. example http://localhost/Prog/EditAppointment.aspx?AppointmentId=1 In the edit page, i have dragged and dropped a sqldatasource that contains the select and update statements. i would now like to use textboxes and dropdowns to display the data for the particular record selected (in this case appointmentid=1). however i dont want to use a formview or automated component, just to drag and drop the textboxes myself. how do i bind the textboxes in the HTML code to the data from the select statement? My current select parameters look as follows. <SelectParameters> <asp:QueryStringParameter Name="AppointmentId" QueryStringField="AppointmentId" Type="Int32" /> <asp:ControlParameter ControlID="Textbox1" Name="PatientNo" PropertyName="Text" Type="String" /> </SelectParameters> Perhaps there is an error in my select parameters or does the problem lay elsewhere? Any help would be appreicated. Thanks, James.
