ODBC Connection String Issue's 1

[chuckh70]

Hello,

I have been working on several web based reports for a little while now, and just about ready to turn them in when I noticed all of the records display 8hrs ahead. After talking with Tech Support I was informed I can't connect to SQl Server. I must use the ODBC and connect through there server to get the data, and have it display properly.

After some messing around I managed to figure out the connection string, but I am not sure how to change my function to use it or if I can. Or is there a way to cheat and have it subtract 8 hours?

here is the ODBC string I need to use, and my function.

Thanks again for the help.

ARAuthentication=;DSN=AR System ODBC Data Source;UID=CH202806;ARServer=bkapp04;PWD=pword;SERVER=NotTheServer

Function  SetData()
	
Dim  connString As String = "server=bkwbreporting01;uid=sa;pwd=sa;database=ARSystem"  
    Dim conn As SqlConnection  = new SqlConnection(connString)
    
    Dim selectQuery As string = "Select assigned_to_group_, COUNT(*) AS tcktOpen FROM HPD_HelpDesk WHERE status >= 4 and DATEADD(dd, Create_Time / 86400, '1/1/1970') >= '" & nmStart & "' GROUP BY assigned_to_group_ Order By assigned_to_group_"
    'Dim selectQuery As string = "SELECT DATEADD(dd, Create_Time / 86400, '1/1/1970') AS 'Date Open', COUNT(*) AS tcktOpen FROM HPD_HelpDesk WHERE (Assigned_To_Group_ = '" & MySelect.Value & "') and (Status <=4) GROUP BY DATEADD(dd, Create_Time / 86400, '1/1/1970') Order By DATEADD(dd, Create_Time / 86400, '1/1/1970')ASC"
    'Dim selectQuery As string = "SELECT DATEADD(dd, Create_Time / 86400, '1/1/1970') AS 'Date Open', COUNT(*) AS tcktOpen FROM HPD_HelpDesk WHERE (Assigned_To_Group_ = '" & MySelect.Value & "') AND (Status <=4) GROUP BY DATEADD(dd, Create_Time / 86400, '1/1/1970') Order By DATEADD(dd, Create_Time / 86400, '1/1/1970')ASC"
    Dim myCommand As SqlCommand  = new SqlCommand(selectQuery, conn)
   	
   	conn.Open()
   	
   	Dim myReader As SqlDataReader = myCommand.ExecuteReader()
   	
 	'set dataReader to data property
   	Chart.Series.Data = myReader
   	
   	'Cleanup
 	myReader.Close()
   	conn.Close()
	

End Function

 

[ca8msm]

You already have the basics of what you could do in your SQL query.

Couldn't you just wrap another DATEADD around each of you date fields but rather than adding days add hours and set the value to -8.

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[chuckh70]

So I could just wrap it around my current date add?

Something like

DATEADD(hh,DATEADD(dd, Create_Time / 86400, '1/1/1970')-8)

 

[ca8msm]

I haven't got SQL Server here to test it but yes that looks fine.

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[chuckh70]

tells me it requires 3 arguments?

Thanks for the help by the way.

 

[ca8msm]

Yes it does (sorry missed that in your above code).

You have to include all 3 as you have done in your previous DateAdd. e.g.

DATEADD(hh, -8, DATEADD(dd, Create_Time / 86400, '1/1/1970'))

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[chuckh70]

ok this works thank you, but I noticed another slight problem. my dateadd strips it down to just the date so the new date add just takes it from midnight and makes it 4 p.m.

 

[chuckh70]

Got it thanks a bunch.