search a range of data by date to display in datagrid view
-
hi, i'm new in this. i want to display a list of data in my data grid view by the date that have been request. example i want a list from 1 feb 2008 untill 20 feb 2008. but nothing come out from the data grid except the title for each column in my datagrid. so this is the code. or is it something wrong with my datagrid properties [code] Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim startDate As String Dim endDate As String Dim ds As DataSet = Nothing startDate = y1.Text endDate = y2.Text Try Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM Senarai_Log where Date >= '" & startDate & "' and Date <= '" & endDate & "'", New SqlConnection("Server=MAHANI;Database=SPGALatihan;Persist Security Info=True;User ID=sysadm;Password=sysadm")) cmd.Connection.Open() Dim sqlDR As SqlDataReader = cmd.ExecuteReader() DataGrid1.DataSource = sqlDR DataGrid1.Visible = True DataGrid1.DataBind() DataGrid1.DataSource = Nothing cmd.Connection.Close() DataGrid1.Visible = True Catch ex As Exception ds = Nothing Label8.Visible = True Label8.Text = "NO DOKUMEN INI TIADA DALAM REKOD. SILA SEMAK DI DALAM SENARAI LOG" End Try End Sub End Class [/code]
-
hi, i'm new in this. i want to display a list of data in my data grid view by the date that have been request. example i want a list from 1 feb 2008 untill 20 feb 2008. but nothing come out from the data grid except the title for each column in my datagrid. so this is the code. or is it something wrong with my datagrid properties [code] Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim startDate As String Dim endDate As String Dim ds As DataSet = Nothing startDate = y1.Text endDate = y2.Text Try Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM Senarai_Log where Date >= '" & startDate & "' and Date <= '" & endDate & "'", New SqlConnection("Server=MAHANI;Database=SPGALatihan;Persist Security Info=True;User ID=sysadm;Password=sysadm")) cmd.Connection.Open() Dim sqlDR As SqlDataReader = cmd.ExecuteReader() DataGrid1.DataSource = sqlDR DataGrid1.Visible = True DataGrid1.DataBind() DataGrid1.DataSource = Nothing cmd.Connection.Close() DataGrid1.Visible = True Catch ex As Exception ds = Nothing Label8.Visible = True Label8.Text = "NO DOKUMEN INI TIADA DALAM REKOD. SILA SEMAK DI DALAM SENARAI LOG" End Try End Sub End Class [/code]
zaimah wrote:
Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM Senarai_Log where Date >= '" & startDate & "' and Date <= '" & endDate & "'",
It's best that you learn parameterized queries. The reason it doesn't work is because the dates are in the wrong format and your SQL is improperly written. I highly suggest reading this[^] to understand parameterized queries and what your wrote it a huge security problem.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008 -
zaimah wrote:
Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM Senarai_Log where Date >= '" & startDate & "' and Date <= '" & endDate & "'",
It's best that you learn parameterized queries. The reason it doesn't work is because the dates are in the wrong format and your SQL is improperly written. I highly suggest reading this[^] to understand parameterized queries and what your wrote it a huge security problem.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008thanks dave for giving me the article that r related. but as i said b4, i'm new in this, i didn't understand much about the article except 4 the error msg explaination. in my prgm the user suppose to enter date in this format "dd/mm/yyyy". so, by saying it's in the wrong format is it means that i should not declare it as string? sorry again for my english is not so good. in what way does my code can make injection attacks happen?
-
thanks dave for giving me the article that r related. but as i said b4, i'm new in this, i didn't understand much about the article except 4 the error msg explaination. in my prgm the user suppose to enter date in this format "dd/mm/yyyy". so, by saying it's in the wrong format is it means that i should not declare it as string? sorry again for my english is not so good. in what way does my code can make injection attacks happen?
OK, it sounds like you really need to pickup a beginners book on VB.NET and work through it. Your database should not be storing dates and times as strings. This makes it very difficult to retrieve a set of records between two dates. They should be stored as SQL Date types instead. When you go to search the database for records between two entered dates, you have to convert the text entered by the user (this is a string) to a DateTime object. Look at the documentation for DateTime.TryParse[^] for this. When you get the two dates, you use them as parameters in a parameterized SQL query that does the searching. That's where that article link I posted comes in. That shows you how to setup and use parameterized queries. Concantenting strings together makes for some very bad, and easily breakable, SQL code that can be difficult to find bugs in.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008 -
OK, it sounds like you really need to pickup a beginners book on VB.NET and work through it. Your database should not be storing dates and times as strings. This makes it very difficult to retrieve a set of records between two dates. They should be stored as SQL Date types instead. When you go to search the database for records between two entered dates, you have to convert the text entered by the user (this is a string) to a DateTime object. Look at the documentation for DateTime.TryParse[^] for this. When you get the two dates, you use them as parameters in a parameterized SQL query that does the searching. That's where that article link I posted comes in. That shows you how to setup and use parameterized queries. Concantenting strings together makes for some very bad, and easily breakable, SQL code that can be difficult to find bugs in.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008thanks a lot for ur explaination. i'm really new in this, so many people that r expert in this usually don't entertain a beginner like me. thanks a lot. i will buy the book that u have suggested. one more thing, i'm confuse about one thing, what is the different between vb.net and asp.net? I'm using visual studio. if i see the html view, i can see the word , so i know it is an asp coding. but if see in the aspx.vb, which is the one that r asp? i'm still confuse in that.