Select only one Data in a Field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select only one Data in a Field

Author

Topic

dreddski
Starting Member

9 Posts

Posted - 2008-06-09 : 03:06:04

Hi there,
Need help..
I have a table name Changekitlist and have fields name CKNumber,ck_ID,ck_Name, etc.. Now, I plan to see each CKNumber one at a time so that I can put it in an array which I'll use in VB6 program.

However, If I use the command: "SELECT CKNumber from Changekitlist"
it displays all the CKNumber list that I have (CK001 to CK500) and I cannot store it in an array properly because it display all CKNumber at once and store all the 500 CKNumbers at once in the array.

What will I do so I can display one CK Number at a time, store in an array and loop so that I'll get the next CKNumber and store it again.. Need your advice please. Thanks!

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-06-09 : 03:11:24

hi pls try this

SELECT distinct CKNumber from Changekitlis

ok tanx...

dreddski
Starting Member

9 Posts

Posted - 2008-06-09 : 04:53:13

Hi soora,
Thanks but I already tried that. It will still show all the CKNumbers on the Changekitlist because they are all unique.

Looking for a way to display it one by one and store it in an array as i increment it by one.

thank!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-09 : 04:54:25

quote:
Originally posted by dreddski

Hi soora,
Thanks but I already tried that. It will still show all the CKNumbers on the Changekitlist because they are all unique.

Looking for a way to display it one by one and store it in an array as i increment it by one.thank!

Didint get that. Can you ilustrate it with some sample data?

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-06-09 : 05:25:18

hi

pls try with group by clause
hope it helps u
if not pls post ur sample data and its output...

ok tanx...

dreddski
Starting Member

9 Posts

Posted - 2008-06-09 : 06:56:32

Hey there...I'm having a syntax error with a Group by Clause.. can you help me..anyway, here is some details.

A. Using SELECT DISTINCT CKNumber FROM Changekitlist
When I use my vb6 code.. CKTransaction.Rs.Getstring
Output is:
CK101
CK102
CK103
.
.
.
CK500

B. I tried this: SELECT CKNumber FROM Changekitlist where ck_ID=20
Output is:
CK120

With the second command, the output is correct and this is what I like actually. However, what I'm doing is I need to store all CKNumbers into an array which means 500 CKNumbers will be stored so I can use it in my program. I'm trying to substitute a variable on ck_ID so that I can increment it with For Next.. but I cant..:(

If you also have some knowledge on vb6, please help me how it will be done. But maybe the SQL command is the key here to solve this.. Need your expertise...Thanks.

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-06-09 : 07:19:59

hi
pls try using group by

SELECT min(CKNumber) from Changekitlis group by CKNumber

this will give u all distinct cknumber in ur table
and in case of duplicate cknumbers,this will give the smallest cknumber.

so i think u can use all this cknumber to ur vb application.
i dont know vb actually..

any query probs inform me..
ok..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-09 : 07:29:00

quote:
Originally posted by dreddski

Hey there...I'm having a syntax error with a Group by Clause.. can you help me..anyway, here is some details.

A. Using SELECT DISTINCT CKNumber FROM Changekitlist
When I use my vb6 code.. CKTransaction.Rs.Getstring
Output is:
CK101
CK102
CK103
.
.
.
CK500

B. I tried this: SELECT CKNumber FROM Changekitlist where ck_ID=20
Output is:
CK120

With the second command, the output is correct and this is what I like actually. However, what I'm doing is I need to store all CKNumbers into an array which means 500 CKNumbers will be stored so I can use it in my program. I'm trying to substitute a variable on ck_ID so that I can increment it with For Next.. but I cant..:(

If you also have some knowledge on vb6, please help me how it will be done. But maybe the SQL command is the key here to solve this.. Need your expertise...Thanks.

You need to use a variable in VB to store value of parameter you're passing (20) and then append it to form sql string and execute it.

something like this:-

http://en.allexperts.com/q/Visual-Basic-1048/pass-parameter-parameterized-query.htm

dreddski
Starting Member

9 Posts

Posted - 2008-06-09 : 07:34:44

Soora,
appreciate your help but the last suggestion still won't work. I mean it displays all the results from CK001 to CK500 at the same time :(. I know there is nothing wrong with the SQL command that you gave me but it shows all, instead of showing only one.

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-06-09 : 07:41:42

hi
i think u r using those cknumber from 1 to 500 again to select,in that case u will end up with duplicates..

so if u want unique ones, pls post ur table structure mentioning ur required fileds too..
then i think we can solve..
ok

dreddski
Starting Member

9 Posts

Posted - 2008-06-09 : 09:06:45

Hi there soora,
Here's the sample table format. I'm trying to select one row at a time and store each CKNumber...

ck_ID CKNumber Change Kit Name Description Device
1 CK101 NS7K-2AF1-01 14x20 QFP 2AF1-Dingo
2 CK102 NS7K-2AF1-02 14x20 QFP 2AF1-Dingo
3 CK103 NS7K-2BB6-01 35x35 BGA 2BB6-Nucleus
4 CK104 NS7K-2BL5-01 35x35 BGA 2BL5-Jedi
5 CK105 NS7K-2BL5-02 35x35 BGA 2BL5-Jedi
6 CK106 NS7K-2BV3-01 17x17 BGA 2BV3-Kamino
7 CK107 NS7K-2BV3-02 17x17 BGA 2BV3-Kamino
8 CK108 NS7K-2BV3-03 17x17 BGA 2BV3-Kamino
9 CK109 NS7K-2BV3-04 17x17 BGA 2BV3-Kamino
10 CK110 NS7K-2CP5-01 23x23 BGA 2CP5-Earth
11 CK111 NS7K-2CP5-02 23x23 BGA 2CP5-Earth
12 CK112 NS7K-2CP5-03 23x23 BGA 2CP5-Earth
13 CK113 NS7K-2CP5-04 23x23 BGA 2CP5-Earth
14 CK114 NS7K-2CP5-05 23x23 BGA 2CP5-Earth
15 CK115 NS7K-2CP6-01 31x31 BGA 2CP6-Fire
16 CK116 NS7K-2CP6-02 31x31 BGA 2CP6-Fire
17 CK117 NS7K-2CT607-01 16x16 BGA 2CT6(Sip16)-Domino
18 CK118 NS7K-2CT606-01 19x19 BGA 2CT6-Domino
20 CK119 NS7K-2CT60405-01 14x20 QFP 2CT6(Sip2/8)-Domino

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-06-09 : 09:17:08

ok tanx
then what will u do with cknumber u got with group by query in ur application,
pls say tat too..

ok

dreddski
Starting Member

9 Posts

Posted - 2008-06-09 : 09:33:08

hey there soora..
Here is the idea:
I will select only CK001 and store it in the array
example:

strSQL = (the SQL Command to show one data only in one field)
.open strSQL, connection, adOpenForwardOnly, adLockreadonly,adcmdText
Do While NOT RS.EOF
strCKNumber(500) = RS.GetString (value should be CK001)
RS.movenext
Loop

CK001 will now be stored as strCKNumber(1)

After it loops, the CKNumber is suppose to change to CK002 to store again in the array. As long as the Field is not EOF it will continue to loop.

When I store all the value on the Array.. I can now have the option to compare it with other variables in the program.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources