Hi,
Ive got a class in my code which contains data from several tables. A user profile has a single record in a user_profile tables, for each of these records there is several records in a another table, which itself has several records in a tags table.
So far the save method on the class saves the record to the user profile table, then loops through the next two tables info to insert each record. I want to decrease the number of trips to the database and am wondering if anyone can advise on the best way to do this.
I am considering using a dataset to retrieve and store the records, instead of a datareader Im using currently. Then when Im done altering the data in the dataset (all three tables) I can commit all the changes in one database transaction?? I have been avoiding datasets due to the fact datareaders are apparently faster.
My other option is to try make three calls to the database, one per table, updating my stored procedures to accept arrays of data using XML??
Can anyone tell me what is the best option for me?
I have a code to update an access database from one of my dataset tables, but i want to insert columns manually
currently i am using this code to update my db
Dim cb As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(dtadpt)
AccessConn.Open()
dtadpt.Update(DataSet, "recordsforupdate")
AccessConn.Close()
this code automatically inserts all the columns of dataset table to the access database table.
what i want is to inset the values for eg. in col4 of my dataset table into col5 of my access table, for that i want to manually use "inset into" statement to update the db table, but i am having problem with the syntax for using dataset..can anyone help please
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 Could you please help me solving this problem? I have a stored procedure called subscribe for inserting a new row to subscriptions table. Then I added a new query (nonquery) to my dataset called 'Subscribequery' for handling the stored procedure. now, I want to run the query lke this: DataSet1TableAdapters.SubscribeQuery C = new DataSet1TableAdapters.SubscribeQuery(); C.Subscribe(Profile.UserName, Convert.ToInt32(Subscriptions.Rows[1].Cells[1].Text));
but nothing is added to table. what can I do? Should I be looking for something like Update(dataset) method for my query? many thanks in advance
Hi all, I'm trying to update various rows in my DB with a new value once an action occurs. I am using the following code which does not throw any exceptions, but also does not change the values. Can someone steer me in the right direction? public static void ChangeRefCode() { SqlConnection dbConn = new SqlConnection(ConnectDB.getConnectString()); SqlDataAdapter dataAdapt = new SqlDataAdapter("Select * from Posting",dbConn);
I am using ASP.NET 2.0 WebForms and I was trying to use a DataSet to add rows programatically without adding the actual records to the MS SQL Server Databases. Is this possible or should I be doing this another way? DataSet myDS = new DataSet();DataTable myTable = new DataTable("table1");myTable.Columns.Add("col1", typeof(string));myDS.Tables.Add(myTable);myTable.Rows.Add("MyValue"); Thanks.
Hello,I am trying to update the Image type field named as BlobData in sqlServer 2000. I capture the record in a data set to have the schema and try to update the BlobData field of type Image by assigning it a value of buffer as below. but my assignment seems to be wrong and generates an error saying Object reference not set. Code========================= Dim fileBuffer(contentLength) As Byte
Dim attachmentFile As HttpPostedFile = Me.fileUpload_fu.PostedFile
Catch ex As Exception Me.error_lbl.Text = ex.Message Return False Finally cn.Close() End Try ========================== Can anyone please help this one out. Cheers.Imran.
I have 4 sets of select queries under 1 stored proc, now on the report calling the stored proc via dataset. when i run the dataset the only first set of the select query related fields appearing under the dataset.
But on the back end sql server, if i execute the same stored proc, i get 4 resultsets in one single executioln.
i am not seeing the remaingin 3 resultsets, on the reports dataset.
Is it possible on the reports or not.
In the asp.net project i was able to use that kind of stored procedures whcih has multiple select statements in 1 procedure., i use to refer 0,1,2,3 tables under a dataset.
I have a dataset that was created via a source + lookups + derived columns.
I wish to take this dataset and treat is as a table within a sql statement so that I can update a permanent table with the a specific value within the temp dataset.
In sql this is what I am trying to do:
SELECT COUNT(*) AS Count_of_Employees, DEPT FROM Employees GROUP BY DEPT
UPDATE Departments SET Number_of_Employees = Count_of_Employees WHERE Dept = dept.
Hi, I need help with updating a record. When I run the following code underneath the update button, the record does not update and I get no error message. Anyone have any ideas?
Code Snippet
Dim ListingID As String = Request.QueryString("id").ToString Dim Description As String = DescriptionTB.Text Dim PlaceName As String = PlaceNameTB.Text Dim Location As String = LocationTB.SelectedValue Dim PropertyType As String = LocationTB.SelectedValue Dim Price As Integer = PriceTB.Text Dim con As New SqlConnection(ListingConnection)
Dim sql As String = "Update Listings SET Description = ?, PlaceName = ?, Location = ?, PropertyType = ?, Price = ? WHERE ListingID = ?" Dim cmd As New SqlCommand(Sql, con)
Hi, I have an application where I'm filling a dataset with values from a table. This table has no primary key. Then I iterate through each row of the dataset and I compute the value of one of the columns and then update that value in the dataset row. The problem I'm having is that when the database gets updated by the SqlDataAdapter.Update() method, the same value shows up under that column for all rows. I think my Update Command is not correct since I'm not specifying a where clause and hence it is using just the value lastly computed in the dataset to update the entire database. But I do not know how to specify a where clause for an update statement when I'm actually updating every row in the dataset. Basically I do not have an update parameter since all rows are meant to be updated. Any suggestions? SqlCommand snUpdate = conn.CreateCommand(); snUpdate.CommandType = CommandType.Text; snUpdate.CommandText = "Update TestTable set shipdate = @shipdate"; snUpdate.Parameters.Add("@shipdate", SqlDbType.Char, 10, "shipdate"); string jdate =""; for (int i = 0; i < ds.Tables[0].Rows.Count - 1; i++) { jdate = ds.Tables[0].Rows[i]["shipdate"].ToString(); ds.Tables[0].Rows[i]["shipdate"] = convertToNormalDate(jdate); } da.Update(ds, "Table1"); conn.Close();
i created a query to insert a row in DataSet in Visual Studio 2005. i gave the method name to the query i created. as i understood it returns '1' if successful or '0' if not.
Is there any way to use a graphical designer to build your insert & update SQL statements in Enterprise manager? I mean Access has an EASY way to build them, surely SQL does too?
I would just build them in Access and copy the SQL, but then I'm stuck replacing all the "dbo_" with "dbo." and other little nuances.
In Sql 2005, I can't get this to work. I am getting an error that says SQL does not support subquery. SQL has a problem with my subquery. It works fine with one subquery, but adding multiple gives me an an error.
INSERT INTO STU_SUMMARY (SUMMARY_PERIOD, CHILD_COUNT, NO_DATA_ERRORS, PROG_CD_DESCREP, TOT_CURR_SPEDSTU) VALUES (GETDATE(), (select count(*) counter from DATA_ERROR_DETAIL), (select count(*) counter from DATA_ERROR_DETAIL), (select count(*) counter from DATA_ERROR_DETAIL), (select count(*) counter from DATA_ERROR_DETAIL), )
I want to use SSIS in order to synchronize data. The OLEDB and ADO.NET Destination Adapter just inserts the rows read from the source. Since I have PK Constraint on destination, the tranform fails. How can I ask to update the records in destination DB?
Hi all,I am using a Strongly Typed DataSet (ASP.NET 2.0) to insert new data into a SQL Server 2000 database, types of some fields in db are nvarchar. All thing work fine except I can not insert unicode data(Vietnamese language) into db.I can't find where to put prefix N. Please help me!!!
Can someone please help me modify the following pivot query into an INSERT INTO query (i.e. results are exported into a new table)... SELECT RespondantID, [1] As Q1, [2] As Q2, [3] As Q3, [4] As Q4, [5] As Q5, [6] As Q6, [7] As Q7, [8] As Q8, [9] As Q9, [10]
As Q10 FROM (SELECT RespondantID, QuestionID, Answer FROM [3_Temp]
WHERE SurveyID=1) AS preData PIVOT
( MAX(Answer) FOR QuestionID IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]) )
I have a question about something odd happening with an insert query.
I am working on a password protected site and I want to moniter general student use. I have an on page load even which picks up their user id and the url and sends it to the database
Because my site is a beta site, I also want the users to be able to add their comments as and when they want, so I built a small feedback form on each page.
The quick feedback form has a panel to hide it until a user clicks on a radio button and this reveals a text box and a submit button.
However, since I have added this second sql connection with an insert, I now get an error message I was getting a connection string error when I tried to point at the connection string in the web.config file, so I put the full path in to the database and now I get an error message that says the conTrackUser has not been declared, but as you can see from the code it certainly has. So I'm beginning to thing that the error is something entierly different, but that's probably me being thick.
I've pasted the two insert blocks below, I can't see anything wrong with them, but then I'm mostly groping about in the dark with asp.net.
Is it not possible to have two insert commands on a page? That would be daft.
Here is the code.
Track and initilize the radio button and panel for the feedback:
Sub Page_Load(Sender As Object, E As EventArgs) Dim UserID as String = session("UserID") pnlQuickFeedback.Visible = False If Not Page.IsPostback Then Dim conTrackUser As SqlConnection Dim strInsert As String Dim cmdInsert As SqlCommand
conTrackUser = New SqlConnection("server='server'; user id='me'; password='nuts'; database='database'")
Does anyone know how to improve performance on insert statements. I have to run a query of several thousand insert statements, but it just takes too long. Does anyone know of any good tips to improve performance?
I have a table adapter for one of my SQL2005 tables, and in two different fields I accept a date time. Now 99% of the times, new rows to this table will be filled out using DateTIme.Now(), as a Time Stamp is what I'm going for.
Here is the line of code in question...cops_current_data_adapter.Insert(ProductOrder, Convert.ToInt16(Session["StationId"].ToString()), PartNumber, DateTime.Now, DateTime.Now, Convert.ToInt16(qty), 0); The second DateTime.Now is the one that can be null, and it's throwing a formatting error everytime I try and drop it in there. It's a FormatException, and there's not much more to the example except unhelpful tips like be careful when conveting a string to a dateTime, which I'm not doing. Needless to say for the code to compile, and then throw a Format error at runtime is a bit frustraiting. Any suggestions would be most appreciated
Ok I think I will need to use a temp table for this and there is no code to share as of yet. Here is the intent.
I need to insert data into two tables (a header and detail table) the Header Table will give me lets say an order number and this order number needs to be placed on the corresponding detail lines in the detail table.
Now if I were inserting a single invoice with one or more detail lines EASY, just set @@Identity to a variable and do a second insert statement.
What is happening is I will be importing a ton of Invoice headers and inserting those into the header table. The details are already in the database across various tables and and I will do that insert based on a select with some joins. As stated I need to get the invoice number from IDENTITY of the header table for each DETAIL insert.
I am assuming the only way to do this is with a loop... Insert one header, get identity; Insert the detail table and include the IDENTITY variable, and repeat.
when I try and insert something like sp_PRO '123154', 'it's good', '23.23', 1
I can't insert "'" and "," because that is specific to how each item is delimited inorder to insert into the stored procedure. But if I hard code this into a aspx page and don't create a stored procedure I can insert "'" and ",". I have a scenario where I have to use a stored procedure...confused.
I am using a sql server 2000 database to log the results from a monitorthat I have running - essentially every minuite, the table describedbelow has a insert and delete statements similar to the ones below runagaint it.Everything is fine for a few weeks, and then without fail, all accessesto the table start slowing down, to the point where even trying toselect all rows starts timing out.At that point, the only way to make things right that I have found, isto delete the table and recreate it.Am I doing something specific that sql server really doesn't like? Isthere a better solution then deleting and recreating the table?CREATE TABLE [www2] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[stamp] [datetime] NULL CONSTRAINT [DF_www2_stamp] DEFAULT (getdate()),[success] [bit] NULL ,[report] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[level] [int] NULL ,[iistrace] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOINSERT INTO [www2] ([Report],[Success],[Level],[iistrace],[Stamp])VALUES ('Error on: <ahref="http://www2.klickit.com/include/asp/system_test.asp">http://www2.klickit.com/include/asp/system_test.asp</a><br><br>The operation timedout<br><br>(Test Activated From: Lynx/2.8.2rel.1libwww-FM/2.14)',0,1,'',getDate())DELETE FROM [www2] WHERE (Stamp<getDate()-3) AND (Success=1) AND (ReportNot Like 'ResetThanks in advance,Simon Withers*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I'm writing an application for Windows Mobile 5 / Pocket PC using VB.NET 2005. The database is connected using an instance of SqlCeConnection and updated by an SqlCeCommand.
The application can perform select queries on data originally entered into the database through Visual Studio, or perform update / insert queries at run time. Anything inserted or updated can be returned by a select query whilst the application is running, however, anything I have inserted or updated doesn't appear to be written to the SDF file and hence is not in the database after restarting the application.
Am I missing something that's different between performing queries on an SQL CE database on Pocket PC and an ODBC source in a normal Windows application?
I have the following stored procedure that inserts records and updates the new record. The parameter @rpt_id has a value of -1 when entering the procedure. It needs to be updated with the new record if (identity) once the record is inserted, bu sometimes the update does not happen. The new records ends up with -1 in the rpt_id column.
I have included the stored procedure. I will appreciate any ideas?
I have 18 odd subscribers at the moment, my publisher and disttribution is on the same machine with push subscriptions.
The questions I have
nr 1. While trying to initialize new subscribers I get loads of deadlocks even after I stop dist cleanup agent. This *I think* cause some other unexpected problems.
nr2. The queue reader would fail saing it cannot find the "insert" proc on the publisher, although it exists. I have changed anything on the publication so I'm not sure how this happens or why.
nr3. I replicate a varbinary(max) column and on the odd occasion get the "Length of LOB data" errors which I then set with sp_configure. The catch here is that the length never exceeds a "len()" of 4000, thus the reported LOB and my calculation doesn't tie up.
I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to
1) separate the master (static) data from the flat files,
2) check whether that data is present in the master table, if not then insert that data into the master table
3) If data present then move that existing record to an history table and then update the main master table.
All the 7 fields need to be checked to uniquely identify a single record in the master table.
How can this be done? Whether we can us a combination of data flow items or write a sql procedure to do all this.
I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters. I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.
I have a small number of rows in a dataset, Table 1. There is a CLOB on a large dataset, Table 2. They join on a PK. I would like to retrieve this CLOB and add it to the data flow for Table1. In short I want to emulate the following:
Table 1: Small table without CLOB, 10 rows. Table 2: Large table with CLOB, 10,000,000 rows
select CLOB from table2 where pk = (select pk from table1)
I want this to return the CLOBs for the small number of rows in Table 1. The PK is indexed obviously so it should be a fast look up.
Table 1 and Table 2 live on different Oracle databases. How do I perform this operation efficiently in SSIS? It seems the Lookup and Merge Join wont do this.
