在vb.net中访问选择查询 [英] Access Select query in vb.net

问题描述

大家好，

我有一个正在编写的应用程序，它将系统数据添加到DB，并且我想从代码中运行查询并将数据传递到gridview.
第一部分在将系统数据传递给db时运行良好，但是当我尝试在DB上执行选择查询时，在尝试运行SelectQuery时会抛出异常没有为一个或多个必需参数提供值". > 我想念什么?
请在下面查看我的代码:

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:

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 [e-mail] 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 [e-mail] 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 [e-mail] 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

推荐答案

select语句到底应该做什么?

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

看起来它试图从名为"ApplicationLogging"的表中选择所有字段，其中"AppName"列中的数据等于"AppTest"列中的数据.

如果您试图将AppTest设置为参数...请查看您的insert语句在做什么.它使用四个问号来确定参数是什么，然后必须在代码中进行设置.您将使用select语句执行类似的操作.看起来像这样:
"Select * from [ApplicationLogging] where AppName=?"
然后，在调用填充之前，您需要执行以下操作:

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:

SelectQuery.Parameters.AddWithValue("AppTest", AppTest)

另外...我不确定您为什么在其中有针对选择查询的执行阅读器语句....我认为您不需要它.只需设置您的DataAdapter并执行.Fill.

如果您需要更多示例，请尝试此处 [此处 [

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.

这是一种怪异的格式，对吗?根据我的经验，参数化查询采用命名参数的形式，如

从id = @id的员工中选择*"

然后将一个名为id的参数添加到参数集合中.

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.

您在选择查询中缺少引号请尝试下面的

you missing quotes in select query try bellow

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

这篇关于在vb.net中访问选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！