SQL Server 2008 :: Expression Of Non-Boolean Type Specified In Context Where Condition Is Expected
Apr 10, 2015
I am getting an error when running this query in SSRS- "an expression of non-boolean type specified in a context where a condition is expected , near ',' " on the following query in the WHERE clause section.
SELECT
MG_BL_ITINERARY.ETA_DT,
MG_BL_ITINERARY.TO_LOCATION_CD
FROM MG_BL_ITINERARY
[code]...
what I need to change in the WHERE clause to rectify this error ?
Hi,I am trying to create an update statementon a table with a foreign key to the Userstable (userid).I am getting the error:An expression of non-boolean type specified in a context where acondition is expected, near ')'.Below is the Update statement.UPDATELastLogin SETdate= '2007-08-14 05:34:09.910',status= 1 ,activity = 0WHERE(SELECT ll.status, ll.activity, ll.dateFROM LastLogin ll, Users uWHERE ll.userid = u.useridAND u.email = 'dushkin@hotmail.com')Thanks!
This is nutty. I never got this error on my local machine. The only lower case m in the sql is by near the variable Ratingsum like in line 59. [SqlException (0x80131904): Incorrect syntax near 'm'.An expression of non-boolean type specified in a context where a condition is expected, near 'type'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +196 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +269 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 view_full_article.btnRating_Click(Object Src, EventArgs E) +565 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746</pre></code> Here is my button click sub in its entirety: 1 Sub btnRating_Click(ByVal Src As Object, ByVal E As EventArgs) 2 'Variable declarations... 3 Dim articleid As Integer 4 articleid = Request.QueryString("aid") 5 Dim strSelectQuery, strInsertQuery As String 6 Dim strCon As String 7 Dim conMyConnection As New System.Data.SqlClient.SqlConnection() 8 Dim cmdMyCommand As New System.Data.SqlClient.SqlCommand() 9 Dim dtrMyDataReader As System.Data.SqlClient.SqlDataReader 10 Dim MyHttpAppObject As System.Web.HttpContext = _ 11 System.Web.HttpContext.Current 12 Dim strRemoteAddress As String 13 Dim intSelectedRating, intCount As Integer 14 Dim Ratingvalues As Decimal 15 Dim Ratingnums As Decimal 16 Dim Stars As Decimal 17 Dim Comments As String 18 Dim active As Boolean = False 19 Me.lblRating.Text = "" 20 'Get the user's ip address and cast its type to string... 21 strRemoteAddress = CStr(MyHttpAppObject.Request.UserHostAddress) 22 'Build the query string. This time check to see if IP address has already rated this ID. 23 strSelectQuery = "SELECT COUNT(*) As RatingCount " 24 strSelectQuery += "FROM tblArticleRating WHERE Itemid=" & articleid 25 strSelectQuery += " AND ip = '" & strRemoteAddress & "'" 26 'Open the connection, and execute the query... 27 strCon = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("sqlConnectionString").ConnectionString 28 conMyConnection.ConnectionString = strCon 29 conMyConnection.Open() 30 cmdMyCommand.Connection = conMyConnection 31 cmdMyCommand.CommandType = System.Data.CommandType.Text 32 cmdMyCommand.CommandText = strSelectQuery 33 intCount = cmdMyCommand.ExecuteScalar() 34 intSelectedRating = Int(Me.rbRating.Text) 35 conMyConnection.Close() 36 'Close the connection to release these resources... 37 38 If intCount = 0 Then 'The user hasn't rated the article 39 'before, so perform the insert... 40 strInsertQuery = "INSERT INTO tblArticleRating (rating, ip, itemID, comment, active) " 41 strInsertQuery += "VALUES (" 42 strInsertQuery += intSelectedRating & ", '" 43 strInsertQuery += strRemoteAddress & "', " 44 strInsertQuery += articleid & ", '" 45 strInsertQuery += comment.Text & "', '" 46 strInsertQuery += active & "'); " 47 cmdMyCommand.CommandText = strInsertQuery 48 conMyConnection.Open() 49 cmdMyCommand.ExecuteNonQuery() 50 conMyConnection.Close() 51 Me.lblRating.Text = "Thanks for your vote!" 52 Comments = comment.Text.ToString 53 54 If Len(Comments) > 0 Then 55 emailadmin(comment.Text, articleid) 56 End If 57 'now update the article db for the two values but first get the correct ratings for the article 58 strSelectQuery = _ 59 "SELECT SUM(rating) As RatingSum, COUNT(*) As RatingCount " 60 strSelectQuery += "FROM tblArticleRating WHERE Itemid=" & articleid 61 conMyConnection.Open() 62 cmdMyCommand.CommandText = strSelectQuery 63 dtrMyDataReader = cmdMyCommand.ExecuteReader() 64 dtrMyDataReader.Read() 65 Ratingvalues = Convert.ToDecimal(dtrMyDataReader("RatingSum").ToString) 66 Ratingnums = Convert.ToDecimal(dtrMyDataReader("RatingCount").ToString) 67 Stars = Ratingvalues / Ratingnums 68 conMyConnection.Close() 69 'Response.Write("Values: " & Ratingvalues) 70 'Response.Write("Votes: " & Ratingnums) 71 72 UpdateRating(articleid, Stars, Ratingnums) 73 Else 'The user has rated the article before, so display a message... 74 Me.lblRating.Text = "You've already rated this article" 75 End If 76 strSelectQuery = _ 77 "SELECT SUM(rating) As RatingSum, COUNT(*) As RatingCount " 78 strSelectQuery += "FROM tblArticleRating WHERE Itemid=" & articleid 79 conMyConnection.Open() 80 cmdMyCommand.CommandText = strSelectQuery 81 dtrMyDataReader = cmdMyCommand.ExecuteReader() 82 dtrMyDataReader.Read() 83 Ratingvalues = Convert.ToDecimal(dtrMyDataReader("RatingSum").ToString) 84 Ratingnums = Convert.ToDecimal(dtrMyDataReader("RatingCount").ToString) 85 Stars = Ratingvalues / Ratingnums 86 If (Ratingnums = 1) And (Stars <= 1) Then 87 lblRatingCount.Text =" (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Vote" 88 ElseIf (Ratingnums = 1) And (Stars > 1) Then 89 lblRatingCount.Text = " (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Vote" 90 ElseIf (Ratingnums > 1) And (Stars <= 1) Then 91 lblRatingCount.Text =" (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Votes" 92 ElseIf (Ratingnums > 1) And (Stars > 1) Then 93 lblRatingCount.Text = " (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Votes" 94 End If 95 96 'Response.Write(String.Format("{0:f2}", Stars)) 97 'Response.Write("Values: " & Ratingvalues) 98 'Response.Write("Votes: " & Ratingnums) 99 If (Stars > 0) And (Stars <= 0.5) Then 100 Me.Rating.ImageUrl ="./images/rating/05star.gif" 101 ElseIf (Stars > 0.5) And (Stars < 1.0) Then 102 Me.Rating.ImageUrl = "./images/rating/05star.gif" 103 ElseIf (Stars >= 1.0) And (Stars < 1.5) Then 104 Me.Rating.ImageUrl = "./images/rating/1star.gif" 105 ElseIf (Stars >= 1.5) And (Stars < 2.0) Then 106 Me.Rating.ImageUrl = "./images/rating/15star.gif" 107 ElseIf (Stars >= 2.0) And (Stars < 2.5) Then 108 Me.Rating.ImageUrl = "./images/rating/2star.gif" 109 ElseIf (Stars >= 2.5) And (Stars < 3.0) Then 110 Me.Rating.ImageUrl = "./images/rating/25star.gif" 111 ElseIf (Stars >= 3.0) And (Stars < 3.5) Then 112 Me.Rating.ImageUrl = "./images/rating/3star.gif" 113 ElseIf (Stars >= 3.5) And (Stars < 4.0) Then 114 Me.Rating.ImageUrl = "./images/rating/35star.gif" 115 ElseIf (Stars >= 4.0) And (Stars < 4.5) Then 116 Me.Rating.ImageUrl = "./images/rating/4star.gif" 117 ElseIf (Stars >= 4.5) And (Stars < 5.0) Then 118 Me.Rating.ImageUrl = "./images/rating/45star.gif" 119 ElseIf (Stars >= 4.5) And (Stars <= 5.0) Then 120 Me.Rating.ImageUrl = "./images/rating/5star.gif" 121 End If 122 dtrMyDataReader.Close() 123 conMyConnection.Close() 124 End Sub
If you want to reduplicate the error, click over here and try to submit a rating: http://www.link-exchangers.com/view_full_article.aspx?aid=51 Thanks for helping me figure this out.
I have a execute sql task which insert data into a table, but before that I need to make sure no data is available for particular date. we have a table where we are defining date for which date we want to data, and we are fetching that particular date using ssis variable 'Date'. today I was trying to load data dor '2015-05-10'. But it gave me error like '[Execute SQL Task] Error: "Value does not fall within the expected range.'. Below are the my execute sql task query:
delete from STG_Shipped_Invoiced where Transaction_Date=? INSERT INTO STG_Shipped_Invoiced (div_Code, inv_inv_id, tot_Net_Amt, trans_date, trans_type, Created_date, Transaction_Date) select inv.DIV_CODE as Div_Code, inv.INV_ID as inv_inv_id, inv.TOT_NET_AMT as Tot_Net_Amt,
[Code] ....
Here I have defined a variable 'Date', where we are passing the date. Am i doing anything wrong with the delete query?
Hello,In VB/VB.NET, I can use an expression as such to evaluate true/false:Dim blnValue As Boolean = (SomeObject.Property = "Some value")Can I do this in T-SQL?declare @var bitset @var = ?Thanks.
Our end users is getting below error, when they try to connect to our database:
"Cannot generate SSPI Context."
That is Windows based application and we have done everything like restart our DB Server , reinstall exe in users system, but still issue is same.The issue has occured from when DB Server has restarted and at the same time few users are connected. before that it was working fine. we could not find what is issue.
Hi - I'm using VWD, VB, and created a dataset/tableadapter to insert a record into a SQL Express database. The database has a couple of columns, but specifically a Datetime column. Using the default insert created, I have the following code: Dim da as New partyDetailsTableAdapters.partyDetailsTableAdapterProfile.partyid = da.Insert(Profile.UserName, tbName.Text, DateTime.Now) The compiler throws an error though, saying 'Expression expected' - and it squiggles an underline under the closing bracket after DateTime.Now - I have no problem if I'm trying to update a record using: Dim da as New partyDetailsTableAdapters.partyDetailsTableAdapterDim pd as partyDetails.partyDetailsDataTablepd = da.GetPartyDetailsByID(Profile.partyid)da.Update(Profile.UserName, tbName.text, DateTime.Now, Profile.partyid, Profile.partyid) Have I an error in my Insert section? Thanks for any help, Mark
I wish to make a query with if condition Implemented in a database sql server 2008 R2.
I would like to set up a system FEFO (first expired first out) based on batch number based on the dates of Lapsed but since I struggle to put my request in place. The principle of my request is:
if amount of movement (QTEMVT)> = amount entered by the user via a user interface then withdraws the amount entered by the user in the batch (NUMLOT) the amount of movement of the item that lapses the first (execution of my request). else if amount of movement (QTEMVT) <amount entered by the user then the difference between the amount entered by the user and the amount of movement (QTEMVT) (execution of my request) and the following conditions: the amount of movement (QTEMVT) = the amount of movement (QTEMVT) of this item stored in my database and the amount of movement (QTEMVT) <> 0 by taking the difference of the item requested directly from the batch (NUMLOT) of the item directly after lapses.
Basically I want to set up an item management query based on batch number and expiration dates.
ps: QTEMVT = quantity of the item stored in my database NUMLOT = batch number items DATFABRIC = manufacturing date items DATPEREMP = expiry date items
Here is my request:
SELECT f.NUMLOT,f.DATFABRIC,f.DATPEREMP,f.QTEMVT FROM FAIRE f INNER JOIN mouvement m ON f.CODMVT = m.CODMVT INNER JOIN TYPE_MOUVEMENT tm ON tm.CODTYPMVT = m.CODTYPMVT INNER JOIN SOUS_SITE ss ON ss.CODSOUSIT = m.CODSOUSIT INNER JOIN SITE s ON s.CODSITE = ss.CODSITE
Select SEC.EntryQty from Item.StockEntryChild SEC where SEC.CEntryNo = ( select SEM.EntryNumber from item.StockEntryMaster SEM where SEC.CBranchID=SEM.BranchID and SEC.CEntryNo=SEM.EntryNumber and SEM.MasterID=1 and SEM.isStockIn=1 )
I am looking for a query where in I can have a conditional statement within inner join after ON statement as shown below.
Declare @roleid int select @roleid = roleid from Role where Name ='Admin' select empid,empName,deptName from employee em inner department dm on CASE when @roleid>0 then 1=dm.RoleId else em.RoleId=dm.RoleId end
Obviously, I'm new to SQL Server, because I'm still scratching my head wondering where in the world the boolean data type is. Can anyone please explain to me how boolean data types REALLY work in SQL Server? I've created a few Bit type fields, but when using a datagrid, they sometimes show up as 0 and 1 and sometimes as True/False, and I can't see a difference in how the grids (or fields(!)) are set up that would explain this. Any insight would be greatly appreciated!
What data type should I use as a replacement of Boolean in SQL server? I need to create a table column with a "two-state" data type and must ensure, that only one record can hold value "True" in this column... Any ideas? Thanks
I know this is a stupid question, but I've searched and can't seem to find it.
What do you use in SQL when creating a table and the field is a "yes/no" type field? In VB isn't that a boolean data type? What is the corresponding SQL data type?
I have a stored procedure with several insert into statements. On occasion one of the insert into queries doesn't return any data. What is the best way to test for no records then, skip that query?
I only want to UniqIds that only have the CODE of ABC... and if it contains ANYTHING other than ABC then It doesnt return that UniqID... Now keep in mind there's multiple different codes.. I'm just looking for a bit of code that drops any ID's that don't have my criteria.
I am creating a complex expression in a Send Mail Task for the MessageSource. The expression contains the values of many package variables. I want to conditionally include some variables in the expression depending on what value they contain.
Is it possible to create conditional logic (if then else) within an expression? I've got to conditionally include 8 different variables.
Hello:Is it possible to use CASE expression in AND condition? i.e.------------------------------------------CREATE PROC spBlah( @id INT, @val INT)ASSELECT * FROM aTableWHERE tableID = @idAND ( CASE @val WHEN 1 THEN otherCol = someValue END CASE @val WHEN 2 THEN otherCol != someOtherVlaue END )-------------------------------------------
I am working to move an application from MySQL to SQL Server. The person who developed the MySQL application has little database experience, and took some shortcuts that the lax nature of MySQL allows. One query with which I am struggling looks something like this:
SELECT StartTime + Offset, min(Sensor), max(Sensor) FROM SensorData WHERE SensorID = @SensorID AND StartTime + Offset > @BeginTime AND StartTime + Offset < @EndTime GROUP BY (StartTime + Offset) / 100 ORDER BY StartTime + Offset
What we are trying to accomplish is to return minimum and maximum sensor values over a number of periods between the BeginTime and EndTime. When I run this query in MySQL on a sample dataset, it returns a small number of rows, with each one being the min/max values for a portion of the overall period.
Under MS SQL Server 2008, SP3, I get the two following error messages: Column 'SensorData.StartTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Column 'SensorData.Offset' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I am developing the SSIS and stuck on copying specific files.
1. We receive CSV file to our drive on a daily basis.
2. The csv file name has the last 8 digits formatted with the yyyymmdd. For example, the file name might be abcdef_20150226.csv This means it will be our CSV file for today, Thursday, February 26, 2015.
3. There are a lot of files in this directory.
[URL]
What we would like to do:
Add the constraints (or expression) that will copy the files from this directory to another directory that have the date equivalent to Monday only. For example, the file abcdef_20150226.csv will not be copied because it is Thursday file. But the abcdec_20150223 will be copied to a new Directory because it is Monday.
In my report I want to use OR condition instead of AND in Table Filter Expression. By default this option is disabled in VS2005 Pro. How do I enable this? or is there any other way to do this? Thanks in advance.
I have a page for inventory price entry that I have used for a while. Now I need to add a checkbox for whether or not the price includes shipping. I added the checkbox to the form and had it posting 'True' or 'False' to the database as nchar(10) data type. When the gridview pulls up the data, I have the Item Template like this, so it shows a disabled checkbox either checked or not: <asp:CheckBox ID="CheckBox2" runat="server" Checked='<%# Convert.ToBoolean(Eval("Shipping")) %>' Enabled="False" /> This works fine for displaying the values, but I copied the checkbox to the Edit Item Template, but did not disabled this one. At first, I didn't change the databindings, leaving it Convert.ToBoolean(Eval("Shipping")), which allowed me to go into Edit mode, change the checkbox, then click update. At which point it would return to it's original state (meaning Update wasn't actually updating). However if I change the databindings, then the page won't display. I checked the SQL statement, and sure enough, it has theUpdateCommand="UPDATE [PartsTable] ... SET [Shipping] = @Shipping... WHERE [PartID] = @original_PartIDAfter fiddling with the sql statement, now I get Object cannot be cast from DBNull to other types. I think that means that the checkbox is sending a null value to the database.Any insight as to how to get this to work is much appreciated. Thanks in advance.
What I am trying to do is count persons in buckets "non-recidivists" and "recidevists" based on how many bkg_nbr they have per STATE_NBR. If they have more than 1 bkg_nbr per STATE_NBR then put them in the "recdivists" bucket. If they only have a 1 to 1 then put them in the "non-recidivists" bucket.
Hi, I have declared an internal paramter and given the default non queried value as
select @split = case when max(rowid)%2 = 1 then (max(rowid)/2) + 1 else max(rowid)/2 end
and when i run it i am getting the error -- The property 'DefaultValue' of report parameter split doesnt have the expected type. What should i do to solve it. Any help is appreciated.