.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

query negative and positive values and not their boolean counterpart

Posted By:      Posted Date: September 06, 2010    Points: 0   Category :Sql Server
Good Day Need help.. I have this simple table, CREATE TABLE [dbo].[sample] ( [id] [int] NOT NULL , [number] [int] NULL , ) ON [PRIMARY] i wanted to make a select query that would display two columns(positive, negative) the sql statement that i made: "select (sample.number > 0) as positive, (sample.number < 0) as negative from sample " what happened is that, it displays the boolean counterpart and not the value.. I wanted to show this kind of data: substitute the "False" value into 0 and "-1/true" value with the exact absolute numerical value.   |debit||credit| |   0   ||  200 | |   0   ||  57   | |   50 ||  0     |   is this possible?  

View Complete Post

More Related Resource Links

Application to Constantly query Table Values and Refresh with most recent value

Hello, I must apologize in advance because I am not sure if this is the correct place for this question. I am looking for ideas on how to build an application that in the most basic sense contains a field with the most current data from a table in a SQL Server database (this would be based off of a datetime timestamp in the table). I am new to this kind of problem and am unsure where to start looking for solution ideas. The table value updates variably and could possibly update every second to every hour. I would like to design an application that would be lightweight to both the server and the user's machine. This would take the place of an unneeded report on my Reporting Services server. Any and all suggestions would be welcome because I am not sure of options I even have on this. Thanks in advance for your time and consideration, Matt

Need to add hard coded values to LINQ query result

I am running a LINQ query which populates a list used by a DropDownList, I need to insert an "Unassigned" value to the list. The function below queries the values correctly from the db but does not insert the "Unaasinged" value to the list. Please help.   public List<KeyValuePair<int, string>> GetTestList() { KeyValuePair<int, string> item = new KeyValuePair<int, string>(0, "Unassigned"); using (DataClassesDataContext db = new DataClassesDataContext()) { var Query = from records in db.TEST_SUITEs orderby records.TEST_SUITE_ID select new KeyValuePair<int, string> ( records.TEST_SUITE_ID, string.Format(records.TEST_SUITE_ID + " " + records.SUITE_TYPE + " " + records.SUITE_NAME) ); //Query.ToList().Insert(0, item); Query.ToList().Insert(0,item); return Query.ToList(); } }  

Number of query values and destination fields are not the same

If RadioButton1.Checked = True Then rbtn = 1 Else rbtn = 2 End If ' Our insert command strSQLQuery = "INSERT INTO tblJobs " _ & "(filingDate, filingDay, reportDate, reportTime, company, jobsite, location, duration, DrugTestReq, foreman, instructions, jobSteward, nPlumbers, nFitters, nWelders, nTradesmen, nPlAppr, nFitAppr, nServTech, nHelpers) " _ & "VALUES (@DTValue, @TextDay, @DTValue2, @TextValue, @TextValue2,@TextValue3,@TextValue4,@TextValue5,@rbutton,@TextValue6.@TextValue7,@TextValue8,@NumVal,@NumVal2,@NumVal3,@NumVal4,@NumVal5,@NumVal6,@NumVal7,@NumVal8)" ' Create new command object passing it our SQL insert ' and telling it which connection to use. objCommand = New OleDbCommand(strSQLQuery, objConnection) ' Add parameters that our SQL command takes: 'radio button 'OleDbType. objCommand.Parameters.Add(New OleDbParameter("@rbutton", OleDbType.LongVarChar)) '- objCommand.Parameters.Add(New OleDbParameter("@DTValue", OleDbType.Date)) objCommand.Parameters.Add(New OleDbParameter("@TextDay", OleDbType.LongVarChar)) objCommand.Parameters.Add(New OleDbParameter("@DTValue2", OleDbType.Date)) objCommand.

Negative values sums up incorrectly

Hello, I have calculated measure that removes negative values. Problem is the Total is summing up incorrectly. Here the script of the measure. Create Member currentcube.[Measure].[Amount2] as iif ([Measure].[Amount]>0, [Measure].[Amount], null)   Below is sample result. Customer        Amount      Amount2 A                    100            100 B                    -50             C                    100            100 D                    100            100 E                     100            100 TOTAL          &n

Showing positive/negative sign in the output of Int32.ToString(string)

Hi,   Now this may be really simple but I'm unable to make it work or find it in MSDN. What is the format specifier for showing +/- sign of an Int32? I have tried "+0", "-0" and "+-0" and all of them give wrong results for either positive or negative or both kind of values. Note: The specifier should return "+0" for 0.

How to Create an MDX Query Parameter to Select 30 Values from a Dimension?

Hi, I'm using SSRS 2005 to report on an SSAS cube that contains a Procedure dimension.  I don't need to use members of this dimension in my report, but rather need to select records (patients) where their chart has one or more of the codes.  I've researched this today and cannot locate the best approach.  Thus far, I've attempted to create an MDX query parameter as part of my dataset.  However, I don't know whether this is the correct approach, and how to structure the syntax so that only records with one or more procedures are included in the report?  If so, what is the proper MDX syntax for setting my Procedure code equal to the query parameter? Thanks, Sid

Prevent SQL Injection when using values from a ListBox in the query

Hello,I'm using a List Box to get multiple values that will be used in a query.I can loop over the List Box and create the string.i.e. 'blue','red','purple'The string is used in the query: SELECT * FROM TABLE1 WHERE COLOR IN('blue','red','purple'). Is there a way to parametrize multiple values? @COLOR='blue','red','purple'What will be the best practice to prevent SQL injections in this scenario?

How to pass string values to parameterized sql query in Clause?

Hi,I'm using parameterized sql query to get data from database string query = "Select * from employee where employee_city in (@value)";strign city ="'NewDelhi','Bangalore','Mumbai'";I'm using following code to achive thisDataSet ds = new DataSet();SqlConnection con = new SqlConnection("Server=localhost;....");SqlCommand cmd = new SqlCommand();cmd.CommandText =query;SqlParameter param = cmd.Parameters.Add("@value",SqlDbType.VarChar);param.Value = city;SqlDataAdapter dap = new SqlDataAdapter();dap.SelectCommand = cmd;dap.Fill(ds);But this is not giving the result.If run the query in SQLServer query window as "Select * from employee where employee_city in ('NewDelhi','Bangalore','Mumbai')", records are there.But the same query will not return any records from ADO.Net.How to solve this?Thanks,Ashokan

Default values on query strings?

Hi!I have alot of query strings on one of my seartch pages. The problem is that even if the corsponding setting is not set by the enduser it will be visible in the URL. Is there a way to avoid this?In this case the Controller actions take an object of the following class : public class AdList { public AdList() { this.Page = 1; this.ModelViewAd = new PagedList<ModelViewAdList>(); this.CS = new CategorySelect(); this.LS = new LocationSelect(); } public PagedList<ModelViewAdList> ModelViewAd { get; set; } /// <summary> /// SeartchString /// </summary> [DisplayName("Sökord")] public string S { get; set; } /// <summary> /// CategorySelection /// </summary> public CategorySelect CS { get; set; } public LocationSelect LS { get; set; } /// <summary> /// Paging /// </summary> public int Page { get; set; } [DisplayName("Säljes")] public Boolean O1 { get; set; } [DisplayName("Köpes")] public Boolean O2 { get; set; } [DisplayName("Bytes")] public Boolean O3 { get; set; } [DisplayName("Uthyres")] public Boolean O4 { get; set; } [

Sending Query values in Fast queries are getting double escaped



I'm trying to send a query string using javascript to a Search centre Result.aspx page

I'm trying to send a value for s(scope) to search only for Intranet web pages. My when I submit this form I'm getting double escaped.

I'm actually trying to search for sdfsd in Intranet Web Pages scope my query string got converted to this-



%2b = + which I don't want.


jQuery("#QuickSearch").attr("method", "GET");<br/>
  jQuery("#QuickSearch").attr("action", gRes + "/searchcentre/pages/results.aspx");<br/>
  searchterm = trim(jQuery("#searchvalue").val())<br/>
  var validEntryPattern = (searchterm.length > 0);<br/>
  if (validEntryPattern == true) {<br/>

Assign Query String Values to Selectcommand parameter in




I have two pages EventList.aspx and EventDetail.apsx. When user selects any events [basically list item] on EventList.aspx page [all events are rollup in single sote collection] in am redirected to EventDetail.aspx page with QueryString ListID and ListItemId.

On EventDetail.aspx page i am using


"<Webs Scope='SiteCollection'></Webs><Lists><List ID='{ListID}'></Li

SQL Statement to Query Boolean Data Example


Hello All,


Looking for a sample sql statement to query a db table that boolean data.


Here is the scenario...

Records are lodging businesses with fields for each of the amenities.  Example of fields below.








After db is complete users will need to be able to query the db by checking checkboxes of the amenities they desire, click search, and have only those businesess appear with relavent amenities.

What might the a sameple sql statement look like for this?  FYI... at this time unknown how many amenities fields there might be and may need to add new amenities at any time.

Select * From TableName Where.....




Retrieving multiple values from a select query



This code works just fine for me to select * from x,y,z. However what I want to do is retrieve the values return and assign them to variables. How do I go about this? Thanks in advance.


 Using MyConnection As New SqlConnection("Data Source=sql2008.aspnethosting.co.uk;Initial Catalog=xxx_xxxx;Persist Security Info=True;User ID=xxx_xxx;Password=xxxxx")
            Dim MyCommand As New SqlCommand("SELECT txfrequency,mode,details FROM frequencies WHERE county = @location", MyConnection)
            MyCommand.Parameters.Add("@location", SqlDbType.NVarChar).Value = e.PostBackValue
            Dim r As SqlDataReader = MyCommand.ExecuteReader()
            GridView1.DataSource = r
            'SELECT COUNT(*) FROM TableName WHERE SomeWhereClause 
        End Using


Forum FAQ: How do I hide repeating values in query results?



Sometimes, we need to hide some repeating values in query results.

For example:

CategoryId   SubCategoryId     SubCategoryName

1            11              subA

1          &

convert result to positive if negative?



i have a select statement below. The result of r1- @r1 may be positive or negative.

i want it to always result as positive, so it is a negative number then it to show the same number as positive. can i do this?


r1 = 7, @r1 = 4

r1 - @r1 = 3


if r1 = 5, @r1 = 6

r1 = @r1 = -1, but i want this to be 1, because i am trying to work out the difference between the two numbers.

can this be done within a select statement? my select statement is:

SELECT     imageurl, (r1 - @r1)  AS difference

Query Builder - Show values where date is greater than DateTime now


Visual Web Developer 2010 Express

ASP.Net and C#


Hi Guys,

My issue is that I am pulled three tables together using the Query Builder and I only want to show data where a specific date is greater than todays date. Here is what i have so far:

SELECT        salesitems.sona, salesitems.sonitem, ZOverdueOrders.ZOOType, ZOverdueOrders.ZOOReason, salesitems.sduedate, salesitems.soitemstate
FROM            salesorders INNER JOIN
                         salesitems ON salesorders.son = salesitems.sona LEFT OUTER JOIN
                         ZOverdueOrders ON salesitems.sona = ZOverdueOrders.ZOOSON AND salesitems.sonitem = ZOverdueOrders.ZOOSONI
WHERE        (salesitems.sduedate > CONVERT(DATETIME, '2010-09-01 00:00:00', 102))
ORDER BY salesitems.sona, salesitems.sonitem

Where it says '2010-09-01 00:00:00' I want that to be the todays date.


Is this possible? Can anyone help please?

Thanks in advance for any help!!!!




ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend