Question Locked

Query button in Visual Basic with Query result

0 helpful
2 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have query thaqt work in Query Analyzer how do i made an asp.net "aspx" web page with buttons that start the query and show me the result of query.

I need to import an .xls in table "nimic"

My query:
*/
-- cut to 9 digits
UPDATE nimic SET nr = '0' + LTRIM(RTRIM(nr)) WHERE LEN(LTRIM(RTRIM(nr))) = 8
UPDATE nimic SET nr = '00' + LTRIM(RTRIM(nr)) WHERE LEN(LTRIM(RTRIM(nr))) = 7
UPDATE nimic SET nr = '000' + LTRIM(RTRIM(nr)) WHERE LEN(LTRIM(RTRIM(nr))) = 6
UPDATE nimic SET nr = '0000' + LTRIM(RTRIM(nr)) WHERE LEN(LTRIM(RTRIM(nr))) = 5
UPDATE nimic SET nr = '00000' + LTRIM(RTRIM(nr)) WHERE LEN(LTRIM(RTRIM(nr))) = 4
UPDATE nimic SET nr = '000000' + LTRIM(RTRIM(nr)) WHERE LEN(LTRIM(RTRIM(nr))) = 3
UPDATE nimic SET nr = '0000000' + LTRIM(RTRIM(nr)) WHERE LEN(LTRIM(RTRIM(nr))) = 2
UPDATE nimic SET nr = '00000000' + LTRIM(RTRIM(nr)) WHERE LEN(LTRIM(RTRIM(nr))) = 1
*/
-- numbers need to be
between first an last number
DECLARE @n1 nvarchar(15),
@n2 nvarchar(15)

SET @n1 = '028824001' //first number
SET @n2 = '028940049' //last number

SELECT nr FROM nimic
WHERE nr NOT BETWEEN @n1 AND @n2
AND LEN(nr) = 9
UNION
SELECT nr FROM nimic
WHERE SUBSTRING(nr, 2, 9) NOT BETWEEN @n1 AND @n2
AND LEN(nr) = 10
ORDER BY 1

*/
-- Show me DUPLICATE numbers
SELECT DISTINCT TT.nr_duplicate, C.nr, T.*
FROM nimic T
INNER JOIN
(SELECT nr, count(*) AS nr_duplicate
FROM nimic
GROUP BY nr
HAVING count(*) > 1)
TT ON TT.nr = T.nr
INNER JOIN nimic C ON C.nr = T.nr

2 replies

by tony hopkinson

query.

Way too big a topic, depending on what you have, how much you know or don't.

www.w3cschools.com, is a good place to pick up what you need.

Including some more sql....

update nimic set nr = trim(nr)
Update nimic set nr = Replicate('0',9 - DataLength(nr)) + Nr where Datalength(nr) < 10
would be betterer than nine updates and a heck of a lot of calls to ltrim and rtrim

Not mad keen of the rest of it either, why do you need nr three times in the output?

This discussion is closed to new replies.