vb6 search button problem!

[Adrian86]

Hi guys!...i got a problem with my search button in vb6! i have tried so many time fixing and finding whats is the problem but i still cant manage it to figure out whats the problem!....im doing a car rental system!...im searching for the plate no/firstname or lastname!....the error i alwayz get in the coding yellow line is "rs.Open query, conn" when i trying to find in the search button....anyway hears the code for the search button....hope this helpz and hope you can help me!


Private Sub Command3_Click()
Dim pr

Call connectme
conn.Open

pr = InputBox("Please Enter Any Key to Search!" & Chr(10) & Chr(13) & "PLATE NO./FIRSTNAME/LASTNAME", "Searching...")
If pr = "" Then

ElseIf pr = "All" Or pr = "all" Then
Call myquery
Else
query = "SELECT c.Rent_ID,a.firstname,a.lastname,b.Plate_No,b.Brand,c.Date_Rented,c.No_of_days FROM customertable a, cartable b, renttable c WHERE a.Cus_Id = c.Cus_Id AND b.Car_Id = c.Car_Id AND c.Status='1' AND (a.firstname LIKE '" & pr & "%' or b.lastname LIKE '" & pr & "%') AND c.Status='1'"
rs.Open query, conn
Set DataGrid1.DataSource = rs
Call datgrid
End If

End Sub

[si_the_geek]

Welcome to VBForums

You've told us where the error occurs, but not what the error is.... without that info it is very hard to work out what the problem might be.

[Adrian86]

well the error message i got was :


"Run-time error ';2137217904(80040e10)':
No value given for one or more required parameters




Thats about it!..hope this can help!...anyway is they any easy tutorial to create crystal report on vb6?

[GaryMazzone]

AND c.Status='1'"
Is status numeric? Get rid of the single qoute in front of the 1
If text add a single qoute after the 1

And try again

[brucevde]

That cryptic error message usually means a spelling mistake or specifying a field that doesn't exist in a table.

In the select list you have a.lastname while in the where clause you have b.lastname. Is that correct?

[BrianS]

And get rid of the duplicate one.

AND c.Status='1'"
Is status numeric? Get rid of the single qoute in front of the 1
If text add a single qoute after the 1

And try again

[Adrian86]

Hear the abit more coding guys!



Private Sub Command1_Click()
rent_opt = 1
frmnewrent.Show (1)
End Sub

Private Sub Command2_Click()
Dim did

did = DataGrid1.Columns(0).Text
If Not did = "" Then
rentid = did
rent_opt = 2
frmnewrent.cmd_save.Caption = "Update"
frmnewrent.Show (1)
End If

End Sub

Private Sub Command3_Click()
Dim pr

Call connectme
conn.Open

pr = InputBox("Please Enter Any Key to Search!" & Chr(10) & Chr(13) & "PLATE NO./CUSTOMER ", "Searching...")
If pr = "" Then

ElseIf pr = "All" Or pr = "all" Then
Call myquery
Else
query = "SELECT c.Rent_ID,a.lastname,a.firstname,b.Plate_No,b.Brand,c.Date_Rented,c.No_of_days FROM customertable a, cartable b, renttable c WHERE a.Cus_Id = c.Cus_Id AND b.Car_Id = c.Car_Id AND c.Status='1' AND (a.lastname LIKE '" & pr & "%' or b.firstname LIKE '" & pr & "%') AND c.Status='1'"
rs.Open query, conn
Set DataGrid1.DataSource = rs
Call datgrid
End If

End Sub

Private Sub Command4_Click()
Dim confirm
Dim did

did = DataGrid1.Columns(0).Text
lastname = DataGrid1.Columns(1).Text
firstname = DataGrid1.Columns(2).Text
plateno = DataGrid1.Columns(3).Text
carname = DataGrid1.Columns(4).Text
daterent = DataGrid1.Columns(5).Text
dateret = Format(Now, "mm/dd/yyyy")
If Not did = "" Then
confirm = MsgBox("This will RETURN the Car Now?..proceed anyway!", vbOKCancel + vbExclamation, "Warning!")
If confirm = vbOK Then
Call connectme
conn.Open
conn.Execute "Update cartable set Status='1' WHERE Plate_no='" & plateno & "'"
conn.Execute "Update renttable set Status='2' WHERE Rent_Id=" & did
conn.Execute "Insert into returntable(Rent_Id,lastname,firstname,Plate_No,Carname,Date_Rented,Date_Returned,Process_by) VALUES ('" & did & "','" & lastname & "','" & firstname & "','" & plateno & "','" & carname & "','" & daterent & "','" & dateret & "','" & myuser & "')"
MsgBox "The Return Transaction was successfully saved!", vbInformation
conn.Close
Call myquery
End If
Else
MsgBox "There are no records to delete!", vbCritical
End If
End Sub

Private Sub Command5_Click()
Unload Me
End Sub
Private Sub Command6_Click()
frmlistcar.Show
End Sub
Private Sub Form_Load()

Call myquery

''************ USER LEVEL

If userlevel = 3 Then
Command1.Enabled = False
Command2.Enabled = False
Command4.Enabled = False
Else
Command1.Enabled = True
Command2.Enabled = True
Command4.Enabled = True
End If

End Sub

Public Sub myquery()

Call connectme

conn.Open

query = "SELECT c.Rent_ID,a.lastname,a.firstname,b.Plate_No,b.Brand,c.Date_Rented,c.No_of_days FROM customertable a, cartable b, renttable c WHERE a.Cus_Id = c.Cus_Id AND b.Car_Id = c.Car_Id AND c.Status='1' ORDER BY c.Date_Rented DESC"
rs.Open query, conn
Set DataGrid1.DataSource = rs

If Not rs.EOF Then
rs.MoveLast
reccnt.Caption = rs.RecordCount
Else
reccnt.Caption = 0
End If
Call datgrid
DataGrid1.MarqueeStyle = 3


End Sub
Public Sub datgrid()
DataGrid1.Font.Size = 11
DataGrid1.Font.Name = "Arial"
DataGrid1.HeadFont.Size = 11
DataGrid1.HeadFont.Bold = True
DataGrid1.HeadFont.Name = "Arial"
DataGrid1.Columns(0).Caption = "Rend ID"
DataGrid1.Columns(0).Width = 1000
DataGrid1.Columns(1).Caption = "Lastname"
DataGrid1.Columns(1).Width = 1500
DataGrid1.Columns(2).Caption = "Firstname"
DataGrid1.Columns(2).Width = 1500
DataGrid1.Columns(3).Caption = "Plate NO."
DataGrid1.Columns(3).Width = 1200
DataGrid1.Columns(4).Caption = "Car Name"
DataGrid1.Columns(4).Width = 2000
DataGrid1.Columns(5).Caption = "Date Rented"
DataGrid1.Columns(5).Width = 1200
DataGrid1.Columns(6).Caption = "No.Days"
DataGrid1.Columns(6).Width = 1200


End Sub




hope this can make it more clearer!

[BrianS]

No point in posting more code if you don't answer the previous questions.

First thing first, validate your connection and querys.

[Adrian86]

well i did what you all ask me to do but i still getting the same problem, thats why i post the whole code!

[si_the_geek]

You didn't mention that before, so we didn't know.

As to doing all that you have been asked, unfortunately that doesn't seem to be the case... in post 6 you were told that you should remove the duplicate AND c.Status='1', and it is still there.

In post 5 you were asked to check if the field lastname is in table b , and you seem to have changed b.lastname to b.firstname - whereas it should presumably have been changed to a.lastname