Re: Performance question - Database Discussion Boards

Update database from DataGridView and handle exception

amannini11-Dec-13 0:03

11-Dec-13 0:03

Hi,

i'm implementing that using the method exposed in VB DataGridView Automatically Saving Updates to a Bound Database[^] but
how to handle exception thrown when DataAdapter.Update is called in RowValidated?

I can catch the exception with a Try but how to cancel the move to a new row and to force user to correct the edited row?
What i would obtain is a "SQL Server Management Studio-style". When you edit a row and move to another, if an exception is detected (duplicate key for example), a message is shown, the edit glyph is mantained in the edited row and cursor remain on the edited row.

Thanks

Re: Update database from DataGridView and handle exception

Eddy Vluggen11-Dec-13 7:06

Eddy Vluggen

11-Dec-13 7:06

Before it's validated, it will be validating[^].

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Performance question

Johnny J.9-Dec-13 20:12

Johnny J.

9-Dec-13 20:12

Does anybody know if there is a performance difference between these two example queries:

SQL

SELECT * FROM tblFox WHERE stringFieldValue IN ('Ring-ding-ding-ding-dingeringeding!', 'Wa-pa-pa-pa-pa-pa-pow!')

SQL

SELECT * FROM tblFox WHERE stringFieldValue='Ring-ding-ding-ding-dingeringeding!' OR stringFieldValue= 'Wa-pa-pa-pa-pa-pa-pow!'

????

I would Google it if I knew what to enter to get a relevant answer... Sigh | :sigh:

Anything that is unrelated to elephants is irrelephant
Anonymous
-----
The problem with quotes on the internet is that you can never tell if they're genuine
Winston Churchill, 1944
-----
I'd just like a chance to prove that money can't make me happy.
Me, all the time

Re: Performance question

tsunamigang9-Dec-13 20:42

tsunamigang

9-Dec-13 20:42

I did not know exactly that there is performance difference. But query using IN will reduce your typing and looks good. If there are few more data to search in OR condition, You type field name again and again??

Re: Performance question

Jörgen Andersson10-Dec-13 1:26

Jörgen Andersson

10-Dec-13 1:26

Depends on what database you're using and what indexes you have in place, but normally the query optimizer would realize that they are the same thing.

Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers.
Buckminster Fuller

Re: Performance question

thatraja10-Dec-13 2:01

thatraja

10-Dec-13 2:01

Johnny J. wrote:
Does anybody know if there is a performance difference between these two example queries:

Not so much difference I think. But I prefer IN instead of OR in this case. Using IN you could write cleaner query. What would you do if there're 10+ OR conditions in your query?

Johnny J. wrote:
I would Google it if I knew what to enter to get a relevant answer...

I tried http://www.google.com/search?q=SQL+IN+vs+OR[^] & got this one
SQL Speed Test: IN vs OR[^]

thatraja

Code converters | Education Needed
No thanks, I am all stocked up. - Luc Pattyn
When you're wrestling a gorilla, you don't stop when you're tired, you stop when the gorilla is - Henry Minute

Re: Performance question

David Mujica10-Dec-13 2:44

David Mujica

10-Dec-13 2:44

If you are using SQL Server, you could use "Show Execution Plan" and see if there is a difference.

If you are using Oracle, I believe there is a 1000 element limit to the "IN" clause. At a previous company I worked for we got bit by this. (This was a while ago, maybe Oracle 9, it may not be an issue now)

Re: Performance question

Jörgen Andersson10-Dec-13 2:51

Jörgen Andersson

10-Dec-13 2:51

Oracle still has this limit (in older versions it was 256 elements)
The purpose with the limit is that parsing the query is quite resource demanding and if you're having more than 1000 elements in an in statement they're claiming that you're doing something wrong anyway.
Use an array instead.

Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers.
Buckminster Fuller

search based on free text(multiple words entered by user)

rakeshjena9-Dec-13 18:16

rakeshjena

9-Dec-13 18:16

Based on the full text entered by user in "search" textbox , i want to search every words that is present in various columns of my tbl_products table. how to achieve this.i prefer to use contains than like.plz help me. Examples are welcomed than links plz help

Re: search based on free text(multiple words entered by user)

Mycroft Holmes9-Dec-13 18:38

Mycroft Holmes

9-Dec-13 18:38

Contains is a c# or .net concept, for sql you need to use wild cards % in your stored procedure.

SQL

Set @SearchText = '%' + @SearchText + '%'
Where Col1 like @SearchText
 or Col2 like @SearchText 
 or Col2 like @SearchText

Never underestimate the power of human stupidity
RAH

Re: search based on free text(multiple words entered by user)

Eddy Vluggen10-Dec-13 8:10

Eddy Vluggen

10-Dec-13 8:10

rakeshjena wrote:
Examples are welcomed than links

It's not something that can be explained with a copy/paste example. If you want a full-text search, then you have to configure[^] the server.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

search based on free text(multiple words entered by user

rakeshjena9-Dec-13 18:09

rakeshjena

9-Dec-13 18:09

TempTables

tsunamigang8-Dec-13 18:23

tsunamigang

8-Dec-13 18:23

What is the use of TempTables in SQL?? Is it a preferred one to use temptables??

Re: TempTables

Abhinav S8-Dec-13 19:19

Abhinav S

8-Dec-13 19:19

Quick Overview: Temporary Tables in SQL Server 2005[^] explains temp tables in detail.
For less data, use a table variable instead of a temporary table.

Apps - Color Analyzer | Arctic | XKCD | Sound Meter | Speed Dial

Re: TempTables

tsunamigang9-Dec-13 18:02

tsunamigang

9-Dec-13 18:02

Thanks man

Variable column SQL statments

Wombaticus8-Dec-13 6:13

Wombaticus

8-Dec-13 6:13

SO I have two tables - one of "tasks" and another to log the time they are carried out each day - viz:

table "tasks"
ID - autoincrement PK
sJob - varchar task name

table "tasks_log"
ID - autoincrement PK
JobID - mapped to tasks.ID
dDateTime - date and time task is performed

Now, what I want to do is produce in one SQL statement a result set in which each row consists of the task (sJob), followed by the time it was carried out on each day within a given range

So... the result set will have variable columns depending on the date range, and the SQL sdtatement will need to be built dynamically.

A (probably not the best!) partial solution is as follows

Suppose we want the results for three days, Dec 1 to Dec 3

SELECT T.sJob, L1.dDateTime AS Dec_1, L2.dDateTime AS Dec_2, L2.dDateTime AS Dec_3
FROM tasks AS T
LEFT JOIN tasks_log AS L1 ON L1.JobID = T.ID
LEFT JOIN tasks_log AS L2 ON L2.JobID = T.ID
LEFT JOIN tasks_log AS L3 ON L3.JobID = T.ID
WHERE
DATE(L1.dDate) = '2013-12-01'
AND DATE(L2.dDate) = '2013-12-02'
AND DATE(L3.dDate) = '2013-12-03'
ORDER BY T.sJob

One obvious drawback of this is that it will be limited to a relatively small date range, but worse is that there is something I haven't told you! Smile | :)

That is, that not all the tasks are carried out on all days.
Given this, the result set will only consist of those tasks that were, in fact, carried out on each day within the given date range.

What we need, if it's not asking the impossible, is to list them all, with NULLs showing on days any particular task was not performed.

Obviously there are other approaches, but it wuild be nice if there was simple (...) SQL statement that could do it all in one go... Smile | :)

Re: Variable column SQL statments

Mycroft Holmes8-Dec-13 11:47

Mycroft Holmes

8-Dec-13 11:47

What you need is a pivot query. This article [^]may help.

The ides is to prepare your data before pivoting. Create a table with the dates you want to report, and in your pivot join those date to the data table and then pivot the result on the dates.

Never underestimate the power of human stupidity
RAH

Re: Variable column SQL statments

Wombaticus8-Dec-13 12:39

Wombaticus

8-Dec-13 12:39

Thanks!

ms access select query?

murali_utr8-Dec-13 4:17

murali_utr

8-Dec-13 4:17

hi,

I need select query to select number of records(1 to 50 then 51 to 100 from 500 rows)from n rows of selection. not from id I think row id.

thanks in advance.

Have A Nice Day!
Murali.M

Blog

Re: ms access select query?

PIEBALDconsult8-Dec-13 4:38

PIEBALDconsult

8-Dec-13 4:38

You mean like SELECT TOP 50 ... ?

Re: ms access select query?

murali_utr8-Dec-13 4:47

murali_utr

8-Dec-13 4:47

no any 50 records.

Have A Nice Day!
Murali.M

Blog

Re: ms access select query?

PIEBALDconsult8-Dec-13 5:04

PIEBALDconsult

8-Dec-13 5:04

TOP 50 may be any 50.

Backup & restore of data Like organization chart in sqlserver

mehdin698-Dec-13 1:46

mehdin69

8-Dec-13 1:46

Hi dear friends
I have a database (sql server) that every username should Enter some information in system & his (Admin,Admin User )then his (Admin) should give those Update Information to his (Users).
Now, I have some Problems to solve this & need your help
1) This program offline & there are no network!
2) The (Users) shouldn’t have all database & they have just information about themselves
This like organization chart that each user can just access to his information.
I want to know, how can I do backup & restore this information? In which way?
Do you Know any software that can Restore?
Backup this information the best possible way?

Image of this Message[]

Re: Backup & restore of data Like organization chart in sqlserver

Mycroft Holmes8-Dec-13 11:50

Mycroft Holmes

8-Dec-13 11:50

This is not a backup/restore issue. You need to do some research into replication and distributed database strategies. This is not a trivial subject.

Never underestimate the power of human stupidity
RAH

MYSQL - right join - where

nelsonpaixao7-Dec-13 8:46

nelsonpaixao

7-Dec-13 8:46

Hello there guys,

i trying to perform some right join to keep all records from tbl custumers, it works fine but when i do the "where" condition i noticed it killed my right join
how can i solve this?
i have a way to get the output i want but is very messy, so i guessed u guys could help me here, i sure is very easy Dead | X|

SQL

select Customers.cname as c_id, sum(Products.price*Purchases.quantity) as z
from Purchases
right outer join Customers on (Customers.CID = Purchases.cust)
left outer join Stores on (Stores.SID = Purchases.Stores)
left outer join Products on (Products.SKU = Purchases.prod)
###where Stores.city = "Porto"
group by Customers.cname
order by z desc

(my aim is to show total value of purchaces per client in some store, but i need to include all client even those that didn´t shop (zero values))

thanks

nelsonpaixao@yahoo.com.br

trying to help & get help

modified 7-Dec-13 15:11pm.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.