vb 6 and SQL 2005

Dear,
I want to know what is wrong in this statement.

Dim wsd As New ADODB.Recordset
Dim tcompname
tcompname = Trim(currentnode.FullPath)
wsd.Open ("select * from Usertable where User='" & tcompname & "'"), cn, adOpenStatic, adLockOptimistic

It is always returning me EOF even when condition specified is correct. Why so. I m able to search numeric conditions, but on strings it does not work. Returns EOF,.
Please help
With Regards

ShowkatdarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RiteshShahCommented:
where is your connection, you have defined recordset but didn't define anything about which SQL Server you want to connect with which ID/PASS and database
0
RiteshShahCommented:
0
RiteshShahCommented:
I guess you have "cn" in your code so you are using connection but I guess there is some problem in that only. moreover, you can check is there any data in table or not?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Anthony PerkinsCommented:
>>I want to know what is wrong in this statement.<<
Where do I start?

Why don't you post the table structure for Usertable and some sample data?

Have you tried running the SQL statement from SSMS?  If so what was the query and what were the results?
0
ShowkatdarAuthor Commented:
I m connecting to database in module. That is ok, no issue with that. Here when trying to search strings i m facing issue.Returns EOF. Is my search command OK
Thanks
0
RiteshShahCommented:
in that case, I guess you are not meeting criteria of WHERE condition, have you tried to manually run that query in SSMS?

suppose you entered "Ritesh" in text box and searching with your query like

select * from Usertable where User='Ritesh'

there is possiblity, there is no user like 'Ritesh'. do one thing, try using following command.

wsd.Open ("select * from Usertable where User like '%" & tcompname & "%'"), cn, adOpenStatic, adLockOptimistic

enter only one character like 'a' in your textbox and check, whether it works
0
ShowkatdarAuthor Commented:
Here it goes

This i have put in module and gives me connectivity with my database

Public cn As New ADODB.Connection
cn.Open ("Provider=SQLOLEDB.1;Data Source=(local);uid=sa;password=abcd;Initial Catalog=Kite")

This i have put on Tree view double click event
Dim wsd As New ADODB.Recordset
Dim tcompname
tcompname = Trim(currentnode.FullPath)
wsd.Open ("select * from Usertable where User='" & tcompname & "'"), cn, adOpenStatic, adLockOptimistic




E.jpg
0
RiteshShahCommented:
have you ever tried checking what value comes to "tcompname " variable? before wsd.Open, have one msgbox and see what comes.

msgbox tcompname

0
ShowkatdarAuthor Commented:
Yes i have done that. It is ok aswell. Then i checked the string length where it is varying, so u  might see in my code
tcompname = Trim(currentnode.FullPath)
Using Trim to match the strings.
Something is wrong, but dont know where
0
RiteshShahCommented:
all I suggest is first manually try to run query in SSMS. suppose you get value 'abcd' in tcompname than try running query.

select * from Usertable where User='abcd'

see, what you get in SSMS, have you tried running the same query directly in SQL Server?
0
Anthony PerkinsCommented:
Once again, could we have some sample data so that we can see what could be failing.  You should however be aware that if you have leading spaces you will not get any matches with the code as it stands.  In other words:
If the User = "  ABC"
Your WHERE condition will fail as you are doing a Trim()
0
Anthony PerkinsCommented:
Also, you should be aware that "user" is a reserved keyword so this:
wsd.Open ("select * from Usertable where User='" & tcompname & "'"), cn, adOpenStatic, adLockOptimistic

Should be:
wsd.Open "select * from Usertable where [User]='" & tcompname & "'", cn, adOpenStatic, adLockOptimistic

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShowkatdarAuthor Commented:
Good Eye,Excellent.
U were right,all was happening coz of user keyword as it is reserved keyword. It never came in my mind
Thankx for your support
0
Anthony PerkinsCommented:
>>U were right,all was happening coz of user keyword as it is reserved keyword. <<
This would imply that somewhere you have the infamous "On Error Resume Next".  It is very rarely a good idea to use this and even then used sparingly.  It is akin to saying:  "If there are any errors, I don't care, ignore them"  If you did not have that, you would have got an error indicating what was the problem and you could have been saved all the time and effort.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.