|
Thank all of you...
BUT never mind... i found a way around my problem... sorry for stating my question so unclear. I wasn't even sure what i needed.
What i wanted to do is add the spaces in front of some of the Values in the database. This is then easier when i have to populate the flatfile using ssis package. (The reason why i store the numbers as CHAR() is just a staging table. Easier to get the fixed length right )
Thank you all once again for the effort.
"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
|
|
|
|
|
i have applied the following query but in output one record appears repeatedly, can u check whether there is any error in query santax, perhaps i have used 'AND' 2 TIMES
SELECT crime_mgmt.cr_location,crime_mgmt.cr_date,offensive_issue.c_desc FROM crime_mgmt,offensive_issue WHERE crime_mgmt.fir_num = offensive_issue.fir_num AND crime_mgmt.cr_date BETWEEN '" + crimewithdate.text.ToString() + "' AND '" + crimewithdate.text1.ToString() + "'ORDER BY crime_mgmt.cr_date ASC
|
|
|
|
|
SELECT crime_mgmt.cr_location,crime_mgmt.cr_date,offensive_issue.c_desc
FROM crime_mgmt inner join offensive_issue on crime_mgmt.fir_num = offensive_issue.fir_num
WHERE crime_mgmt.cr_date BETWEEN '" + crimewithdate.text.ToString() + "' AND '" + crimewithdate.text1.ToString() + "'ORDER BY crime_mgmt.cr_date ASC
and dont forget to enclose it properly as far as thequotes are concerned. you're better off chking the value of the actual query formed while debugging.
Rocky
You can't climb up a ladder with your hands in your pockets.
|
|
|
|
|
Could be possible of your date format. just try to check your input date and date store in table.
Devjit Das.
|
|
|
|
|
mavii wrote: have applied the following query but in output one record appears repeatedly
This can happen when there are multiple rows for same "fir_num"
can you provide table structure with sample data.
Regards
KP
|
|
|
|
|
Interesting, and quite disappointing, that no one picked you up on the fact that your code is susceptable to a SQL Injection attack.
Please learn about SQL Injection Attacks and tips on how to prevent them[^] - most likely by following that advice you will solve other problems as well.
|
|
|
|
|
Colin Angus Mackay wrote: Interesting, and quite disappointing, that no one picked you up on the fact that your code is susceptable to a SQL Injection attack
I'm sorry Colin. I didn't even read his post until after I read your response. Mind you, it's interesting that the system he was working on had something to do with crime management (I'm inferring this from the table name). I would have thought that he'd want to make things as secure as possible beforehand.
|
|
|
|
|
Pete O`Hanlon wrote: I would have thought that he'd want to make things as secure as possible beforehand.
Indeed. Unless, of course, he's in league with certain elements.....
|
|
|
|
|
Hi,
i'm searching for a stored procedure (or other way) to create scripts from objects in a database, given the object as parameter.
Reason i do this is :
I want to create a script of all objects modfied after a certain date. Those i can retrieve using
select * from sys.all_objects where /*name like 'tbl_%' and*/ modify_date > '2007-10-29'
but now i want to create automatically a script of those modified objects.
Thx
Kurt
|
|
|
|
|
hi
You can use EnterPriseManager to create the script of objects.
Then why r u searching for Stored Procedures.
Reagrds
Joe
|
|
|
|
|
hi,
i want it to generate automatically, not using the enterprise manager or studio..
a select from systemobjects and use those to generate all new or modified tables, triggers and stored procs... in one action, without selecting them yourself.
greetz
kurt
|
|
|
|
|
hi genius people i need help again.. and tnx again in advance!!!
i just want to get the execution time of every query made or views or stored procedure executed.. just like in the query analyzer when you execute a command or any query.. i just want it displayed while on
run time or after the run time
is it possible?or am i just too newbie for this..
|
|
|
|
|
Hi all
How to convert FLOAT to INT in SQL Server2000
When i use cast or convert function get this error
Server: Msg 232, Level 16, State 3, Line 1
Arithmetic overflow error for type int, value = 2400000000.000000.
The statement has been terminated.
Thank
|
|
|
|
|
Max value for INT data type is 2147483647
Use BIGINT as the value you are trying to convert is more than the max value of INT
Regards
KP
|
|
|
|
|
It is clear, but if i'm still needed to convert bigest value to more easy, is an way exists???
|
|
|
|
|
In SQL there are INT & BINT data types
currently you are using INT
instead user BIGINT
Regards
KP
|
|
|
|
|
hai ,
i got count(Empid) from one viewtbl and count(dept) from another table in one storedprocedure,
i want the percentage of count(Empid) and count(dept)
Thanks in advance
Thanks
Subbu.
|
|
|
|
|
How is this different than the question you asked just several hours earlier, that Pete already answered? Unless this is an entirely different question, you'd probably be better off continuing the thread you already started.
Sorry if it's obvious to everyone else...
David
---------
Empirical studies indicate that 20% of the people drink 80% of the beer. With C++ developers, the rule is that 80% of the developers understand at most 20% of the language. It is not the same 20% for different people, so don't count on them to understand each other's code.
http://yosefk.com/c++fqa/picture.html#fqa-6.6
---------
|
|
|
|
|
I'm starting a new project next year (business application)
I will be the only one working on it from a start,
but my SQL knowledge is limited (not the SQL syntax, but the database design)
Until now I've sorted my problems on the way, but that's not a long term solution that I'm looking for,
so can anyone point me to some good articles (or books in e-book format as I live in a country with limited book availability)
Thanks in advance
|
|
|
|
|
progress-database-design-guide/[^]
en.wikipedia.org/wiki/Database_design
www.databasedesign-resource.com
well u should consider getting urself acquianted with basic db design principles and normalization (uptp 3NF atleast).
wikipedia can also be helpful for u and Google offcourse is also there to help u out.
Hope that helps u
best of luck
Rocky
You can't climb up a ladder with your hands in your pockets.
|
|
|
|
|
I've only been in database design a few years, and have learned a lot of things the hard way. But I'm sure there a lot of other things I haven't learned yet, or am *about to* learn them the hard way.
The link you provided, Rocky# wrote: progress-database-design-guide/[^]
says in one place:
"primary keys with business meaning as well as composite keys are a bad idea."
This I did not know, and even now am not convinced of. I have tended to make my designs exactly the opposite. Let me give an example of a table I recently designed.
CREATE TABLE tblScheduledInventory(
asofDate SMALLINT -- number of days since Jan 1, 2000
, partsGroupID SMALLINT -- REFERENCES tblPartsGroups
, locationID SMALLINT -- REFERENCES tblLocations
, statusCode SMALLINT -- "installed", "spare", etc.
, inventoryTypeID SMALLINT -- "active", "in-reserve", etc.
, CONSTRAINT pkScheduledInventory PRIMARY KEY
(asofDate,partsGroupID,locationID,statusCode,inventoryTypeID)
, cnt INT
);
I need the five fields within the PK to be compositely unique. In addition to that, I KNOW that during use of this table, the records are going to be "marched-thru" in a date-order, and for each date, the records will be traversed in a parts-grouping order. Therefore, I WANT the clustered index (the actual ordering of records within storage) to be by asofDate, then by partsGroupID. This should produce faster queries since all the records of interest will be clustered near one another, and not scattered all over the hard-drive. The only way to achieve this (that I know of) is to make the primary key a composite index of the items that need to be unique, with the most important item being first in the list.
Perhaps someone could shed some light on *why* this might be a bad design practice. Examples would really help me understand.
BTW, in other readings I've discovered there are alternate opinions regarding this point. Some say the way I do it is OK.
David
---------
Empirical studies indicate that 20% of the people drink 80% of the beer. With C++ developers, the rule is that 80% of the developers understand at most 20% of the language. It is not the same 20% for different people, so don't count on them to understand each other's code.
http://yosefk.com/c++fqa/picture.html#fqa-6.6
---------
|
|
|
|
|
thanks for ur response David its really nice!
and uknow I believe in one thing: "Rules always have exceptions"... Generally they do say n its true to quite some extent that one should avoid making unnecessary composite PKs when a single attribute PK shud do. Well actually the thing is that having unnecessary composite PKs can make ur design vulnerable to having some anomalies u know it can fail satisfy 2nf. But still it all depends on ur particular scenario bcz in a way ur scenario can be peculiar to many.
I think ur idea there is quite intelligent and looks quite good to me mate!
Rocky
You can't climb up a ladder with your hands in your pockets.
|
|
|
|
|
Thank you for your help
I've been quite new into this - and since I've lost my mentor, any help is more than welcome.
|
|
|
|
|
ur welcome mate!
someday every buddy has to become a mentor himself.
Rocky
You can't climb up a ladder with your hands in your pockets.
|
|
|
|
|
sorry if in the wrong Forum, but I have a question about creating an Access form that works off of 2 tables.
Here is what I have:
tbl_Inventory: RecID, Name, Date, PartNo, Description<br />
tbl_Defects: RecID*, DefectType (RecID* matches RecID in tbl_Inventory)<br />
tbl_DefectTypes: ID, Descriptive_Value<br />
I then have a form where a user can manually enter the values to fill tbl_Inventory. ONE of those fields asks for Defect Type (a combo pulldown control). This form works with tbl_Inventory and tbl_Defects. When there is a defect, tbl_Defects gets the RecID from tbl_Inventory and assigns Defect Type (mechanical, operator, obsolete)
How do I establish my form's (Record) Source? Do I use both tbl_Inventory and tbl_Defects? tbl_Inventory actually has 23 fields and when I tried to use the SQL builder for Record Source of the form, I ran out of "space" (columns) for all my fields.
Any where I can look to get a quick tutorial on using more than one table as the source for ONE form and have the two tables synched when I update in the form?
Much thanks to this newbie Access developer.
Johnny
|
|
|
|