在datagridview对象上显示来自mysql服务器的数据.没有显示任何东西 [英] Displaying data from a mysql server on a datagridview object. is not displaying anything
问题描述
我尝试过使用数据集工具在设计端链接数据集,但是由于它不断消失而无法正常工作,这是另一种方法,它无法在数据网格视图上显示任何值,我不确定为什么我对vb的这一方面还很陌生,所以如果您也能解释一下,那就太好了.预先感谢.
I have tried making a dataset using the dataset tool to link it on the design side however that hasn't worked as it keeps disappearing and this is the other way it does not display any values on the datagrid view i am unsure why and i am fairly new to this side of vb so if you could explain it as well that would be great. Thanks in advance.
Imports MySql.Data.MySqlClient
Public Class Search
Private Async Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Try
If TextBox1.Text = "" Then 'this acts as a simple presence check on the textbox
Else
Dim val = "name"
If RadioButton1.Checked = True Then 'This changes the type of search i do as it filters which column the query looks in
val = "type"
End If
Await getDataSet(TextBox1.Text) ' waits for infomation to be retrieved
End If
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message) 'Catches any errors
End Try
End Sub
Async Function getDataSet(partname As String) As Task(Of DataSet) 'This retrieves the values that matches the users input
Return Await Task.Factory.StartNew(
Function()
Dim connectionString = "server=localhost; userid=root; password=; database=partstest1; CharSet=utf8;" 'These are the login details for the database in the form of a connection string
Dim commandText = "SELECT ID, Benchpoint, Name, Type, BrandID FROM `parts` WHERE `name` Like '%" & TextBox1.Text & "%';"
Using connDB = New MySqlConnection(connectionString), objCmd = New MySqlCommand(), objAdpt = New MySqlDataAdapter()
connDB.Open()
objCmd.Connection = connDB
objCmd.CommandText = commandText
objCmd.CommandType = CommandType.Text 'These lines specify the command i am using and execute it
objAdpt.SelectCommand = objCmd
Dim objDs = New DataSet()
objAdpt.Fill(objDs) 'Puts all of the values into a dataset
PartsDataGridView.DataSource = objDs.Tables(0) 'This shows the datasource and displays it
Console.WriteLine(objDs)
Return objDs
End Using
End Function)
End Function
End Class
推荐答案
-
安装MySQL/connector/etc
Install MySQL/connector/etc
为Visual Studio安装MySQL(需要使其在VS中显示为数据源)-
Install MySQL for Visual Studio (needed to get it to show as a datasource in VS) - https://dev.mysql.com/downloads/windows/visualstudio/
向项目添加新的数据集
Add a new dataset to your project
双击数据集,右键单击它的表面,选择添加" >>"TableAdapter"
Double click the dataset, right click the surface of it, choose Add >> TableAdapter
选择现有连接,如果没有现有连接,则选择新建连接".选择MySQL数据库(如果没有,请从步骤2安装MySQL for Visual Studio)
Choose existing connection or New Connection if you have no existing. Choose MySQL Database (install MySQL for Visual Studio from step 2 if this is absent)
输入服务器详细信息,选择保存密码,在向导中选择是,包括敏感....并单击 Next ,然后选择 Yes ,保存连接字符串,单击下一步,选择使用SQL语句,下一步,输入一个根据查询条件选择合适的查询主键(例如
SELECT * FROM yourtable WHERE id = @id
, Next ),调用方法 FillById 和 GetDataById ,以将它们与以后可能添加的其他查询(例如,)区分开. > FillByCity 或 GetDataByStatus .在此向导的最后,您应该具有一个类似于数据库表的数据表和表适配器:Enter server details, choose to save the password, back in the wizard choose Yes, include sensitive.. and click Next, choose Yes, save the connection string, click Next, choose Use SQL Statements, Next, enter a suitable query that selects based on the primary key such as
SELECT * FROM yourtable WHERE id = @id
, Next, call the methods FillById and GetDataById to distinct them from other queries you may add later like FillByCity or GetDataByStatus. At the end of this wizard you should have a datatable and tableadapter that looks like your database table:切换到表单设计器,并确保数据源窗口可见(单击查看菜单 >> 其他Windows > >> *数据源**),在数据源"面板中展开所有节点
Switch to the form designer, and also ensure that the Data Sources window is visible (click View Menu >> Other Windows >> *Data Sources**), expand all the nodes in the Data Sources panel
将代表您表的节点(我的表称为 person )拖出数据源窗口,然后将其拖放到表单上.将会出现几件事:
Drag the node representing your table (mine is called person) out of the datasources window and drop it on the form. Several things will appear:
- 已绑定到显示人员数据的数据集数据表的datagridview
- 带有文本框的工具条,用于输入ID(请记住您进行了带参数的查询),
- 另一个带有导航器和保存"按钮的工具条,
- 数据集(需要存储数据),
- 一个绑定源(充当数据集的数据表和网格之间的链接,知道当前显示的记录,可以进行排序和过滤),
- tableadapter(一种增强的数据适配器,它将数据从db提取到数据集中并发送回所有更改),
- 表适配器管理器(一种知道以何种顺序运行分层数据的设备的设备)
- 另外,为了演示其工作原理,还将所有UNDERNEATH person节点拖到表单上-您可以看到它们具有不同的图标.它们将重用表单上的大多数组件,但是您将获得绑定到绑定源的文本框,日期时间选择器,复选框等,因此将显示基础列表中的当前项目(datagridview将它们全部显示,并带有指示符当前是哪一行)
- 运行项目,在文本框中键入ID,点击** FillById *,在底部的另一行中写入一些新信息-当您更改DGV中的行时,它将提交到基础数据集/数据表中,请注意随着 Current 行的概念的更新,在DGV中的行之间来回滑动会导致文本框发生变化(导航网格,或单击导航器中的箭头会更改bindingsource.
Current
属性,并且所有控件都通过bindingsource进行绑定
- Run the project, type an ID into the textbox, hit **FillById*, write some new info into another row at the bottom - it will commit to the underlying dataset/datatable when you change row in the DGV, note that flicking back and forth between the rows in the DGV causes the textboxes to change as the notion of Current row is updated (navigating the grid, or clicking the arrows in the navigator changes the bindingsource .
Current
property, and all controls bind through the bindingsource
- 命中保存-现在转到MySQL Workbench中的数据库中查找-新记录就在那里.您已经制作了完整的数据绑定UI,并且没有编写任何代码-如果您想查看的话,所有代码都在FormX.vb中-由设计人员为您编写.看看
请注意,对于临时ID,它仍然显示-4(在我的用户界面中);该程序未下载数据库分配的新ID.为此,您应该能够返回到数据集,右键单击tableadapter,单击 Advanced Options ,然后在 Refresh the datatable 上打勾-这将保存适配器后,使适配器更新所有本地ID.如果ID是两个日期表之间数据关系的一部分,则ID可以级联更新.在MySQL中,这行不通(我现在正在向他们报告错误),但是您可以通过手动编辑代表数据集的XML文件来使其工作(在解决方案资源管理器中右键单击数据集",选择打开方式.选择 XML编辑器,找到插入语句,例如:
Note that it still says -4 (in my UI) for the temp id; the program didn't download the new ID that was assigned by the DB. To make it do this, youre supposed to be able to go back to your dataset, right click the tableadapter, click Advanced Options, and tick on Refresh the datatable - this will cause the adapter to update any local IDs after it saves the row. IDs can cascade update if they are part of a datarelation between two datetables. In MySQL this doesn't work (I'm reporting a bug to them now), but you can make it work by manually editing the XML file representing the dataset (right click the DataSet in solution explorer, choose Open With.. Choose XML Editor, locate the insert statement eg:
INSERT INTO `person` (`Name`, `Birthdate`, `Salary`) VALUES (@p1, @p2, @p3)
在其后添加另一个语句,如下所示:
Add another statement after it so it looks like:
INSERT INTO `person` (`Name`, `Birthdate`, `Salary`) VALUES (@p1, @p2, @p3) ;SELECT `Id`, `Name`, `Birthdate`, `Salary` FROM `person` WHERE `Id` = last_insert_id()
现在,保存表将使UI刷新,该ID将由数据库中的自动增量计算得出的ID值
Now, saving the table will cause the UI to refresh with the ID values calculated by the auto increment in the DB
这篇关于在datagridview对象上显示来自mysql服务器的数据.没有显示任何东西的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!