Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Problem with sql statement in vb script using variables. Expand / Collapse
Author
Message
Posted Thursday, January 3, 2013 2:24 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, Visits: 180
Hi all,
I have been trying to resolve this for hours but failed, hence have to post here. I have this vbscript:

Private Function CheckNeedSendMail() As Integer
Dim startFilterDate As DateTime
Dim endFilterDate As DateTime
endFilterDate = CType(Dts.Variables("StgEndDate").Value, DateTime)
startFilterDate = endFilterDate.AddHours(-25)
Dim strStart As String = startFilterDate.ToString()
Dim strEnd As String = endFilterDate.ToString()


Dim sqlConnStr As String = "Data Source=" & RdVarStr("DB_Server_Name") & ";Initial Catalog=" & RdVarStr("DB_Init_Cat") & ";Integrated Security=True;"

Dim rowCounter As Integer
Using sqlConn As New Data.SqlClient.SqlConnection(sqlConnStr)
sqlConn.Open()
Using sqlCmd As New Data.SqlClient.SqlCommand("SELECT Count(*) from RPT_TACS_ACCESS_DTL where EXIT_DT >= " & startFilterDate & " and EXIT_DT <= " & endFilterDate & " and WORKED_HRS >= (select VALUE from TERMACCESS_CONFIG where MODULE = 'SSIS TACSReport' and [KEY]= 'Var_WorkHrs_Limit') +':00'", sqlConn)
Dim sqlDtReader As Data.SqlClient.SqlDataReader
sqlDtReader = sqlCmd.ExecuteReader()
While sqlDtReader.Read()
rowCounter = sqlDtReader.GetInt32(0)
End While
sqlDtReader.Close()
End Using
End Using
Return rowCounter

End Function

I keep hitting incorrect syntax near '7' and '+' when i dont even have this two value.... what went wrong?????
Post #1402245
Posted Thursday, January 3, 2013 2:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 5,230, Visits: 9,461
Looks like it's to do with the way the command string is being built. Perphaps you could put a line in your code to print the command string before it's executed? What is the "+':00'" at the end for? If you're trying to append ":00" on to the end of a number, you need to do something like this:

SELECT CAST(COUNT(*) AS varchar(6)) + ':00'
...

John
Post #1402253
Posted Thursday, January 3, 2013 2:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, Visits: 180
John Mitchell-245523 (1/3/2013)
Perphaps you could put a line in your code to print the command string before it's executed?

John


How do i do this?
Post #1402256
Posted Thursday, January 3, 2013 2:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 5,230, Visits: 9,461
That's more a VB question than a SQL Server question, and outside my area of expertise. You could try using MsgBox.

John
Post #1402258
Posted Thursday, January 3, 2013 2:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, Visits: 180
John Mitchell-245523 (1/3/2013)
What is the "+':00'" at the end for? If you're trying to append ":00" on to the end of a number, you need to do something like this:

SELECT CAST(COUNT(*) AS varchar(6)) + ':00'
...

John


Thanks for helping. The "+':00'" is to add :00 to my variable to make the query works. For example after using sub query if the Var_WorkHrs_Limit is 07, then it will query WORKED_HRS >= 07:00 I have been using this way for another similar method and no problem for the script. I do not think that is the cause.
Post #1402260
Posted Thursday, January 3, 2013 3:01 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, Visits: 180
John Mitchell-245523 (1/3/2013)
That's more a VB question than a SQL Server question, and outside my area of expertise. You could try using MsgBox.

John


Oh i tried using msgbox to see whats wrong before posting here. MsgBox doesnt really helps pointing out where is the problem.
Post #1402263
Posted Thursday, January 3, 2013 3:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 5,230, Visits: 9,461
10e5x (1/3/2013)
Oh i tried using msgbox to see whats wrong before posting here. MsgBox doesnt really helps pointing out where is the problem.

Why not? If you put this in your code:

MsgBox("SELECT Count(*) from RPT_TACS_ACCESS_DTL where EXIT_DT &gt;= " & startFilterDate & " and EXIT_DT &lt;= " & endFilterDate & " and WORKED_HRS &gt;= (select VALUE from TERMACCESS_CONFIG where MODULE = 'SSIS TACSReport' and [KEY]= 'Var_WorkHrs_Limit') +':00'")

how does that not help? If it really doesn't, you'll need to do your own research to find out how to display the code some other way.

John

Post #1402268
Posted Thursday, January 3, 2013 5:32 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:34 AM
Points: 575, Visits: 3,358
do you have to put quotes round your dates?

instead of

where EXIT_DT &gt;= " & startFilterDate & " and

you do

where EXIT_DT &gt;= ' " & startFilterDate & " ' and

Post #1402309
Posted Thursday, January 3, 2013 6:39 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 22, 2013 9:13 AM
Points: 496, Visits: 601
Look at line 15, you are using two variables to represent your filter dates, but the string representation doesn't include the single quotes ' ' around the date. So you're putting together your SELECT clause without single quotes around the date variables. Try including the single quotes before and after each variable as part of the strings and see if that does the trick.

Your error message is simply due to SQL Server not being able to parse the line as 'single quotes' are required around values like these.


EDIT: Sorry davidandrews13, didn't spot your post above. Ditto that.


---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!

Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
Post #1402338
Posted Thursday, January 3, 2013 7:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:16 AM
Points: 13,110, Visits: 11,939
For an even better solution stop using dynamic pass through sql and do this with a stored proc. The advantages are numerous. You can avoid sql injection, debugging is a lot less difficult, changes to the data logic does not require a recompile and deployment of the software, the list goes on and on.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1402370
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse