I am not sure this is the right place to post my question, if not please let me know where I should post it. I have been struggling with it for 2 hours:(.
I have a query that works fine in the sql query analyzer. I need to put it in the asp.net page using vb.net. When I cancatenate all the pieces together with the 'case when', it fails. Is it because of the single quotes or what?
Can someone please take a look at the simple query?
New System.Data.SqlClient.SqlDataAdapter("SELECT T1.C1 as D#," & "(case When T1.Production_Date is null then " & "' " & "'" & " else T1.Production_Date end)" & ", T1.Production_Plant as Location, convert(int,T1.Inv_Level) as Inv_Level, T1.Container_Size, T1.Level_ID, T1.Unit_of_Measure, T1.Performance_Comments, T1.Product_Number, T1.Unit_ID,Products.P_Name FROM T1 INNER JOIN Products ON T1.Product_Number = Products.Product_Number and T1.Unit_ID = Products.Unit_ID WHERE ((T1.Product_Number)=" & "'" & strPID & "'" & ") AND (( T1.U_ID=" & "'" & strUID & "'" & "))", con)
I am looking to modify this Case Statement. Where it says ELSE '' I need it to display the actual contents of the cell. 1 = Yes , 0 = No, (any other integer) = actual value. Right now if the value is anything other than 1 or 0, it will leave the cell blank. CASE dbo.Training.TrainingStatus WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '' END AS TrainingStatus Thank You.
I'm trying to work my way through the steps of using a User Id and Password in a connection string.
I'm working with SQL 2005 Express, VS2005, in the development server. Got an error I can't get around...tried it several diffent ways on a slightly more complicated test site...no joy...so went to the MSDN tutorial...made the most "vanilla" test I could think of, and still can't figure it out.
I thought it would be simple enough that I could post the whole thing (below)
The test works fine with Integrated Security = True in the connection string. When I remove that phrase, I get the error:
{"CREATE DATABASE permission denied in database 'master'. An attempt to attach an auto-named database for file E:MyPathApp_DataVSST_DB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."}
This occurs on the cn.Open statement below.
It gets past the login, so I know that the SQL User and password match up correctly.
========================= Page Code Behind (no controls on page) ------------
Imports System.Data Imports System.Data.SqlClient
Partial Class _Default Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim cn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("VSST_CN").ToString())
Dim cmd As SqlCommand = New SqlCommand("SELECT COUNT(*) FROM VSST_Table", cn) cn.Open() Dim rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection) rdr.Read() Response.Write(rdr(0).ToString())
End Sub End Class
================= The DB
Table VSST_Table, ID is int, primary key, identity Field1, Field2, Field3, Field4, Field5 are varchar(50)
I added one record ("A", "B", "C", "D", "E") to the table through VS2005 Server Explorer
This shows up in Count = 1 from running the page when Integrated Security = True
================= In SSMSE: (this is ALL I did, tried to use the minimum so not to confuse...)
I added the SQL Authentication Server level user "VSST" with the password "vsst123" (and the login works, as noted above)
I attach the .mdf
I add VSST to the Database Users, and give it db_owner
I add VSST to the Table with all permissions checked.
=====================================
I can't figure this out. This is a very vanilla test and I'm stumped. I'm about to give up on SQL Authentication entirely (at least for now), and just try to filter my inputs for SQL Injections...that's the only reason I have (at this stage in my biz plan) for needing SQL Authentication. On the other hand, I really don't like being this stumped on something that is so widely promoted as a common practice.
In Excel they are showing Sale as -ve quantity and purchase as +ve quantity.
The database has quantity always as +ve figure and a separate column "isPurchase" set to true or false depending on whether purchase or sale.
So I need Derived Column to return a bolean (or int) depending whether quantity is positive or negative. I tried each of the following in the Expression but all of them were invalid expressions.
CASE WHEN [TotalQuantity] > 0 THEN 0 ELSE 1 END
If [TotalQuantity] > 0 THEN 1 ELSE 0 END
IIF ([TotalQuantity] > 0, 1,0)
Can anyone help me with correct syntax, or correct Data Flow Transformation if Derived column is wrong.
I am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?
I am trying to make a single display page for an author's books.the books page only displays books of a type "type" (novels, non-fiction, etc)I would like to make it so that it can also show all books if "type" isn't selected. I THOUGHT the string would look like this: <asp:SqlDataSource ID="SqlDSBooks" runat="server" ConnectionString="<%$ ConnectionStrings:csK2Reader %>" SelectCommand="SELECT * FROM [Books] ( If @Type <> "" then WHERE ([Type] = @Type)) ORDER BY [SortDate] DESC"> But it doesn't seem to want to work. I get a "server tag is not well formed" error.
I have a table called "member" that has two fields (amongst others) called "firstname" and "city"I want to return a list that sorts people who live in the same city, but does not return people who do not have a city in common.For example if this is my data:Karen - ParisMike - RomeTim - Dallas
Jim - ParisChris - DallasJohn - ManhattanSarah - OrlandoDavid - DallasThe query would return this:Dallas - ChrisDallas - DavidDallas - TimParis - JimParis - KarenI have this SQL so far: Select city, firstname From member Order By city, firstnamebut it includes all the cities including those that only have 1 resident.Can someone tell me how to do this?Thanks a lot,Chris
hi,i have three fields in the database tablep1 p2 rank10% 20% 3 21% 40% 2now i am passing the input as 15%...this 15% is related to 3rd rank.. so i need the o/p rank as 3..if the p1 and p2 datatypes are varchar..then how we can we write the query for that one tgo get the rank as 3.Ramesh
Hi All Can anyone tell me what this simple SQL query would be: Find all the words "black", "dvd" and "player" from any of the fields "Product", "Brand" or "Description".
I am trying to select players from a database based on the userid not having any user's name in it with an order by ASC. I basically want all the players that haven't been taken. Here is what I came up with and it gives me empty.
strSQLA="SELECT UserID, playerName from Allplayers WHERE UserID = '' ORDER BY playername ASC"
I hope it's considered appropriate to post a SQL query question here; it's not I'll gladly post this question elsewhere. That having been said, I've run into a simple but quite tricky SQL query and was wondering if the community might be able to help. Here's what's going on: I have a "Documents" table. Let's call it DI have a "Benefits" table. Let's call it B.There is a many-to-many relationship between D and B so I have a third table to store the relationships. Let's call that table BD. When I create a document it creates a row in D. Let's call that D1, D2...DN. When I create a benefit it creates a row in B. Let's call that B1, B2...BN When I associate a document to a paritcular benefit, let's say that if D1 and B2 are associated, that I created row B2-D1 in my BD table. Now here's what my challenge is. Suppose I have these entries in my tables: D: D1, D2, D3B: B1, B2, B3 Table BD:B1-D1B2-D1B2-D2 When the user goes to add documents to benefit B1, I want to show the user only documents that are not yet added to B1. In this case, I want to show the user D2 and D3 but not D1 since that one's already added. How do I write a SQL query to do this? Here's what I've been using, but it shows too many results (e.g. included D1, D2, D3) SELECT D.*, BD.*FROM Documents AS d LEFT JOIN rel_BenefitsDocuments AS bd ON d.doc_id = bd.doc_idWHERE (benefit_id <> @benefit_id OR benefit_id IS NULL) Any thoughts?
I want to write a sql query for an asp page which will display only unique rows from the specified column along with the number of count for each unique row.
Example:
Table that I want to query
Last Name || First Name Gates || Bill Boyce || Mike Gates || Bill Gates || Phil
Results I want:
Last Name || First Name || Count Gates || Bill || 2 Boyce || Mike || 1 Gates || Phil || 1
I am new to SQL and this question may be most easiest to many of you. Here is what I need.
I have two identical tables (exactly the same in structure) having a compound primary key with a combination of 3 columns. Can someone give me the most efficient query that fetches all the rows from table1 that are not in table2.
from sales_contact sc , invoice i , invoice_line_item il , sales_region_special_section srss , sales_region sr , issue_date idd
where sc.sales_contact_id = i.sales_contact and i.invoice_id = il.invoice and srss.sales_region = sr.sales_region_id and il.issue_date = idd.issue_date_id and srss.invoice_line_item = il.invoice_line_item_id
order by sc.sales_contact_id
================
heres some of the result set I want only the records with the greatest id for each unique sales contact...
AF85F32E-8E34-4C40-9468-00148A34E903, 41, N AF85F32E-8E34-4C40-9468-00148A34E903, 42, N 5D26328A-192B-4E4E-9B34-010C4E077CF8, 77, N 5D26328A-192B-4E4E-9B34-010C4E077CF8, 70, N 5D26328A-192B-4E4E-9B34-010C4E077CF8, 34, BC B44C914E-6001-40CE-8AB6-0126BD572D45, 25, NW B44C914E-6001-40CE-8AB6-0126BD572D45, 26, NW B44C914E-6001-40CE-8AB6-0126BD572D45, 24, NW B44C914E-6001-40CE-8AB6-0126BD572D45, 28, HC B44C914E-6001-40CE-8AB6-0126BD572D45 , 28, NW
this would be an ideal result set using the data above...note there are situtations where there are n* for a greatest issue id.
AF85F32E-8E34-4C40-9468-00148A34E903, 42, N 5D26328A-192B-4E4E-9B34-010C4E077CF8, 77, N B44C914E-6001-40CE-8AB6-0126BD572D45, 28, HC B44C914E-6001-40CE-8AB6-0126BD572D45, 28, NW
This should be easy, I'm just having a brainfart at the moment, can't remember how to do this:
Say I have a bunch of records in a table, with an ID field as Key. I want to return the count of times each ID shows up, so for the following example data:
This should be obvious but for some reason I can't see it.
When this is done I'm going to join in another table to get a name based on the ID with the highest count. I believe it's faster to do a TOP 1 here rather than after the join, but I want to verify that while I'm at it also.
I am a complete newbie to SQL. I have a simple query to make in SQL, but I'm not being able to construct the statement properly. I've already tried googling this info, but with little luck. If someone could help me here, I'd be very thankful.
The problem:
I have a table with a list of questions. All questions have a serial number. The query has to return whether a particular question is the last one in the table or not (according to serial no.)
Logically, this is the query -
Code:
select islast = 0 if count(*) from question where serialno > $serial != 0 else select islast = 1;
The '$serial' part will get replaced dynamically before query execution through another program.
Any idea how to code this in valid SQL? I would like to make it a simple statement and not a stored procedure. Thanks.
Hey.. hope someone can help, i desperatly need some assistance. I have a few query issues but first things first.. one is that i have a table of messages and users having an online conversation, so fields are msg id, subject, topic, message, user, reply to and time it was sent. Im trying to do a query that will tell me which user sent the most messages, ive tried using count and stuff like that but not really getting anywhere. It will basically have to count each time a user has spoken and then give me the name of the user that has sent the most messages, ive been playing around with stuff like SELECT Count(*) AS Expr1 FROM Table1 WHERE User='andro8472' OR User='bumies';
That will count the times those users have spoken but cant get further
any help would be greatly appreciated really stuck at the mo..
I'm having a bad day as I just cannot get a query to work and its doing my head in and it should be a simple query.
I'll try and explain what I need.
Have a simple query with one join, this is it so far:
SELECT DISTINCT Replace(StaffName,'''''',' ') AS StaffName,OutReason,OutStartDate ,OutEndDate,OutStartTime,OutEndTime,OutID FROM vwOutOfOffice LEFT OUTER JOIN vwStaffList ON OutUser=StaffID WHERE OutComplete=0
What I'm having trouble with adding to the WHERE is as follows, I need a list of Staff who are out of the office based on the various criteria:
Example Current date=29/02/2008 Current time=14:00
Start date---End Date----Start Time--End Time--Should Appear 27/02/2008--28/02/2008--09:00------17:00-----No (because of date) 28/02/2008--03/03/2008--08:00------17:00-----Yes 28/02/2008--29/02/2008--17:00------17:00-----Yes 29/02/2008--29/02/2008--12:00------14:00-----Yes 29/02/2008--29/02/2008--14:30------17:00-----No (because of time) 02/03/2008--02/03/2008--08:00------10:00-----No (because of date)
There may be others I've missed but as long as the dates combined with the times match correctly I need the records to appear.
Please help before I end up putting my head through the monitor.
ID Number NameJoin DateSex 101 Jason01/02/1996M 117 Robert06/25/1999M 113 Doug08/12/1999M 161 Marian07/30/1998F
Please let me know the SQL queries for the following questions for the above table:
1)Write a Select statement that selects the persons whose Type is "M" and who have joined in the last four years and place the result in an array. Today's date is 12/31/1999.
2) Write a Select statement that selects the persons who do not have unique ID Number and place the results in an array.
3) Write code to sort the array by Name + Join Date. Assume that the array has already been created and is sorted in the same manner as the table above. The Join Date is of type smalldatetime. The Name field should be sorted case insensitively.
How do I retrieve all rows from a middle_name column that have a character length of <= 2. This table is populated with middle names and middle initials. I want to isolate the middle initials only.
SELECT ('Section : ' + F.Section + ' ' + (SELECT S.SectionName FROM SFM_Section AS S WHERE S.Company = F.Company AND S.Department = F.Department AND S.Section = F.Section ) ), F.Variety, F.Category, isnull(SUM(F.AreaCutCurrent), 0) AS AreaCutCurrent, isnull(SUM(F.TonnageCurrent), 0) AS TonnageCurrent, isnull(SUM(F.YieldCurrent), 0) AS YieldCurrent FROM SFM_Field AS F WHERE F.CropYear = 2007 and ActiveFlag = 1 and Section = 04 GROUP BY F.Company, F.Department, F.Section, F.Variety, F.Category ORDER BY F.Section, F.Variety, F.Category
that returns me the following results:
Section:04 Region Haute|M0052/78|R07|1.84 |137.64 |74.804 Section:04 Region Haute|M0695/69|R01|37.71|2817.65|434.009 Section:04 Region Haute|M0695/69|R02|35.08|2282.33|523.234 Section:04 Region Haute|M0695/69|R05|21.2 |1563.01|147.579 Section:04 Region Haute|M0695/69|R09|6.52 |484.39 |74.293 Section:04 Region Haute|M0695/69|R11|0 |0 |0 Section:04 Region Haute|M1397/86|R04|8.35 |793.61 |190.141 Section:04 Region Haute|M1400/86|GS |18.03|2093.91|116.135
What should I do if I don't want the records that the sum is equal to 0 to appear in my results? (Like the 3rd row from the end)
You probably seen this Q before but I have no idea what to search and and therefor any direction would be appreciated.
I have three columns table, two are int and one is datetime (for sorting). I would like to query all values witch are unique within the two integers, however, in some cases data looks like this: int1 = 1 int2 = 2 and another record like: int1 = 2 int2 = 1
this pair should be considered as a duplicate and second record should not come with the results set.
Hi AllI know that I am supposed to submit a schema of my table, but it is a verybasic one so I'll crack straight on with my query.In essence I have a 3 column table called STOCKTRANS, which consists ofSTOCKCODE, TRANSACTIONDATE and TRANSACTIONTYPE.Each time a user orders a stock item I insert the stock code and the datethey ordered it into this table, egSTOCKCODE TRANSACTIONDATE TRANSACTIONTYPEIVP 1-4-04 1STP 31-6-04 8KGC 8-7-04 6IVP 11-8-04 8etc etcetcWhat I want to create is a simple query that informs what the last orderdate was for a stock code. As you can see from the above example, I mayhave hundreds of the same stock code with different dates so I thought thatthe following query would work:select STOCKCODE, MAX(TRANSACTIONDATE) as 'LastOrderDate'from STOCKTRANSwhere STOCKCODE = 'IVP' and TRANSACTIONTYPE in (1,8)But it doesn't.NOTE: the reason for the transaction types of 1 and 8 is because these arethe types I want. The other types are used for credits, returns, etc.First of all the above, brings me back loads of rows when all I want is:IVP 11-8-04 << this being the last date of order for this stock item.Just as a test I removed the STOCKCODE out of the select and found that Idid get my one liner, but for some reason I was only getting the last datefor transaction types 1 only - not the last date for a combination of 1 and8 transactions.Can you help??Many thanksRgds Robbie
I have Two Tables, TableA and TableB, both containing a common field,Feild1.How do I find all records in TableA, where Field1 is not in TableB?Regards,Ciarán
I know this is super-basic, but I'm a newbie and I can't get it to work...I'm trying to call a stored procedure that has a datetime as one of itsparameters. How the heck do I get a datetime?? I'd even settle for knowing Iwas declaring variables correctly...DECLARE @Date datetime (right?)???EXEC usp_AddRequest 313,'E',@Date,'QUAL'^^^^^ <- this is the parameter that wants to be adatetimeHow do I make Date correspond to an actual date/time? How do I assign it tobe equal to SELECT GETDATE()? Why doesn't SET @DATE = SELECT GETDATE() work?And why the heck is it so hard to find the answers online?? I've Googledendlessly and found nothing...--Christopher Benson-Manica | Jumonji giri, for honour.ataru(at)cyberspace.org |
