Sql Problem - Database Discussion Boards

Mycroft Holmes20-Aug-09 13:15

20-Aug-09 13:15

return (ModelsRow[])this.Models.Select("C_Make = '" + make + "' AND C_Model LIKE '%" + model + "%' or C_Model like '306%'");

This answer is too simple I must be missing something in your question.

I use stored procs for 99.9% of my database work and therefore can design the SQL to do exactly what I want in QA/EM. I suggest you do this to get the result you want and then move it to a string if that is where you want to have you SQL. Also look into parameterised queries, you current model is subject to sql injection.

Never underestimate the power of human stupidity
RAH

Re: LIKE Predicate

TheIdleProgrammer20-Aug-09 22:10

TheIdleProgrammer

20-Aug-09 22:10

Hi Mycroft,

Mycroft Holmes wrote:
return (ModelsRow[])this.Models.Select("C_Make = '" + make + "' AND C_Model LIKE '%" + model + "%' or C_Model like '306%'");

This won't work because the '306' is what's actually in C_Model; at the point this statement is executed I don't actually know what that is. I just used that '306' string as a typical example.

To be honest, the more I've thought about this the more I've decided it's a stupid thing to do in the context of my application anyway so I've abandoned the idea for now. I'm sorry if I've wasted your time. Many thanks for your help anyway.

Thanks again,

Chris Chambers.

Re: LIKE Predicate

Mycroft Holmes21-Aug-09 0:33

Mycroft Holmes

21-Aug-09 0:33

Not a problem

Never underestimate the power of human stupidity
RAH

Re: LIKE Predicate

David Skelly20-Aug-09 22:53

David Skelly

20-Aug-09 22:53

I'm not sure if I understand your question. I think this will do what you want:

select * from Models
where C_Model like '%306 -97%'
or '306 -97' like C_Model + '%'

It won't be the fastest query in the world, but it should do what you want. It will pick out models that have 306 -97 anywhere in the model number, (e.g. '4306 -97') and it will also get all the rows with model numbers '306'. If there are any rows with model number '3' or '30' it will get those too, but that's what you seem to be asking for.

Re: LIKE Predicate

TheIdleProgrammer20-Aug-09 23:07

TheIdleProgrammer

20-Aug-09 23:07

Hi David, thanks for your input.

I might try that to see if it works in my application, however as you rightly said it's likely to pick up quite a few entries that I don't want so as I said to Mycroft I think I've decided not to go ahead with it. I'll let you know if that works though.

Regards,

Chris Chambers.

Re: LIKE Predicate

Luc Pattyn20-Aug-09 4:58

Luc Pattyn

20-Aug-09 4:58

I think Mycroft is right.

And if all else fails, you could still use substrings and go for an exact match of the first N characters.

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.

Re: LIKE Predicate

TheIdleProgrammer20-Aug-09 22:11

TheIdleProgrammer

20-Aug-09 22:11

Thanks for your input Luc, much appreciated.

Regards,

Chris Chambers.

Sql Problem

AhmedGebaly19-Aug-09 23:37

AhmedGebaly

19-Aug-09 23:37

Id Credit Debit CurrentCredit Current Debit
51 21500 0 21500 0
52 0 21500 0 0
89 0 2150 0 2150
93 0 2140 0 4290
128 2140.5 0 0 2149.5
129 2151.5 0 2 0
171 0 1628 0 1626
190 1628 0 2 0

i have column of credit and debit and want to get columns of current credit and current debit
example:
51. credit=21500 and debit=0
i have credit more than debit so this increase credit and decrease debit
then
current credit = 0+21500

52. credit=0 and debit=21500
i have debit more than credit so this decrease credit and increase debit
then current credit =0 and current debit =0

89 credit=0 and debit =2150
i have debit more than credit so this decrease credit and increase debit
then current credit =0 and current debit=0+2150=2150

93 credit=0 and debit =2140
i have debit more than credit so this decrease credit and increase debit
then current credit =0 and current debit=2140+2150= 4290
and so on...

Re: Sql Problem

David Skelly20-Aug-09 2:26

David Skelly

20-Aug-09 2:26

That's very interesting. Is there a question in there?

Re: Sql Problem

Kschuler20-Aug-09 3:17

Kschuler

20-Aug-09 3:17

Are you just looking for this?

SELECT SUM(CurrentCredit) - SUM(CurrentDebit) AS FinalBalance FROM myTable

Re: Sql Problem

Jerry Hammond20-Aug-09 3:41

Jerry Hammond

20-Aug-09 3:41

As interesting as your data is, what is your query?

"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering

set row number in select query

nainakarri19-Aug-09 20:34

nainakarri

19-Aug-09 20:34

Hi
I have a table with duplicate rows and distinct id. I have written a select query using distinct and selecting only required fields to avoid duplicates.

Select dictinct(occassion), startdate from table1

Now what i want is to give unique row number to each distict row

For this i tried as below but could not succeed
Declare @num int
set @num =0
select (set @num = @num+1) as num,
dictinct(occassion), startdate from table1

After search in google , i got row_number(). But this i am unable to use as i am using distinct. Can u pls let me know how i can achieve this.

Thanks in advance
Naina

Naina

Re: set row number in select query

_Damian S_19-Aug-09 20:41

_Damian S_

19-Aug-09 20:41

What are you going to do with the unique row number? How will it assist you? Where are you using it?

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!!

Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!!
Do something wonderful - make a donation to Camp Quality today!!

Re: set row number in select query

nainakarri19-Aug-09 20:44

nainakarri

19-Aug-09 20:44

Hi

Thanks for the reply.
It is very useful for me. As i will use it in my web page (front end) where other functionality related to this unique number is related to.
Please help me to get this.
Regards
Naina

Naina

Re: set row number in select query

Coding C#20-Aug-09 3:36

Coding C#

20-Aug-09 3:36

Cant you just use ID in the record from table itself?

Coding C#

ExciteTemplate

how to connect to magic database

elimpiado19-Aug-09 19:53

elimpiado

19-Aug-09 19:53

hi all,

would like to receive help on how to connect to a magic software database using vb.net.

thank you very much.

Re: how to connect to magic database

Mycroft Holmes19-Aug-09 20:22

Mycroft Holmes

19-Aug-09 20:22

I'll bite, if you can't find something at connectionstrings.com then you need to go back to the vendor. Never heard of it before!

Re: how to connect to magic database

Ashfield19-Aug-09 21:00

Ashfield

19-Aug-09 21:00

I came across Magic Software back in the early 90's, I have to say (at the time) it was very inmpressive but too expensive for the company I was employed by at the time. I haven't come across it since, so I assumed like many other companies, it had gone under. Great product, dodgy name Smile | :)

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

how to attatch, restore database to msde using vb.net or c# .ne

Ageesh18-Aug-09 22:43

Ageesh

18-Aug-09 22:43

hi..

i would like to attach OR RESTORE a database TO MSDE USING VB.NET OR C#.
PLZ HELP ME OUT TO SOLVE THIS PROBLEM...

THNKS & RGDS

AGEESH

Re: how to attatch, restore database to msde using vb.net or c# .ne

Mycroft Holmes19-Aug-09 17:46

Mycroft Holmes

19-Aug-09 17:46

I would help but I have never used MSDE, isn't this a forerunner to sql server express? Looks like some likely responses with this search[^]

How to calculate the total memory occupied by the database

Krishna Aditya18-Aug-09 22:42

Krishna Aditya

18-Aug-09 22:42

Hi,

I am using sqlserver 2008, How can I calculate the total memory occupied by the Database with tables (>30) and also data in it.

I mean if I have DB (DB_name) and with few tables(tblabc, tbldef......) with data in it, how to calculate the total memory occupied by the database in the server.

Kindly Help me.

Thanks

Ramm

Re: How to calculate the total memory occupied by the database

dan!sh 18-Aug-09 23:07

dan!sh

18-Aug-09 23:07

Like this:

use YourDBName
SELECT size FROM sys.database_files

It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD

Re: How to calculate the total memory occupied by the database

Krishna Aditya18-Aug-09 23:18

Krishna Aditya

18-Aug-09 23:18

Hi D@nish,

I executed the query, it showed, 1408 and 2768 without data in database.
even with data it showd the same numbers.

please help me.

Thanks
Ramm

Re: How to calculate the total memory occupied by the database

Mycroft Holmes18-Aug-09 23:23

Mycroft Holmes

18-Aug-09 23:23

Well have a rat around the management studio, sysobjects also has lots of info.

Take a look at the system views, they are design to give you info on your databases.

Re: How to calculate the total memory occupied by the database

Andy_L_J18-Aug-09 23:32

Andy_L_J

18-Aug-09 23:32

Right Click your database in Object Explorer, Select Reports - Standard Reports - Disk Usage

explore these reports for common reqs.

I don't speak Idiot - please talk slowly and clearly

I don't know what all the fuss is about with America getting it's first black president. Zimbabwe's had one for years and he's sh*t. - Percy Drake , Shrewsbury

Driven to the arms of Heineken by the wife

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