Problem with sql Query WHERE(condition)

[chanlichin]

I am facing the problem in select the item dates. This is because i only want the condition of month from table dates. So i think the data set i write it get the error. Any one can help me,Thanks.

con.Open()
        sql = "SELECT DISTINCT Month(dates) FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "' AND Year(dates) = " & cbYear.Text & ";"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "summary")
        con.Close()

        maxrow = ds.Tables("summary").Rows.Count

        For a = 0 To maxrow - 1
sql2 = "SELECT * FROM summary WHERE Year(dates) = " & cbYear.Text & " AND plants = '" & yieldsummary.cbPlant.Text & "' AND line = '" & cbLine.Text & "'AND [B]Month(dates) = '" & ds.Tables("summary").Rows(a).Item("dates") & "'[/B]"

Next a

[sknake]

Which query is giving you the error -- you have two? Also can you please post the exception you're receiving?

[yorro]

The code just connects to the database, it doesn't retrieve data

You should read this.
http://www.daniweb.com/forums/post979710.html#post979710

Which query is giving you the error -- you have two? Also can you please post the exception you're receiving?

Probably from the first one because the second is just an SQL command.

Kindly mark thread as solved.

[chanlichin]

con.Open()
        sql = "SELECT DISTINCT Month(dates) FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "' AND Year(dates) = " & cbYear.Text & ";"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "summary")
        con.Close()

        maxrow = ds.Tables("summary").Rows.Count

        For a = 0 To maxrow - 1
sql2 = "SELECT * FROM summary WHERE Year(dates) = " & cbYear.Text & " AND plants = '" & yieldsummary.cbPlant.Text & "' AND line = '" & cbLine.Text & "'AND [B]Month(dates) = '" & ds.Tables("summary").Rows(a).Item("dates") & "'[/B]"

Next a

The error msg come out by sql2 as "The column dates is not belong to table summary. So i just wonder what is the problem actually, this is because the first sql do not have the problem at all when i SELECT Month(dates). I get to know the problem is in my sql2 (WHERE Month(dates) = '" & ds.Tables("summary").Rows(a).Item("dates") & "')My full coding as below:

sql = "SELECT DISTINCT Month(dates) FROM summary WHERE plants = '" yieldsummary.cbPlant.Text & "' AND Year(dates) = " & cbYear.Text & ";"  
 da = New OleDb.OleDbDataAdapter(sql, con)
  da.Fill(ds, "summary")

        maxrow = ds.Tables("summary").Rows.Count

        For a = 0 To maxrow - 1


sql2 = "SELECT * FROM summary WHERE Year(dates) = " & cbYear.Text & " AND plants = '" & yieldsummary.cbPlant.Text & "' AND line = '" & cbLine.Text & "'AND dates = '" & (ds.Tables("summary").Rows(a).Item("dates")) & "'"
 da2 = New OleDb.OleDbDataAdapter(sql2, con2)
da2.Fill(ds2, "summary")

        Dim maxrow2 As Integer = ds2.Tables("summary").Rows.Count 
For b = 0 To maxrow2 - 1

                a1 = ds2.Tables("summary").Rows(b).Item("PartID")
                a2 = ds2.Tables("summary").Rows(b).Item("PartMD")
                a3 = ds2.Tables("summary").Rows(b).Item("PartOD")
                YellowGreen(a) = YellowGreen(a) + (a1 + a2 + a3)



Next b

Next a