Updating Multimple Rows In A Data Source

Apr 9, 2008

I have a sql data source that is filtered by a date range.  The results are then presented in a Gridview.  What I am hoping to do is add a button that will update all the filtered rows in the sql data source.  Is this possible?

The data source is shown below:

<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:MYSTR%>"

 SelectCommand="SELECT * FROM [dbo_cheques] WHERE (([chq_banked] >= @chq_banked) AND ([chq_banked] <= @chq_banked2))"

UpdateCommand="UPDATE dbo_cheques SET chq_printed = @chq_printed WHERE (chq_id = @chq_id)">

<SelectParameters>

<asp:ControlParameter ControlID="Calendar1" Name="chq_banked" PropertyName="SelectedDate"

Type="DateTime" />

<asp:ControlParameter ControlID="Calendar2" Name="chq_banked2" PropertyName="SelectedDate"

Type="DateTime" />

</SelectParameters>

<UpdateParameters>

<asp:Parameter Name="chq_printed" />

<asp:Parameter Name="chq_id" Type="Int32" />

</UpdateParameters>

</asp:SqlDataSource>

 

View 1 Replies


ADVERTISEMENT

Add Multimple Data Using LINQ

Dec 30, 2007

 Hello,          How do I add multiple data using LINQ, this one doesn't work. ...    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click        Dim i As Integer        For i = 0 To 3            Dim db As personalDataContext = New personalDataContext            Dim p As personal = New personal            p.name = "John"            p.number = "01213"            p.picture = "image/image.jpg"            db.personals.InsertOnSubmit(p)        Next    End Sub... cheers,imperialx 

View 1 Replies View Related

Sql Data Source Updating

Mar 19, 2007

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?

View 1 Replies View Related

Using SSIS 2005 To Strip Out Bad Rows In Excel And Then Insert Detailed Rows Into OLE DB Data Source

Apr 6, 2006

Environment:
 
Running this code on my PC via VS 2005
.Net version 2.0.50727 on the server (shown in IIS)
Code is in ASP.NET 2.0 and is a VB.NET Console application
SSIS 2005
 
Problem & Info:
 
I am bringing in an Excel file.  I need to first strip out any non-detail rows such as the breaks you see with totals and what not.  I should in the end have only detail rows left before I start moving them into my SQL Table.  I'm not sure how to first strip this information out in SSIS specfically how down to the right component and how to actually code the component to do this based on my Excel file here: http://www.webfound.net/excelfile.xls

Then, I assume I just use a Flat File Source coponent or something to actually take the columns in the Excel and split into an OLE DB Datasource to shove each column into a corresponding column in my SQL Server Table.  I have used a Flat File Source in the past to do so with a comma delimited txt file but never tried with an Excel.
 
Desired Help:

 
How to perform
 
1)       stripping out all undesired rows
2)       importing each column into sql table

View 1 Replies View Related

Gridview - Updating Is Not Supported By Data Source 'SqlDataSourceGridView' Unless UpdateCommand Is Specified

May 13, 2008

 Hi all,I have a gridview that bound to a SqlDataSource called SqlDataSourceGridView. I have enabled Edit in my GridView, but I do all the updating in code behind with stored procedure (using onRowUpdating). Now each time when I click "Update", the update went through and all the data got updated, but I received this error: Updating is not supported by data source 'SqlDataSourceGridView' unless UpdateCommand is specified.



Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.

Exception Details: System.NotSupportedException:
Updating is not supported by data source 'SqlDataSourceGridView' unless
UpdateCommand is specified. What do I need to do to fix this problem?Thanks a lot. 

View 6 Replies View Related

SQL 2012 :: SSIS - Updating Source And Target Data Structure

Feb 17, 2015

I have a SSIS package that simply moves data from a SQL database A to another SQL database B. I have update (increased) the size of a nvarchar column, on both A and B.I am wondering if there is a way to "refresh" somehow the SSIS package so I don't have to rebuild and redeploy it.The error I get now is a truncation error: "Text was truncated or one or more characters had no match in the target code page".

View 2 Replies View Related

Updating Data In Multiple Rows..

Sep 20, 2007

Hi all..

First of all Thanks for all the help, I received over the years from MSDN..

Here is my new problem...


I have a SQL table like following table..







State

City

StartDt

EndDt


1

AK

ANCHORAGE

4/1/2007

12/31/2049


2

AK

ANCHORAGE

4/1/2007

12/31/2049


3

AK

ANCHORAGE

5/1/2006

3/31/2007


4

AK

ANCHORAGE

5/1/2006

3/31/2007


5

AK

ANCHORAGE

6/1/2004

4/30/2006


6

AK

ANCHORAGE

6/1/2004

4/30/2006


7

AK

COLDFOOT

10/1/2006

12/31/2049


8

AK

COLDFOOT

10/1/1999

12/31/2049

Now here is what I want to do€¦
1> Sort the table based on Start Date (the picture shown is already sorted..) for example first 6 rows for AK - Anchorage
2> select the rows with same city (rows 1-6)
3> Select the rows with distinct start date (rows 1-3-5)
4> Change the End Date of the second selected row (row 3 in this case) to I day below the start date of 1 selected row. (4/1/2007 €“ 1 day = 3/31/2007)
5> proceed till end of selected rows.. 1-3-5
6> do the same thing for rows 2 and 4.
7> follow the same procedure for rest of the file.

The selected file should look like this when done..







State

City

StartDt

EndDt


1

AK

ANCHORAGE

4/1/2007

12/31/2049


2

AK

ANCHORAGE

4/1/2007

12/31/2049


3

AK

ANCHORAGE

5/1/2006

3/31/2007


4

AK

ANCHORAGE

5/1/2006

3/31/2007


5

AK

ANCHORAGE

6/1/2004

4/30/2006


6

AK

ANCHORAGE

6/1/2004

4/30/2006


7

AK

COLDFOOT

10/1/2006

12/31/2049


8

AK

COLDFOOT

10/1/1999

9/30/2006

Is there way to do this in SSIS? any recommened appprach>?

Any help with this is highly appreciated..
Thank You..

View 1 Replies View Related

Ignore First X Rows In Excel Data Source

Aug 31, 2006

Hi all,

Is it possible to ignore the first x rows of the Excel file in an Excel Data Source component ??

Séb.

View 3 Replies View Related

How Do I Extract Data From Selected Rows From My Excel Source File?

Jun 21, 2006

The columns in my excel source contain data of different types with the column name being a string and the data in those columns being integers. Is there any way to only extract numeric data , in short I want column names to be omitted. Also the data is distributed unevenly , beggining at various rows in each column.

Thanking in advance :)

View 3 Replies View Related

Flat File Data Flow Source Returning Limited Rows

Jan 14, 2008



All,
I'm having an issue with the Flat File Data Flow Source returning only a limited set of the rows that are in the flat file. Basically, I connect to the flat file fine, it goes to retrieve the data (tab delimited file) and only returns 190 of 392 rows. Is there a limitation on the # of rows this data flow source can retrieve or something? I've look all through the settings and properties of the task as well as the connection manager and nothing is obvious as to what is causing this. Hopefully someone ou tthere has run into this before and can help me retrieve all rows. Thanks in advance!

bakerz

View 4 Replies View Related

Built In Limit Or Setting That Limits The Number Of Rows From An OLE DB Source Table In A Data Flow Task?

Feb 16, 2007

I have a table that I'm loading as part of a control flow that in turn is copied to a target table by using a data flow task. I am doing this because a different set of fields may be used from the source entry to create the target entry based on a field in the source table. That same field may indicate that multiple entries need to be created in the target table from one source table entry for which I use a multi-cast transformation.

The problem I'm having is that no matter how many entries there are in the source table, the OLE DB Source during execution only shows 7,532 entries being taken from the source table. If there are less than 7,532 entries in the source table, everything processes fine. More than 7,532 and the data flow task only takes 7,532 and then seems to hang. It also seems as though only one path of the multi-cast transformation is taken when the conditional split directs a source entry down that path.

Seems like a strange problem I know, but any insight anyone could provide is appreciated. Thanks.

View 1 Replies View Related

To Validate # Of Excel Rows(source) And Sql Server Table(destination) Rows Are Equal

Feb 20, 2008

Hi,

When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.

How can we handle transactions in SSIS
1. when some error/something happens during export and the # of rows are not exported fully to destination, how to rollback the transaction in SSIS.

Any sort of help would be highly appreciated.

Thanks,

View 2 Replies View Related

To Validate # Of Excel Rows(source) And Sql Server Table(destination) Rows Are Equal

Feb 20, 2008

Hi,

When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.

Any sort of help would be highly appreciated.

Thanks,

View 1 Replies View Related

An Error Has Occurred During Report Processing. A Data Source Instance Has Not Been Supplied For The Data Source DetailDS_get_o

Mar 13, 2008

hi ,

i am trying for a drill through report (rdlc)

ihave written the following code in drill through event of reportviewer, whenever i click on the first report iam getting the error like

An error has occurred during report processing.


A data source instance has no
t been supplied for the data source "DetailDS_get_orderdetail".







the code is



using System;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

//using Microsoft.ApplicationBlocks.Data;

using Microsoft.Reporting.WebForms;

using DAC;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

ReportViewer1.Visible = false;

}

protected void Button1_Click(object sender, EventArgs e)

{

DAC.clsReportsWoman obj = new clsReportsWoman();

DataSet ds = new DataSet();

ds = obj.get_order();

ReportViewer1.LocalReport.DataSources.Clear();

ReportDataSource reds = new ReportDataSource("DataSet1_get_order", ds.Tables[0]);



ReportViewer1.LocalReport.DataSources.Add(reds);

ReportViewer1.LocalReport.ReportPath = "C:/Documents and Settings/km63096/My Documents/Visual Studio 2005/WebSites/drillthrurep/Report.rdlc";

ReportViewer1.LocalReport.Refresh();

ReportViewer1.Visible = true;

}

protected void ReportViewer1_Drillthrough(object sender, DrillthroughEventArgs e)

{

DAC.clsReportsWoman obj = new clsReportsWoman();

ReportParameterInfoCollection DrillThroughValues =

e.Report.GetParameters();



foreach (ReportParameterInfo d in DrillThroughValues)

{

Label1.Text = d.Values[0].ToString().Trim();

}

LocalReport localreport = (LocalReport)e.Report;

string order_id = Label1.Text;

DataSet ds = new DataSet();

ds = obj.get_orderdetail(order_id);



ReportViewer1.LocalReport.DataSources.Clear();

ReportDataSource reds = new ReportDataSource("DetailDS_get_orderdetail", ds.Tables[0]);

ReportViewer1.LocalReport.DataSources.Add(reds);

ReportViewer1.LocalReport.ReportPath = Server.MapPath(@"Reportlevel1.rdlc");

ReportViewer1.LocalReport.Refresh();





}



}

the code in method get_orderdetail(order_id) is

public DataSet get_orderdetail(string order_id)
{
SqlCommand cmd = new SqlCommand();
DataSet ds = new DataSet();
cmd.Parameters.Add("@order_id", SqlDbType.VarChar, 50);
cmd.Parameters["@order_id"].Value = order_id;
ds = SQLHelper.ExecuteAdapter(cmd, CommandType.StoredProcedure, "dbo.get_orderdetail");
return (ds);
}pls help me.

View 1 Replies View Related

Updating The XSD Used By XML Source

Apr 22, 2008

I'm using the XML source component with an autogenerated XSD. There's a small error in the XSD definition, the ID column is defined as unsigned short instead of non-negative integer. I've changed this in the XSD file itself, and renamed it but the DTSX package keeps failing with an error that
the id value is to large.
Obviously it's still using the old XSD instead of then new one. How do I fix this?
(The XSD location in the XML component is referring to the correct XSD file)

View 4 Replies View Related

Updating A Sql Table From A .csv, .txt Or .xls Source

Mar 3, 2006

I have to update a table in my sql database with data exported (csv, txt or xls) from another source.  The source data and my table have a matching unique field.  The source data may contain records I already have and need to updated with any changed data in the source file or new records that need to be inserted into my sql table.
I am using SQLExpress, SSMSE, ASP.NET 2.0, and VWD Express.  I am relatively new to this environment.  Can anyone suggest a solution or where I can find a solution?
I could do this when I was building apps in Access.
Thanks
George
 

View 3 Replies View Related

Updating A Table By Both Inserting And Updating In The Data Flow

Sep 21, 2006

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.

Any suggestions?

View 7 Replies View Related

Updating Fact Field From Source Table....

Apr 11, 2008



Hi,


I have source table , fact table and four dim. tables , I have to update a field in fact table from source table.

How can I do it?

thanks...

View 3 Replies View Related

Amo And Creating Data Source And Data Source View Code

Feb 2, 2008

,
Hi
In this code how can I create a new data source and new data source view and model and structure that it run dynamic.
In this code I have a lot of errors, that they are about server and database don€™t have in current code,
In this code, first I should definition server or no?

Database dbNew = new Database (databaseName,
Utils.GetSyntacticallyValidID(databaseName, typeof(Database)));
srv.Databases.Add(dbNew);
dbNew.Update(true);
***********************************************************

How can I create data source and data source view and model and structure?
Please say code of that, and guide me.
databasename and srv is unknown.
Do I add other reference with analysis services?
Please explain about these codes:
************************************************************************
1)
RelationalDataSource dsNew = new RelationalDataSource(
datasourceName,
Utils.GetSyntacticallyValidID(
datasourceName,
typeof(RelationalDataSource)));

db.DataSources.Add(dsNew);
dsNew.ConnectionString = connectionString;

dsNew.Update();
2)

RelationalDataSourceView rdsv;

rdsv = db.DataSourceViews.Add(
datasourceviewName,
Utils.GetSyntacticallyValidID(
datasourceviewName,
typeof(RelationalDataSourceView)));

rdsv.DataSourceID = ds.ID
***************************************************************
3)
OleDbConnection cn = new OleDbConnection(ds.ConnectionString);
OleDbCommand cmd = new OleDbCommand(
"SELECT * FROM [" + tableName + "] WHERE 0=1", cn);
OleDbDataAdapter ad = new OleDbDataAdapter(cmd);

DataSet dss = new DataSet();
ad.FillSchema(dss, SchemaType.Source);

*************************************************************
4)

// Make sure we have the name we thought

dss.Tables[0].TableName = tableName;

// Clone here - the original DataTable already belongs to a DataSet
rdsv.Schema.Tables.Add(dss.Tables[tableName].Clone());
rdsv.Update();


5)

MiningStructure ms = db.MiningStructures.Add(miningstructureName, Utils.GetSyntacticallyValidID(miningstructureName,
typeof(MiningStructure)));
ms.Source = new DataSourceViewBinding(dsv.ID);
ms.CaseTableName = "Customer";

Add columns:
ScalarMiningStructureColumn smsc;

// From table "Customer" we will add a couple of columns
// CustomerID - key
smsc = new ScalarMiningStructureColumn("Customer ID",
Utils.GetSyntacticallyValidID("Customer ID", typeof(ScalarMiningStructureColumn)));
smsc.IsKey = true;
smsc.Content = "Key";
smsc.KeyColumns.Add("Customer", "customer_id", OleDbType.Integer);
ms.Columns.Add(smsc);

*******************************************
6)

MiningModel mm = ms.MiningModels.Add(miningmodelName,
Utils.GetSyntacticallyValidID(miningmodelName,
typeof(MiningModel)));


mm.Algorithm = "Microsoft_Decision_Trees";
mm.Parameters.Add("COMPLEXITY_PENALTY", 0.3);


MiningModelColumn mc = new MiningModelColumn("Customer ID",
Utils.GetSyntacticallyValidID("CustomerID",
typeof(MiningModelColumn)));
mc.SourceColumnID = ms.Columns["Customer ID"].ID;
mc.Usage = "Key";
mm.Columns.Add(mc);


mm.Update();

Please exactly say, whatever I want
Thanks a lot for your answer
Please don€™t move this question because I don€™t know where I should write this.

View 1 Replies View Related

How Do I Add An ODBC Connection Data Source As A Data Flow Source

Mar 2, 2007

I have set up a new connection as a connection from data source, but I cannot see how to use this connection to create my Data Flow Source. I have tried using an OLE DB connection, but this is painfully slow! The process of loading 10,000 rows takes 14 - 15 minutes. The same process in Access using SQL on a linked table via DSN takes 45 seconds.

Have I missed something in my set up of the OLE DB source / connection? Will a DSN source be faster?

Thanks in advance

ADG

View 2 Replies View Related

Updating Multiple Rows

Mar 17, 2008

How would I update a table where id = a list of ids?Do I need to parse the string idList? I am being passed a comma seperated string of int values from a flex application.example: 1,4,7,8  Any help much appreciatedBarry  1 [WebMethod]
2 public int updateFirstName(String toUpdate, String idList)
3 {
4 SqlConnection con = new SqlConnection(connString);
5
6 try
7 {
8 con.Open();
9 SqlCommand cmd = new SqlCommand();
10 cmd.Connection = con;
11 cmd.CommandText = "UPDATE tb_staff SET firstName = @firstName, WHERE id = @listOfIDs";
12
13
14
15 SqlParameter firstName = new SqlParameter("@firstName", toUpdate);
16 SqlParameter listOfIDs= new SqlParameter("@listOfIDs", idList);
17
18
19
20 cmd.Parameters.Add(firstName);
21 cmd.Parameters.Add(listOfIDs);
22
23 int i = cmd.ExecuteNonQuery();
24 con.Close();
25 return 1;
26
27 }
28 catch
29 {
30 return 0;
31 }
32 }
33
34
  

View 3 Replies View Related

Updating 2 Or More Rows At The Same Time

Jul 23, 2004

Hi,

I am working on a SQL Server table designed by a partner company and cannot change the data structures. There is a table with a list of people available for calling out to a security system (keyholders).

From a web form, I need to allow my users to change the telephone calling order of the keyholders in the table.

The two important fields are AccountCode nVarChar and CallOrder nVarChar - where AccountCode + CallOrder must be unique.

As an example, the table may contain records with the following data..

1234, 1, Fred
1234, 2, Bert
1234, 3, Bob

If the user wants to make Bob the number 1 keyholder, Fred number 2 and Bert number 3 - what is the best practice for me to approach the update ?

Is this a job for ADO.Net or T-SQL ?

Thanks in advance.

Steve.

View 3 Replies View Related

Updating/Inserting Rows

Jul 16, 2002

Does anybody have a sample SQL script that will select table A and compare it to table B. If a row exists in both table A and table B, it will update the columns in table B with the columns in table A. If the row does not exist in table B, it will insert a row in table B using the row in table A. Is this possible?

Thanks Mitch

View 2 Replies View Related

Updating Grouped Rows...

Jul 9, 2004

I am new here, and I am sure this is a simple query, but im being forcefed database chores from my job, so i have to teach this stuff to myself/get help from places like this,
I need help with a query,
lets say that there are columns a,b,c,d,e,f,g
if columns c,d,e are the same, than I want the info in column g changed to the info in column b in the first record of that group
the reason I am doing this is,
I have like items (sku's) grouped in my database, and i want to create a blanket part number for skus that have matching descriptions which is the information in colums c,d,e,
I want to link them to the part number of the first product with that description, and add that part number in a new column at the end of the grouped SKU's record

this is what i start out with

a b c d e f g
2 4 5 6 9 8
2 5 5 6 9 9
2 7 5 6 0 5
1 2 3 4 5 6
1 3 3 4 5 7
1 4 3 4 5 8
1 5 3 4 5 9

i want to end up with
a b c d e f g
2 4 5 6 9 8 4
2 5 5 6 9 9 4
2 7 5 6 0 5 7
1 2 3 4 5 6 2
1 3 3 4 5 7 2
1 4 3 4 5 8 2
1 5 3 4 5 9 2

View 2 Replies View Related

Updating N Rows At Once - Best Practice (again)

Aug 14, 2006

I have put this thread on as a follow up discussion to some concepts that surfaced here:http://www.dbforums.com/showthread.php?t=1208316here:http://www.dbforums.com/showthread.php?t=1604936and also here:http://www.dbforums.com/showthread.php?t=1606555 The gist is: Is it ever correct excusable advisable to pass a non-normalised csv list of data to a stored procedure? When I first asked this I thought it was simply an implementation issue. Pat thinks it is but he also holds an ideological objection too. If I seem a little obsessed with this topic it is simply thata) I don't want to hijack yet another threadb) I am Anyway - I PMed Pat about this and he (quite rightly) suggested I post publicly. So here we go: I think I have struggled a little getting my head round the concepts you are using. And here (I think) is why: Normalisation starts with a logical model and ends with the physical implementation of the database (as I see it...). Relational theory pervades these two but extends further to SQL and database objects (views, sprocs etc). I work exclusively (with the exception of some legacy I stuff I simply must update) with the sproc database-API methodology. As such, my databases are abstracted from the other tiers. Now - I think this is the crux of it for me - I typically think of the sproc API as another tier (perhaps tier 1.5). Although the database itself and the objects that manipulate it are housed by a single application I don't actually think of the sprocs as part of the database. They are dependant upon it and inextricably intertwined with it but they are not a part of it in my eyes. As such, I don't worry about producing denormalised output and I have never (up until you got me thinking about it) worried about accepting non-normalised input. The sproc is not the database. The sproc is just a code procedure that acts upon the database. As such - if it accepts a typless, denormalised input but subsequently types (validates) it and normalises it before it makes any changes to the data within the database then I struggle to see the scale of the problem. I am writing as something of a stickler for normalisation myself - I think that rather than querying how closely one should adhere to normalisation principles I am pondering at what point these principles come into play. As a follow up question - if SQL Server sprocs were to accept (for example) a table variable as an argument would this meet your requirement (since the data is verified and normalised before it gets to the sproc) or would you require a solution like this to be an ANSI Standard too? So - that's the nub of it - comments welcome ona) The practicality of passing csv lists to sprocs and subsequently parsing them and b) How "correct" or "incorrect" this is. Blindman and Pat both object on both counts (I think).Thrasmachus didn't have a problem with it (however to be fair that was a very early response to a specific question I asked). I don't hold a particularly strong opinion either way – in fact the “loop through the changes at the client and fire the sproc n times” route is probably much easier to code in any case - I'm just interested in the idea that it violates relational theory in some way.

View 13 Replies View Related

Updating Rows Of A Column

Apr 10, 2008

Hi, I need to update column week14 in table PastWeeks with data from Eng_Goal and then result of some calculation from table AverageEngTime in the row Goal and Used respectively. I used the following and was not successful. Please advice. Thank you.

Update Pastweeks
set
week14 = (SELECT Eng_Goal,((Mon_Day + Mon_Night + Tue_Day + Tue_Night + Wed_Day + Wed_Night + Thu_Day + Thu_Night + Fri_Day + Fri_Night + Sat_Day + Sat_Night + Sun_Day + Sun_night)* 100/168) FROM AverageEngTime where Shifts = 'Average')
where Weeks = ('Goal','Used' )

View 8 Replies View Related

Need Help In Updating Multiple Rows

Jul 23, 2005

Hi,New to writing sql scriptI get this error in my sql scriptServer: Msg 512, Level 16, State 1, Line 1Subquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.The statement has been terminated.I want to write a single sql script which will update column 1 (FK)in table A with column 1 (PK) in table BHere's an example of what I need to doTable DATA_A-------------column C1 (Foreign Key)111111222222333333334455Table DATA_B------------column C1 (Primary Key) Column C2 Column C311 ABC NULL12 ABC 2004-12-1222 EFG NULL23 EFG 2003-12-1233 HIJ NULL34 HIJ 2003-12-1244 KLM 2005-02-0255 JJJ NULLI need to update Table DATA_A set column C1 with 11 data to point toTable DATA_B column C1 with 12 data. Currently, the problem is theTable DATA_A Column C1 is pointing to the wrong primary key which hasNULL data in COLUMN C3. I need to point to the correct Primary Key withDate filled in Column 3. The two primary key is tied together bycolumn C3.Here's my SQl scriptUPDATE DATA_A SET C1 =(SELECT C1 FROM DATA_BWHERE C2 in(SELECT B1.C2 FROM DATA_B B1WHERE EXISTS(SELECT * FROM TABLE_B B2 WHERE B2.C3 is NOT NULL)AND EXISTS(SELECT * from TABLE_B B2 WHERE B2.C3 is NULL)AND B2.C2 = B1.C2GROUP BY B1.C2HAVING COUNT(B1.C2) = 2)AND C3 IS NOT NULL)WHERE(SELECT C1 FROM DATA_BWHERE C2 in(SELECT B1.C2 FROM DATA_B B1WHERE EXISTS(SELECT * FROM TABLE_B B2 WHERE B2.C3 is NOT NULL)AND EXISTS(SELECT * from TABLE_B B2 WHERE B2.C3 is NULL)AND B2.C2 = B1.C2GROUP BY B1.C2HAVING COUNT(B1.C2) = 2)AND C3 IS NULL)Thanks - Been struggle at this for a whileMLR

View 1 Replies View Related

Updating Rows Problem

Jan 28, 2008

Hi,
I am preparing a project that contains multiple tables, because of huge operations to be performed, so i split these into two forms. I used one form for "Inserting Data in the multiple tables" and another form used for "Updating and Deleting Data from the multiple tables". I used a common identification number in all the tables are "License_Number" for inserting, retreving, updating and deleting the data. I used tabcontrol to place lot of controls on it.

The form-1 which is used for Inserting, works fine.
The problem occuring in the second form which i used for updating.

The following code fill the data into the Textboxes and as well as in the datagridview. Below two tables "SetBack_Details" and "Far_Details" are fill the data in datagridview.

Private Sub FillDataFromAllTables(ByVal LicenseNumber As Long)

Try
'Fill Owner Personal Details Data
CheckOwnerID = RetrieveOwnerIDFromPD(LicenseNumber)
Me.Owner_Personal_DetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.Owner_Personal_Details, CheckOwnerID)

'-------------------------------------------------------------------
'Fill Project Details Data
Me.ProjectDetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.ProjectDetails, LicenseNumber)

'-------------------------------------------------------------------
'Fill Consultant Details Data
Me.Consultant_DetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.Consultant_Details, LicenseNumber)
'Fill Applicant Details Data
Me.Applicant_DetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.Applicant_Details, LicenseNumber)
'-------------------------------------------------------------------
'Fill Property Details Data
Me.Property_DetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.Property_Details, LicenseNumber)
'-------------------------------------------------------------------
Me.SetBack_DetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.SetBack_Details, LicenseNumber)
'-------------------------------------------------------------------
Me.FAR_DetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.FAR_Details, LicenseNumber)
Catch ex As Exception
MessageBox.Show(ex.Message.ToString, "Project Details")
End Try
End

The data which i retrieves in a single record easily updated but problem occurs in DataGridView.

The data which binds to textboxes is easily updating because of a single record. Example is below
UPDATE PROJECTdETAILS SET PNAME=@PNAME, ZONE=@ZONE, ZONEAREA=@ZONEAREA WHERE LicenseNumber=@LicenseNumber

The data which binds to datagridview is problem. I know the following statement is not appropriate to update the data rows in datagridview.
UPDATE SETBACK_DETAILS SET DIRECTION=@DIRECTION, BORDERS=@BORDERS, DIMENSION=@DIMENSION WHERE LICENSENUMBER=@LICENSENUMBER
What happening to the above statement is , all rows with this LICENSENUMBER contains same data. Before updating data contains in different information but after updating which ever last row is updating, it contains the last row values to all records.

Example :
101 xyzcompany X-Road X-Area
101 xyzcompany X-Road X-Area
101 xyzcompany X-Road X-Area
101 xyzcompany X-Road X-Area
101 xyzcompany X-Road X-Area
.
.
The following code i used to save the data
Private Sub SaveData()
Me.ValidateChildren()
Me.ProjectDetailsBindingSource.EndEdit() 'EndEdit for Project Details
Me.Consultant_DetailsBindingSource.EndEdit() 'EndEdit for Consultant Details
Me.Applicant_DetailsBindingSource.EndEdit() 'EndEdit for Applicant Details
Me.Property_DetailsBindingSource.EndEdit() 'EndEdit for Property Details
'Me.SetBack_DetailsBindingSource.EndEdit() 'Endedit for SetBack_Details

Me.ProjectDetailsTableAdapter.Update(Me.LicenseFARModifyDataSet.ProjectDetails) 'Update Project Details
Me.Consultant_DetailsTableAdapter.Update(Me.LicenseFARModifyDataSet.Consultant_Details) 'Update Consultant Details
Me.Applicant_DetailsTableAdapter.Update(Me.LicenseFARModifyDataSet.Applicant_Details) 'Update Applicant Details
Me.Property_DetailsTableAdapter.Update(Me.LicenseFARModifyDataSet.Property_Details)
'Me.SetBack_DetailsTableAdapter.Update(Me.LicenseFARModifyDataSet.SetBack_Details)
End Sub


Please help me in the regard how to update the data in DataGridView which contains multiple recrods with different information for a single LicenseNumber. I try so many times with different sort of techniques but i fails, sometimes i get concurrency violation error and many more.
Hope anybody understand my problem. Please help me in this regard.

Thanks.

Best Regards,
Kashif Chotu


View 12 Replies View Related

Add Rows To A DataSet Without Updating The MS SQL Server?

Jan 9, 2006

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.

View 1 Replies View Related

Updating Multiple Rows With An Expression

Dec 26, 2006

OK, I am trying to update a particular column with a numerical number. Here is the query I am using.

UPDATE viewerblock SET dummycat = (?) WHERE dummyproduct = 0

I am trying to number dummycat row to a certain number for example


dummycat-----------------dummyproduct
1--------------------------0
2--------------------------0
3--------------------------0
4--------------------------0
5--------------------------0
6--------------------------0


do you see what i am trying to do? I am simply trying to number the dummycat column where ever dummyproduct = 0.

is this possible to do?

View 3 Replies View Related

T-SQL (SS2K8) :: Updating Multiple Rows

Jul 2, 2014

I need to update a empty column in our SQL database with the login ID for employees of our company.The table is called SY01200 and were I need to put the login ID is column INET5, and the login ID is just me stripping off the company's email address(removing the @company.com), and I need to update the INET5 column only where Master_Type = 'EMP'

And here is the Query that I am using to strip the email select LEFT(convert(varchar(40),EmailToAddress),LEN(convert(varchar(40),EmailToAddress))-14) As LoginName from sy01200 where Master_Type = 'emp'And here is what I thought the Query would be to update however I got and error saying more than 1 arguement returned

UPDATE sy01200
SET INET5 = (select LEFT(convert(varchar(40),EmailToAddress),LEN(convert(varchar(40),EmailToAddress))-14) As LoginName from sy01200 where Master_Type = 'emp')
WHERE Master_Type = 'EMP'

View 2 Replies View Related

T-SQL (SS2K8) :: Updating Rows In Batches?

Nov 6, 2014

I have a production table with 400 million rows.

I have a staging table which has 48 million rows. This data is the same as the production data, except one column has a different value.

Create Table Production
(
Id Int Identity(1,1),
Code Varchar(20),
ReferenceSequence int
)

-- Staging Table

Create Table Staging
(
Code Varchar(20),
NewSequence int
)

I need to update the production table with the newSequence value from staging to replace the ReferenceSequence. I.e:

Update Production

Set ReferenceSequence = Staging.NewSequence
From Staging

where Production.Code = Staging.CodeHowever, updating 48 million rows at once will generate a lot of logging!

How can I do 1 million rows at a time, commit the changes then do the next million?

I've tried some of the examples on the following page [URL], but they look to just update the tables with the same values.

View 4 Replies View Related

Updating Multiple Rows At Once In To SQlServer2005

Sep 25, 2007

Hi,

I have a table called "tblProducts" with following fields:-

ProductID(Pk, AutoIncrement), ProductCode(FK), ProdDescr.

So to the above table I have added a new field/column named "ProdLongDescr(varchar, Null)"

So, I need to populate this newly added column with specific values for each row depending on "ProductCode" which is different forevery row. The problem is that I have 25 rows.So instead of Writing 25 individual update scripts, is there a way in which single query will do the same job instead of writing one update query for each row ?. If so can some one guide me how to achieve that OR point to me a good resource.

Below are a couple of Individual update scripts I Wrote. "ProductCode" is different for all 25 rows.

Update tblValAdPackageElement SET ProdLongDescr = 'Slideshows' WHERE ProductCode = 'SLID'
And szElementDescr='Slideshow'
if @@error <> 0
begin
goto ErrPos
end

Update tblValAdPackageElement SET ProdLongDescr = 'CategorySlideshows' WHERE ProductCode = 'SLDC'
And szElementDescr='CategorySlideshow'
if @@error <> 0
begin
goto ErrPos
end

Thanks,

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved