Finally Something i can help with - Database Discussion Boards

Chris Meech16-Nov-07 11:26

16-Nov-07 11:26

This KB article[^] has given me a good starting point except it is ADO.NET with managed C++. Would anyone know of an article that is ADO with native C++ and would connect to SQL Server 2005 Express Edition. Thanks.

Chris Meech
I am Canadian. [heard in a local bar]
Donate to help Conquer Cancer[^]

I ask for help on the following SQl statement

Vimalsoft(Pty) Ltd16-Nov-07 4:38

Vimalsoft(Pty) Ltd

16-Nov-07 4:38

hi all, the Bolow Query find me Records that have Duplicates,

select Property_ID,Lis_key from Property_Private
group by Property_ID,Lis_key
Having count(Property_ID) = 2
order by Property_ID

.

Now Property_ID is not a Primary_key, in this table, i have created another Field and named it "Primary_key", that will be an identity Column and a Primary key. So from the above Query i want to delete only the record that have been found by this query.

Thanks

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

Re: I ask for help on the following SQl statement

andyharman16-Nov-07 8:25

andyharman

16-Nov-07 8:25

Try http://www.codeproject.com/script/comments/forums.asp?forumid=1725&Page=4&userid=212103&mode=all&select=2041593&df=100&tid=2041593&noise=2&mpp=50#xx2041593xx[^]

Regards
Andy

Please help on making a order transaction

tgreeny16-Nov-07 1:59

tgreeny

16-Nov-07 1:59

Hi,

What would be the best way of making a transaction from a aspx webpage to the SQL database for example,

where a customer orders a product, hits the order button and then this transaction goes through to the database where it automatically updates the records. Any ideas?

cheers

Re: Please help on making a order transaction

Pete O'Hanlon16-Nov-07 2:33

Pete O'Hanlon

16-Nov-07 2:33

Your button handler will be responsible for reading the data from the page. A separate layer will contain code which will map to an insert into a database (this insert will be done through a stored procedure). In the case of an update, use an update statement instead of an insert.

Deja View - the feeling that you've seen this post before.

My blog | My articles

Replace Text

Mohammed Elkholy16-Nov-07 1:26

Mohammed Elkholy

16-Nov-07 1:26

hi , first i would thank you for help

i have a column in table contains a code for students

the code format is 11-1-07-0001

what i want , i want to replace the first tow digits from 11 to 01

without any efection to the other characters

MD_NADA

Re: Replace Text

Shpendh16-Nov-07 1:48

Shpendh

16-Nov-07 1:48

did you try it with SUBSTRING?

spaps

Finally Something i can help with

Support12316-Nov-07 2:11

Support123

16-Nov-07 2:11

This is not something that happens to me every day... for once i can finally give help to someone else and not be the one asking the question Smile | :)

SELECT YourStudentNumberColumn AS [Test],<br />
		'01' + SUBSTRING(YourStudentNumberColumn,3,LEN(YourStudentNumber) - 2) AS [TestAfterUpdate]

Notice the 3 in the substring, this is the start position. The LEN(YourStudentNumber) - 2 is the amount of character to use. So it will start at position 3 being the first '-' in your student number and end at the end of the student number as it gets the length of the YourStudentNumber subtracts 2 as you start at position 3 (the first '-'). If this is unclear run the query in sql and change the '- 2' to '- 3' and you will see what it does.

the SUBSTRING works like this.
Substring(YourValue, StartPosition(1 for beginning of your value), AmountOfCharacters)

I added the select statement so that you can make sure that this is the correct value that you need.

If it is... you can run it like this for the UPDATE:

UPDATE YourTableName<br />
SET YourStudentNumberColumnName = '01' + SUBSTRING(YourStudentNumberColumnName,3,LEN(YourStudentNumber) - 2)

"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison

Re: Finally Something i can help with

Mohammed Elkholy16-Nov-07 2:46

Mohammed Elkholy

16-Nov-07 2:46

thank you very much , this is what i want

i dont have any words to say

but i am thankfull for any one in the world liks to help the others

MD_NADA

triggers

dandamudi padma16-Nov-07 1:00

dandamudi padma

16-Nov-07 1:00

hi i had to create Rowlevel triggers and my req is-- if a new row is added in table1 then the same current row has to be updated in my table2.please can anybody help me

padma

Re: triggers

DerekFL16-Nov-07 4:44

DerekFL

16-Nov-07 4:44

CREATE TRIGGER sdfsd
ON Table1
FOR UPDATE
IF (UPDATE(Column1)
UPDATE Table2 SET Column1 = i.Column1
FROM Table2 t2
INNER JOIN INSERTED i ON i.RowID = t2.RowID

Re: triggers

DerekFL16-Nov-07 4:56

DerekFL

16-Nov-07 4:56

dandamudi padma wrote:
if a new row is added in table1

Did you mean when a row is UPDATE same row has to be updated on table2?

Re: triggers

dandamudi padma16-Nov-07 17:57

dandamudi padma

16-Nov-07 17:57

thanks for ur reply but

what i mean is if a row is inserted in table1 after insert the same row has to be Inserted in table2.

can u give solution for this

Regards
padma

padma

DB transfer

mehrdadc4816-Nov-07 0:21

mehrdadc48

16-Nov-07 0:21

Hi,
How can I transfer a DB from SQL Server 2005 to 2000 completely(with all tables, diagrams, stored procedures and ...)

Best wishes

Re: DB transfer

ChandraRam16-Nov-07 0:35

ChandraRam

16-Nov-07 0:35

mehrdadc48 wrote:
How can I transfer a DB from SQL Server 2005 to 2000 completely(with all tables, diagrams, stored procedures and ...)

I think you can "Attach" the database from 2005 to the 2000 server. You will need to stop the SQL service before doing this.

right align

Support12315-Nov-07 23:29

Support123

15-Nov-07 23:29

Hi,

Can you set a column to store data right aligned? (the reason i want to know this is because all our values are set to char() datatype in SQL database. This will mean that the left out characters / the character that are short will be filled in with blank spaces. Having the value right aligned will mean that the spaces will be put in front of the value and not at the back and that is what i am looking for (if this is possible).)

OR

is there a way to select the values from the database as right aligned (i will be using the REPLACE(TheRecordFromTheTable, ' ','') , this will help to remove the added spaces from the back due to char() datatype.)

Thank you in advance...

"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison

Re: right align

joemonvarghese15-Nov-07 23:48

joemonvarghese

15-Nov-07 23:48

hi

plz clarify your question...

Regards
Joe

Re: right align

Joe16-Nov-07 0:04

Joe

16-Nov-07 0:04

Support123 wrote:
is there a way to select the values from the database as right aligned

May I know what is your objective in doing so?

Re: right align

Krish - KP16-Nov-07 0:18

Krish - KP

16-Nov-07 0:18

Is this what you are trying

declare @a char(20)<br />
set @a = 'testing'<br />
select right(space(20) + rtrim(@a), 20)

1. Removing spaces at end
2. Adding at the begining
3. Ignoring extra spaces from begining

Regards
KP

Re: right align

ChandraRam16-Nov-07 0:34

ChandraRam

16-Nov-07 0:34

Support123 wrote:
Can you set a column to store data right aligned? (the reason i want to know this is because all our values are set to char() datatype in SQL database. This will mean that the left out characters / the character that are short will be filled in with blank spaces. Having the value right aligned will mean that the spaces will be put in front of the value and not at the back and that is what i am looking for (if this is possible).)

OR

is there a way to select the values from the database as right aligned (i will be using the REPLACE(TheRecordFromTheTable, ' ','') , this will help to remove the added spaces from the back due to char() datatype.)

Is there a particular reason why you are storing numbers as strings? Wouldn't storing numbers as binary fields be more efficient?

Re: right align

Support12316-Nov-07 1:50

Support123

16-Nov-07 1:50

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 Smile | :)

)

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

urgent: can anyone plz tell the error in santex of SQL Query

mavii15-Nov-07 22:28

mavii

15-Nov-07 22:28

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

Re: urgent: can anyone plz tell the error in santex of SQL Query

Rocky#15-Nov-07 22:38

Rocky#

15-Nov-07 22:38

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.

Re: urgent: can anyone plz tell the error in santex of SQL Query

InsDev15-Nov-07 22:46

InsDev

15-Nov-07 22:46

Could be possible of your date format. just try to check your input date and date store in table.

Devjit Das.

Re: urgent: can anyone plz tell the error in santex of SQL Query

Krish - KP15-Nov-07 23:03

Krish - KP

15-Nov-07 23:03

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

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