Alright, so I have this problem. I want to make it easy for me and
others to be able to run a query and easily choose whether we want
Merchants or NonMerchants. Previously, we would have to comment out
bits of code and would leave things messy (it would also leave room for
error). So, I'm thought DECLARE and SET would work. Wrong.
This is what I have....
DECLARE @Merchant VARCHAR
SET @Merchant = (Select CONVERT(VARCHAR, Id) + ','
FROM AdminAdvertiserTypesDDL
WHERE Id IN (1,3,4,5)
) // Includes Active, Out of Business, Cease to do business, Inactive
I've also tried...
SET @Merchant = '1,3,4,5'
Then, in the query itself I try:
WHERE AdminAdvertiserTypesDDL.Id = @Merchant
or
WHERE AdminAdvertiserTypesDDL.Id IN @Merchant
or
WHERE AdminAdvertiserTypesDDL.Id IN (@Merchant)
or
WHERE AdminAdvertiserTypesDDL.Id LIKE @Merchant
Either way, it will ONLY show me the merchants whose Id is 1. When I
make the query:
In a t-sql 2012, I want to declare variables with multiple values and I am having problems since sql server thinks I am working with numbers when I am really working with character and bit values. Here is a copy of the sql that I am trying to use:
DECLARE @Account varchar(100) DECLARE @Processed bit set @Account = '58100,98326,09897' set @Processed ='0,1'
Thus would you show me what I can do so that the sql server knows that I want the values in the set states above to be varchar or character value for @Account and bit value for @Processed?
I am SSRS user, We have a .net UI from where we want to pass multi select values, but these values are comma separated in the database. how can I write a sql query such that when I select multi values on my UI, the comma separated values are take care of.
I am trying to create a report using Reporting Services.
My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.
I can currently get one value but how to get the information I need to be able to use in my reports.
So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.
Any help would be appreciated.
Thanks.
I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.
What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report
As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.
As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem
Thanks for the suggestions that were made, I apoligize for not making the problem clearer.
Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.
--Pulls the Service Opportunity
SELECT cs.value AS "Service Opportunity"
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE ca.name = 'Service Opportunity'
--Pulls the Number of Hours
SELECT cs.value AS 'Number of Hours'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Num of Hours'
--Pulls the Person Grade Level
SELECT cs.value AS 'Grade'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Grade'
--Pulls the Person Number, First and Last Name and Grade Level
SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"
FROM student s
INNER JOIN cperson cs ON cs.personid = s.personid
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE cs.value =(SELECT cs.value AS 'Grade'
WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')
I have created a single FULLTEXT on col2 & col3. suppose i want to search col2='engine' and col3='toyota' i write query as
SELECT
TBL.col2,TBL.col3 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col2,'engine') TBL1 ON
TBL.col1=TBL1.[key] INNER JOIN
CONTAINSTABLE(TBL,col3,'toyota') TBL2 ON
TBL.col1=TBL2.[key]
Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.
I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information. for e.g.; i was thinking to concatinate both fields like col4= ABengineBA + ABBToyotaBBA and in search i use SELECT
TBL.col4 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1 ON
TBL.col1=TBL1.[key] Result = 1 row
But it don't work in following scenario col4= ABengineBA + ABBCorola ToyotaBBA
SELECT
TBL.col4 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1 ON
TBL.col1=TBL1.[key]
Result=0 Row Any idea how i can write second query to get result?
If I have a Select statement like this in my C# code: Select * From foods Where foodgroup In (@foodgroup) And I want @foodgroup to have these values ... "meat", "dairy", fruit", what is the correct way to add the parameter? I tried meat, dairy, fruit 'meat', 'dairy', 'fruit' but neither worked. Is this possible?
Please be easy on me...I haven't touched SQL for a year. Why given;
Code Snippet USE [Patients] GO /****** Object: Table [dbo].[Patients] Script Date: 08/31/2007 22:09:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Patients]( [PID] [int] IDENTITY(1,1) NOT NULL, [ID] [varchar](50) NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [DOB] [datetime] NULL, CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED ( [PID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF
Hello all,I'm trying to request a number of URLS (one for each user) from my database, then place each of these results into a separate string variables. I believed that SqlDataReader could do this for me, but I am unsure of how to accomplish this, or if I am walking down the wrong road. The current code is below (the section in question is in bold), please ignore the fact that I'm using MySQL as the commands work in the same way. public partial class main : System.Web.UI.Page{ String UserName; String userId; String HiveConnectionString; String Current_Location; ArrayList Location; public String Location1; public String Location2; public String Location3; //Int32 x = 0; private void Page_Load(object sender, EventArgs e) { if (User.Identity.IsAuthenticated) { UserName = Membership.GetUser().ToString(); userId = Membership.GetUser().ProviderUserKey.ToString(); HiveConnectionString = "Database=hive;Data Source=localhost;User Id=hive_admin;Password=West7647"; using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(HiveConnectionString)) { // Map Updates MySql.Data.MySqlClient.MySqlCommand Locationcmd = new MySql.Data.MySqlClient.MySqlCommand( "SELECT Location FROM tracker WHERE Location = IsOnline = '1'"); Locationcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Locationcmd.Connection = conn; conn.Open(); MySql.Data.MySqlClient.MySqlDataReader LocationReader = Locationcmd.ExecuteReader(); while (LocationReader.Read()) { Location1 = LocationReader.GetString(0); //Location2 = LocationReader.GetString(1); // This does not work.. } LocationReader.Close(); conn.Close(); // IP Display MySql.Data.MySqlClient.MySqlCommand Checkcmd = new MySql.Data.MySqlClient.MySqlCommand( "SELECT UserName FROM tracker WHERE PKID = ?PKID"); Checkcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Checkcmd.Connection = conn; conn.Open(); object UserExists = Checkcmd.ExecuteScalar(); conn.Close(); if(UserExists == null) { MySql.Data.MySqlClient.MySqlCommand Insertcmd = new MySql.Data.MySqlClient.MySqlCommand( "INSERT INTO tracker (PKID, UserName, IpAddress, IsOnline) VALUES (?PKID, ?Username, ?IpAddress, 1)"); Insertcmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress; Insertcmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName; Insertcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Insertcmd.Connection = conn; conn.Open(); Insertcmd.ExecuteNonQuery(); conn.Close(); } else { MySql.Data.MySqlClient.MySqlCommand Updatecmd = new MySql.Data.MySqlClient.MySqlCommand( "UPDATE tracker SET IpAddress = ?IpAddress, IsOnline = '1' WHERE UserName = ?Username AND PKID = ?PKID"); Updatecmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress; Updatecmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName; Updatecmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Updatecmd.Connection = conn; conn.Open(); Updatecmd.ExecuteNonQuery(); conn.Close(); } } } } Can anyone advise me on what I should be doing (even if its just a "you should be using this command) if this is not correct? In fact any pointers would be nice !Thanks everyone!
how to declare multiple derived columns in SSIS Derived Column Task in one attempt.as i have around 150 columns coming from Flat file. I had created the required Expression in Excel and now i want add those in derived column task but its allowing only 1 expression at a time.
I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?
itemnum - over 16000 records closedate - can be 2001, 2002 location - is 1, 2, 3 etc
Like to find itemnum where location is 1 and itemnum has closedate of 2001, if it does have close date of 2001 than check if another record of same item with closedate of 2002. If yes, than diplay that item.
The result would be itemnum which have closedate of 2001 as well as 2002. Each item may have more than one record for each year.
Hi. I;m trying to sort out some values. Ok so for sort. We have a contract and a renewal column. A contract can have many renewals. I'm trying to get the latest renewal for each contract (latest renewal is the one with the higher number.
so im trying this..
select distinct c.policyno,c.renewalno from CONTRACT AS c inner join multicontract as d ON c.multicontractID = d.ID where c.renewalno in (select max(crn.renewalno) from contract crn where c.id= crn.id
But i keep getting multiple results (p.e. contra 12 ,renewal 13,14,15 and i want contract 12, renewal 15) Any help?
hi everyone how do i insert multiple rows in a database ? i came up with something like this after googling but it does not work INSERT INTO tblSold (LID, BuyerID,Date)select ('759','2106','2441') UNION ALLselect ('0','0','0') UNION ALLselect ('10/25/2007','10/25/2007','10/25/2007')
Hi there I have an exel spreadsheet with a very long list of towns. How can I import/insert that into my "Towns" table in sql express? I can't seem to find any way to import it and I'm not sure how to do multiple inserts. Thanks
I have a situation where I need two values (both are integers) returned from a stored procedure. (SQL 2000)
Right now, I use the statement "return @@Identity" for a single value, but there is another variable assigned in the procedure, @NewCounselingRecordID that I need to pass back to the calling class method.
I was thinking of concatenating the two values as a string and parsing them out after they are passed back to the calling method. It would look something like "21:17", with the colon character acting as a delimiter.
However, I feel this solution is kludgy. Is there a more correct way to accomplish this?
i have about 2,000 record and i need to insert them in my table. how do i insert these informations using this syntax?? au_id au_name au_fname 1003 vivian latin 1005 cecy mani 1004 bili david
insert into autors (au_id, au_name,au_fname) Values ('1101', 'Rabit','jesicca')
For example: Student ID: 100 Student Name: Kelly Preston Subjects: Geography, History, Math
How can I store these values in a database table? I know the normal "INSERT" statement, but how would I store the multiple subjects for a single student ID?
My "Student ID" is auto generated. If I create a new row for each subject, the Student ID will be different for each subject, which I dont want.
Or I can create a new field called "RowNumber" and keep that the primary key.. For example:
Row Number StudentID StudentName Subject 1 100 Kelly Geography 2 100 Kelly History 3 100 Kelly Math
If this is the only way to store the multiple sibjects, then for a given student ID (say 100), how can I retreieve the associated name and subjects? What is the query for that?
hi iam totally new to databases , as a project i have to design a database of users...they have to register first like any site..so i used stored procs and made entries to database using insert command...its working for now..now every user will search and add other users in the database..so every user will have a contact list...i have no idea how to implement this...so far i created a table 'UserAccount' with column names as UserName as varchar(50)Password as varchar(50)EmailID as varchar(100)DateOfJoining as datetimeUserID as int ---> this is unique for user..i enabled automatic increment..and this is primary key..so now every user must have a list of other userid's.. as contact list..Any help any ideas will be great since i have no clue how to put multiple values for each row..i didnt even know how to search for this problems solution..iam sorry if this posted somewhere else..THANK YOU !if it helps..iam using sql server express edition..and iam accessing using asp.net/C#
I have a table say #temp1 with coulmn Id and Description and I do have another table #temp2 with column id and Projectdescription but in #temp2 there could be more then one value against one id like the data in #temp2 is look like
id Projectdescription 1 Computer project 1 update in computer project 1 another update in computer 2 Physics project 2 another update
but #temp1 has only one accurance of id and data initially looks like id Description 1 NUll 2 NUll and I would like to update this table description from #temp2 Projectdescription column so that data in #temp1 table look like after update
id Description 1 Computer project,update in computer project, another update in computer
2 Physics project, another update
I mean I would like to have concatination form of Projectdescription in description column against specific ID I can achieve this by using UDF but i dont want to use that I just want to do it by update statement not even by using cursor
I have a web form with a check box list with 5 values. Each of them is an int value, and the user can select multiple values from the check box list. For example:
Status:
1) Single 2) Married 3) In Relationship 4) Divorced 5) Any
These values correspond to a column called "RelationshipStatus" which holds an int value of NULL or 0-5.
So the value passed to my SQL query would be any combination of 1, 2, 3, 4, or 5 (Which denote any of the above). It could look like this:
@Status = '132'
Now my question is how would I do a select statement that finds any row with one of those value (Any row with 1, or 3, or 2)? If there is a '5' in the varible then the select statement should return any row regardless of the value.
Table Name: "USER_TABLE" Column Name: "Relationship_Status" Value Type: INT
I have an existing query, which runs pretty good. However, I need to create a new report, in which I would require to alter the existing query to return two values from each of the subqueries back to the main query.
The following is the current query:
SELECT tbLogTimeValues.DateTimeStamp as DateTimeStamp ,tbLogTimeValues.FloatVALUE AS Value FROM tbLogTimeValues
[Code] ....
Need putting together a query that returns two values from the first sub query (DateTimeStampTemperature and ValueTemperature) and from the second query return (DateTimeStampHumidity and ValueHumidity).
I would simply have to change the Path LIKE '%' + 'Temperature Interval%' within the humidity subquery to read:
Path LIKE '%' + 'Humidity Interval%'
Obviously, I would like to keep the DateTimeStamp comparisons as part of the main query, as the customer has access to this via parameters, and should be the same for both subqueries.
I'm trying to write a query which allows that multiple values from onecolumn are placed in one record.ex:tableNrLetters1A2A2B2C3A3B3C3D3E4AThe result I want to get from an select:NrAll Letters1A2A, B, C3A, B, C, D, E4AOlivier
I have some 5 parameters which I've specified as multi-valued and my report uses a stored procedure. When I select all values in my dropdown (parameters) within my reports nothing shows up. How can I pass multiple values of a parameter into my stored procedure such that it works?
i can't believe my situation is unique, but my searches for answers have proven fruitless, so please bear with me.i have a date field in my database, and have previously used a simple textbox to allow users to insert a date. i want to make this a little step a little nicer, however, and have added a dropdown for the month, a textbox for the date (i'm not "good" enough to dynamically change this around for a dropdown list), and a dropdown list for the next few years.what "insert" statement would allow me to accomplish this? what i currently have doesn't blow up, but neither does it actually insert anything. here's my statment:<asp:SqlDataSource ID="sdsVersion" runat="server" ConnectionString="<%$ ConnectionStrings:DB %>" InsertCommand="INSERT INTO [Version] ([Major], [Minor], [Sub], [Rev], [Date]) VALUES (@Major, @Minor, @Sub, @Rev, @Month + '-' + @Day + '-' + @Year)"> <InsertParameters> <asp:Parameter Name="Major" Type="Int32" /> <asp:Parameter Name="Minor" Type="Int32" /> <asp:Parameter Name="Sub" Type="Int32" /> <asp:Parameter Name="Rev" Type="Int32" /> <asp:ControlParameter ControlID="ddlMonths" Name="Month" PropertyName="SelectedValue" Type="Int32" /> <asp:ControlParameter ControlID="tbDate" Name="Day" PropertyName="Text" Type="Int32" /> <asp:ControlParameter ControlID="ddlYears" Name="Year" PropertyName="SelectedValue" Type="Int32" /> </InsertParameters></asp:SqlDataSource>
Hi, I am trying to insert multiple values from another table as well as an addition value defined by me. Here's my code which is obviously incorrect at the AND statement: Insert Into table_1 (ID, Col_1, Col_2)Select ID, Col_A, Col_B From table_A AND table1.Col_3 = 'XYZ' Where table_A.Col_A IS NOT NULL
Pls advise on the correct way of constructing this statement. Many Thanks
Hi, I'm trying to insert multiple checkboxlist values from a databound checkboxlist into a SQL Server Express DB. I need to insert the values JobID and CategoryID into an intermediate table which is made of two columns, JobID and CategoryID, which form the primary key. I'm using the following code:Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim insertCommand As SqlCommand Dim strConnection As String = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True"Dim objConnection As New SqlConnection(strConnection) Try objConnection.Open() Dim ctr As Integer Dim str As StringFor ctr = 0 To CheckBoxList1.Items.Count - 1 If CheckBoxList1.Items(ctr).Selected Then str = "INSERT INTO CategoryVacancies (JobID, CategoryID) values (@JobID, @CategoryID)"insertCommand = New SqlCommand(str, objConnection) insertCommand.Parameters.AddWithValue("@CategoryID", CheckBoxList1.SelectedValue)insertCommand.Parameters.AddWithValue("@JobID", JobIDLabel.Text) insertCommand.ExecuteNonQuery() End If Next objConnection.Close()Catch ex As Exception errorLabel.Text = "Failed because:" & ex.Message End Try End Sub I get this error: Violation of PRIMARY KEY constraint 'PK_CategoryVacancies'. Cannot insert duplicate key in object 'dbo.CategoryVacancies'. The statement has been terminated. It always inserts only the first value of the checkboxlist, instead of looping through and inserting all the rows. Anyone know what's going wrong? Many Thanks
Hi All, am new to sql server in my application I am having one Asp.net web page, the user has to enter values for 5 fields(Empno,Empname,salary,deptno,deptname) in that web page and for that i created two tables in sql serverEmp Table- empno(primary key),empname,salaryDept Table- Deptno(primary key),empno(Foreign key ),Deptname(with some check constraint.) After the user enter all the values in the Asp.net web page now i want to store data into database for that i wrote the following stored procedure...create procedure usp_EmpDept @empno integer, @empname varchar(15), @salary money, @deptno integer, @deptname varchar(10) As Insert into emp(empno,empname,salary)values(@empno,@empname,@salary) Insert into dept(deptno,Empno,deptname)values(@deptno,@empno,@deptname) but the problem is whenever some constaint violation for eg. if some check constraint violation in Dept table its inserting the values in the Emp table only, but my requirement is, It must enter into both tables if there is no constaraint violation otherwise it has to ignore both the tables. And also please suggest is there any better way to insert values into two tables other than using the stored procedure Any help will be greatly appreciated..Thanks,Vision..
I have a sql select query that I'm pulling from a "Years" table to link to 3 columns in an Items table.ZCValuesYear table has two colums: YearID and YearYearID Year1 20042 20053 20064 20075 2008...I want to bind the "Year" value to the three colums in the ZCItem table: ItemUseFirstYearID ItemUseLastYearID ItemYearIDThe query below will pull all the "ID's" for each of the colums, but how would I make it pull the "Year" value (instead of record 4, it would pull 2007 instead)?<asp:SqlDataSource ID="sqlItemSelect" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnString %>" SelectCommand="SELECT ZCPartVault.PartVaultID, ZCPartVault.PartVaultItemID, ZCValuesYear.Year, ZCItem.ItemName, ZCItem.ItemUseFirstYearID, ZCItem.ItemUseLastYearID FROM ZCPartVault FULL OUTER JOIN ZCItem ON ZCPartVault.PartVaultItemID = ZCItem.ItemID FULL OUTER JOIN ZCValuesYear ON ZCItem.ItemUseLastYearID = ZCValuesYear.YearID AND ZCItem.ItemUseFirstYearID = ZCValuesYear.YearID AND ZCItem.ItemYearID = ZCValuesYear.YearID" > </asp:SqlDataSource>
