Hi All,

I am novice in VB 6.0. I have to search data from access table using Employee no field which is unique and populate it on the form. But, I am encountering an error whenever I try to run the source code.

I am getting runtime error as "No value given for one or more required parameters" at Set rs = cmd.Execute statement. When I ran the "?cmd.CommandText" in immediate window , I am getting the sql query with Emp_No entered which is correct but when I am trying to run the sql query alone its giving "Compile error: Expected expression". I am unable to understand where the expression is missing in sql query. Please help!!!



Below is my code

Private Sub SearchCmd_Click()


Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim sConnString As String
Dim sEmp As String

Set rs = New ADODB.Recordset

sEmp = EmpTxt.Text

'Connecting to the Database
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Tool\P_and_E\P_and_E.mdb"

conn.Open sConnString
Set cmd.ActiveConnection = conn

If Form2.PrjOpt = True Then

'Query to fetch details from Personal_Info table
cmd.CommandText = "Select Name,Location,NID,Passport_No,Pin,D-O-B,Aet_Join_Date,Infy_Mail_Id,Ph_Res,Ph_Off,Ph_Cell,Address,Status from Personal_Info where Emp_No = " & CLng(sEmp)
cmd.CommandType = adCmdText

Set rs = cmd.Execute

'Load form to display details
Form7.Show

'rs.MoveFirst
'Display values from database in form
Form7.EmpTxt.Text = rs.Fields(0).Value
Form7.NameTxt.Text = rs.Fields(1).Value
Form7.LocTxt.Text = rs.Fields(2).Value
Form7.NidTxt.Text = rs.Fields(3).Value
Form7.PassportTxt.Text = rs.Fields(4).Value
Form7.PinTxt.Text = rs.Fields(5).Value
Form7.DOBTxt.Text = rs.Fields(6).Value
Form7.AetJDTxt.Text = rs.Fields(7).Value
Form7.IDTxt.Text = rs.Fields(8).Value
Form7.ResTxt.Text = rs.Fields(9).Value
Form7.OffTxt.Text = rs.Fields(10).Value
Form7.MobTxt.Text = rs.Fields(11).Value
Form7.AddTxt.Text = rs.Fields(12).Value
Form7.StatusTxt.Text = rs.Fields(13).Value

'Query to fetch details from Project_Info table
cmd.CommandText = "Select * from Project_Info where Emp_No = " & CLng(sEmp)
cmd.CommandType = adCmdText
Set rs = cmd.Execute

'Display values from database in form
Form7.DepTxt.Text = rs.Fields(4).Value
Form7.PrjJDTxt.Text = rs.Fields(5).Value
Form7.AppTxt.Text = rs.Fields(7).Value
Form7.LvlTxt.Text = rs.Fields(8).Value


Form7.EmpTxt.Locked = True
Form7.NameTxt.Locked = True
Form7.PassportTxt.Locked = True
Form7.IDTxt.Locked = True
Form7.ResTxt.Locked = True
Form7.OffTxt.Locked = True
Form7.MobTxt.Locked = True
Form7.AddTxt.Locked = True
Form7.PinTxt.Locked = True
Form7.NidTxt.Locked = True
Form7.AppTxt.Locked = True
Form7.LocTxt.Locked = True
Form7.DepTxt.Locked = True
Form7.LvlTxt.Locked = True
'Form7.Text15.Locked = True
'Form7.Text16.Locked = True
'Form7.Text17.Locked = True

End If


Set rs = Nothing
Set cmd = Nothing

'Close connection to database
conn.Close
Set conn = Nothing

End Sub

Hi,
"No value given for one or more required parameters" This may occurred due to misspelling of the field name. So check your field name which you given in the database and field name in the Query.

Thanks for replying. I ahve checked all the field names in table and in query. They all are same. Any other suggestion why I am getting this error.

Use code tags

Hi,
This may be query problem. Try to execute your query in MS Access with sample Emp_No, If the query is succeed then Copy the query into vb.

Hi,

I guess D-O-B is the culprit..
Field names containing special characters and spaces need to be enclosed in square bracket..
change your sql statement like this :


also check all the field names...

Regards
Veena

Hi All,

Thanks for your replies. Sql query has started working now. But, now I am getting another error error 3021:Either EOF or BOF is true or the current record has been deleted .

I am getting this error at How do I rectify this error??

Shilpa

Hi,

After Opening the recordset, Check if any row has been returned.. this can be done by checking the EOF of the the recset.. Change your code like this:


Regards
Veena

Regards
Veena

Hi,

I tried the code as suggested by Veena. Msgbox saying "Record not found" is being displayed which means that debugger is not entering into the recordset. I am not finding where I am making a mistake in writing the code. Why recordset in not being read!!! Please help!!!!!

Regards,
Shilpa

Hi,

I have tried accessing the database after getting the value by executing ?cmd.CommandText . Data is not being returned there though if I try directly in access table, it is giving me the data.

Please suggest where I am going wrong in this?

Regards,
Shilpa