Error encountered while searching for data from access table

Question

Shilpa Jain 0 Light Poster

16 Years Ago

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

visual-basic

4 Contributors
16 Replies
146 Views
1 Week Discussion Span
Latest Post 16 Years Ago Latest Post by Shilpa Jain

selvaganapathy 31 Posting Pro

16 Years Ago

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.

Jishnu 160 Posting Pro

16 Years Ago

Use code tags :D

selvaganapathy 31 Posting Pro

16 Years Ago

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.

QVeen72 104 Posting Shark

16 Years Ago

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 :

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)

also check all the field names...

Regards
Veena

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.

Shilpa Jain 0 Light Poster · Answer 1 · 2008-06-09T17:58:20+00:00

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.

Shilpa Jain 0 Light Poster · Answer 2 · 2008-06-10T13:33:30+00:00

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

Form7.EmpTxt.Text = rs.Fields(0).Value

How do I rectify this error??

Shilpa

QVeen72 104 Posting Shark · Answer 3 · 2008-06-10T14:17:28+00:00

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:

Set rs = cmd.Execute
If Not rs.EOF Then
    Form7.Show
    Form7.EmpTxt.Text = rs.Fields(0).Value 
    ' Write code to all other textboxes...
Else
    MsgBox "Record not Found..."
End If

Regards
Veena

Shilpa Jain 0 Light Poster · Answer 4 · 2008-06-10T15:57:29+00:00

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

Shilpa Jain 0 Light Poster · Answer 5 · 2008-06-10T16:55:40+00:00

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

QVeen72 104 Posting Shark · Answer 6 · 2008-06-10T17:30:08+00:00

Hi,

As it is, there is no need to use a command object to open a record set, you can directly open record set...

Check this code:

Dim sSQL As String
sSQL = "Select * from Personal_Info " _
& " Where Emp_No = " & Val(sEmp)
RS.Open sSQL, Conn,adopenStatic,adLockReadOnly

Also Note: Emptxt.Text contains, Name of the employee, and you are using:
sEmp = Emptxt.Text......?
You are searching for ID, but using Name in SQL String....??

REgards
Veena

Shilpa Jain 0 Light Poster · Answer 7 · 2008-06-11T12:06:53+00:00

Hi,

Thanks Veena. That problem is resolved. sEmp is a text string which searches for Employee number and not Name of the emplyoee. But, it is displaying Name in the Emp No field on the form!!! How?? :confused:

I am getting another error now. I have to take data from another table to be populated on the same form. I am opening another recordset for the same. But, it gives me " Run-time error '3001' : Arguments are of wrong type" .

Below is the code that I am using:

Private Sub SearchCmd_Click()

'imports System.Convert
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
Dim strsql, strsql1 As String

Set rs = New ADODB.Recordset
Set rs1 = New ADODB.Recordset

sEmp = EmpTxt.Text

strsql = " SELECT Personal_Info.Name, Personal_Info.Location, Personal_Info.NID," _
& "Personal_Info.Passport_No, Personal_Info.PIN, Personal_Info.[D-O-B]," _
& "Personal_Info.Aet_Join_Date, Personal_Info.Infy_Mail_Id, Personal_Info.Ph_Res," _
& "Personal_Info.Ph_Off, Personal_Info.Ph_Cell, Personal_Info.Address," _
& "Personal_Info.Status FROM Personal_Info " _
& "WHERE (((Personal_Info.Emp_No)= '" & sEmp & " '))"


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


rs.Open strsql, conn, adOpenStatic, adLockReadOnly

If Me.PrjOpt = True Then

'Query to fetch details from Personal_Info table
'cmd.CommandText = "SELECT Personal_Info.Name, Personal_Info.Location, Personal_Info.NID, Personal_Info.Passport_No, Personal_Info.PIN, Personal_Info.[D-O-B], Personal_Info.Aet_Join_Date, Personal_Info.Infy_Mail_Id, Personal_Info.Ph_Res, Personal_Info.Ph_Off, Personal_Info.Ph_Cell, Personal_Info.Address, Personal_Info.Status FROM Personal_Info WHERE (((Personal_Info.Emp_No)= '" & sEmp & " '))"
'cmd.CommandType = adCmdText

MsgBox (rs.RecordCount)

'Display values from database in form

'Load form to display details
Form7.Show
Form7.ForeColor = RGB(200, 190, 100)


Form7.EmpTxt.Text = rs.Fields(0).Value
Form7.NameTxt.Text = rs.Fields("Name").Value
Form7.LocTxt.Text = rs.Fields("Location").Value
Form7.NidTxt.Text = rs.Fields("NID").Value
Form7.PassportTxt.Text = rs.Fields("Passport_No").Value
Form7.PinTxt.Text = rs.Fields("PIN").Value & ""
Form7.DOBTxt.Text = rs.Fields("D-O-B").Value
Form7.AetJDTxt.Text = rs.Fields("Aet_Join_Date").Value
Form7.IDTxt.Text = rs.Fields("Infy_Mail_Id").Value
Form7.ResTxt.Text = rs.Fields("Ph_Res").Value & ""
Form7.OffTxt.Text = rs.Fields("Ph_Off").Value & ""
Form7.MobTxt.Text = rs.Fields("Ph_Cell").Value & ""
Form7.AddTxt.Text = rs.Fields("Address").Value
Form7.StatusTxt.Text = rs.Fields("Status").Value


rs.Close
conn.Close

'Query to fetch details from Project_Info table
strsql1 = " SELECT Project_Info.Unit, Project_Info.Proj_Join_Date," _
& " Project_Info.Application, Project_Info.Supp_Level" _
& " From Project_Info" _
& " WHERE (((Project_Info.Emp_No)='" & sEmp & "'))"

conn.Open sConnString

rs1.Open strql1, conn, adOpenStatic, adLockReadOnly

MsgBox (rs1.RecordCount)

'Display values from database in form
Form7.DepTxt.Text = rs.Fields("Unit").Value
Form7.PrjJDTxt.Text = rs.Fields("Proj_Join_Date").Value
Form7.AppTxt.Text = rs.Fields("Application").Value
Form7.LvlTxt.Text = rs.Fields("Supp_Level").Value

End If
rs1.Close

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.StatusTxt.Locked = True
'Form7.Text16.Locked = True
'Form7.Text17.Locked = True

Set rs = Nothing
Set rs1 = Nothing

conn.Close
Set conn = Nothing

End Sub

One more thing. Error is occuring at rs1.Open strql1, conn, adOpenStatic, adLockReadOnly statement

QVeen72 104 Posting Shark · Answer 8 · 2008-06-11T12:28:25+00:00

Hi,

you are opening strql1
but the variable containg the Query string is strsql1

FIRST LINE IN YOUR CODE MODULE SHOULD BE "OPTION EXPLICIT"

This option enforces you to declare Variable before using them..
with above option, you can get rid of such silly errors...

Regards
Veena

Shilpa Jain 0 Light Poster · Answer 9 · 2008-06-12T10:33:28+00:00

Thanks Veena. The problem is resolved now :)

One more thing to ask. I have a reset command button on the form. It clears the entered data but when I enter the Employee number again it does not fetch any data. Recordcount is shown as 0. If I use backspace and change the employee number data is being fetched. Any suggestion what could be the reason for this??

Regards,
Shilpa

QVeen72 104 Posting Shark · Answer 10 · 2008-06-12T11:46:58+00:00

Hi,

In what event you have written the code to Fetch the details..?
You need to Write the code (To fetch data) in txtEmpNo_LostFocus event...

Regards
Veena

Shilpa Jain 0 Light Poster · Answer 11 · 2008-06-12T16:54:06+00:00

Hi ,

I tried fetching data using EmpTxt_LostFocus event as suggested by Veena. But , still it is not fetching data for a new number after clicking reset button. Here is the code:

Private Sub SearchCmd_Click()

If IsNull(sEmp) Or sEmp = "" Then
     MsgBox "Please enter NID", vbOKOnly + vbExclamation, "Empty Field"
Else
     Call EmpTxt_LostFocus
End If

End Sub

Private Sub ResetCmd_Click()
         EmpTxt.Text = " "
End Sub

Option Explicit
Dim conn As New ADODB.Connection
Dim sConnString  As String
Dim sEmp As String
Dim strsql As String
Dim rs as Recordset


Private Sub EmpTxt_LostFocus()

Set rs = New ADODB.Recordset
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
Set rs3 = New ADODB.Recordset

sEmp = EmpTxt.Text

'Query to fetch details from Personal_Info table
strsql = " SELECT Personal_Info.Name, Personal_Info.Location, Personal_Info.NID," _
& "Personal_Info.Passport_No, Personal_Info.PIN, Personal_Info.[D-O-B]," _
& "Personal_Info.Aet_Join_Date, Personal_Info.Infy_Mail_Id, Personal_Info.Ph_Res," _
& "Personal_Info.Ph_Off, Personal_Info.Ph_Cell, Personal_Info.Address," _
& "Personal_Info.Status FROM Personal_Info " _
& "WHERE (((Personal_Info.Emp_No)= '" & sEmp & " '))"


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


rs.Open strsql, conn, adOpenStatic, adLockReadOnly

If Me.PrjOpt = True Then

MsgBox (rs.RecordCount)

    If Not rs.BOF Then
    'Load form to display details
        Form7.Show
        Form7.ForeColor = RGB(200, 0, 0)
        
        'Display values from database in form
        Form7.EmpTxt.Text = CLng(sEmp)
        Form7.NameTxt.Text = rs.Fields("Name").Value
        Form7.LocTxt.Text = rs.Fields("Location").Value
        Form7.NidTxt.Text = rs.Fields("NID").Value & ""
        Form7.PassportTxt.Text = rs.Fields("Passport_No").Value
        Form7.PinTxt.Text = rs.Fields("PIN").Value & ""
        Form7.DOBTxt.Text = rs.Fields("D-O-B").Value & ""
        Form7.AetJDTxt.Text = rs.Fields("Aet_Join_Date").Value
        Form7.IDTxt.Text = rs.Fields("Infy_Mail_Id").Value & ""
        Form7.ResTxt.Text = rs.Fields("Ph_Res").Value & ""
        Form7.OffTxt.Text = rs.Fields("Ph_Off").Value & ""
        Form7.MobTxt.Text = rs.Fields("Ph_Cell").Value & ""
        Form7.AddTxt.Text = rs.Fields("Address").Value & ""
        Form7.StatusTxt.Text = rs.Fields("Status").Value & ""
    Else
        
        MsgBox "Record not Found for Personal Info!!"
        
    End If

rs.Close
conn.Close

Set conn = Nothing

End Sub

Shilpa Jain 0 Light Poster · Answer 12 · 2008-06-17T12:09:26+00:00

Hi,

Thanks to all for their suggestions. This problem is resolved now.

Regards,
Shilpa