Problem Updating Data While Using Datasets And Sqldatasource
Apr 3, 2008
hello.
i'm having the following problem.
in one page i have a dataset created at runtime along side with sqldataadapter and an sqlconnection.
i'm using a dataset here since i'm working with heirarchical tables.
when i click on child table [in a spcific column] it opens up a new aspx page with an editing form [formview in edit mode]
when
i press the update button it claims to update the data, but when i
close the form and reopen it from the same column it opens the form
again, but with the old data, and the new data doesn't get updated
until i close the openinig aspx page [the one with the table]
refreshing it doesn't work, nor does creating a postback.
should u always close everys sqldatasource connection string and the dataset associated with it, if so how do u do that (ie the proper way) and is it possible to to do it in the session area of global.aspx, NEt 2.0 or should it be done when exiting the page containing the data stuff, also same question for NET 1.0 datasets and adapters and strings,
I'm using visual basic coded datasets right now, and caching them, so as all of my web app users access the cache once the first person's gone to my site and initiallized the page. If Cache("ds_cache") Is Nothing Then Dim ds As New DataSet blah blah code blah blah blah
Cache.Insert("ds_cache", ds)
End If What I was wondering is, if it's possible to cache the datasets that get created and are used by Sqldatasources. Or if by chance caching is automatically done and controlled for these datasets, as it's the server that makes and handles them. The reason I wonder about this because I'm not sure how you go about referencing one of those datasets, because you can't give them a name/id. And I've not found mention anywhere of you being able to do so. The real point or question I'm trying to get at is to see which performs better , or uses less requests back to the server for the data as a lot of my data will be repeatedly used by all of my users and so I'd like to save myself from using as many repeated connections as possibles in regards to using a typed dataset versus filling my controls from a sqldatasource? Thank you in advance for your response.
I am looking for a way to update a sqldatasource what I have is a ASP Wizard applicationstep oneis a dataview with the select ability it displays an ID and Namein step two what i want it to do is take the ID from step ones select and put that into the where clause so I have select * from table where id = step1selectedID
When I bind a GridView to a SQLDataSource where are the update parameter values set. What event will allow me to see the paramter values. I am trying to understand a little better how things work under the covers.
Hello, I just started working with ASP.NET.I'm trying to use the CheckBoxList Control. As I understand it, you can bind a SqlDataSource to this control and it loads the list for you. However tp precheck the items, you have to do this manually. This part works fine. Next part was to save whatever the user checks. I wrote stored Procedure and now just trying to pass 1 parameter to the stored procedure using a second SqlDataSource. I get the error: "A severe error occurred on the current command. The results, if any, should be discarded."Here is the second datasource I am using to try and save the data:<asp:SqlDataSource ID="sds_PersonRole" runat="server" ConnectionString="<%$ ConnectionStrings:Development %>"SelectCommand="usp_selectPersonRole"SelectCommandType="StoredProcedure"UpdateCommand="usp_updatePersonRole"UpdateCommandType="StoredProcedure"><SelectParameters> <asp:ControlParameter ControlID="gv_Person" Name="PERSON_ID" PropertyName="SelectedValue" Type="Int32" /></SelectParameters><UpdateParameters> <asp:Parameter Name="strXML" Size="8000" Type="String" /> <asp:Parameter Direction="InputOutput" Name="err_msg" Type="String" Size="150" DefaultValue="0" /></UpdateParameters></asp:SqlDataSource>I have a code behind file with the following modules: (btnEditPerson is clicked to start the process. the sds_PersonDetails is updated via form contolls and works fine.) Error occurs on the bolded line (Stored procedure is just accepting the string and saving to a field. It works fine, I tested it. Its just erroring out before it runs the stored procedure. Protected Sub btnEditPerson_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnEditPerson.Click sds_PersonDetails.Update() gv_Person.DataBind() sds_PersonRole.Update()End Sub Protected Sub sds_PersonRole_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles sds_PersonRole.Updating Dim command As Data.Common.DbCommand command = e.Command 'un-check all checkboxlist items (count - 1 to account for starting at 0) Dim listCount As Integer = cbl_Role.Items.Count() - 1 Dim strXML As String strXML = "<personRole>" For x As Integer = 0 To listCount If cbl_Role.Items(x).Selected() = False Then strXML = strXML & "<person id='" & gv_Person.SelectedValue & "' />" strXML = strXML & "<role id='" & cbl_Role.Items(x).Value & "' />" End If Next strXML = strXML & "</personRole>" command.Parameters("@strXML").Value = strXML lbl_ErrMsg.Text = command.Parameters("@err_msg").Value.ToString() End Sub
I have such a problem:i try to update a row in my table using: protected void selectButton_Click(object sender, EventArgs e) { String taskID = projectsGridView.SelectedRow.Cells[0].Text; usersSqlDataSource.UpdateCommand = "update [Users] set [TaskID]=@task where [UserID]=1"; usersSqlDataSource.UpdateParameters.Add("task", taskID); usersSqlDataSource.Update(); }And i receive error on usersSqlDataSource.Update():You have specified that your update command compares all values on SqlDataSource 'usersSqlDataSource', but the dictionary passed in for oldValues is emptyWhat have i done wrong? Parameter are not set?
Hi, I'm new in ASP 2.0. I need to incorporate edit and delete capability in GridView. Using the wizard, i've generated this code. When I delete a row, it gets deleted but update does not work. I've tried several ways. I got no error or exception. But row is not updated. I've checked database, and I think the update query is not executing at all. Please let me know, what I'm doing wrong? Here is the source code for reference. I'm using Visual Studio 2005 with SQL server 2005 Express Edition. Regards
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
when i added a table in the layout tab, and drag in fields from the 2 datasets, for the dataset1, i would get fields like "=Fields!shirts.Value". but, when i drag in fields from dataset2, it shows up like "=Sum(Fields!shoes.Value, "dataset1")".
i don't want the sum, i just want the value. when i take out the "sum" part of the field expression, i get an error that says:
[rsFieldReference] The Value expression for the textbox €˜shoes_1€™ refers to the field €˜shoes€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
I have one dataset DS1 connected to one SQL 2005 server DB, another dataset DS2 to different server on different physical machine, different SQL 2005 DB. I'm trying to build only one chart which data is based on those two different datasets DS1 and DS2. I understand that I can use subreports to use two different datasets, but how can I crossjoin those data together in one chart?
In the BI development studio when I have to change the data source for data sets within a report, I have to go to each of the datasets individually to do this. Is there a quicker way to do this. Say I want to change data source for the entire report in BI dev studio.
I have a question about loading data on the page lode event. The question is more conceptual then how to.
Using C#, I would like to make a call to a SQL 2000 Server, with the use of a stored procedure return one row with eleven fields. Then use the data to fill five different controls. There is no manipulation of the data it is just presented as information. With a DataReader and the GetValues method using the resultant array I can fill the controls (or can I), or with a DataSet and the Load method do the same thing. Now this code can be placed in the code behind page or it can be implemented in a class. This is the only page that uses this combination of fields and controls; however I would think a more generic call to the data could be made then pick and chose the fields as needed. What process makes the most sense? Is what I just described even possible (as yet I haven’t tried it)?
As always thanks in advance for any thoughts, comments, and suggestions.
I have to create tablix report using data from 2 datasets. The data source for one of the dataset, DataSet1Â is Microsoft SQL Server and the data source for the other dataset, DataSet2 is Excel.
I have to pic 3 columns from DataSet1 and one column from DataSet2.
I'm using SQL 2008 Februar CTP and trying to use SSIS for Data Source as described in http://msdn2.microsoft.com/en-us/library/ms159215(SQL.100).aspx. I've created SSIS package and preformed steps described in http://msdn2.microsoft.com/en-us/library/ms345250(SQL.100).aspx (after fixing version to 10.0.0.0).
Now I got next error when trying to add SSIS DataSource (in Report Designer - Visual Studio):
Error messageThe data extension SSIS could not be loaded.
I have two charts which are based on their own separate datasets. One chart shows data aggregated by MONTH, the other chart shows data aggregated by WEEK. I have put both of these charts inside a rectangle and set their visibility based on whether or not there is data returned by their respective datasets. This part is working just fine.
However, I have added a tablix to the report, deleted all columns and rows leaving only one remaining row/column so it looks like a textbox. This tablix is sitting on top of the two charts. The problem i'm having is that when both charts return no data and they do not show up in the report, the tablix still remains visible. I need to set the visibility but based on a condition between the two different datasets behind each chart.
Here's what I need: If ONE of the charts returns data, then I need the tablix to be visible. If neither chart returns data, then I need the tablix to be invisible. I'm trying to write an IIF statement comparing the two datasets but I get inscope errors. Isn't it possible to compare values which are contained in two separate datasets in SSRS?
Seems like something like the following logic should work:
IIF(CountRows("Monthly_DataSet") > 0 OR CountRows("Weekly_DataSet") > 0 , FALSE, TRUE)
Obviously the above expression will return syntax errors, but you'll get the point as to how i'm thinking. How can I set the visibility of the tablix based on these two charts?
building some kind of ecommerce site. I want to allow the user to modify the image that he has stored for a product. The image is stored in a directory, in the table GAMME I only have the image name. When the user selects a new Image, I first delete the old image in the directory, then save the new image, but what I can't do is to update the table with the new image name, when I write sqldatasource1.update() I have an error "the dictionnary passed with old values is empty"(translated from frengh). the update statement in sqldatasource1 is: " UPDATE Gamme SET imagename = @imagename, imagesize = @imagesize WHERE (product_ID = @original_product_ID)"
on events sqldatasource1.updating I have written: e.Command.Parameters("@imagename").Value = (FileUpload1.FileName).ToString() e.Command.Parameters("@taillevignette").Value = FileUpload1.PostedFile.ContentLength e.Command.Parameters("@original_product_ID").Value = Session("Product_ID").ToString this is not written but the session("product_ID") is the good value the problem must come from the last lign, but I don't Understand the problem and how to solve it.
I would like to update about 4,000 records. I would only be updating one column for 4,000 rows in a table that has 50,000 rows. The update information is not the same for each row. I will be updating this from an excell file. Can somebody please tell me how to do this without messing the rest of the data in the table?
I rebuilt my databases using bcp,in order to change sort order and char set. That was OK,the data were re-inserted,but now i can only modify data by SQL Server Enterprise Manager.When a try to chage data by Visual Data Manager or VB application using ODBDC,it's accused that the database is not updatable,it's read only.But in the properties it's updatable.Anybody has a hint of what'sgoing on??
What is the best to update line_no in following code. As you can see 1 invoice can have multiple lines. I would like to assign sequential number to all lines in invoice.
CREATE TABLE #xx (invoicenumber INT, line_no int )
INSERT INTO #xx VALUES(1000,0) INSERT INTO #xx VALUES(1000,0) INSERT INTO #xx VALUES(1000,0) INSERT INTO #xx VALUES(1000,0) INSERT INTO #xx VALUES(2000,0) INSERT INTO #xx VALUES(2000,0) INSERT INTO #xx VALUES(2000,0) INSERT INTO #xx VALUES(2000,0)
SELECT * FROM #xx
result of select * from #xx should be: 10001 10002 10003 10004 20001 20002 20003 20004
What is the best to update line_no in following code. As you can see 1 invoice can have multiple lines.
CREATE TABLE #xx (invoicenumber INT, line_no int )
INSERT INTO #xx VALUES(1000,0) INSERT INTO #xx VALUES(1000,0) INSERT INTO #xx VALUES(1000,0) INSERT INTO #xx VALUES(1000,0) INSERT INTO #xx VALUES(2000,0) INSERT INTO #xx VALUES(2000,0) INSERT INTO #xx VALUES(2000,0) INSERT INTO #xx VALUES(2000,0)
SELECT * FROM #xx
result of select * from #xx should be: 1000 1 1000 1 1000 1 1000 1 2000 2 2000 2 2000 2 2000 2
I have posted on this problem before, and got some wonderful help, but the problems keep growing!
In my table, I have projects,activities, credit amount, debit amount, starting balance. Each project can have several activities associated with it. Each project has a starting balance. Each activity posts an expense to the starting balance of the project. If the project has enough money to handle the charges made by the activities, all the activity expenses can be "posted". Then I want to re-adjust the balance and check the second activity. If there is still enough money to handle the charge from the second activity, then the expenses can be posted and the balance adjusted again. In this checking, I am creating a field called status, and flagging if the activity can clear or not, and a new balance field. The data I have can fall into different examples, listed below.
First example:Only one activity for a project project: 122400 activity: 0000 Cr_Amt: 2145.00 Dr_Amt: 0 Balance: 1190.00
In the above case, as the balance is less than the Cr_Amt, I want to set the Cr_Amt = Balance, and the new balance = 0 and have done that as follows:
Update r set r.Post_Cr = r.Bal_2300, r.new_Status = 'Can Clear', r.new_Balance = 0 From Rev_Rec_Check r WHERE r.project IN (Select project from Rev_Rec_Check d group by d.Project having count(d.project) = 1)
That seems to work for me.
Second Example: More than one activity, with credits and debits project: 145587 activity: 0000 Cr_Amt: 0 Dr_Amt: 2500 Balance: 1452
So, in this case, I need to add the Dr_Amt to the Balance and create a new balance (3952), and then check that the Balance can cover the Cr_Amt
UPDATE t SET t.New_Balance=(t.Bal_2300 + b.postDrSum) - b.PostCRSum, t.New_Status=(CASE WHEN (t.Bal_2300 -b.PostCRSum) >0 THEN 'Can Clear' ELSE 'Still Check' END) FROM Rev_Rec_Check t CROSS APPLY (SELECT SUM(post_Cr) as postCrSum, SUM(post_Dr) as postDrSum FROM Rev_Rec_Check a WHERE (a.project =t.project) AND (a.activity<=t.activity)) b
This does seems to work, and gives me the correct status, and adjusts the new balance correctly, but I realize that doing that doesn't achieve what I want. Ideally, in this example - where there is more than one project/activity, this is what I would like to see: project: 145587 activity: 0000 Cr_Amt: 0 Dr_Amt: 2500 Balance: 1452 newBalance: 3952
I want the amount that gets posted (the Cr_Amt) to reflect what is left in the balance.
There could be cases where there are several activities for one project, so I want to be able to scroll through each activity and post what I can from what they have left in their balance.
I don't know how to approach this. Am I making it more complicated than it actually is? Have I made any sense in trying to explain it?
i'm a newbie in database and i need some ideas for the below mentioned problem.
i'm creating sql table from a txt file using DTS package, now once the table gets created, i need to multiple some revenue accounts with xyz number and expense accounts with abc number.
i'm thinking more towards the line of writing a store procedure but don't have any experience in it.
PLease guide me in the right direction, also if a good book could be refered for future; which will help me query language and this kind of issues, that will be GREAT!!
I have the following below. I have a page that has a text box. I want it to look in the database (SQL Server 2005) and display the number (Assessor_Score) in the box if it exist. If it does not exist I want the user to put a number in and have it insert a new record to the database. I also want the user to be able to change the number that does show up. The code below just inserts two identical records into the database. Also, how do I assign Assessor_Score a value from the textbox to use in the insert statement? Thanks
'Open Connection set conn = Server.CreateObject("ADODB.Connection") conn.open NCRLAP_Conn set rs = Server.CreateObject("ADODB.Recordset") set rs2 = Server.CreateObject("ADODB.Recordset")
''''''''''''''''''Get QRS Assessor's Score'''''''''''''''''' str = "SELECT AssessorScore.iAsmtID, AssessorScore.sSubScale_Short, AssessorScore.Assessor_Score FROM AssessorScore WHERE (((AssessorScore.sSubScale_Short)='" & sSubScale_Short & "') AND ((AssessorScore.iAsmtID)=" & iAsmtID & "))" set rs = conn.execute(str) If rs.EOF = True then 'This score is not currently in Assessor_Score 'Insert now
str = "INSERT INTO AssessorScore (iAsmtID,sSubScale_Short,Assessor_Score) " & _ "VALUES ('" & iAsmtID & "','" & sSubScale_Short & "','" & Assessor_Score & "')" response.Write(str) conn.execute(str) Else This score is currently in Assessor_Score Assessor_Score = rs("Assessor_Score") UPDATE now str = "Update AssessorScore SET AssessorScore.iAsmtID = " & iAsmtID & ", AssessorScore.sSubScale_Short = " & sSubScale_Short & ", AssessorScore.Assessor_Score = " & Assessor_Score & " WHERE (((AssessorScore.sSubScale_Short)=" & sSubScale_Short & ") AND ((AssessorScore.iAsmtID)=" & iAsmtID & "))" End If 'response.Write(str) set rs = nothing conn.execute(str) str = ""
Is it possible to somehow update data as it is copied from source to destination using SSIS.
Currently I extract data from Database A, load it into database B, then clean/update the data in database B and then load it into database C using DTS on 2000.
What I would like to do is extract data from database A, clean/update it, then load it straight into database C without having to load it into database B first.
I am unable to clean/update the source data in database A, as this would be the obvious thing to do.
I have a page containing a FormView, which gets its data from a SqlDataSource control and displays details of a job. Two of the fields are location and job title. I want to re-use this data to create a dynamic page title. I know I can do this by setting the Page.Title to what I want to, but do I have access to the data outside of the FormView to which the data source is bound? If so how? Or will I have to perform an additional SELECT statement to get this data again?
and what this does is fills the dataset with the information from the select command in the dataadapter and then gets the value for the first row of the fieldname
It doesnt seem like this should be that big of a deal, but it has become very frustrating trying to find the answer, does anyone know how to do this? Please post some demo code if possible
Hi Everybody,I have an ordinary SqlDataSource on my page <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:P4SConnection %>" ProviderName="<%$ ConnectionStrings:P4SConnection.ProviderName %>" SelectCommand="SELECT CO_Id,CT_Name,CO_Username,CO_Password,CO_Company,CO_Email,CT_Id,CT_Position,CT_Admin, CO_Session,CO_LastLogin FROM company_contact LEFT JOIN company ON company_contact.CT_Company = company.CO_Id WHERE (CT_Username= & LCase(txtName.Text) & "> </asp:SqlDataSource>How do i use any of these values?I know I can display the values in a GridView or something but I dont want to display them.I originaly was using ODBC and just used the followingDim Row As DataRow = thisTable.Rows(0) Dim lP_ID As String = Row(0).ToString() Dim TheContact As String = Row(1).ToString() Dim theName As String = Row(2).ToString() Dim thePassword As String = Row(3).ToString() Dim TheCompany As String = Row(4).ToString() Dim TheEmail As String = Row(5).ToString() Dim lP_ContactID As String = Row(6).ToString() Dim ThePosition As String = Row(7).ToString() Dim bAdmin As Boolean = Row(8).ToString() Dim sP_Session As String = Row(9).ToString() Dim sP_Last As String = Row(10).ToString()Then I could use the values wherever I wanted.How do I do this with a SqlDataSource?Thanks,G
hello guyshere is my problem:i am developing a asp.net web app in .net 2.0. i have some sensitive data in my database. which is encrypted using DES ( with some key which is only known by the top level authorities ). now there is an option of changing the secret key. on changing the key the sensitive data has to decrypted using the old key and then again encrypted using the new key. Now if the no of records increases i am afraid that it might take a longer time and the application might look as it got hanged. guys i have no clue on how to do this. if you guys have any idea on how to implement this please let me know. any help would be appreciatedVignesh
hello all, I ran into a problem using the "sql data source updating " method. i'm using a form view with about 20 parameters that are bind to controls in the form view and 5 other parameters that i'm setting the value to in the "sql data source updating" method. Every thing updates find execept for the last 5 parameters that i'm setting the values to in the "sql data source updating" method. My store procedure(sp) updates and insert mulitple tables. For some UNKOWN reason the tables that uses the parameteres in the "sql data source updating " are inserting multple records when only 1 record should be inserted. Have anyone have this problem?
