The data I have has a 1 in for each Yes answer and a 2 for each no answer. I just want the select statement to show the word yes when there's a 1 and the word no when there's a 2. I don't need to update or change the database. Could anyone lead me in the right direction here? Thanks
View 4 RepliesAn ADODB error spawn everytime I query more than 15 fields in a specific SQL Table. My sql statement is something like this:
set rs=server.createobject("ADODB.Recordset")
rs.open "select * from mytbl",application("mycon"),1,3
And the error is something like this:
"you can't query more than the maximum field "
Hi,Is there a way to exclude fields in a query other than just includingthe ones you want. If there are 20 fields and you want to see all but3, it would be a lot easier to exclude the 3.Thanks
View 8 Replies View RelatedI thought this was working, but apparently it was not. I was wondering how I would create a SELECT Statement for values that are blank (Equal to ""). I really could have swore that this was working, but I guess it wasn't.
Here is my code:
Code Block
try
{
string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Environment.CurrentDirectory + @"MyDB.accdb;Jet OLEDB:Database Password=MyPassword;";
// create an open the connection
OleDbConnection conn = new OleDbConnection(conString);
OleDbCommand command = new OleDbCommand();
command = conn.CreateCommand();
// create the DataSet
DataSet ds = new DataSet();
// run the query
command.CommandText = "SELECT ID AS [#], Company AS [Company], FName AS [First Name], LName AS [Last Name], Type AS [Type] FROM Table1 WHERE Tags = @P0;";
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter = new OleDbDataAdapter(command);
command.Parameters.Add("@P0", OleDbType.VarChar).Value = "";
adapter.Fill(ds);
// close the connection
conn.Close();
bindingSource1.DataSource = ds.Tables[0];
DataGridView.DataSource = bindingSource1;
// set the size of the dataGridView Columns
this.DataGridView.Columns[0].Visible = false;
this.DataGridView.Columns[1].Width = 234;
this.DataGridView.Columns[2].Width = 50;
this.DataGridView.Columns[3].Width = 50;
this.DataGridView.Columns[4].Width = 75;
//Sort on the Title Column
DataGridViewColumn sortColumn = DataGridView.Columns[1];
ListSortDirection direction;
direction = ListSortDirection.Ascending;
DataGridView.Sort(sortColumn, direction);
//Set the Selected Property of the First Row to False
DataGridView.Rows[0].Selected = false;
}
catch
{
}
Thank you again for your help.
Is it possible to combine fields and text in a select statement?
In a dropDownList I want to show a combination of two different fields, and have the value of the selected item come from a third field. So, I thought I could maybe do something like this:
SELECT DISTINCT GRP AS GroupName, "Year: " + YEAR + "Grade: " + GRD AS ShowMe
FROM GE_Data
WHERE (DIST = @DIST)
I hoped that would take the values in YEAR and GRD and concatenate them with the other text. Then my dropDownList could show the ShowMe value and have the GroupName as the value it passes on. However, when I test this in the VS Query Builder, it says that Year and Grade are unknown column names and changes the double-quotes to square brackets.
If this is possible, or there's a better way to do it, I'd love some more info.
Thanks!
-Mathminded
Hello,
I've been busy all night searching and reading trying to figure out how I can do the following.
I have a table that tracks user IDs in multiple fields. When I select records from this table I need a way to resolve those ID fields back into user names by referencing the users table. SQL statement thus far...
SELECT A.Username as NameA, B.Username As NameB, FROM theTable, Users As A, Users As B WHERE theTable.UserIDA ???
How do I resolve theTable.UserIDA and theTable.UserIDB back to Users.Username so that the records returned fill the fields NameA and NameB?
Your help is greatly appreciated.
Thanks,
-Mike
Ontario, Canada
Dear All
I am trying to populate an OledbDatareader for binding to a ASP datagrid.
For this I use select statement to display combined fields in a datagrid cell.
Eg. Select (Field1+ '<br/>' + Field2 + '<br/>' + Field 3) As Address .. and so on.
But the problem is if any of the three field is null the combined field 'Address' returns as Null.
Please help me to overcome this problem.
Regards
kalanad ( beginner)
Hi to allI wish to be able to have a standard select statement which hasadditional fields added to it at run-time based on suppliedparameter(s).iedeclare @theTest1 nvarchar(10)set @theTest1='TRUE'declare @theTest2 nvarchar(10)set @theTest2='TRUE'selectp_full_nameif @theTest1='TRUE'BEGINother field1,ENDif @theTest2='TRUE'BEGINother field2ENDfrom dbo.tbl_GIS_personwhere record_id < 20I do not wish to use an IF statement to test the parameter for acondition and then repeat the entire select statement particularly asit is a UNIONed query for three different statementiedeclare @theTest1 nvarchar(10)set @theTest1='TRUE'declare @theTest2 nvarchar(10)set @theTest2='TRUE'if @theTest1='TRUE' AND @theTest2='TRUE'BEGINselectp_full_name,other field1,other field2from dbo.tbl_GIS_personwhere record_id < 20ENDif @theTest1='TRUE' AND @theTest2='FALSE'BEGINselectp_full_name,other field1from dbo.tbl_GIS_personwhere record_id < 20END......if @theTest<>'TRUE'BEGINselectp_full_namefrom dbo.tbl_GIS_personwhere record_id < 20ENDMake sense? So the select is standard in the most part but with smallvariations depending on the user's choice. I want to avoid risk ofbreakage by having only one spot that the FROM, JOIN and WHEREstatements need to be defined.The query will end up being used in an XML template query.Any help would be much appreciatedRegardsGIS Analyst
View 2 Replies View RelatedHello!
I am trying to write an update t-SQL statement from the following select statement:
SELECT EduNextContacts.ssn
FROM EduNextContacts Left JOIN
EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND
EduNextContacts.campaign_code = EduContactsAuditChanges.campaign_code
GROUP BY EduNextContacts.ssn
HAVING(SUM(CASE EduContactsAuditChanges.release_code WHEN '103' THEN 1 ELSE 0 END) +
SUM(CASE EduContactsAuditChanges.release_code WHEN '102' THEN 1 ELSE 0 END) >= 2)
I tried many versions of writing my update statement with no luck. My most recent version is as follows:
UPDATE EduNextContacts
SET EduNextContacts.overLMLimit = 'Y'
FROM EduNextContacts Left JOIN
EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND
EduNextContacts.campaign_code = EduContactsAuditChanges.campaign_code
WHERE EduNextContacts.ssn = (SELECT DISTINCT EduContactsAuditChanges.ssn
FROM EduNextContacts Left JOIN
EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND
EduNextContacts.campaign_code = EduContactsAuditChanges.campaign_code
GROUP BY EduContactsAuditChanges.ssn
HAVING(SUM(CASE EduContactsAuditChanges.release_code WHEN '103' THEN 1 ELSE 0 END) +
SUM(CASE EduContactsAuditChanges.release_code WHEN '102' THEN 1 ELSE 0 END) >= 2))
And gives the following error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Can anyone help shed some light on how to make an update statement from my SELECT query above?
Thanks in advance,
Harry
I need to convert a date like 08/1/2009 to 0809
I also need to show currency as 100.00 and not 100.0000
How can I do these in a select statement?
SELECT CONVERT(Varchar(20),ExpirationDate,10) AS ExpirationDate, Amount FROM tblPayment
I appreciate any help!
Hello,
I've been busy all night searching and reading trying to figure out how I can do the following.
I have a table that tracks user IDs in multiple fields. When I select
records from this table I need a way to resolve those ID fields back
into user names by referencing the users table. SQL statement thus
far...
SELECT A.Username as NameA, B.Username As NameB, FROM theTable, Users As A, Users As B WHERE theTable.UserIDA ???
How do I resolve theTable.UserIDA and theTable.UserIDB back to
Users.Username so that the records returned fill the fields NameA and
NameB?
Your help is greatly appreciated.
Thanks,
-Mike
Hi,
I have a field called "Starting DateTime" and I want to convert into my local time. I can convert it in the report with the expression "=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Starting_DateTime.Value)", but that is too late. I want to convert it in the Select statement of the query.
Can anyone help me please?
Thx
Hello... im having a problem with my query optimization....
I have a query that looks like this:
SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)
it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
I have a table (Not my own, cannot be changed) that has this structure:
Table1
ID, field, data
-----------------------
1, fname, john
1, lname, doe
1, address, 123 any st
2, fname, jack
2, lname, sprat
2 address, 345 some st
2, phone, 321-555-1234
I want this:
Table2
ID, fname, lname, address, phone
--------------------------------------------------
1, john, doe, 123 any st,,
2, jack, sprat, 345 some st, 321-555-1234
Notes:
This query will need to run on a regular schedule.
There are over 100 fields, I don't want to do this 100 times:
--UPDATE table2
--SET fname = table1.data
--FROM table2 INNER JOIN
-- table1 ON tablw2.ID = table1.ID
--WHERE table.field = fname
Any Ideas.
I have a select statement which has to convert some date fields stored
as varchar based on critera of adding numbers to the date. What I have below
will return data show below but gives me an error message when it encounters
any data with null in the field. Is there something wrong in my conversion?
Thanks
declare @tdate varchar(10)
declare @rldate datetime
select @tdate = 20000630
select @rldate = @rldate + 2
select patient_.df_admit_date, patient_.df_ppdb_date from patient_
where (convert(datetime, df_ppdb_date, 101)+ 12) >= @tdate or
(convert(datetime, df_admit_date, 101) + 2 ) >= @tdate
19980910 20000626
19981215 20000627
19590114 20000621
20000629 NULL
20000706 NULL
20000711 NULL
(6 row(s) affected)
Server: Msg 242, Level 16, State 3, Line 5
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
I want to combine two of my fields somewhat like when you combine to char fields and concatenate them. But these two fields are int. How do i do that? Here's my query right now:
Code Block
SELECT AutoID,
CONVERT(Varchar(Mars_Calender_Year )+ CONVERT(Varchar(Mars_Calender_Period_Code) as MarsId
FROM NavisionReplication.dbo.Tbl_Mars_Calender
ORDER BY Mars_Calender_Year DESC
what am i doing wrong?
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies View RelatedOk I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
I am extract data from one of our Sql servers with the code below
Code:
Select SectDesc, PrGDesc, 'Product Description'=SetDescF, ProdSet, ProdSect, SectChar, Prdct.ProdGrp, OrigPr
From Prdct
INNER JOIN qryPRDGroupDets on Prdct.ProdGrp=qryPRDGroupDets.PGCode
where supersed =''
And OrigPr Not Like '9%' And OrigPr Not Like '%MDM%'
And LISTPR1>'0' And STANCOST>'0'
Which works fine, but what I need to do is reference the "OrigPr" field and mark it as "valid" or "Invalid", the "OrigPr" the field contains alpha numeric data e.g. A000, A001, A002 - ZZ99 and so on, amongst all of the potential different types of codes we have codes that end in treble Zero (0) e.g. A000 which are valid, but if they end in double 00 e.g. AA00 then this is invalid, the problem I have is I can't just add
Code:
'Marker'= Case When Right(OrigPr, 2) = '00' Then 'Invalid' ELSE 'Valid' End
For it will mark the A000 as invalid, is there a way of getting around this...
I have a query that I need a hand on. I am trying to add togther somefiends based on values of another.What I would like to add a billing total by saying more or less thefollowing:SELECT labor_hours, labor_cost, expidite_fee, flat_rate,include_repair_cost, include_cal, include_flat_rate, include_parts,cur_bill,(labor_hours * labor_cost) AS labor_total,(ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER BYdateCAL DESC),0)) AS cal_total,(ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROMrepair_partsID WHERE orderID=79559),0) +ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROMmisc_part_assocID WHERE orderID=79559),0)) AS parts_total,((labor_hours * labor_cost) + expidite_fee + flat_rate +ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER BYdateCAL DESC),0) +ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROMrepair_partsID WHERE orderID=79559),0) +ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROMmisc_part_assocID WHERE orderID=79559),0)) AS actual_total,(expidite_feeIF include_repair_cost = 1+ (labor_hours * labor_cost)IF include_flat_rate = 1+ flat_rateIF include_cal = 1+ ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDERBY dateCAL DESC),0)IF include_parts = 1+ ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROMrepair_partsID WHERE orderID=79559),0) +ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROMmisc_part_assocID WHERE orderID=79559),0)) AS billing_totalFROM view_inventoryWHERE orderID=79559I know the IF part is whacked, that's where I need the help. Is thistype of thing even possible? Or even efficent? Is it wise to subqueryfor totals (not like I have a choice based on the applicationrequirements)? help.
View 2 Replies View RelatedhiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View 2 Replies View RelatedHi guys,
I have the query below (running okay):
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:
Field02
----------------------
22810
8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760
22810
Is there any good suggestion?
Thanks in advance for any help,
Aldo.
Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E
can any one help me in this?
please give me a sample query.
Thanks and Regards,
Kiran Suthar
I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
UPDATE TABLE1
SET TABLE1.FIELD2 = 1
ELSE
UPDATE TABLE2
SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
I am working on a project in which a customer wants to be able to list and search their inventory and display the items in a table/grid on a web page.Each item in their inventory has a set of properties - for example, manufacturer, price, serial number, name, etc. The complicated part is that they want an admin to be able to modify/add/delete the set of properties. So for example, they could add the attribute "size." Given that, I think what is needed is 3 tables - one to store the set of properties schema, one for the items, and one to store the actual properties for each item. I know i COULD use alter table statements to add and delete columns, but that doesn't seem like the "right" solution.I would like to be able to write a query such that each row returns the item and all its properties - then i can easily bind to a datagrid. However, what would the query be to do this? I also need to be able to allow the customer to query for items with certain properties - i imagine the sql for that to be similar to this:SELECT * FROM items d WHERE d.Id IN(SELECT a.ItemId FROM attributes a WHERE a.Name = "Manufacturer" AND a.Value = "Samsung") AND d.Id IN(SELECT a.ItemId FROM attributes a WHERE a.Name = "SerialNumber" AND a.Value = "3223")
View 1 Replies View RelatedOn my aspx page I have a basic sqldatasource and gridview - both were set up using the wizards. The SQL Server 2000 database table that the sqldatasource is querying has some fields in it that are set to not allow nulls, however, the gridview control is not displaying all of the fields in the table - including some of the non-null fields.
My problem is, when I run an insert on the table from my aspx page, I get an error that says: "Cannot insert the value NULL into column 'ColumnName', table 'TableName'; column does not allow nulls. INSERT fails. The statement has been terminated."
The 'ColumnName', as you may have guessed, is one of the aforementioned columns that doesn't allow nulls, but isn't in the GridView.
How can I do an insert on this table without messing with the non-null fields. Those fields are relevant to the purposes of the gridview, so I don't want to display them, let alone allow editing of them.
Any suggestions on my options would be greatly appreciated!
Thanks
Capella07
Can someone help me with this :
Case WHEN ISNULL (dbo.BLH.n1name,'NOTHING')= 'NOTHING' then dbo.BLH.coname else dbo.BLH.n1name End as CustName
as the strored proc is not returning value when the fileds is blank
cheers
I am attempting to update a table that drives a report. The report is at the order level. An order can have several types of demand (revenue $): Ship and/or Backordered. When I update one of these demand fields on the report table, I would expect those orders that have that type of demand to update, and those orders that don't have that type of demand to remain at their current value ($0 in this example which is the table default). But what is happening is that orders that don't have that type of demand are changing to NULL. What am i doing wrong with my update statement that is causing this?
Code:
CREATE TABLE #sales(
OrdNo int,
StatusGrp varchar(10),
Demand decimal(10,2)
)
INSERT INTO #sales
VALUES (1,'Ship',100.00)
[Code] .....
I need to build an update query for all my article beginning with '0.%' for my varchar field refkey, but depending on some conditions.
For example.
Case st.base <> '' and st.qttbase <> 0
Theo refkey = B
Case st.cos <>'' and st.qttcos <> 0
Then refkey = C
Case st.refo <> '' and st.qttrefo <> 0
Then refkey = R
But my problem are That i can have Many combination on refkey, for example:
B,BC,BCR,C,CB,CBR,R...and so on.
Also i need to separate the letters with comma, like:
B
B,C
B,C,R
.....
Can someone help with this? Let me know if what I'm trying to do is possible...
Here's code example:
@ClientID int,
@QuoteID int,
@Base real,
@One real,
@DwellingLimit real
AS
BEGIN
Insert Into tblOne(ClientID,QuoteID,GuideID,GuideRate,GuideMult,Premium)
Select @ClientID, @QuoteID, GuideID,GuideRate, @+"GuideMult"+,GuideRate*GuideMult
From tblTwo
Where Choose = 'True';
END
I need the value stored in tblTwo.GuideMult (ie. One, BaseRate) to be translated
into the numerical value shown on a webform (ie. @One, @BaseRate) and then
insert the numerical values into tblOne.GuideMult
Clear as mud? Does somebody have a better way to do this?
Is there a method to convert "Select * From Table" to "Select field1,field2,...fieldn From Table" ?
Thanks
In the below code i want to use select statement for getting customer
address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname
from customer table.Rest of the things will be as it is in the following code.How do i do this?
INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,
[code]....
I have 3 tables, with this relation:
tblChats.WebsiteID = tblWebsite.ID
tblWebsite.AccountID = tblAccount.ID
I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:
SELECT * FROM tblChats c
LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID
LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID
WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180