Allyni Memes Allyni Memes - 1 month ago 7
Vb.net Question

Select from SQL Server database with specific range using textbox

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.

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.

Answer Source

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

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)

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

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