Question SQL dates - Nullreferenceexception was unhandled error

[Andy Kerr]

I have a Monthcalendar from which a date is chosen and a button is clicked. I can store this and have tried formatting it a number of ways so it can be used in an SQL statement to list all incidents which were recorded after that date.


PrivateSub MonthCalendar1_DateSelected(ByVal sender AsObject, ByVal e As System.Windows.Forms.DateRangeEventArgs) Handles MonthCalendar1.DateSelected
Dim date_chosen AsString
date_chosen = MonthCalendar1.SelectionEnd
Button1.Text = MonthCalendar1.SelectionEnd & Environment.NewLine & "Click here"
Dim dateTimeInfo AsDateTime = date_chosen
Dim strMonth AsString = dateTimeInfo.ToString("d")
Db.Open(DB_SQL)
Format$(strMonth,"dd/mm/yyyy")
MsgBox(strMonth) - this seems to throw out the correct "date"
rs3.Open("select * from TBL_Student_RSS where datetime > strMonth", Db)This rs3... is resulting in the nullreferenceexception error.

Dim myDA As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
Dim myDS AsDataSet = NewDataSet
　
myDA.Fill(myDS, rs3, "MyTable")
DataGridView1.DataSource = myDS.Tables("Mytable")
Db.Close()
EndSub


It must be obvious but I searched numerous web sites and come up with confusion.
Any help would be very welcome

 

[Herman]

Vomiting a chunk of unformatted unindented code like this doesn't help anyone track down the problem, including you... I wrote down some of the issues as comments in a cleaned up version:

Private Sub MonthCalendar1_DateSelected(ByVal sender AsObject, ByVal e As System.Windows.Forms.DateRangeEventArgs) Handles MonthCalendar1.DateSelected
    Dim date_chosen As String ' Why all the hopscotch here?
    date_chosen = MonthCalendar1.SelectionEnd ' The first two variables aren't even used....
    Dim dateTimeInfo As DateTime = date_chosen ' Could have simply been:
    Dim strMonth As String = dateTimeInfo.ToString("d") ' Dim strMonth As String = MonthCalendar1.SelectionEnd.ToString("dd/mm/yyyy")

    ' That's VB6, has no place in a VB.NET program anymore...
    ' In addition its absolutely unneeded, you could apply the same format to the strMonth variable above...
    Format$(strMonth,"dd/mm/yyyy")
    
    Dim myDA As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
    Dim myDS As DataSet = New DataSet

    ' Why call on the SelectionEnd property again when you have already copied the value to a local variable?
    ' Button1.Text = strMonth & VbCrLf & "Click Here"
    Button1.Text = MonthCalendar1.SelectionEnd & Environment.NewLine & "Click here"

    Db.Open(DB_SQL) ' There is no "Db" declared anywhere that we can see

    MsgBox(strMonth)

    ' There is no "rs3" declared anywhere that we can see.
    ' In addition, the strMonth variable is out of scope AND of invalid type, and "datetime" is a type not a value...
    ' rs3.Open("select * from TBL_Student_RSS where GetDate() > Convert(DateTime, '" & strMonth & "')", Db)
    rs3.Open("select * from TBL_Student_RSS where datetime > strMonth", Db)
    myDA.Fill(myDS, rs3, "MyTable")

    DataGridView1.DataSource = myDS.Tables("Mytable")
    Db.Close()
End Sub

 

[Andy Kerr]

Thanks for the notes. The basics from a book get you so far and its clear I'm still missing all sorts. I'll look through your notes at a later point. Thanks again.

 

[Andy Kerr]

Herman - huge thanks. Helped me get the whole lot looking better and was able to remove no end of what was indeed vomit.

The issues remain however. The SQL query should remove all data from tbl_student_rss except where a date in the field "datetime" > the date chosen from Month calendar

Public Classrecentissuess

Dim Db AsNew ADODB.Connection
Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
Dim ds AsDataSet = NewDataSet
Dim rs3 As ADODB.Recordset

---

​

PrivateSub MonthCalendar1_DateSelected(ByVal sender AsObject, ByVal e As System.Windows.Forms.DateRangeEventArgs) Handles MonthCalendar1.DateSelected

Db.Open(DB_SQL)
Dim strMonth As String = MonthCalendar1.SelectionEnd.ToString("dd/mm/yyyy")
Rs3.Open("select * from TBL_Student_RSS where datetime > strMonth", db) 'datetime is a field, strmonth is the date chosen from Monthcalendar1
da.Fill(ds, rs3, "MyTable") ' should this not throw out the incidents which are after the date selected?
DataGridView1.DataSource = ds.Tables("Mytable")
Db.Close()
​

EndSub

​

 

[SLPx]

Ok one thing coming in to the mind is, does Format("xx/mm/xxxx") is actually provide a valid date. If i'm not mistaken, the term 'm' is used to minutes and not months ain't it?

For month, its 'M' isn't it?

 

[Paszt]

Andy, here's a link on how to use BB code to make better looking code: http://www.vbdotnetforums.com/misc.php?do=bbcode#code

As you can see, when you simply paste the code, the formatting can get messed up and some spaces can be removed. Use code blocks and that doesn't happen and makes it easier to read and hopefully more people will be willing to help you with your issue.

 

[Andy Kerr]

Paszt - Thanks for the advice. Anything to improve is helpful

 

[Andy Kerr]

I get no data at all when I know its there

Public Class recentissues
　
Private Sub MonthCalendar1_DateSelected(ByVal sender AsObject, ByVal e As System.Windows.Forms.DateRangeEventArgs) Handles MonthCalendar1.DateSelected

Dim rs3 As ADODB.Recordset = New ADODB.Recordset
Dim Db As ADODB.Connection = New ADODB.Connection
Dim da As OleDb.OleDbDataAdapter = NewOleDb.OleDbDataAdapter
Dim ds As DataSet = New DataSet
Dim dateMonth As Date = MonthCalendar1.SelectionEnd
Dim qrystr As String



Db.Open(DB_SQL)　
qrystr = "select * from TBL_Student_RSS where DateTime between " & dateMonth & "and" & Date.today
Debug.WriteLine(qrystr) 'shows select * from TBL_Student_RSS where DateTime between 07/02/2012 and 11/04/2012
rs3.Open(qrystr, Db)
da.Fill(ds, rs3, "MyTable")
DataGridView1.DataSource = ds.Tables("Mytable")

Db.Close()​

EndSub



Can anyone help please? This gives me no errors whatsoever but the datagrid is filled with nothing. (which isnt correct)

 

[SLPx]

select * from TBL_Student_RSS where DateTime between

1. Term DateTime is a reserved word in SQL Server/SQL Server Express/Oracle.
If you are using such db, change the field name.

2. Date time need to parse between Date Literal (i.e #)
so the correct SQL be
select * from TBL_Student_RSS where [DateTime] between #07/02/2012# and #11/04/2012#

 

[Andy Kerr]

Thanks so much for ALL the help on this one.
:chuncky:
SLPx - renamed the fieldname in sqlexpress, then used dd-MMMM-yyyy to put date in the correct format.

I enclose finished code for reference.

Public Class recentissues
　
Private Sub MonthCalendar1_DateSelected(ByVal sender AsObject, ByVal e As System.Windows.Forms.DateRangeEventArgs) Handles MonthCalendar1.DateSelected

Dim rs3 As ADODB.Recordset = New ADODB.Recordset
Dim Db As ADODB.Connection = New ADODB.Connection
Dim da As OleDb.OleDbDataAdapter = NewOleDb.OleDbDataAdapter
Dim ds As DataSet = New DataSet
Dim dateMonth As String= MonthCalendar1.SelectionEnd.ToString("dd-MMMM-yyyy")
Dim qrystr As String = "select * from TBL_Student_RSS where [incidentdate] > '" & dateMonth & "'"




Db.Open(DB_SQL)　
rs3.Open(qrystr, Db)
da.Fill(ds, rs3, "MyTable")
DataGridView1.DataSource = ds.Tables("Mytable")

Db.Close()​

EndSub

 

[SLPx]

Dim dateMonth As String= MonthCalendar1.SelectionEnd.ToString("dd-MMMM-yyyy")
Dim qrystr As String = "select * from TBL_Student_RSS where [incidentdate] > '" & dateMonth & "'"

Insert a line as
debug.print qryStr


Then goto output window and get the string that will display when you run this programme

Goto SQL Express and execute it, If you are getting a result then it will be OK
Or else it will say where the error is.

Again Use just two M's

 

[Andy Kerr]

thanks but its all working now. Whats the procedure when an issue is resolved? Many posts seem to have "new" titles beginning "resolved"