asked on

query to select values from oracle db based on a value

Hello,

from my oracle db i would like to retreive data based on the combobox value, but the problem is want to retreive only the value starting after '.'

for eg:- if my combobox value is 1234, then i want to retreive from oracle 001, where in the oracle db the value is like 1234.001

so i would like to retreive only the values starting after '.'

im trying this but its not showing any result

can somebody please help, its little bit urgent

thanks

...........................................
oRec.Open "SELECT substr(proj_nr1, instr(proj_nr1, '.')) FROM regdet where substr(proj_nr1, 0, instr(proj_nr1, '.')-1)='" & result4 & "'", oConn

SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ryan Chong

>>so i would like to retreive only the values starting after '.'
oops, try like:

SELECT substr('1234.001', 1, instr('1234.001', '.')-1) , substr('1234.001', instr('1234.001', '.')+1) FROM
dual

? so become as:

...
sqlstr = "SELECT substr(proj_nr1, instr(proj_nr1, '.')+1) FROM regdet where substr(proj_nr1, 1, instr(proj_nr1, '.')-1) = '" & result4 & "' "
oRec.Open sqlstr, oConn
...

hope this helps, regards

sunshine737

ASKER

query is working, i tried it in the sql and even my old one is working but its not populating the data.......something wrong with the code below??????

oRec.Open "SELECT substr(proj_nr1, instr(proj_nr1, ',')+1) FROM regdet where substr(proj_nr1, 1, instr(proj_nr1, ',')-1) = '" & result4 & "'", oConn

While Not oRec.EOF

Tabelle2.ComboBox2.AddItem (oRec.Fields("proj_nr1").Value)
oRec.MoveNext

Wend

EDDYKT

do you see any value from oRec

ie

oRec.movefirst
While Not oRec.EOF

msgbox oRec("proj_nr1")
Tabelle2.ComboBox2.AddItem (oRec("proj_nr1"))
oRec.MoveNext

Wend

sunshine737

ASKER

>>>

no its not showing any values

ASKER CERTIFIED SOLUTION

Shiju S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sunshine737

ASKER

thanks shijusn and others

its working

sunshine737

ASKER

one more small question the same above query if i want to retreive one more field, what should i do,

i mean (proj_nr1 and proj_desc)

Ryan Chong

>> one more small question the same above query if i want to retreive one more field, what should i do

You can try select the additional field like:

...
sqlstr = "SELECT substr(proj_nr1, instr(proj_nr1, ',')+1) AS RESULT, proj_desc FROM regdet where substr(proj_nr1, 1, instr(proj_nr1, ',')-1) = '" & result4 & "'"
...

cheers