I need to have a select statement to take all the products with the LABEL_ID containing 1 AND 2. I can't use the IN operator as it will return me results with LABEL_ID 1 OR 2. Is there any solution to this? Sorry I'm relatively new to mysql and not familiar with the select statements.
I'm currently working on something like a search engine. Hope to have some good advice here :)
I'd like to change query:SELECT DM.*, 'condition1', NULL FROM DMWHERE (condition1)UNIONSELECT DM.*, NULL, 'condition2' FROM DMWHERE (condition2)to one SELECT like thisSELECT DM.*, WasCondition1, WasCondition2 FROM DMWHERE (condition1) or (condition2)but how to fill in the WasConditionX column?The UNION version was bad because in case a row fulfilled 2conditions, it was repeated instead of joining them, such like this:SELECT DM.*, 'condition1', 'condition2' FROM DMWHERE condition1 AND condition2UNIONSELECT DM.*, 'condition1', NULL FROM DMWHERE condition1 AND NOT condition2UNIONSELECT DM.*, NULL, 'condition2' FROM DMWHERE NOT condition1 AND condition2or (say DM has columns A, B, C):SELECT A, B, C, SUM(cond1), SUM(cond2) FROM(SELECT DM.*, 1 AS cond1, 0 AS cond2 FROM DMWHERE condition1UNIONSELECT DM.*, 0 AS cond1, 1 AS cond2 FROM DMWHERE condition2) AS DMGROUP BY A, B, C
I'm using sql server 2005, i want to know is there any substitute for BCP in sql server 2005, like how we have DTS in 2000 and SSIS in 2005, I heared that BCP will be deprecated in the next version of Sql server. so i dont want to implement BCP in 2005 version,
Will someone please tell me whether i have to implement BCP in sql server or we have some other option, i dont want to use SSIS instead of BCP because latter is faster.
Do anyone know how I can replace or Substitute the current value of a form textbox with another value.For example, the string "YES" appears on the textbox but hit the SAVE button I would like "TRUE" to be entered in the SQL Server database table. Thanks.
Hi I have only been coding in .Net for about six months, and am not sure if this is a C# problem or an SQL one. I use the Data Access Application Block in my program.
I have two optional fields on my form (RangeFrom and RangeTo). If the user chooses not to enter data into these textboxes(textbox = ""), an entry on the db is created with null values. It works.
But sometimes the user wants to enter 0 as either an upper or lower end of a range. This is where my problem comes in. My program saves 0 as null too.
In my program I do a test on the textboxes and populate two float values in a business object (objQuestion) accordingly, like this:
//properties public float RangeFrom { get { return cvintRangeFrom; } set { cvintRangeFrom = value; } }
public float RangeTo { get { return cvintRangeTo; } set { cvintRangeTo = value; } }
// some code deleted for readability....
public int AddOption() { string cvstrSpName = "addOption"; SqlParameter [] cvstrStoredParams = SqlHelperParameterCache.GetSpParameterSet(gcstrConnectionString, cvstrSpName, true); //lines deleted for readability... //check if the optional fields have a value associated with them. if not, assign dbnull.value. cvstrStoredParams[4].Value=(cvintRangeFrom != Convert.ToSingle(null) ? cvintRangeFrom : (object)DBNull.Value); cvstrStoredParams[5].Value=(cvintRangeTo != Convert.ToSingle(null) ? cvintRangeTo : (object)DBNull.Value); //lines deleted for readability... SqlHelper.ExecuteNonQuery(gcstrConnectionString, CommandType.StoredProcedure, cvstrSpName, cvstrStoredParams); return(cvintOptionID = Convert.ToInt32(cvstrStoredParams[0].Value)); }
I use Convert.ToSingle when working with nulls (or possible nulls) because I get an error when I use float.parse for this.
The thing is, after this method AddOption has been executed, I test the value if the business object's rangefrom (that is where I entered 0) and display it on my form. I still shows a 0, but on my database table it is null!
objQuestion.AddOption(); //txtrangefrom.Text=""; on the next line I test the value in the business object... txtrangefrom.Text=objQuestion.RangeFrom.ToString(); // and this displays 0!!! //txtrangeto.Text=""; txtrangeto.Text=objQuestion.RangeTo.ToString();
So to me it seems the problem seems to be either the DAAB or on the SQL side, but hopefully somebody can prove me wrong! I was thinking that it could also be float.parse/Convert.ToSingle methods and have done various tests, but I am none the wiser... Any help or ideas will be greatly appreciated...
Hello once again, My last question concerns (temporary) tables. In a web form (VWD Express, Asp,Net 2.0, VB, .mdf db, Ajax) I have two gridviews. One gets its data from textboxes and dropdownlists as the user clicks an ok button. This data is then sent to a "junk" table in the db. The other gridview retrieves this data "automatically" and displays the data, filtered and grouped by some SQL statements. When the user finally clicks an Insert record button, the data from this second gridview gets inserted in another table in the db and the data from the first "junk" table is disposed. I guess it shouldn't be necessary to insert, retrieve, and delete the data in and from the "junk" table. Even though it does work like a charm, I guess it would be better if I could do it wit hsome kind of "temporary table", array or the like so that the db wouldn't need to be updated to this extent. Still, I really like the simplicity of using gridviews and the rest of the page's code is complex enough, so I don't really want to substitute this behaviour for tons of hand-written, hard-to-follow, and hard-to-integrate code. What are my options? Any help would be greatly appreciated. Pettrer
Do a manual installation of SQL Server 2005 (which could be an upgrade from SQL Server 2000) and record the steps. Replay the steps for an unattended installation of SQL Server 2005.
Problem
There isn't a setup.ini file for SQL Server 2005. There is a template.ini for unattended installations of SQL Server 2005, but it doesn't obviously map to selections on manual installation dialog boxes.
Desired workaround
Capture the steps of a manual installation and use them to populate template.ini.
Relevant background for a potential solution
A manual installation creates log files. So does an unattended installation. But the unattended installation writes all of the log files into a single zip file.
What is important to know is that an unattended installation creates an additional file, a *datasource.xml file. This XML file is found in the zip file with all of the other log files. If you open this XML file, you'll see that the attribute names correspond to keywords in template.ini.
Potential solution
If a manual installation could be forced to generate a *datasource.xml file, then it should be possible to map the settings in the file to keywords in template.ini.
So, how can a *datasource.xml file be created? In post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=359678&SiteID=1, Jeffrey Baker says "add LOGNAME=<path to cab> and run setup again". Unfortunately, these instructions are too vague. Where is this added? To the command line, to a setup.ini (if so, in which directory, and in which section should he addition be made?). I did lots of searches and couldn't find the answers.
I have a table with a datetime field (named "lnDateDone") and an integer field (named "PlannedHour"). I want to build a new datetime field with the same value as lnDateDone and replacing the hour part by the PlannedHour field. Example:
lnDateDone = '2014-04-09 13:22:31.544' HourPlanned = 14 new datetime field = '2014-04-09 14:00:00'
I tried the following:
CAST(SUBSTRING(CAST(lnDateDone AS VARCHAR(25)),1,12) + CAST(PlannedHour AS VARCHAR(2)) + SUBSTRING(CAST(lnDateDone AS VARCHAR(25)), 15, LEN(lnDateHeureFin)) AS SMALLDATETIME) AS DatePrevue
but it won't work when casted in DateTime format, only in varchar, but I want it in datetime as I need to do compute some values using date manipulation functions.
Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you
set ansi_nulls off
go
declare
@inFileName VARCHAR (100),
@inFileSize INT,
@Id int,
@inlanguageid INT,
@inFileVersion VARCHAR (100),
@ExeState int
set @inFileName = 'A0006337.EXE'
set @inFileSize = 28796
set @Id= 1
set @inlanguageid =null
set @inFileVersion =NULL
set @ExeState =0
select Dr.StateID from table1 dR
where
DR.[FileName] = @inFileName
AND DR.FileSize =@inFileSize
AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)
Just a quick question that I really couldn't find using google or anything because it was too bland. How do you return the whole database while still using the LIKE operator. I have a textbox that you can use to search the database but I want all the data to be displayed underneath the box so you can "browse" the data if you don't know the name of what you are looking for. What would I put as the default value? I used just defaultvalue="%" and that worked in 1 area, but not another. Is this the right way? Thanks, Chris
I have a query and for the life of me I cant figure it out. I have a web form with a number of textboxes on it e.g. Title, subtitle, isbn. I also have a search button. This search page allows a user to enter any combination of fields in the form and then when they click the search button, a datagrid is diaplyed showing the results (gets the results from a SQL DB). I have got the code working if the user selects say one item, but I dont know how to allow it so that the user can enter any combination of items in the form and the search button will search for it based on what the user says. I think im gettng confused with my logical operators in the sql statement. If any one can take a look I would be greatful.public void BindData(){String @BookID = TxtBookID.Text;String @Title = TxtTitle.Text;String @Subtitle = TxtSubtitle.Text;String @ISBN = TxtISBN.Text;String @AuthorFName = TxtForename.Text;String @AuthorLName = TxtSurname.Text; SqlDataAdapter ad = new SqlDataAdapter("SELECT Book_ID_Internal, Title, Subtitle, ISBN, Edition, Publication_Date, Author.First_Name, Author.Last_Name FROM Book, Author WHERE Book.Author_ID = Author.Author_ID AND Book.Book_ID_Internal = '" + @BookID + "' AND Book.Title = '" + @Title + "' AND Book.Subtitle = '" + @Subtitle + "' AND Book.ISBN = '" + @ISBN + "' AND Author.First_Name = '" + @AuthorFName + "' AND Author.Last_Name = '" + @AuthorLName + "'", conn);DataSet ds = new DataSet();ad.Fill(ds, "Book");GridView1.DataSource = ds;GridView1.DataBind();} protected void BtnSearch_Click(object sender, ImageClickEventArgs e){BindData();}
I have a select statement that consists on something like this :
SELECT * FROM Products WHERE Ref LIKE '%" & Search & "%'
well this works ....but the problem is that i have some Ref in this format "MC-909" and if the user makes a search with the word "MC909" ...it dosent return any value ...
where nt.Project_type='A' and nt.status='Done' and nt.project_id like '06%'
I also need to add an additional condition : like '07%'. How can I do this, i. e I need the projects where the type = A , status = Done and project_id starting with 06 and 07.
Hi , This is my first message on this forum. Well, I want to write a query which finds the length of the employee names whose name starts with J, A or S?.
This is what I did:
SELECT LENGTH(ENAME) FROM EMP WHERE ENAME LIKE 'A%' OR LIKE 'J%' OR LIKE 'S%'
This query does not work. Can anyone tell me what is wrong with it?
I have a column named tags which contains tagnames seperated by commas now i have to compare this column with a parameter named tagname which contains a single tagname how to do this.Please help me
here is my procedure
ALTER PROCEDURE [dbo].[ProcSoftwaresGetSoftwaresbyTags] ( @FileTag Varchar(64) ) AS BEGIN
SELECT Category.CategoryName, Softwares.SoftwareFileID, Softwares.CategoryID, Softwares.FileName, Softwares.FileImage,Softwares.FilePath, Softwares.FilePublisher, Softwares.Adminrating, Softwares.FileDownloadCount,substring(Softwares.FileDescription,1,200)as FileDescription,convert(Varchar(12),Softwares.FilePostedDate,109)as FilePostedDate,Softwares.FileSize, SystemRequirements.OperatingSystem FROM Category INNER JOIN Softwares ON Category.CategoryID = Softwares.CategoryID INNER JOIN SystemRequirements ON Softwares.SoftwareFileID = SystemRequirements.SoftwareRequirementID where substring(Softwares.FileTag,0,3) LIKE substring(@FileTag,0,3) and FileStatus=1 END
I have two tables athat are structurally identical. I wish to obtain rows that are in the first table that are not in the second one (e.g. the EXCEPT operator). EXCEPT is not supported directly in TSQL...
I'm having problems using the between operator in a query statement.
Select * from <mytable> where date between @date1 and @date2
The date values with a hour specified, aren't returned. What is the approach you would recommend here?
Thx
EDIT: by playing with this problem I've figured out I can append the hour to the date like this: <date> between @fromDate + '00:00:00' and @toDate + '23:59:59'
this seem to work, but I'm not sure if this is correct
Hi, I'm trying to build a form that will allow users to choose their own parameters for the Select statement in the SqlDataSource. These results would then be displayed back to a GridView control. The only problem I am having is figuring out how to allow them to choose the operator (=, <, >, <>, etc) from a dropdown list. Does anyone have any suggestions on how to do this with a SqlDataSource control? It is probably something simple and any help would be much appreciated. Thanks
please do help. Have been wrestling with this for about 3 hours and read a buncha forums + searched the tutorial lists.. AARRGH!
Anyhow,
I have to paginate a datalist (and I really can't use a datagrid because of the layout, blame the bluddy graphic designer)
I want to return the top 8 rows, the next 8 top rows and so on.
This is the sql string I have: 'retrieve pagination in order to construct the rest of the sql string Dim startrec As Integer If pageno = 1 Then startrec = 0 Else startrec = (pageno - 1) * pagesize End If ' this builds the sql string, getting ONLY the records we need for this. Page size is a constant expressed on the base page ' startrec is the record where I want to start in the db. strsql = "select top " & pagesize & " * " & strsqlbasic & " and itemID>" & startrec & " order by itemnotes1 asc" noresults.text = strsql & " <br> " & searchwhat & searchfor
strsqlbasic is constructed elsewhere and is just the 'from X where y = var
Of course, this returns all records where the value of itemID is greater than the value of startrec. How would I change this so it returns the next 4 rows starting from the row number given by the value of startrec?
strsql = " select * from dic where english='" & SearchBox.Text Or TextBox4.Text & "'"The machine said Input string was not in a correct format. How can i select database with or operator in asp.net .Is it possible to select database in sql from more textbox ?
Consider the following SQL query: SELECT ENAME,SAL FROM EMP,ASG,PAY,PROJ WHERE EMP.ENO=ASG.ENO AND PAY.TITLE=EMP.TITLE AND ASG.PNO=PROJ.PNO AND ASG.DUR=48 AND BUDGET>200000 Give the possible operator trees:right-deep,left-deep and bush Tank you very much!
