Database Discussion Boards

David Mujica23-Mar-09 11:00

23-Mar-09 11:00

Here is an example:

BEGIN TRANSACTION
select * from employee
ROLLBACK

This is syntactilly correct, but logically not very valid. A SELECT statement does not change the database, therefore a commit or rollback really does nothing except end the transaction. The above example should really should never be used, by starting a transaction you could be creating unnecessary locks to be granted.

Transactions are typically used to guarantee that ALL updates are made or NO updates are made.

For example: (syntax is not 100% accurate, but you should get the idea)

BEGIN TRANSACTION
try
UPDATE table1 set newbalance = newbalance - 100 where ID=ABC
INSERT into audittable values (NOW(),'ABC','WITHDRAWL')
COMMIT
catch ex
ROLLBACK
end try

This example shows that 2 tables are being updated and if they are successful, the transaction is committed, if there is an error then a rollback is performed.

Item Stock detail using sale and purchase table [modified]

adeelidrees23-Mar-09 2:36

adeelidrees

23-Mar-09 2:36

Hi dear,

This is my first post and want help from some one.

My question is below:

I have five tables with fields named below:

- purchase (*purchase_id, purchase_date, company_id)
- purchase-detail (purchase_id, qty, product_id, unit_id)
- sale (*sale_id, sale_date)
- sale-detail (sale_id, qty, company_id, product_id, unit_id))

Note - * represents primary key

(company_id, product_id, unit_id) makes one item.

I am creating a crystal report using vb.net 2008 and I have to create specific Item detail, in which date it is sold out , in which date it is purchased.

I have created crystal report 4 columns below in which data will be placed

Date(sale/purchase) | Purchase Quantity | Sale Quantity | Rem. Quantity
23/03/09                     | 300                          |                         | 300
24/03/09                     |                                 | 100                  | 200
25/03/09                     | 100                          | 200                  | 100

The above format will be used with different possibilities like above.

I have different and same dates in sale and purchase tables with different quantities for same date .

Please help me to solve this question . I am unable to create the queries.

Many thanks in Advance

modified on Monday, March 23, 2009 8:53 AM

SqlDataReader Number of Rows

Jasmine Pomelo23-Mar-09 2:05

Jasmine Pomelo

23-Mar-09 2:05

Hi,

I've used an SqlDataReader to retrieve some rows from my database. I can then iterate through the results calling its Read() method but how can I determine the number of rows without having to call the Read() method?

Thanks Smile | :)

Re: SqlDataReader Number of Rows

Eddy Vluggen23-Mar-09 2:20

Eddy Vluggen

23-Mar-09 2:20

Do a SELECT COUNT(*) that you read before entering the loop, or use a DataSet (with DataAdapter.Fill).

I are troll Smile | :)

Re: SqlDataReader Number of Rows

Jasmine Pomelo23-Mar-09 2:28

Jasmine Pomelo

23-Mar-09 2:28

Thanks for your reply Smile | :)

I'm a bit of a SQL noob so I'll have to look into using a DataSet but doing a SELECT COUNT will be effectively calling the same query twice (apart from actually returning the columns). Won't that be a great deal of overhead?

I just thought the SQLDataReader must know how many rows were returned when it's created from calling the SQLCommand's ExecuteReader() method. Is that the case?

Re: SqlDataReader Number of Rows

Eddy Vluggen23-Mar-09 2:55

Eddy Vluggen

23-Mar-09 2:55

Member 6038196 wrote:
Won't that be a great deal of overhead?

A great deal? Not unless you're going to call this query often, couting the rows in a table and returning the result is quite fast.

Member 6038196 wrote:
I just thought the SQLDataReader must know how many rows were returned when it's created from calling the SQLCommand's ExecuteReader() method. Is that the case?

If memory serves correct, it didn't - it just keeps getting records until it runs out of records. If you want both the records and the count in a single SQL-statement, use the DataAdapter.Fill method Smile | :)

I are troll Smile | :)

Re: SqlDataReader Number of Rows

Jasmine Pomelo23-Mar-09 3:10

Jasmine Pomelo

23-Mar-09 3:10

Thanks - much appreciated Smile | :)

Re: SqlDataReader Number of Rows

Pete O'Hanlon24-Mar-09 2:20

Pete O'Hanlon

24-Mar-09 2:20

Member 6038196 wrote:
I just thought the SQLDataReader must know how many rows were returned when it's created from calling the SQLCommand's ExecuteReader() method. Is that the case?

A datareader only ever knows about 1 row. It's what's known as a Firehose cursor, whereby data is squirted through as quickly as possible. You could always just increment a count on each read and use that.

"WPF has many lovers. It's a veritable porn star!" - Josh Smith

As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.

My blog | My articles | MoXAML PowerToys

Re: SqlDataReader Number of Rows

tech60324-Mar-09 13:24

tech603

24-Mar-09 13:24

Another solution for you if the others do not work,

SqlDataReader reader = cmd.ExcecuteReader();

int count = 0;

while ( reader.read){

// do your normal code for gathering column info

count++;
}

when its done reading you'll have your data and your count.

Hope that helps.

Matthew Vass
QA Analyst
mvass@hostmysite.com
HostMySite.com

Problem with CASE statement in WHERE clause

marijus523-Mar-09 1:49

marijus5

23-Mar-09 1:49

I am trying to select data from tables A and B

SELECT A.* , B.*
FROM A INNER JOIN B ON A.Id = B.Id
WHERE (A.Id=@id) AND(A.Date BETWEEN B.Date1 AND B.Date2)

the problem appears that fields B.Date1 and B.Date2 are not necessary containing data,
they can be null. so, my solution is

WHERE (A.Id=@id) AND
(A.Date BETWEEN CASE B.Date1 WHEN null THEN @DateMin ELSE B.Date1 END
AND CASE B.Date2 WHEN null THEN @DateMax ELSE B.Date2 END)

when i run this select i get error:
"The version of sql server in use does not support datatype 'udt'"

i can't figure out why i get this error. is there something to do with
@DateMin and @DateMax format ?? i think i am using the right format

can anybody help me,
thanks

Re: Problem with CASE statement in WHERE clause

Ashfield24-Mar-09 2:27

Ashfield

24-Mar-09 2:27

As far as I know, there is no such ptredefined variables as @DateMin and @DateMax - assuming you are using sql server.

Something like this should get you what you want

WHERE (A.Id=@id) AND 
(A.Date BETWEEN coalesce(B.Date1,A.Date) AND coalesce(B.Date2,A.Date))

The coalesce means take the first non-null value

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: Problem with CASE statement in WHERE clause

marijus524-Mar-09 5:33

marijus5

24-Mar-09 5:33

thanks or your answer

i have this problem sorted out with ISNULL()

WHERE (A.Id=@id) AND
(A.Date BETWEEN ISNULL(B.Date1,@DateMin) AND coalesce(B.Date2,@DateMax))

@DateMin = '01/01/1900'
@DateMax = '01/01/2079'

works perfectly. why i couldn't come out with this at first

Re: Problem with CASE statement in WHERE clause

Ashfield24-Mar-09 10:31

Ashfield

24-Mar-09 10:31

Just for info, coalesce is the ansi standard and has the advantage of allowing more than 2 options eg

coalesce(val1,val2,val3, val4) etc wll take the first non-null value rather than just isnull(val1,val2)

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: Problem with CASE statement in WHERE clause

marijus524-Mar-09 13:24

marijus5

24-Mar-09 13:24

good one , i'll remember this,

thanks

Re: Problem with CASE statement in WHERE clause

Ashfield24-Mar-09 22:12

Ashfield

24-Mar-09 22:12

No problem, its a useful one to know when you have several values to work with.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Delete Duplicates in MySQL

Brady Kelly22-Mar-09 23:36

Brady Kelly

22-Mar-09 23:36

I've given a client the following query to delete duplicate phone no. records in an MSSQL database, but now they need to also do it on MySQL, and they report that MySQL complains about the format of the query. I've included the setup of a test table with duplicates for my code sample, but the actual delete query is what counts.

I'm asking this in ignorance and urgency, as I am still busy downloading and installing MySQL, and just maybe somebody can help in the mean time.

create table bkPhone
(
    phoneNo nvarchar(20),
    firstName nvarchar(20),
    lastName nvarchar(20)
)
GO

insert bkPhone values('0783313780','Brady','Kelly')
insert bkPhone values('0845319792','Mark','Smith')
insert bkPhone values('0834976958','Bill','Jones')
insert bkPhone values('0845319792','Mark','Smith')
insert bkPhone values('0828329792','Mickey','Mouse')
insert bkPhone values('0834976958','Bill','Jones')

alter table bkPhone add phoneId int identity

delete from bkPhone
where phoneId not in
(
    select min(phoneId)
    from bkPhone
    group by phoneNo,firstName,lastName
    having  count(*) >= 1
)

Re: Delete Duplicates in MySQL

Blue_Boy23-Mar-09 4:22

Blue_Boy

23-Mar-09 4:22

Hope this will help you.

<br />
where phoneId not in <br />
(     <br />
select phoneId    from bkPhone  order by phoneid asc limit 1<br />
<br />
)

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

Installing SQL Server 2005 Developer Edtion

klaydze22-Mar-09 3:45

klaydze

22-Mar-09 3:45

hi, anyone can help me installing SQL Server 2005 Developer Edtion.
I have a screenshot of the error after installing the SQL Server 2005.

http://s713.photobucket.com/albums/ww134/vhanjess/?action=view¤t=Step11.jpg[^]

Actually, i'm installing this for 1 week now and i don't know how to fixed it. Maybe one of you will help me to fixed this.

Thanks and Regards

klaydze

if(you type your code here) {
Messagebox.Show("You help me a lot!");
}
else {
You help me = null;
}

Re: Installing SQL Server 2005 Developer Edtion

Mycroft Holmes22-Mar-09 13:25

Mycroft Holmes

22-Mar-09 13:25

At a guess you have permissioning issues. Have you created a user for SQL and the services to use.

No one can really help you based on a screen shot of the installation summary. Study the detail of the errors, the first one probably caused all the others so fix it first. Go through the installation directions IN DETAIL, there are 1000s of developer versions installed out there so it is not rocket science.

Never underestimate the power of human stupidity
RAH

Re: Installing SQL Server 2005 Developer Edtion

klaydze25-Mar-09 4:59

klaydze

25-Mar-09 4:59

hi, thanks for the reply. My OS is Windows Vista Home Premium Edition.

I read many instruction on how to properly install this, i also follow some KB but no one help me to install the SQL.

Thanks and Regards.

if(you type your code here) {
Messagebox.Show("You help me a lot!");
}
else {
You help me = null;
}

how to shrink the log file of a sql server 2000 database with a full recovery model? [modified]

ma.amer21-Mar-09 10:36

ma.amer

21-Mar-09 10:36

hi all,,,
i have a database with a large log file database and i don't know how to do to make it smaller,,,
the file have a large area space what can i do?
thanks in advance,,,

modified on Saturday, March 21, 2009 4:55 PM

Re: how to shrink the log file of a sql server 2000 database with a full recovery model?

Eddy Vluggen22-Mar-09 1:15

Eddy Vluggen

22-Mar-09 1:15

You can shrink the logfile on demand. There's help on how to do that from the Management Studio here[^], or if you want to do this from SQL, look here[^].

That will only help until the file fills up again, or you'll have to hunt down the reason why the logfile is filling up. The documentation on the transaction log can be found here[^].

Good luck Smile | :)

I are troll Smile | :)

Re: how to shrink the log file of a sql server 2000 database with a full recovery model?

ma.amer22-Mar-09 3:24

ma.amer

22-Mar-09 3:24

thanks alot,,,

How to activate Oracle server ?

E_Gold21-Mar-09 10:02

E_Gold

21-Mar-09 10:02

Hi How to activate Oracle server ?
and how to close ?

thank's

Password Setting

jayachandra.c21-Mar-09 5:12

jayachandra.c

21-Mar-09 5:12

Hi All,

In general, Sql Server having its default password. We can change the password also. But if I want to set a password for individual Databases in SqlServer 2005,
is it possible ?

Thanks in advance.

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