Click here to Skip to main content
15,898,373 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi All,

I have an application i am writing that adds system data to a DB and the I want to run a query from the code and pass the data to a gridview.
The first part runs fine in passing the system data the the db but when i try to do the select query on the DB it throws the exception "No value given for one or more required parameters" when trying to run the SelectQuery.
What am I missing?
Please see my code below:
VB
Public Class Form1

    Dim ProjectRef As String
    Dim AppNameSelect As String
    Dim AccessDateFrom As Date
    Dim AccessDateTo As Date
    Dim Username As String
    Dim ComputerName As String
    Dim AccessTime As DateTime
    Dim AppName As String

    'Define database connection string, update and select query
    Dim ConnString As String = _
        "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = \\database\TESTONLY.accdb; "
   
    Dim DbUpdate As String = _
   "INSERT INTO [ApplicationLogging] (AccessDate, UserName, ComputerName,  AppName  ) Values(?,?,?,?)"

    Dim DbSelect As String = _
          "Select * from [ApplicationLogging] where AppName = AppTest"


    Private Sub ReportGen_Click(sender As System.Object, e As System.EventArgs) Handles ReportGen.Click

        ProjectRef = ProjectRefInput.Text
        AppNameSelect = ApplicationSecection.Text
        AccessDateFrom = DateFrom.Text
        AccessDateTo = DateTo.Text


        'Collect the data from the system
        Username = System.Security.Principal.WindowsIdentity.GetCurrent.Name
        ComputerName = Environment.MachineName
        AccessTime = Date.Today
        AppName = My.Application.Info.AssemblyName
        
        Try

            Using cnn As New OleDbConnection(ConnString)
                cnn.Open()

                Using cmd As New OleDbCommand(DbUpdate, cnn)
                    cmd.Parameters.AddWithValue("AccessDate", AccessTime)
                    cmd.Parameters.AddWithValue("UserName", Username)
                    cmd.Parameters.AddWithValue("ComputerName", ComputerName)
                    cmd.Parameters.AddWithValue("AppName", AppName)
                    cmd.ExecuteNonQuery()
                    Using SelectQuery As New OleDbCommand(DbSelect, cnn)
                        SelectQuery.ExecuteReader()
                        Dim ResultsTable As New OleDb.OleDbDataAdapter(SelectQuery)
                        Dim DataTable As New DataTable("INTOOLS_Results")
                        ResultsTable.Fill(DataTable)
                        DataGridView1.DataSource = DataTable


                    End Using
                   
                End Using
                cnn.Close()
                cnn.Dispose()

            End Using

        Catch ex As Exception
            'Unable to find or update the database
            Err.Number = 2
            Call ErrorCodeHandle()

        End Try

    End Sub

    Private Sub ErrorCodeHandle()
        Select Case Err.Number 'Evaluate the error number.
            Case 1 'Incorrect domain
                MsgBox("You are not Authorised to use this application...........")
                MsgBox("Please contact Support@myapp.com for assistance")
                MsgBox("This application will now terminate.")
                Call endgame()

            Case (2) 'Unable to locate database file
                MsgBox("Unable to locate a required file (Error 6565)")
                MsgBox("Please contact Support@myapp.com for assistance")
                MsgBox("This application will now terminate.")
                Call endgame()

            Case (3) 'Creation of log file error handler - not currently used
                MsgBox("There was a problem writing to the log file. please try again and if the error persists please contact Support@myapp.com for assistance.")

        End Select

    End Sub

    Private Sub endgame() ' Terminates the calling appication or process

        Dim AppID As Process = Process.GetCurrentProcess()

        ' Kill the calling procedure
        AppID.Kill()

        'Clean up any open or locked file or connections
        AppID.Dispose()

    End Sub
Posted
Updated 19-Aug-12 21:00pm
v2

What exactly is the select statement supposed to be doing?

"Select * from [ApplicationLogging] where AppName = AppTest"

This looks like it trying to select all fields from a table named "ApplicationLogging" where the data in a column named "AppName" is equal to the data in a column named "AppTest".

If you were trying to setup AppTest as a parm...look at what your insert statement is doing. It uses four question marks to establish what the parms are, and then you had to set them in code. You would do something similar to that with your select statement. It would look like this:
"Select * from [ApplicationLogging] where AppName=?"
Then before calling the fill you would do this:
VB
SelectQuery.Parameters.AddWithValue("AppTest", AppTest)


Also...I'm not sure why you have an execute reader statment in there for the select query....I don't think you need it. Just set up your DataAdapter and do the .Fill.

If you want more examples, try google.[^]. Here[^] is one example I found. Here[^] is another.

Hope this helps.
 
Share this answer
 
That's a weird format, is it correct ? Paramaterised queries in my experience take the form of named paramaters as in

"select * from employees where id = @id"

and then you add a parameter called id to the parameters collection.
 
Share this answer
 
Comments
codejet 20-Aug-12 12:48pm    
Access does not use named parameters
Martok867 20-Aug-12 22:16pm    
Thanks for the reply.
I should clear up what i am trying to do.
KSchuller, you are correct in what I am trying to do with the select statement. There will be 3 parms I want to pass to the select statement, one being the AppNameSelect field from the form:
Dim DbSelect As String = _
"SELECT * FROM [ApplicationLogging] where AppName = " & AppNameSelect & " "

This is now workiung (Thanks)
Next I have to allow null values to be returned and do a select on a date range (Datefrom, DateTo)
you missing quotes in select query try bellow
VB
Dim DbSelect As String = _
         "Select * from [ApplicationLogging] where AppName = 'AppTest'"
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900