VB6 recordset.open no value given for one or more parameter error

Dim adoconn开发者_如何学Go As New ADODB.Connection
Dim rs As New ADODB.Recordset

Private Sub Form_Load()
Dim str As String
Set adoconn = Nothing
Text2.Text = Form4.List2.Text
adoconn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Database1.mdb;Persist Security Info=False"
str = "select * from dept where DEPT =" & Form4.List2.Text
rs.Open str, adoconn, adOpenDynamic, adLockPessimistic
Text1.Text = rs(2)
End Sub

Whats wrong with rs.open statement??? It worked for me in one particular form perfectly. But it is giving a run time error in another form:No value given for one or more parameter...

The problem is almost certainly the value of Form4.List2.Text.

Say the value is the single letter a. The dynamic SQL would then be

SELECT * 
  FROM dept 
 WHERE DEPT = a;

Assuming there is no column in table dept named a then Access (Jet, ACE, whatever) would treat it as a parameter and, because you have supplied no value to go with that parameter, you get the error, "No value given for one or more required parameters."

As you are experiencing some of the problems of dynamic SQL (not to mention others such as SQL injection in the WHERE clause), consider creating a database PROCEDURE with strongly type parameters to be executed in VBA code using an ADO Command object e.g. assuming DEPT is INTEGER:

SQL code to be executed once (in ANSI-92 Query Mode):

CREATE PROCEDURE GetDept
(
 arg_dept INTEGER
)
AS
SELECT *
  FROM DEPT 
 WHERE dept = arg_dept;

VBA code:

Private Sub Form_Load()
  Text2.Text = Form4.List2.Text

  Dim cat As Object
  Set cat = CreateObject("ADOX.Catalog")
  cat.ActiveConnection = _
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Database1.mdb;Persist Security Info=False"

  Dim cmd
  Set cmd = cat.Procedures("GetDept").Command

  cmd.Parameters(0).Value = List2.Text  ' <--- test for type mismatch here '

  Set rs = cmd.Execute

  Text1.Text = rs(2)
End Sub

Other considerations are replacing SELECT * with a list of column names, referencing Recordset Fields by name (rather than by ordinal number) and handling a recordset with zero rows.

I have had this error and what it basically means is that there is a syntax error in the SQL you are passing to the Access database which means that Access does not recognise one or more parameters passed to it. Details are given at http://support.microsoft.com/kb/303134.

The most likely place for the error is in the additional string you are passing "Form4.List2.Text". The easiest way to see this is to run the query directly in Access and you should see an "Enter Parameter Value" dialog box appear which basically means access does not recognise one or more field/value names passed to it. Access seems to have a habit of altering the appearance of field names to catch out the unwary e.g. Company/Department as it appears in table view is actually CompanyorDepartment as queries are concerned.

Hope this helps.