使用vb.net搜索MS Access数据库所需的帮助 [英] help needed searching ms access database using vb.net

问题描述

我正在创建一个库存控制系统，而我正处于需要帮助搜索数据库以返回所需数据的阶段.例如，我现在有一个名为Check stock的表格，因为我想输入产品ID并点击搜索，当我按下搜索时，如果没有返回错误消息，则应该对照ms Access数据库检查数据是否存在.

到目前为止，我已经尝试过了，但是发生的是，我输入了正确的产品ID，它显示未找到数据"，但仍显示数据，但是错误的产品ID表示未找到这是要执行的操作.

请有人帮我解决我得到的错误

这是我的代码:

  Dim  b  As  字符串 = " & " & n
        如果 txtsearch.Text = "  然后
            MsgBox(" ，MsgBoxStyle.Critical)
        其他

             Dim 一个 As  新 OleDb.OleDbConnection( b)
            a.Open()
            sql =(" )


             Dim  dscheckstock  As  新建 DataSet(" )
             Dim  dacheckstock  As  新 OleDb.OleDbDataAdapter

            dacheckstock = 新建 OleDb.OleDbDataAdapter(sql，a)
            dacheckstock.Fill(dsstock，" )

             For 索引=  0   To  dscheckstock.表格(" ).Rows.Count- 1 
                 Dim  tblstock  As  DataTable
                tblstock = dscheckstock.Tables(" )


  如果 txtsearch.Text = dscheckstock.Tables(" ).rows(index).Item(" )或 _txtsearch.Text.Tolower = dscheckstock.Tables("  span>).Rows(index).Item(" )" ).Rows(index).Item(" )
                    TextBox2.Text = dscheckstock.Tables(" ).Rows(index).Item(" )
                    TextBox3.Text = dscheckstock.Tables(" ).Rows(index).Item(" )
                    TextBox4.Text = dscheckstock.Tables(" ).Rows(index).Item(" )
                    TextBox5.Text = dsstock.Tables(" ).Rows(index).Item(" )
                    TextBox6.Text =(dscheckstock.Tables(" ).Rows(index).Item(" ))

                 ElseIf 索引= dscheckstock.Tables("  span.).Rows.Count- 1  然后


                    MsgBox(" & " span> "& vbNewLine& 结束 如果

            下一步 

谢谢，

解决方案

这是我目前所拥有的.这样做不是正确的方法，但确实可以.如果返回的数据项为null，也会引发null错误.此错误处理类型还取决于每次单击按钮时都要清除的文本框.

 私有 子 Button1_Click( ByVal 发​​件人 As 系统.对象， ByVal  e  As  System.EventArgs)句柄 Button1.Click
        tbDateOfOrder.Clear()
        tbProductID.Clear()
        tbProductName.Clear()
        tbQuantityOrdered.Clear()
        tbQunatityInStock.Clear()
        tbSupplierID.Clear()
        tbItemCount.Clear()
        尝试
           
             Dim  b  As  字符串 =我的设置.dsstockConnectionString ' 可以在设置或app.config文件中编辑此位置
             Dim  InputSearch  As   String  = tbInput.Text
            如果 InputSearch = "  或 InputSearch = 什么都没有 然后
                MsgBox(" ，MsgBoxStyle.Critical， 无输入")
            其他

                 Dim 一个 As  新 OleDb.OleDbConnection( b)
                a.Open()

                 Dim  mySelect  As  字符串 =(" )

               
                 Dim 适配器 As  OleDbDataAdapter = 新 OleDbDataAdapter( mySelect，b)
                 Dim  dscheckstock  As  DataSet = 新 DataSet
                adapter.Fill(dscheckstock，" )


                 For 索引=  0   To  dscheckstock.表格(" ).Rows.Count- 1 
                     Dim  tblstock  As  DataTable
                    tblstock = dscheckstock.Tables(" )

                    在下面的' 中，我们设置了输入文本.tolower，还设置了产品ID .tolower.并与产品名称相同，以处理案例问题.
                    如果 dscheckstock.Tables("  ).Rows(index).Item(" ).ToString.ToLower.Contains(InputSearch .ToLower)或 _
                        dscheckstock.Tables(" ).Rows(index).Item( 产品名称").ToString.ToLower.Contains(InputSearch.ToLower)" ).Rows(index).Item(" )
                        tbProductName.Text = dscheckstock.Tables(" ).Rows(index).Item(" )
                        tbQunatityInStock.Text = dscheckstock.Tables(" ).Rows(index).Item(" )
                        tbQuantityOrdered.Text = dscheckstock.Tables(" ).Rows(index).Item(" )
                        tbDateOfOrder.Text = dscheckstock.Tables(" ).Rows(index).Item(" )
                        tbSupplierID.Text =(dscheckstock.Tables(" ).Rows(index).Item(" ))
                        '  ElseIf tbProductID.Text =没事了
                        '  MsgBox(找不到商品！"&"& vbNewLine& InputSearch.ToString，MsgBoxStyle.Critical， 找不到商品")
                    结束 如果

                下一步

                如果 tbProductID.Text = 什么都没有 然后
                    MsgBox(" & " "& vbNewLine& InputSearch.ToString，MsgBoxStyle.Critical， 找不到项目")
                结束 如果

                a.Close()' 关闭数据库
                tbItemCount.Text = dscheckstock.Tables(" ).Rows.Count 计算行数
            结束 如果

        捕获，例如 As 异常
            MsgBox(ex.Message.ToString)
        结束 尝试
    结束 子 

i''m creating a stock control system and i am at a stage where i need helping searching the database to return the data i want. For e.g i have a form called check stock now in this for i wish to enter the product id and hit search, and when i press search it should check against the ms access database if the data exists if not return an error message.

So far i have tried this but what happens is that i enter the correct product id it displays "data not found" but still displays the data, but incorrect product id says not found which is what it is meant to do.

Please can someone help me fix the error i get

Here is my code:

Dim b As String = "Provider = Microsoft.Jet.OLEDB.4.0;" & "Data Source = " & fn
        If txtsearch.Text = "" Then
            MsgBox("Incorrect!", MsgBoxStyle.Critical)
        Else

            Dim a As New OleDb.OleDbConnection(b)
            a.Open()
            sql = ("SELECT[Product ID], [Product Name],[Qunatity in stock], [Quantity Ordered], [Date of order], [Supplier ID] FROM STOCK")


            Dim dscheckstock As New DataSet("Stock")
            Dim dacheckstock As New OleDb.OleDbDataAdapter

            dacheckstock = New OleDb.OleDbDataAdapter(sql, a)
            dacheckstock.Fill(dsstock, "stock")

            For index = 0 To dscheckstock.Tables("Stock").Rows.Count - 1
                Dim tblstock As DataTable
                tblstock = dscheckstock.Tables("Stock")


  If txtsearch.Text = dscheckstock.Tables("stock").Rows(index).Item("Product ID") Or _txtsearch.Text.Tolower = dscheckstock.Tables("stock").Rows(index).Item("Product Name") Then
                    txtsearch.Text = dscheckstock.Tables("Stock").Rows(index).Item("Product ID")
                    TextBox2.Text = dscheckstock.Tables("Stock").Rows(index).Item("Product Name")
                    TextBox3.Text = dscheckstock.Tables("Stock").Rows(index).Item("Qunatity in stock")
                    TextBox4.Text = dscheckstock.Tables("Stock").Rows(index).Item("Quantity Ordered")
                    TextBox5.Text = dsstock.Tables("Stock").Rows(index).Item("Date Of Order")
                    TextBox6.Text = (dscheckstock.Tables("Stock").Rows(index).Item("Supplier ID"))

                ElseIf index = dscheckstock.Tables("stock").Rows.Count - 1 Then


                    MsgBox("Not found!"  & "" & vbNewLine & Me.txtsearch.Text, MsgBoxStyle.Critical)

                End If

            Next

Thanks,

解决方案

This is what I have at the moment. It would not be the correct way to do this, but it does work. It will also throw a null error if a data item is returns is null. This error handeling type also depends on the textbox it is checking to be cleared each time you click the button.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        tbDateOfOrder.Clear()
        tbProductID.Clear()
        tbProductName.Clear()
        tbQuantityOrdered.Clear()
        tbQunatityInStock.Clear()
        tbSupplierID.Clear()
        tbItemCount.Clear()
        Try
           
            Dim b As String = My.Settings.dsstockConnectionString 'this location can be edited in the settings or the app.config file
            Dim InputSearch As String = tbInput.Text
            If InputSearch = " " Or InputSearch = Nothing Then
                MsgBox("Nothing was input to search for", MsgBoxStyle.Critical, "No Input")
            Else

                Dim a As New OleDb.OleDbConnection(b)
                a.Open()

                Dim mySelect As String = ("SELECT[Product ID], [Product Name],[Qunatity in stock], [Quantity Ordered], [Date of order], [Supplier ID] FROM dsstock.STOCK")

               
                Dim adapter As OleDbDataAdapter = New OleDbDataAdapter(mySelect, b)
                Dim dscheckstock As DataSet = New DataSet
                adapter.Fill(dscheckstock, "Stock")


                For index = 0 To dscheckstock.Tables("Stock").Rows.Count - 1
                    Dim tblstock As DataTable
                    tblstock = dscheckstock.Tables("Stock")

                    ' below we set out input text .tolower and we also set the product id .tolower. and the same with the product name to handle case issues.
                    If dscheckstock.Tables("stock").Rows(index).Item("Product ID").ToString.ToLower.Contains(InputSearch.ToLower) Or _
                        dscheckstock.Tables("stock").Rows(index).Item("Product Name").ToString.ToLower.Contains(InputSearch.ToLower) Then

                        tbProductID.Text = dscheckstock.Tables("Stock").Rows(index).Item("Product ID")
                        tbProductName.Text = dscheckstock.Tables("Stock").Rows(index).Item("Product Name")
                        tbQunatityInStock.Text = dscheckstock.Tables("Stock").Rows(index).Item("Qunatity in stock")
                        tbQuantityOrdered.Text = dscheckstock.Tables("Stock").Rows(index).Item("Quantity Ordered")
                        tbDateOfOrder.Text = dscheckstock.Tables("Stock").Rows(index).Item("Date Of Order")
                        tbSupplierID.Text = (dscheckstock.Tables("Stock").Rows(index).Item("Supplier ID"))
                        'ElseIf tbProductID.Text = Nothing Then
                        '    MsgBox("Item Not found!" & "" & vbNewLine & InputSearch.ToString, MsgBoxStyle.Critical, "Item Not Found")
                    End If

                Next

                If tbProductID.Text = Nothing Then
                    MsgBox("Item Not found!" & "" & vbNewLine & InputSearch.ToString, MsgBoxStyle.Critical, "Item Not Found")
                End If

                a.Close() ' close the database
                tbItemCount.Text = dscheckstock.Tables("stock").Rows.Count 'count the number of rows
            End If

        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try
    End Sub

这篇关于使用vb.net搜索MS Access数据库所需的帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！