CODEWITHSUNDEEP
sql-servervb.net
Push Back
Push Back

Reputation: 11

SQL count function wont work properly in VB.Net

I am working in my final exam project using VB.NET and SQL SERVER 2005 platform. I need to detect the conflict when I select a specific date or time if it has in between in the database.

I already have my sql query in sql server and it works well. but when I was going to execute it in my vb.net project the problem starts to appear. I'm having a hard time with this. Any help will be appreciated.

myTable

Item            DateFrom             DateTo             TimeFrom              TimeTo
Diamond        3/10/2013           3/20/2013              NULL                 NULL
Cooler         3/10/2013           3/20/2013              NULL                 NULL
Cooler         3/21/2013              NULL             9:30:00 AM            11:00:00 AM 
Diamond        3/21/2013              NULL             8:00:00 AM            9:30:00 AM

mySQL

select count(*) from myTable
where '3/21/2013' between(datefrom)and(dateto) and item = 'Diamond'
or datefrom = '3/21/2013'  and timefrom between '8:00 AM' and '10:00 AM' and item = 'Diamond'
or datefrom = '3/21/2013' and  timeto between '8:00 AM' and '10:00 AM'   and item = 'Diamond'
or datefrom = '3/21/2013' and '8:00 AM' between(timefrom)and(timeto) and item = 'Diamond'

Result: 1

here's my vb.net code

SQLQUERY = "Select Count(*) as Conflicts from myTable" & _
     "Where '" & dtpTFrom.Value & "' between(datefrom)and(dateto) and item = '" &     cmbItems.Text & "'" & _
     "Or (datefrom = '" & dtpTFrom.Value & "'  and timefrom between '" &    Format(CDate(cmTFrom.Text), "hh:mm tt") & "' and '" & Format(CDate(cmTTo.Text), "hh:mm tt")      & "' and item = '" & cmbItems.Text & "')" & _
     "Or (datefrom = '" & dtpTFrom.Value & "' and  timeto between '" & Format(CDate(cmTFrom.Text), "hh:mm tt") & "' and '" & Format(CDate(cmTTo.Text), "hh:mm tt") & "'  and item = '" & cmbItems.Text & "')" & _
 "    Or (datefrom = '" & dtpTFrom.Value & "' and '" & Format(CDate(cmTFrom.Text), "hh:mm tt") & "' between(timefrom)and(timeto) and item ='" & cmbItems.Text & "')"

com = new sqlcommand(SQLQUERY,con) 
dr = com.ExecuteReader() 
dr.Read()
If dr.HasRows Then
    MsgBox(CInt(dr.GetValue(0)))
    If CInt(dr.GetValue(0)) > 0 Then
                MessageBox.Show("Selected item is not available for that date and time.", AppTitle, MessageBoxButtons.OK, MessageBoxIcon.Information)
    Exit Sub
    End If

End If

msgbox result : 0

Upvotes: 1

Views: 1084

Answers (2)

Steve
Steve

Reputation: 216313

Not sure, but I need to post as an answer because code formatting in comment is not quite good.
Let me know if it works

com = new sqlcommand(SQLQUERY,con) 
Dim result = com.ExecuteScalar() 
if Convert.ToInt32(result) = 0 Then
    MessageBox.Show("Selected item is not available for that date and time.", ...)
    Exit Sub
End If

Also there is the problem of Parameters. You need to pass parameters to avoid SQL Injection, but also to be sure that strings like yours are correctly interpreted by the database engine as the dates required by the query

This is a rough translation of your query using parameters instead of a hand made string

SQLQUERY = "Select Count(*) as Conflicts from myTable " & _
     "Where @dateFrom  between(datefrom)and(dateto) and item = @item " & _
     "Or (datefrom = @dateFrom and timefrom between @timeFrom and @timeTo and item = @item) " & _
     "Or (datefrom = @dateFrom and   timeto between @timeFrom and @timeTo and item = @item) " & _
     "Or (datefrom = @dateFrom and @timeFrom between(timefrom)and(timeto)and item =@item)"

com = new sqlcommand(SQLQUERY,con) 
com.Parameters.AddWithValue("@dateFrom",  Convert.ToDateTime(dtpTFrom.Value))
com.Parameters.AddWithValue("@item",  cmbItems.Text)
com.Parameters.AddWithValue("@timeFrom",  Convert.ToDateTime(cmTFrom.Text))
com.Parameters.AddWithValue("@timeTo",  Convert.ToDateTime(cmTTo.Text))

Upvotes: 0

John Woo
John Woo

Reputation: 263803

You have to group your OR conditions by placing them inside a parenthesis, (the reason why you are not getting incorrect result is because the conditions overlap with each other)

SELECT COUNT(*) 
FROM   myTable
WHERE ('3/21/2013' BETWEEN datefrom AND dateto AND item = 'Diamond') OR
      (datefrom = '3/21/2013' AND timefrom BETWEEN '8:00 AM' AND '10:00 AM' AND item = 'Diamond') OR
      (datefrom = '3/21/2013' AND timeto BETWEEN '8:00 AM' AND '10:00 AM'   AND item = 'Diamond') OR
      (datefrom = '3/21/2013' AND '8:00 AM' BETWEEN timefrom AND timeto AND item = 'Diamond')

Upvotes: 2

Related Questions