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.
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)"
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
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.
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,
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?
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?
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(); }
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.
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.
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.
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:
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)
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?
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?
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)
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.
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
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?
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
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!
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??????
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...
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.
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!
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.
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?
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''
