guys,,i use vb6 and access 2007,,i hav a problem with my project,,,,my project is about college admin s/m,,,on runtime if i give certain id's for input it hangs and just crashes,,,i dont know why this happens,,,,,i hav applied the same code in many other places of my project but they dont seem to hang though i've faced the hanging trouble previously but which doesnt happen now,,,,,pls i need ur help desperately,,,,
And how can we help you without seeing the code???
dim s as string Private Sub Form_Load() s = InputBox("Enter the student's id") Set db = OpenDatabase("C:\Program Files\Microsoft Visual Studio\VB98\my new prj\prj1.mdb") Set rt = db.OpenRecordset("studper", dbOpenDynaset) rt.MoveLast lab: If (rt.Fields(12) <> s) Then rt.MovePrevious GoTo lab End If Text1.Text = rt.Fields(0) Text2.Text = rt.Fields(1) Text3.Text = rt.Fields(2) Text4.Text = rt.Fields(3) Text5.Text = rt.Fields(4) Text6.Text = rt.Fields(5) Text7.Text = rt.Fields(6) Text8.Text = rt.Fields(7) Text9.Text = rt.Fields(8) Text10.Text = rt.Fields(9) Text11.Text = rt.Fields(11) Label9.Caption = rt.Fields(12) If Option1.Caption = rt.Fields(10) Then Option1.Value = True End If If Option2.Caption = rt.Fields(10) Then Option2.Value = True End If If Option3.Caption = rt.Fields(10) Then Option3.Value = True End If If Option4.Caption = rt.Fields(10) Then Option4.Value = True End If If Option5.Caption = rt.Fields(10) Then Option5.Value = True End If If Option6.Caption = rt.Fields(13) Then Option6.Value = True End If If Option7.Caption = rt.Fields(13) Then Option7.Value = True End If End Sub
I've used the same kind of code for searching an id in my project,,,in some places it works fine,,but at some places it hangs,,,after entering the id in the box,,,pls sort my prob out,,
Okay,... Your query should be more like this...
strSQL = "SELECT * FROM tablename WHERE fieldname = " & inputednumbervalue
or if string
strSQL = "SELECT * FROM tablename WHERE fieldname = '" & inputedstringvalue & "'"
and then to check to see if any records were returned you could check it like this...
If Rs.RecordCount <> 0 AND Rs.BOF = False AND Rs.EOF = False Then 'have records Else 'no records End If
NOTE: I check for all three conditions as you can see...
lab: If (rt.Fields(12) <> s) Then rt.MovePrevious GoTo lab
is not good programming practice. Not only because of using a GoTo to loop, but because you have no check for BOF, which means if user inputted a value that did not exist in the table, the recordset would error out as you tried to move prior to the first record and read a value. Now if you just went Huh? Well think of it this way, if you were to open a text file and try to read before the first line in that text file, what would you be reading? Nothing in that text file that is for sure.
While very easy to code, this does not lend itself to easy readability or more important, program maintence. While most programmers would know that you are referencing the 13th field of the table, the question is, what is that fields name? You should reference fields like so...
Text1.Text = Rs.Fields("FieldName").Value
Also, by using field numbers as you have, what happens if the database changes structure? Two fields switched with each other, or a field added say at postion 4? Then you would have to go back through every place in your program where that table is referenced and reorder your numbers from 4 on up.
Hope this helps...
I'd try setting a breakpoint and stepping through the code to see where it is hanging. I'm sure you would be able to identify the line which causes the hanging quite easily that way.
If it only hangs after it's been ran a few times, try cleaning up the code (i.e. define the objects correctly and destroy them when you've finished with them).
At this point, if you're still having problems, you should have an idea at what point in your code the problem is occurring and we should be able to help further.
Edit: after spending 5mins trying to type my post with cold hands, it appears someone has beaten me to it :)
thanx for that,,,i've learnt that the problem is with the searching part only...cos the progrm hangs only after getting the id through the input box,,,if there is any other way u can suggest me to search for id effeciently pls suggest me,,,thanx,,i'l try ur technique and tell my response,,,waitin for ur response,,
i need to add that if i've not completed my coding in it about wat the program should do if it has not found the users record,,,,im just planning to add a message box in the else part of the searching place,,which display "no record found",,,by the way pls tell me how i should use the sql querry tat you have posted here,,,i'm totally new to vb...
somestringvariable = inputbox(... strSQL = "SELECT * FROM tablename WHERE fieldname = '" & somestringvariable & "'" 'or no single quotes if field is numeric Set Rs = db.OpenRecordset(strSQL, dbOpenDynaset)