If Statement (EASY)
Jan 8, 2007
I'm trying to group ages in one report, and Case numbers in another. Both are in string datatype, and require more then 3 choices. Im not keen on coding, so i was wondering, how do you make an IFF statement with more then 3 choices, i need about 7, maybe even a little more. *this is for a column grouping in a matrix.
Here's what i have right now...It works..but i need to add more years.
=iif(left(Fields!ICNNo.Value, 1) = "4", "2004",
iif(left(Fields!ICNNo.Value, 1) = "5", "2005",
iif(left(Fields!ICNNo.Value, 1) = "6", "2006", "Unknown")))
Please help! thanks!
View 10 Replies
ADVERTISEMENT
Jul 30, 2007
I am trying to amend an SQL insert statement but it is not working.
It is as follows can anyone tell me what i am doing wrong.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>" DeleteCommand="DELETE FROM [useridTest] WHERE [userID] = @original_userID AND [userName] = @original_userName AND [listCopy] = @original_listCopy"
InsertCommand="INSERT INTO [useridTest] ([userID], [userName], [listCopy]) VALUES (<% user.ProviderUserKey.ToString() %>, @userName, @listCopy)"
View 1 Replies
View Related
May 21, 2008
myCommand2.CommandText = "INSERT INTO testimonials(name,email,testimonial,approved,time) VALUES ('" + exp.escString(txtName.Text) + "','" + exp.escString(txtEmail.Text) + "','" + txtTestimonial.Text + "','" + false + "','" + DateTime.Now + "')"; ok the form has a field txtTestimonial.Text these strings work when i submit them thoughthats all folksthat"s (double quotes) that\"sthat\"s these failthat's that's that\'s that\'s tried up to 7 just to make sure Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 's'.Unclosed quotation mark after the character string ')'. I figure the " works because it ends up being ' " ' in the sql statement and it doesnt mind thatI tried researching this. I could not find any info stating that perhaps sql escapes things differently. I was on the assumption that in the sql itself the database would reconise the before the '.I read i can use html decode but it seems that then i will have to undecode everytime I read from the database and it could be a major pain if I am using a datagrid or something and a big mess. Any help would be greatly appreciated. Jim
View 5 Replies
View Related
Mar 12, 2005
Im starting to learn SQL at school !!
and would like to do something like this. !! but i just cant figure that out.
i have a table that containes all my categories in it. And as you can see i have "PARENT" and "CHILD" categories.
I would like to get something like this by using a simple SELECT statement !! is it possible...
===============
=#1 PARENT ID NAME=
===============
=#1 CHILD NAME HERE
=#2 CHILD NAME HERE
=#3 CHILD NAME HERE
===============
=#2 PARENT ID NAME=
===============
=#1 CHILD NAME HERE
=#2 CHILD NAME HERE
=#3 CHILD NAME HERE
im not trying to show this in my Query Analyser but on a Web Page by using a datagrid.
View 1 Replies
View Related
Jul 20, 2005
Hello,I have 2 ways of updating data I'm using often1) via a cursor on TABLE1 update fields in TABLE22) via an some of variables ...SELECT @var1=FLD1, @var2=FLD2 FROM TABLE1 WHERE FLD-ID = @inputVARUPDATE TABLE2SET FLDx = @var1, FLDy = @var2WHERE ...Now I have a system with 2 databases and I need to update table DB2.TABbased on data in DB1.TAB. Instead of using 1 of the 2 ways I normally use,I thought it would be much easier to get the required data immediately fromDB1.TAB in the update-statement of DB2.TAB ... but the way to do thatconfuses me. I've checked books online and a lot of newsgrouppostingsgiving good information but still I keep getting errors like this ...The column prefix 'x.ADS' does not match with a table name or alias nameused in the query.while executing the following statement ...UPDATE DB2.dbo.TABSETFLD1 = x.FLD1,FLD2 = x.FLD2,...FROM DB1.dbo.TAB x, DB2.dbo.ADSWHERE DB2.dbo.TAB.REFID = x.IDOFTAB1 AND DB2.dbo.TAB.IDOFTAB2 =@InputParameterSo in DB2.TAB I have a field REFID reffering to the keyfield IDOFTAB1 oftable DB1.TABAND I only want to update the row in DB2.TAB with the unique keyfieldIDOFTAB2 equal to variable @InputParameterDo you see what I'm doing wrong?--Thank you,Kind regards,Perre Van Wilrijk,Remove capitals to get my real email address,
View 8 Replies
View Related
Feb 7, 2008
Hi, I was handed an old application written for sql05, it has this (kind of) statement:
Dim CommandText As String = "SELECT c.name FROM db1 c LEFT JOIN "
CommandText += "db2.dbo.Users u ON u.iKey = c.key "
CommandText += "WHERE u.cUserName = '" & User & "'"
As you can see, there are two databases, db1 and db2 and this worked fine in sql05, but when I convert each of the databases to sqlexpress, I get this error: Invalid object name 'db2.dbo.Users'.
So how can I make this work in sqlexpress? Are queries across databases not allowed?
Thanks so much for your help!
View 3 Replies
View Related
Jun 9, 2004
I'm banging my head against the wall. It's probably pretty simple so here goes...
I have a form that supposed to add records to a table on the database. I'm pretty sure I have the dataAdapter and Dataset set right but when I add a record, it complains that the primary key can’t be null. I’ve tried different things, including removing those fields and variables from the stored Proc, changing the Identity Properties of the Table, changing the AutoIncrement settings on the Dataset for that field, but all give me different errors. Nothing actually makes it work. This database has many records in it and the Primary key is a decimal field that starts at 5, then next was 10, then 28, then it started getting consecutive after 110. (111, 112, 113, etc.) (I'm porting over an old database to a new web site, so I'm somewhat contrained by the database.)
I guess my question is for adding records in to a database that has a primary unique key constraint. Certainly this is a pretty common thing. Obviously I do not want users to enter that in themselves, I thought it should simply autocreate/autonumber. So what is the best procedure and gotcha’s I need to watch out for?
View 7 Replies
View Related
Sep 16, 2004
if i have the following code, how do i know if the transaction was actually successful, so basically where in this code can i write TRANSACTION SUCCESFUL?
try
{
//Open up the connection
conn.Open();
//Setup the Transaction
trans = conn.BeginTransaction();
//First query in transaction
pre_query = "Delete from Menu where spec_name="
+ "'"
+ spec_name_array[i].Text.ToString()
+ "'" ;
//Second query in transaction
query = "INSERT into Menu VALUES ('"
+ Calendar1.SelectedDate
+"','"
+ spec_name_array[i].Text.ToString()
+"','"
+ spec_desc_array[i].Text.ToString()
+"','"
+spec_price_array[i].Text.ToString()
+"',1)";
SqlCommand comm = new SqlCommand(pre_query,conn);
//Setup the command to handle transaction
comm.Transaction = trans;
//Execute first query
comm.ExecuteNonQuery();
//Add in second query
comm.CommandText = query;
//Execute second query
comm.ExecuteNonQuery();
trans.Commit();
}
catch(SqlException ex)
{
com_label.Text = "Submission Not complete, did you forget the date or something else?";
//Undo all queries
trans.Rollback();
}
finally
{
conn.Close();
}
View 1 Replies
View Related
Aug 16, 2006
This may not be the right place for this post...but my head is hurting and I can't think right now.
I have a SQL table of users. Each record has a required 'Birthdate' field. I'd like to order this by the days left till the give birthday, keeping in mind that the year may be 1980 or their birthday might have been 2 weeks ago.
Can someone cure my headache?
View 1 Replies
View Related
Mar 3, 2006
I have written 2 custom connection mgr€™s.
One connects the data from an oracle source
One connects so I can put the data on a sql server.
These both work well and it makes it a lot easier across development, test, production.
But now I would like a good way to do this
I have 10 tables to copy from oracle. The only difference is just the name of the table.
static string[] g_tables =
{
"table_name_1",
"table_name_2",
"table_name_3",
€¦
};
foreach (string str_table in g_tables)
{
Oracle.DataAccess.Client.OracleCommand OracleCommand = new Oracle.DataAccess.Client.OracleCommand
(
"select * from " + str_table,
OracleConnection
);
Oracle.DataAccess.Client.OracleDataReader OracleDataReader =
OracleCommand.ExecuteReader();
// dump out to raw file destination
}
I understand there are some pieces left out but that is the basic idea. It seems like I€™m going to have write custom pieces for each step.
It does not seem like there is any advantage to using SSIS. Should I give up now and stick to writing windows services for stuff like this?
I could just write a specific piece for each table but the only difference is the table name.
View 13 Replies
View Related
Sep 5, 2006
I need to determine the top 20 most frequent occurences of a value in a specific field using SQL.
View 4 Replies
View Related
Oct 30, 2007
My table structure...
enddt datetime(8)
status varchar(20)
when enddt time equal to server time.
I want to change status field to expired.
What is the easy way to do this?.
enddt status
8/22/2005 7:00:00 PM expired
9/22/2005 8:30:00 PM expired
10/30/2005 7:30:00 PM live
11/22/2005 9:30:00 PM live
View 6 Replies
View Related
Sep 18, 2007
I completely forgot how to do this. I want one of my groups in the matrix to take its week number (1-7) as Monday-Sunday.
so it would kind of be like this:
=IIF(Fields!DayOfWeek.Value=2,"Mon",
ELSE(Fields!DayOfWeek.Value=3,"Tues",
Else(Fields!DayOfWeek.Value=4,"Wed",
etc etc
Am i doing this right, or am i off??
View 4 Replies
View Related
Sep 5, 2007
If I have a 64 bit version of SQL 2005 installed, will "select @@version" indicate it is a 64 bit version? If not, how do I determine such? Thanks in advance.
View 3 Replies
View Related
Oct 4, 2007
I have a column in my report that i want to show percentage change from the previous year to this year. So i enter all kinds of different type of formats for the formula, and keep getting the wrong answers or error messeges. Here's what i have tried recently:
=SUM((Fields!LYTD_Amount.Value - Fields!YTD_Amount.Value) (Fields!YTD_Amount.Value*100))
Its pretty much Last year to date Minues Year to Date , Divided by Year to Date, Multiplied by 100.
any suggestions? Everytime i try to put an aggregate in the box, its always tricky.
View 13 Replies
View Related
Dec 21, 2006
hi,
I have this simple sql query - it should be pretty obvious what I'm trying to achieve but this syntax isn't accepted on SQL 2005, any suggestions?
SELECT Name FROM Users WHERE UsersID IN (EXEC dbo.ReturnDataByModule 'Groups',1200)
View 6 Replies
View Related
Oct 19, 2007
What is the equivalent of Double for SQL Server?
View 1 Replies
View Related
Mar 16, 2008
Ok - I am still a bit weak on SQL. My understanding of FK relationships in a database are to reinforce the data integrity - Correct? For example, does it make sure that the id given for SiteID does indeed exists. Can it link tables like JOIN in select statements? If not, is there any gains by creating them, such as performance?
View 1 Replies
View Related
Mar 16, 2008
scenario I have 2 tables one is called "Site" which contains columns ID, Address1, Address2,City,StateID,ZipCode and the second table is called "State" which contains columns ID,Name,Abbreviation. I am looking to make a view that contains all the sites with the state inner joined on stateid to make my all my other queries easier. That is so I can just select from the view without having to do the inner join on the state. My question is when a new record is added to table Site do I have "recreate"/update the view?
View 3 Replies
View Related
Jan 6, 2004
I am working on a WebMatrix ASP project. I have a query that returns a System.Data.DataSet but I don't know how to assign the DataSet to a variable so that I can run some validation tests on it.
This is what I have so far...What I want to do is assign the dataset to a variable and to see if it is NULL or Not. I don't how??? Any help would be awesome.
Sub Button1_Click(sender As Object, e As EventArgs)
???? = MyQueryMethod(txtPhone.Text)
Thanks,
Matt
View 5 Replies
View Related
Nov 21, 2004
I have a simple SQL table. The data looks like this:
products_ID----category----product_local
1----CORE----0
2----BASE----0
3----BRANCH----1
4----LEAF----3
I need to create a SQL statement that produces all category items where that ROW's products_ID is not found in ANY product_local in the table. So in the table above only the category BASE and LEAF would be printed because their products_ID are not found in any product_local in the table.
TIA as I am completely stuck!
View 1 Replies
View Related
Nov 30, 2004
OK, I have two cols of data:
COL1 COL2
test ing
tams ert
test pan
ted ted
hom jis
ted sam
tams dom
test ut
I need to simply pull one row of data from the given rows. Whats in COL2 is not relevant. So an example would be:
I want to retrieve a row(any row, but only one) where COL1 = 'test'
An ecceptable result woould be:
COL1 COL2
test ing
or
COL1 COL2
test pan
or
COL1 COL2
test ut
I would appreciate any help. This is driving me nuts. Seems like it would be easy.
TIA,
STue
View 4 Replies
View Related
Mar 1, 2001
Hi there,
I'm using bcp to import data into my database. the datetime format in the bcp file is ymd and my database has mdy datetime format. Is there any easy way to convert ymd into mdy when using bcp?
thanks a lot for your help!
Lena
View 2 Replies
View Related
Apr 5, 1999
Is there an easy way to update a Value field
for all records in One Table from a Value Field in another?
Both Tables have the same number of records.
View 3 Replies
View Related
Aug 2, 2002
Hi,
the following Insert statement is not acting like i would expect it. The final column (Average) divides two columns([Sum of Scores] / [Count Of Scores]) . The Average column is a numeric column with 5 decimal places. Yet, the result of the division is a whole number (no fractions). Why is this.
any help would be appreciated
Thanks,
Jim
Insert [Bankers_Scores_lead] ([ID], EqorMA, Region, Period,IBFirm, UndNeed ,QualIdea ,ProfAggr ,SpKnow ,negskill ,Perchem, RepPri, Repcomp, TranType, [Count Of Scores], [Sum of Scores], Average)
SELECT [ID],
EqorMA,
Region,
Period,
IBFirm,
UndNeed,
QualIdea,
ProfAggr,
SpKnow,
negskill,
Perchem,
RepPri,
Repcomp ,
TranType,
(Case when [UndNeed] >0 then 1 Else 0 END)
+(Case when [QualIdea] >0 then 1 Else 0 END)
+(Case when [ProfAggr] >0 then 1 Else 0 END)
+(Case when [SpKnow] >0 then 1 Else 0 END)
+(Case when [Perchem] >0 then 1 Else 0 END)
+(Case when [negskill] >0 then 1 Else 0 END)
,
(ISNULL([UndNeed],0))
+(ISNULL([QualIdea],0))
+(ISNULL([ProfAggr],0))
+(ISNULL([SpKnow],0))
+(ISNULL([negskill],0))
+ (ISNULL([Perchem],0))
,
((
(ISNULL([UndNeed],0))+
(ISNULL([QualIdea],0))+
(ISNULL([ProfAggr],0))+
(ISNULL([SpKnow],0))+
(ISNULL([Perchem],0)) +
(ISNULL([negskill],0)
))
/((Case when [UndNeed] >0 then 1 Else 0 END)
+(Case when [QualIdea] >0 then 1 Else 0 END)
+(Case when [ProfAggr] >0 then 1 Else 0 END)
+(Case when [SpKnow] >0 then 1 Else 0 END)
+(Case when [Perchem] >0 then 1 Else 0 END)
+(Case when [negskill] >0 then 1 Else 0 END)
))
FROM tmp_Bankers_Scores
Here is the create table statement
CREATE TABLE [dbo].[Bankers_Scores_lead] (
[ID] [int] NOT NULL ,
[EqorMA] [smallint] NOT NULL ,
[Region] [smallint] NOT NULL ,
[Period] [int] NULL ,
[IBFirm] [int] NULL ,
[UndNeed] numeric(8,5) NULL ,
[QualIdea] numeric(8,5) NULL ,
[ProfAggr] numeric(8,5) NULL ,
[SpKnow] numeric(8,5) NULL ,
[negskill] numeric(8,5) NULL ,
[Perchem] numeric(8,5) NULL ,
[RepPri] numeric(8,5) NULL ,
[Repcomp] numeric(8,5)NULL ,
[TranType] [smallint] NULL ,
[Count Of Scores] numeric(8,5) NULL ,
[Sum of Scores] numeric(8,5) NULL ,
[Average] numeric(8,5) NULL
) ON [PRIMARY]
here is the output
ID EqorMA Region Period IBFirm UndNeed QualIdea ProfAggr SpKnow negskill Perchem RepPri Repcomp TranType Count Of Scores Sum of Scores Average
----------- ------ ------ ----------- ----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------- --------------- ------------- ----------
75659 5 2 9 504 9.00000 8.00000 7.00000 8.00000 8.00000 8.00000 1.00000 .00000 11 6.00000 48.00000 8.00000
75659 5 2 9 504 9.00000 8.00000 7.00000 8.00000 8.00000 8.00000 1.00000 .00000 11 6.00000 48.00000 8.00000
79360 6 5 9 501 9.00000 8.00000 9.00000 8.00000 8.00000 8.00000 1.00000 .00000 15 6.00000 50.00000 8.00000
79360 6 5 9 501 9.00000 8.00000 9.00000 8.00000 8.00000 8.00000 1.00000 .00000 15 6.00000 50.00000 8.00000
View 1 Replies
View Related
Aug 15, 2007
Hi I'm new to SQL and I'm stuggling with a simple query in MS-server 2005 and wondered if anyone can help me.
I'm trying to devide the ansewrs of two seperate queries, but both the queries use the same coloumn in the where clause to get the answer. Please can some one help!
eg.
select Sum(column1)
where column1 = 'x'
devided by
select Sum(column1)
where column1 = 'y'
View 2 Replies
View Related
Jul 1, 2004
hi.. guys this is simple for you guys how can i make ms access to display the current date in a col.... when i go to insert a new line i how can i make it so that its allready there? i dont have to type it??????
View 3 Replies
View Related
Jun 1, 2006
I have a ASP page where the user posts part of the name field. Then i need to provides the matches. (providing partial comparision of the field & correcting bad spelling)
example:
User posts 'softwair' (should be software)
the query look like this
Select Name from A where Name is like '%softwair%' <-- but appling soundex on it in the like field, or something like it.
I could make a copy of the column and update all info in the field to the soundex value, but then the like statement would not get it if the value was only part of it. for example say the data values where like 'Microsoft Software', or 'My Software Solutions', ect...
View 1 Replies
View Related
Jun 22, 2007
I'm trying to find records in the 'user' table that DO NOT have a record in the 'stuff' table. So basically the statement should only return record 3 from the user table since there is not record in the stuff table for it. I think it a join, but I'm second guessing my self. Can some one please assist me. Thanks in advance.
user
uid name
1 James
2 Erick
3 Todd
stuff
userid thing
1 car
1 house
2 mansion
View 1 Replies
View Related
Sep 13, 2007
Hey all,
A nice easy BCP question for y'all...
I'm currently running:
DECLARE @sql varchar(2000)
SET @sql = 'BCP master..sysobjects OUT C:sysobjects.csv -c -t, -T -S' + @@ServerName
EXEC master..xp_cmdshell @sql
Which works fine!
But how on earth do I save it to my local PC?
May sound silly, but this is one of my first adventures into BCP and I'd rather not "pollute" the server with my test files!
Thankyall!
View 8 Replies
View Related
Feb 20, 2008
I'm used to forming Select statements for IBM System i imbedded SQL in High Level source programs. I now find myself converting System i RPG programs to VB.NET classes. What I want to know is how to insert the value of a class variable into a SQL Select string. In the IBM world, I would do:
Select STCTYC,STCRID,STTITL,STSTTY From CSLSTT01 Where STCTYC = :County
where County is a program defined variable. How do I get the value in the County variable into the Select string? I tried concatenation but that didn't work either. Thanks.
View 7 Replies
View Related
Jan 5, 2004
I create trigger no table person. When I invoke sql command
insert into person values (.... bla bala bal)
trigger is autommaticaly fired.
The trigger must log sql commads, so i must insert into table log this command, whos execute triger - insert into person values (.... bla bala bal).
How I can do it?
View 5 Replies
View Related
Jan 29, 2004
Hi,
I have a query that I need to pass as a string as the second parameter of the OpenQuery method. Here's my query:
SELECT * FROM mytable WHERE last_name = 'DOE'
Thing is that a string is set with apostrophies, so I don't know how to set my string since apostrophies are alse in my query. Normally, it would look like this:
DECLARE @CQUERY VARCHAR(100)
SET @CQUERY = 'SELECT * FROM mytable WHERE last_name = 'DOE''
But of course this fails. How can I do it then?
Thanks,
Skip.
View 5 Replies
View Related
