使用文本框从具有特定范围的 SQL Server 数据库中选择

问题描述：

Try
    conn = New SqlConnection(strcon)
    conn.Open()
    Dim str As String = "select * from MYTABLE where Year >='#" & Txtfromyear_reprt.Text & "#' and Year <='#" & Txttoyear_reprt.Text & "#'"
    da = New SqlDataAdapter(str, conn)
    Dim ds As New DataSet
    da.Fill(ds, "MYTABLE")
    DgvReport.DataSource = ds.Tables("MYTABLE")
    da.Dispose()
    conn.Close()

Catch ex As Exception
    MessageBox.Show(ex.Message)
End Try

我正在处理我的学校项目，但遇到了无法解决的问题.我在搜索按钮中编写了这段代码，但是当我在运行时单击它时，我的数据网格中没有显示任何数据.

I'm working with my school project but I've encountered a problem in which I can't solve. I wrote this code in my search button but when I click it at runtime, no data is displayed in my datagrid.

我想要的是当我单击它时，我想使用两个文本框将 mytable 中的所有数据显示到 Datagrid 视图中.我有两个文本框，txtfromyear 和 txttoyear 以及一个数据类型为 nvarchar(50) 的数据库列 Year.

What I want is when I click it I want to display all the data from mytable to the Datagrid view using two textboxes. I have two textboxes, txtfromyear and txttoyear and a database column Year with a datatype nvarchar(50).

请帮助我，在此先感谢您.

Please help me, thank you in advance.

答

不要使用字符串连接来构建 sql 查询，永远不要！

Don't use string concatenation to build your sql queries, NEVER!

你对sql注入持开放态度，没有任何借口.而是使用 sql 参数:

You are open for sql injection, there is no excuse for it. Instead use sql parameters:

Dim dateFrom as Date
Dim dateTo as Date
Dim validFromDate = Date.TryParse(Txtfromyear_reprt.Text.Trim(), dateFrom)
Dim validToDate = Date.TryParse(Txttoyear_reprt.Text.Trim(), dateTo)

如果用户没有提供有效日期，现在用有意义的消息退出此方法.您可以检查 validFromDate 和 validToDate 它们是布尔值.其余代码执行If validFromDate AndAlso validToDate:

Now exit this method with a meaningful message if the user didn't provide valid dates. You can check validFromDate and validToDate which are booleans. The rest of the code is executed If validFromDate AndAlso validToDate:

Dim str As String = "select * from MYTABLE where Year >= @fromyear and Year <= @toyear"
da = New SqlDataAdapter(str, conn)
da.SelectCommand.Parameters.Add("@fromyear", SqlDbType.DateTime).Value = dateFrom 
da.SelectCommand.Parameters.Add("@toyear", SqlDbType.DateTime).Value = dateTo
' now you can use da.Fill(ds, "MYTABLE") safely

我刚刚看到您使用 varchar 来存储 datetimes.为什么?在数据库中修复它.

I just saw you use varchar to store datetimes. Why? Fix it in the database.

使用文本框从具有特定范围的 SQL Server 数据库中选择

相关推荐