4 Replies - 3950 Views - Last Post: 08 September 2009 - 05:37 PM Rate Topic: -----

#1 chanlichin   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 12-August 09

Problem with sql Query WHERE(condition) -date format

Post icon  Posted 08 September 2009 - 03:45 AM

Quote

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



Is This A Good Question/Topic? 0
  • +

Replies To: Problem with sql Query WHERE(condition) -date format

#2 motcom   User is offline

  • D.I.C Lover
  • member icon

Reputation: 293
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: Problem with sql Query WHERE(condition) -date format

Posted 08 September 2009 - 06:18 AM

what database are you using

is the ";" correct in this line?

sql = "SELECT DISTINCT Month(dates) FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "' AND Year(dates) = " & cbYear.Text & ";"



Was This Post Helpful? 0
  • +
  • -

#3 chanlichin   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 12-August 09

Re: Problem with sql Query WHERE(condition) -date format

Posted 08 September 2009 - 06:32 AM

View Postmotcom, on 8 Sep, 2009 - 05:18 AM, said:

what database are you using

is the ";" correct in this line?

sql = "SELECT DISTINCT Month(dates) FROM summary WHERE plants = '" & yieldsummary.cbPlant.Text & "' AND Year(dates) = " & cbYear.Text & ";"




Quote

Hi, as i know there is no problem for the first sql. Because when i run the program, the error msg come out by sql2 which is "Column 'dates' is not belong to table summary". The first sql i had already select the month from date. While the second sql2 , i need to select * but the condition is need to retrieves only month from the dates. So my problem is writting the condition WHERE (coding bold).Thanks




 con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\LICHIN\Desktop\mys\YS.mdb"

 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



Was This Post Helpful? 0
  • +
  • -

#4 motcom   User is offline

  • D.I.C Lover
  • member icon

Reputation: 293
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: Problem with sql Query WHERE(condition) -date format

Posted 08 September 2009 - 06:39 AM

try this for your first sql statement

the reason is that the Month() function does not give the column the name "dates" unless you use "AS [dates]"

sql = "SELECT DISTINCT Month(dates) AS [dates] FROM summary WHERE plants = '" yieldsummary.cbPlant.Text & "' AND Year(dates) = " & cbYear.Text & ";"  



Was This Post Helpful? 1
  • +
  • -

#5 chanlichin   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 12-August 09

Re: Problem with sql Query WHERE(condition) -date format

Posted 08 September 2009 - 05:37 PM

Thanks,Motcom...I get it already.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1