vb.net SQL help needed

[juster21]

I am trying to run this query...

strSql = "SELECT * FROM tblMain WHERE (((tblMain.Verifier_ID)='" & lstVerifier.SelectedItem & "') AND ((tblMain.Week_Ending_Date)='" & lstWeekEndingDate.SelectedItem & "'));"

...but I get an error 'Data type mismatch in expression criteria'. The values are a name (text format) and a date (date/time format). How do I modify the query to pull correctly?? thanks!!

[DrBunchman]

The problem is the format of the date you are supplying to the query string. SQL Server likes dates in the format YYYY-MM-DD so you need a function to change it to that format before you use it in your query.

Something like:

 
Private Function FormatDate(ByVal DateIn As DateTime) As String
	Dim sDate As String = (DateIn.Year & "-" & DateIn.Month & "-" & DateIn.Day).ToString
	Return sDate
End Function

which you can call in your code like this:

 
strSql = "SELECT * FROM tblMain WHERE (((tblMain.Verifier_ID)='" & lstVerifier.SelectedItem & "') AND ((tblMain.Week_Ending_Date)='" & FormatDate(lstWeekEndingDate.SelectedItem) & "'));"

Let me know if this helps,

Dr B

[juster21]

The problem is the format of the date you are supplying to the query string. SQL Server likes dates in the format YYYY-MM-DD so you need a function to change it to that format before you use it in your query.

Something like:

 
Private Function FormatDate(ByVal DateIn As DateTime) As String
	Dim sDate As String = (DateIn.Year & "-" & DateIn.Month & "-" & DateIn.Day).ToString
	Return sDate
End Function

which you can call in your code like this:

 
strSql = "SELECT * FROM tblMain WHERE (((tblMain.Verifier_ID)='" & lstVerifier.SelectedItem & "') AND ((tblMain.Week_Ending_Date)='" & FormatDate(lstWeekEndingDate.SelectedItem) & "'));"

Let me know if this helps,

Dr B

Does it matter that I am using Access 2003 as the database?

[DrBunchman]

[QUOTE=juster21]Does it matter that I am using Access 2003 as the database?[/QUOTE

It requires only a small change. Instead of delimiting the date with apostrophe's you need to use hash symbols.

#YYYY-MM-DD# Instead of 'YYYY-MM-DD'. So your sql statement would be:

strSql = "SELECT * FROM tblMain WHERE (((tblMain.Verifier_ID)='" & lstVerifier.SelectedItem & "') AND ((tblMain.Week_Ending_Date)=#" & FormatDate(lstWeekEndingDate.SelectedItem) & "#));"

Let me know if this works for you.

Cheers,

Dr B

[juster21]

It requires only a small change. Instead of delimiting the date with apostrophe's you need to use hash symbols.

#YYYY-MM-DD# Instead of 'YYYY-MM-DD'. So your sql statement would be:

strSql = "SELECT * FROM tblMain WHERE (((tblMain.Verifier_ID)='" & lstVerifier.SelectedItem & "') AND ((tblMain.Week_Ending_Date)=#" & FormatDate(lstWeekEndingDate.SelectedItem) & "#));"

Let me know if this works for you.

Cheers,

Dr B

wonderful!! thank you!!