Re: problem with update - Database Discussion Boards

coolestCoder28-Jul-06 4:19

28-Jul-06 4:19

Hi All,
I have a problem combining results of two queries. The queries are fired on the same table with the same select list, only the where condition changes. It would be better if i show what i actually want.

This is my table data for example.

Field1 Field2
AB 1
SD 2
SDFG 3
AS 4
QWE 5
BBG 6

I want an output as

Field1 Field2
AS 4
QWE 5
BBG 6
AB 1
SD 2

That means , if i give the value for Field2 = 3 in the query then the result will not contain record with Field2 = 3 and the result should display the record with Field2 > 3
first and the with Field2 < 3 both sorted in ascending order.

I am using the union operator for combining results, but the results are not as desired. Also i feel that the union clause is not efficient since it takes about 4-6 secs to fetch the records.

Is there any efficient solution for my problem. Please reply soon as i have already spent considerable time on this query and i am running short of time now.

Any help will be appreciable.

Thanks in Advance.

"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder

Anant Y. Kulkarni

Re: Combining results of two queries

ToddHileHoffer28-Jul-06 5:35

ToddHileHoffer

28-Jul-06 5:35

-- Try this
declare @field2 as int
set @field2 = 3

select field1, field2, case when
field2 > @field2 then 1
else 2 end as SortValue
from test where field2 <> @field2
order by SortValue, field2 asc

how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line. But of course, that's all a "high level" overview of things
--thedailywtf 3/21/06

Datasets + Update Issue [modified]

Tristan Rhodes28-Jul-06 4:07

Tristan Rhodes

28-Jul-06 4:07

I have a data structure that i am storing a set of data in, that is manipulated, before being applied to the database on calling the Save() function.

The database has a unique field, say 'Name', that is constrained as being unique.

This lead me to notice a potential problem that could affect how Datasets work, and i'd like to avoid it without having to turn off the database constraints. The problem is as follows:

Code
====

Firstly, this code is run, adding two records by name to the dataset, and then saving it to the database.

<br />
obj.Add("AAA")<br />
obj.Add("BBB")<br />
obj.Save()<br />

Database
========

After running this code, the database table will look as follows:

id name
1 AAA
2 BBB

Code
====

Then we run the following code, which loads the table, renames the two records in the dataset using a Tower of Babel kind of name swap, and saves it back to the database.

<br />
obj.Load()<br />
obj["AAA"].Name = "---"<br />
obj["BBB"].Name = "AAA"<br />
obj["---"].Name = "BBB"<br />
obj.Save()<br />

Now, at this point, when it tries to do the save, the application should fall over (Assuming i understand how datasets work)

In effect, we have swapped the two item names, "AAA" and "BBB", so we would like the table to read as follows:

Database
========
id name
1 BBB
2 AAA

However, as only two records exist in the dataset, both flagged as 'Updated', the following queries are generated:
"UPDATE SET name = 'BBB' WHERE (id = 1)"
"UPDATE

SET name = 'AAA' WHERE (id = 2)"

At which point, on executing the first query, it would attempt to update the database table to the following state:

Database
========
id name
1 BBB
2 BBB

Whereupon, it would crash, as the unique constraint is broken.

The only possible solution would be if microsoft (in all their wisdom) thought of this, and implemented some kind of update stack, which may also work, as all three updates would be applied in order, and no exceptions would be generated. (But i don't think they do as the update commands are handled by the data adapter and the rows merely have an update type indicator)

Will it work, or will in not?

Cheers guys

Tris

-- modified at 4:54 Sunday 30th July, 2006

Confirmed

Tristan Rhodes31-Jul-06 0:37

Tristan Rhodes

31-Jul-06 0:37

I just confirmed this problem - Still looking for a way around it tho.

<br />
		private void btnSetup1_Click(object sender, EventArgs e)<br />
		{<br />
			DataSet1.EntityAliasRow dr;<br />
<br />
			// Add AAA<br />
			dr = (DataSet1.EntityAliasRow)dt.NewRow();<br />
			dr.EntityID = 7;<br />
			dr.EntityName = "AAA";<br />
			dr.IsPrimaryName = false;<br />
			dt.Rows.Add(dr);<br />
<br />
			// Add BBB<br />
			dr = (DataSet1.EntityAliasRow)dt.NewRow();<br />
			dr.EntityID = 7;<br />
			dr.EntityName = "BBB";<br />
			dr.IsPrimaryName = false;<br />
			dt.Rows.Add(dr);<br />
			<br />
			// Update<br />
			da.Update(dt);<br />
		}<br />
<br />
		private void btnSetup2_Click(object sender, EventArgs e)<br />
		{<br />
			DataSet1.EntityAliasRow dr1;<br />
			DataSet1.EntityAliasRow dr2;<br />
<br />
			da.Fill(dt, 7);<br />
<br />
			dr1 = (DataSet1.EntityAliasRow)dt.Select("EntityName = 'AAA'")[0];<br />
			dr2 = (DataSet1.EntityAliasRow)dt.Select("EntityName = 'BBB'")[0];<br />
<br />
			dr1.EntityName = "---";<br />
			dr2.EntityName = "AAA";<br />
			dr1.EntityName = "BBB";<br />
<br />
			da.Update(dt);<br />
		}<br />

problem with update

Jeeva Mary Varghese28-Jul-06 2:13

Jeeva Mary Varghese

28-Jul-06 2:13

Hello All,

I 've 2 tables say AA and BB.
AA has 2 cols AA_old,AA_new & BB has i field BB_old.
Both AA_old and BB_old has 2 same rows.

I need to replace the values of BB_old with the corresponding values of AA_new.Its like :--

AA BB
AA_old AA_new BB_old
1 11 1
2 22 2

i want to replace BB_old with AA_new: Result will be like

BB_old
11
22

values may repeat

Can anyone help me generate a query Plz.

Thank you.

Regards,
jeeva

Re: problem with update

Eric Dahlvang28-Jul-06 4:24

Eric Dahlvang

28-Jul-06 4:24

UPDATE BB SET BB_old = (SELECT AA_new FROM AA WHERE AA.AA_old = BB.BB_old)

It would be much easier to help you if you posted your actual table and field names. The above SQL is just weird - but it is clearly what you are trying to do.

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Re: problem with update [modified]

Jeeva Mary Varghese28-Jul-06 20:02

Jeeva Mary Varghese

28-Jul-06 20:02

Thanks for the help.

I got the table updated but when i try to update some fields using UI i get an error :-
Key column information is insufficient or in correct.
Too many rowswere affected by update

i cannot update it.

my query is like:-
UPDATE sales1 SET sid = (SELECT sid_new FROM sales2 WHERE sales1.sid = sales2.sid_old)

Plz find me a solution for this.

Regards,
Jeeva

-- modified at 4:51 Saturday 29th July, 2006

Re: problem with update

Eric Dahlvang31-Jul-06 3:32

Eric Dahlvang

31-Jul-06 3:32

Jeeva Mary Varghese wrote:
Key column information is insufficient or in correct.
Too many rowswere affected by update

The Enterprise Manager will give you this error if you have more than one row that contains the same data in every column.

Solution: add a unique Identity column - and you will not get this error anymore (but, your UPDATE might error out when it attempts to create two rows with the same data in every column), or use the query analyzer to do your updates.

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

FirebirdSql.Data.Firebird.FbException ?

nick_net128-Jul-06 1:10

nick_net1

28-Jul-06 1:10

Hi, I am new to firebird database. I got "FirebirdSql.Data.Firebird.FbException" (arithmetic exception, numeric overflow, or string truncation
Implementation of text subtype 52 not located.) while trying to update my firebird database.

My code for updation is :-

Dim dbcon As IDbConnection = New FbConnection(connectionString)
dbcon.Open()

Dim dbcmd As IDbCommand= dbcon.CreateCommand
dbcmd.CommandText = sSQL
dbcmd.ExecuteNonQuery()

my query looks like this:- "UPDATE HISTORY_MASTER SET Status='RUNNING', WHERE Mkey='5e81f9859d223ea420aca993c647b839' AND Status='Start';" Please help me in that.

Thanks
Nicky

ssadsadadgawkjawoidehqwohoiweheiwqhrewoihriwolhrwiehriwehfuiwhfiluwhwifhiwhfiwhfiwehfiwhrwhrwhuiw

Regarding retrieve the data from two different tables

ChennaiBabu28-Jul-06 1:07

ChennaiBabu

28-Jul-06 1:07

Hi All,

greetings.In my appilcation i need to take two different data that is EmployeeName from Employee Table and ClientName from Client Table.How i can write it in a single SQL Query.

Can any one one give me the query please.

Thanks in Advance.

Babu

Re: Regarding retrieve the data from two different tables

Mairaaj Khan28-Jul-06 2:19

Mairaaj Khan

28-Jul-06 2:19

ChennaiBabu wrote:
In my appilcation i need to take two different data that is EmployeeName from Employee Table and ClientName from Client Table.How i can write it in a single SQL Query.

Try using JOIN.
You can find about Joins here[^]

_____________________________
Success is not something to wait for, its something to work for.

Re: Regarding retrieve the data from two different tables

enjoycrack29-Jul-06 6:22

enjoycrack

29-Jul-06 6:22

Or you can also use UNION operator to combine data from 2 select statements....

<< >>

[Message Deleted]

very_short_life27-Jul-06 23:17

very_short_life

27-Jul-06 23:17

[Message Deleted]

Re: pleeeeeeeeeeeeeeeeeeeese help

jebberwocky27-Jul-06 23:41

jebberwocky

27-Jul-06 23:41

Try to use the Response.write to write out the Statement
Then u can use SQL analysis to test it
or try this
=db.OpenRecordset("select * from emp where empno='" & Val(Text1(0).Text) & "' and ename='" & Val(Text1(1).Text) & "' and hidate='" & Text1(5).Text) &"'"

Re: pleeeeeeeeeeeeeeeeeeeese help

Hy Chanhan28-Jul-06 1:20

Hy Chanhan

28-Jul-06 1:20

Hi,

In SQL Statement:
EX:
============

SELECT * FROM table_name WHERE field1 BETWEEN 1 AND 10

============
PLEASE CHECK YOUR STATEMENT AGAIN.
Hy Chanhan

Re: pleeeeeeeeeeeeeeeeeeeese help

Ravi kumar G29-Jul-06 2:52

Ravi kumar G

29-Jul-06 2:52

hi,
pleas give space between "empno" and "and" statement

Ravikumar
[Coimbatore]

Re: pleeeeeeeeeeeeeeeeeeeese help

S Douglas29-Jul-06 20:36

S Douglas

29-Jul-06 20:36

capamaro wrote:
("'select * from emp where empno='" & Val(Text1(0).Text) & "'[insert space] and ename='" & Val(Text1(1).Text) & "'[insert space]and hidate=[#]" & Text1(5).Text & "[#];")

You have an extra single quote in that query and are missing some spacing. When in doubt do a Debug.Print SQLQuery to see how it looks to the database engine.

You should really take a look at the article below, it’s not in the same language, but the content is still worth reading. Protecting the database is your number one goal no matter who the end users are. The way your queries are run they are prime for SQL injection attacks.

SQL Injection Attacks and Some Tips on How to Prevent Them[^]

I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:

Re: pleeeeeeeeeeeeeeeeeeeese help

Ennis Ray Lynch, Jr.31-Jul-06 8:40

Ennis Ray Lynch, Jr.

31-Jul-06 8:40

It is vulnerable to SQL injection.

(Also try wrapping the date in ##

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

How can I join 2 xml documents to get one table.

kalyan_vb27-Jul-06 17:00

kalyan_vb

27-Jul-06 17:00

Hi,

I want to join 2 xml documents to get the result into one table. Can any one gives the syntax.

Regards,
Kalyan

VB.NET , DATAGRID

gordie warwick27-Jul-06 8:22

gordie warwick

27-Jul-06 8:22

I want to update a cell in a datagrid, and then update my database with the cellvalue - Hopefully, using a contextmenu.
I am using a data/adapter.fill for the grid

Cool | :cool: