how to search a string

Question

karthiyayiniyp 7 Newbie Poster

15 Years Ago

sir/madam
i need a help.......how to search a string from oracle to vb.......my codings are not working properly so please correct it........i attach my codings below...

accse.Open "Provider=MSDASQL.1;Password=tiger;Persist Security Info=True;User ID=scott;Data Source=facultydetails"

rsfdcse.Open "select * from csefacultydetails where name= " & "'&name1.Text '", accse, adOpenDynamic, adLockOptimistic

If Not rsfdcse.RecordCount = 0 Then
If rsfdcse.Fields(0) = Val(name1.Text) Then

name1.Text = rsfdcse.Fields(0)
designation.Text = rsfdcse.Fields(1)
qualification.Text = rsfdcse.Fields(2)
department.Text = rsfdcse.Fields(3)
dateofjoining.Value = rsfdcse.Fields(4)
sex.Text = rsfdcse.Fields(5)
address.Text = rsfdcse.Fields(6)
contactno.Text = rsfdcse.Fields(7)
community.Text = rsfdcse.Fields(8)
emailid.Text = rsfdcse.Fields(9)
MsgBox "RECORD FOUND"
End If
accse.Close
rsfdcse.Close
Else
MsgBox "RECORD NOT FOUND"

End If

oracle visual-basic

4 Contributors
15 Replies
180 Views
4 Days Discussion Span
Latest Post 15 Years Ago Latest Post by AndreRet

Dani AI

Generated 6 Months Ago

Quick summary and root causes (thread context): the BOF/EOF error came from attempting to read fields when the Recordset was empty. correctly pointed out the missing quoting in the SQL and advised using field names; then solved the immediate symptom by forcing numeric conversions with Val, but that is brittle and masks the real issues. The stable solution is: (1) detect an empty Recordset with EOF/BOF, (2) treat name as text (not with Val), and (3) use parameters or properly escaped SQL to avoid missing matches or SQL injection.

Safe, robust pattern (ADO, Oracle-friendly): open a parameterized Command or prepared statement, execute it, then check the Recordset before reading fields. RecordCount is unreliable with some cursors/providers, so rely on rs.EOF (and rs.BOF) instead of relying on RecordCount. Also handle possible Nulls when assigning to form controls.

Example pattern (illustrative):

' use ADODB.Command with a parameter, then check EOF
cmd.CommandText = "SELECT name, designation FROM csefacultydetails WHERE UPPER(name) = UPPER(:pName)"
cmd.Parameters.Append cmd.CreateParameter("pName", adVarChar, adParamInput, 200, Trim(name1.Text))
Set rs = cmd.Execute

If rs.EOF Then
  MsgBox "RECORD NOT FOUND"
Else
  ' read fields here, checking IsNull and converting with CStr when needed
End If

Practical checklist and cautions: verify the correct provider/DSN for Oracle (OraOLEDB or the proper ODBC driver), escape single quotes or, better, use parameters to handle names like O'Connor, use UPPER(...) or a case-insensitive comparison for searches, and always close the Recordset before closing the Connection and set objects to Nothing. Using field names (rs!FieldName) is clearer than numeric indexes, but only after confirming the Recordset contains rows. Following these steps will remove BOF/EOF errors and make retrieval reliable.

All 15 Replies

debasisdas 580 Posting Genius

15 Years Ago

What is not working ?

Are you getting any error message ?

Jx_Man 987 Nearly a Senior Poster

15 Years Ago

your information is not clear..and please use code tags

AndreRet 526 Senior Poster

15 Years Ago

accse.Open "Provider=MSDASQL.1;Password=tiger;Persist Security Info=True;User ID=scott;Data Source=facultydetails"
rsfdcse.Open "select * from csefacultydetails where name= " & "'&name1.Text '", accse, adOpenDynamic, adLockOptimistic

is incorrect -

accse.Open "Provider=MSDASQL.1;Password=tiger;Persist Security Info=True;User ID=scott;Data Source=facultydetails" 'THIS LOOKS LIKE MS SQL AND NOT ORACLE< NOT SURE THOUGH.... Make sure you have the correct connection string....

'This for Oracle - CHECK YOUR CONN STRING, IT IS INCORRECT
Provider=OraOLEDB.Oracle;dbq=localhost:1521/XE;Database=myDataBase;User Id=myUsername;Password=myPassword;

rsfdcse.Open "select * from csefacultydetails where name= " & "'" & name1.Text & "'", accse, adOpenDynamic, adLockOptimistic

'Correct recordset call

AndreRet 526 Senior Poster

15 Years Ago

As per my sql statement, I have added the " ' " apostrophized characters.:)

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

karthiyayiniyp 7 Newbie Poster · Answer 1 · 2011-03-07T21:02:24+00:00

the error message is
"either BOF or EOF is true,or the current record has been deleted.
requested operation requires a current record.

accse.Open "Provider=MSDASQL.1;Password=tiger;Persist Security Info=True;User ID=scott;Data Source=facultydetails"

rsfdcse.Open "select * from csefacultydetails where name= " & "'&name1.Text '", accse, adOpenDynamic, adLockOptimistic

If Not rsfdcse.RecordCount = 0 Then
If rsfdcse.Fields(0) = Val(name1.Text) Then" it showing error in this line "

name1.Text = rsfdcse.Fields(0)
designation.Text = rsfdcse.Fields(1)
qualification.Text = rsfdcse.Fields(2)
department.Text = rsfdcse.Fields(3)
dateofjoining.Value = rsfdcse.Fields(4)
sex.Text = rsfdcse.Fields(5)
address.Text = rsfdcse.Fields(6)
contactno.Text = rsfdcse.Fields(7)
community.Text = rsfdcse.Fields(8)
emailid.Text = rsfdcse.Fields(9)
MsgBox "RECORD FOUND"
End If
accse.Close
rsfdcse.Close
Else
MsgBox "RECORD NOT FOUND"

End If

karthiyayiniyp 7 Newbie Poster · Answer 2 · 2011-03-07T21:19:58+00:00

now the errors are cleared but its not retrieving the strings from back end......

karthiyayiniyp 7 Newbie Poster · Answer 3 · 2011-03-07T22:01:19+00:00

karthiyayiniyp 7 Newbie Poster

15 Years Ago

sir please ..... clear my doubts

AndreRet 526 Senior Poster · Answer 4 · 2011-03-07T22:09:30+00:00

Rather change the following -

name1.Text = rsfdcse.Fields(0)
designation.Text = rsfdcse.Fields(1)
qualification.Text = rsfdcse.Fields(2)

to

name1.Text = rsfdcse!Firstfieldname
designation.Text = rsfdcse!Secondfieldname
qualification.Text = rsfdcse!Thirdfieldname

Change the fieldnames to what you have in your table, for instance "name1.Text = rsfdcse!name" etc....:)

That should solve all your problems here, please mark this as solved, found at the bottom of this page, thanks.:)

karthiyayiniyp 7 Newbie Poster · Answer 5 · 2011-03-08T21:49:18+00:00

even then it's not retrieving the data's sir .... but it not showing errors

karthiyayiniyp 7 Newbie Poster · Answer 6 · 2011-03-08T22:08:15+00:00

i solved the problem sir... by default the string from back-end will converted to integer .... so once again we need to convert the integer to string ... i attach my codings for reference.... but thanks for you reply posting sir... it's very helpful.. thanks a lot

Dim s As Integer

accse.Open "Provider=MSDASQL.1;Password=tiger;Persist Security Info=True;User ID=scott;Data Source=facultydetails"
rsfdcse.Open "select * from csefacultydetails where name= " & "'" & name1.Text & "'", accse, adOpenDynamic, adLockOptimistic

s = Val(rsfdcse!Name)

If Not rsfdcse.RecordCount = 0 Then
If CStr(s) = Val(name1.Text) Then
name1.Text = rsfdcse!Name
designation.Text = rsfdcse!designation
qualification.Text = rsfdcse!qualification
department.Text = rsfdcse!department
dateofjoining.Value = rsfdcse!dateofjoining
sex.Text = rsfdcse!sex
address.Text = rsfdcse!address
contactno.Text = rsfdcse!contactno
community.Text = rsfdcse!community
emailid.Text = rsfdcse!emailid
MsgBox "RECORD FOUND"
End If
accse.Close
Else
MsgBox "RECORD NOT FOUND"
End If

AndreRet 526 Senior Poster · Answer 7 · 2011-03-08T22:11:39+00:00

Show me your connection code. I think the problem lies there.

karthiyayiniyp 7 Newbie Poster · Answer 8 · 2011-03-09T20:55:52+00:00

it sloved the problem ... i posted the coding before itself

Dim s As Integer

accse.Open "Provider=MSDASQL.1;Password=tiger;Persist Security Info=True;User ID=scott;Data Source=facultydetails"
rsfdcse.Open "select * from csefacultydetails where name= " & "'" & name1.Text & "'", accse, adOpenDynamic, adLockOptimistic

s = Val(rsfdcse!Name)

If Not rsfdcse.RecordCount = 0 Then
If CStr(s) = Val(name1.Text) Then
name1.Text = rsfdcse!Name
designation.Text = rsfdcse!designation
qualification.Text = rsfdcse!qualification
department.Text = rsfdcse!department
dateofjoining.Value = rsfdcse!dateofjoining
sex.Text = rsfdcse!sex
address.Text = rsfdcse!address
contactno.Text = rsfdcse!contactno
community.Text = rsfdcse!community
emailid.Text = rsfdcse!emailid
MsgBox "RECORD FOUND"
End If
accse.Close
Else
MsgBox "RECORD NOT FOUND"
End If

AndreRet 526 Senior Poster · Answer 9 · 2011-03-09T22:36:04+00:00

Cool. It's good to see someone putting in the effort and solve thir own problem, well done!:)

karthiyayiniyp 7 Newbie Poster · Answer 10 · 2011-03-11T13:30:54+00:00

Cool. It's good to see someone putting in the effort and solve thir own problem, well done!:)

sir i have doubt in automatic sms ... i created the thread .... please help me to slove that problem too

AndreRet 526 Senior Poster · Answer 11 · 2011-03-11T13:40:22+00:00

AndreRet 526 Senior Poster

15 Years Ago

I saw it. Will help soon... :)

how to search a string

Dani AI

Recommended Answers Collapse Answers

All 15 Replies

Recommended Answers