• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2163
  • Last Modified:

run time error '-2147904(80040e10)': no value given for one or more parameters

Hi im trying to connect a database to my form althought im recieving the following error:

run time error '-2147904(80040e10)':
no value given for one or more parameters

when debugging it highlights the following:

mrsSalesPerson.Open pstSalesPersonSQL, gcnMIRO, adOpenStatic, adLockOptimistic, adCmdText

Can someone give me a hand in resolving the error by glancing at the code, and identify exactly what im missing? Thanks :)


Here is the form frmSalesPerson
----------------------------------------------
Option Explicit
'** Declare module-level variables
Private mrsSalesPerson As New ADODB.Recordset
Private mstMode As String
Private mcoControl As Control
Private mstSalesPersonNo As String
Private pstSalesPersonSQL As Control

Private Sub Form_Load()

'Declare the recordset. Note that pstStudents is only used in this procedure,
'whereas mrsStudents is going to be used by a number of procedures.
Dim pstSalesPersonSQL As String
frmSalesPerson.WindowState = 2
pstSalesPersonSQL = "SELECT * FROM tblSalesPerson WHERE SalesPersonStatus = True ORDER BY SalesPersonNo"
mrsSalesPerson.Open pstSalesPersonSQL, gcnMIRO, adOpenStatic, adLockOptimistic, adCmdText

'Display the first record, if there is one. Show nothing if there isn't one.
If mrsSalesPerson.RecordCount > 0 Then
    Call DisplayData
Else
    'Call ClearData 'The "'" will be removed later.
   
End If

fraSalesPersonSearch.Visible = False

End Sub

Private Sub Form_Activate()
If gstFromWhere = "frmStudentSearch" Then
    txtSalesPersonNo.Text = gstSalesPersonNo
   
   
   With mrsSalesPerson
    .MoveFirst
    .Find "SalesPersonNo = '" & txtSalesPersonNo.Text & "'"
    Call DisplayData
    End With
    gstFromWhere = ""
    txtSalesPersonSearch.Visible = False
   
    End If
   
'Set the focus to the exit button - this can't be done on Form_Load.

Call SetInactiveControls
cmdMainMenu.SetFocus
End Sub

Private Sub cmdAdd_Click()

mstMode = "Add"
lblHeading.Caption = "Add Sales Person"
Call ClearData
Call HideViewButtons
mstSalesPersonNo = txtSalesPersonNo.Text
Call SetActiveControls
cmdSave.Visible = True
cmdCancel.Visible = True

End Sub

Private Sub cmdEdit_Click()

mstMode = "Edit"
lblHeading.Caption = "Edit Sales Person"
Call SetActiveControls
Call HideViewButtons
mstSalesPersonNo = txtSalesPersonNo.Text

End Sub

Private Sub cmdDelete_Click()

Dim pstDeleteMsg As String
Dim pinResponse As Integer
Dim pstDeleteSalesPersonSQL As String
Dim prsDeleteSalesPerson As New ADODB.Recordset
pstDeleteMsg = "Are you sure that you want to remove this Sales Person?"
pinResponse = MsgBox(pstDeleteMsg, vbCritical + vbYesNo, "Delete Sales Person?")
If pinResponse = vbYes Then

With mrsSalesPerson

    !SalesPersonStatus = False
   
    .Update
   
    .Requery
   
End With
Call DisplayData
End If

End Sub

Private Sub cmdFirst_Click()
mrsSalesPerson.MoveFirst
Call DisplayData
End Sub

Private Sub cmdLast_Click()
mrsSalesPerson.MoveLast
Call DisplayData
End Sub

Private Sub cmdClose_Click()
frmMainMenu.Show 1
End Sub

Private Sub cmdPrevious_Click()
'Move the pointer to the previous record if not already at the Begining of the File.
mrsSalesPerson.MovePrevious
If mrsSalesPerson.BOF Then
    MsgBox "You are at the Start of the  Sales Persons records.", vbCritical, "Begining of table"
    mrsSalesPerson.MoveFirst
Else
    Call DisplayData
End If
End Sub

Private Sub cmdSearch_Click()
'Makes the  Sales Person search frame visible on the screen
Call HideViewButtons
fraSalesPersonSearch.Visible = True
cmdSave.Visible = False
cmdCancel.Visible = False
Call ClearData

End Sub

Private Sub cmdMainMenu_Click()
'Close frmSalesPerson before returning to frmMainMenu, which has remained open.
Unload frmSalesPerson
Set frmSalesPerson = Nothing
End Sub

Sub DisplayData()

With mrsSalesPerson
txtSalesPersonNo.Text = "" & !SalesPersonNo
txtSalesPersonName.Text = "" & !SalesPersonName
Call SetInactiveControls
End With

End Sub

Sub SetInactiveControls()

For Each mcoControl In Me.Controls
    If TypeOf mcoControl Is TextBox Then
        mcoControl.BackColor = &H8000000D
        mcoControl.ForeColor = vbWhite
        mcoControl.Locked = True
    End If
Next mcoControl
txtSalesPersonSearch.Locked = False
txtSalesPersonSearch.BackColor = vbWhite
txtSalesPersonSearch.ForeColor = &H8000000D

End Sub

Sub ShowViewButtons()

fraNavigation.Visible = True
fraFunctions.Visible = True
cmdMainMenu.SetFocus

End Sub

Sub HideViewButtons()

fraNavigation.Visible = False
fraFunctions.Visible = False
fraStudentSearch.Visible = False

End Sub

Sub ClearData()
For Each mcoControl In frmSalesPerson.Controls
  If TypeOf mcoControl Is TextBox Then
  mcoControl.Text = ""
  End If
Next
End Sub

Sub SetActiveControls()

For Each mcoControl In Me.Controls
    If TypeOf mcoControl Is TextBox Then
        If mstMode = "Add" Then
            mcoControl.Text = ""
        End If
        mcoControl.Locked = False
        mcoControl.BackColor = vbWhite
        mcoControl.ForeColor = vbBlack
    End If
Next mcoControl

txtSalesPersonNo.SetFocus

End Sub
-----------------------------------------------

Here is the Module Form i got:

------------------------------------------------

Option Explicit
'**Declare a New ADODB connection object variable
Public gcnMIRO As New ADODB.Connection

Sub Main()
'**Set the connection object to the database once only in a project
If Left(App.Path, 2) <> "\\" Then
    ChDrive App.Path
End If
ChDir App.Path
'**Open the connection to the database
gcnMIRO.Open "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = MIRO.mdb"
frmSalesPerson.Show
End Sub

Sub CloseConnection()
gcnMIRO.Close
Set gcnMIRO = Nothing
End Sub
0
igotnosmoke
Asked:
igotnosmoke
2 Solutions
 
sudhakar_koundinyaCommented:
One common reason for this error is you have misspelled column names in your sql string (column names in your sql that don't match the column names in your database).

Or, some column names like password may be "reserved words" in your database. You can normally enclose the column names in [ ] to get around this problem.

You can add

response.write strSql
response.end

just before the execute line, and review the actual sql string in your browser. Don't forget to comment out or remove these debug statements when you're done with them.
0
 
Ryan ChongCommented:
sudhakar_koundinya is right

just before the line of :

mrsSalesPerson.Open pstSalesPersonSQL, gcnMIRO, adOpenStatic, adLockOptimistic, adCmdText

try put:

...
debug.print pstSalesPersonSQL
exit sub

mrsSalesPerson.Open pstSalesPersonSQL, gcnMIRO, adOpenStatic, adLockOptimistic, adCmdText
...

then try copy that generated statement to your favourite query builder to execute, see if you got any errors there or not.

regards
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now