Anybody know how to use the In Operator in the select stmt when one of the values is NULL? Here is my code:
select * from Client
where AgencyId is not null
--and AgencyName is not null
and left(controlnumber,2)<>'72'
and left(controlnumber,2) <>'13'
and CPAFirmId IN ('0','',NULL)
Since the IN operator requires all values to be in quotes, it does not work properly when NULL is specified. I also tried 'NULL' but that gives an error.
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)
In my report i have CNAME parameter , which allows null value. I checked Allow null value check box in report parameter properties.
when i preview the report , it displays checked NULL check box beside CNAME parameter . I want to give some meaningful name(i.e.ALLCustomers) to this checkbox instead of NULL.
In the flat file SampleID and Product are populated in the first row only, rest of the rows only have values for Rep_Number, Protein, Fat, Solids.
SampleID and Product are blank for the rest of the rows. So my task is to fill those blank rows with the first row that has the sampleID and Product and load into the table.
I have a report that is run on a monthly basis with a default date of null. The stored procedure determines the month-end date that it should use should it be sent a null date.
The report works fine when I tell it to create a history entry; however, when I try to add a subscription it doesn't appear to like the null parameter value. Since I have told the report to have a default value of null it doesn't allow me to enter a value on the subscription page.
Now, I suppose I could remove the parameter altogether from the stored proc, but then the users would never be able to run the report for a previous time period. Can someone explain to me why default values aren't allowed to be used on subscriptions when they seem to work fine for ad hoc and scheduled reports? This is really quite frustrating as most of my reports require a date value and default to null so that the user doesn't have to enter them for the latest data.
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
I run a stored procedure for which I have a return variable. The stored procedure returns the ID of a row in a table if it exists:
m_sqlCmd.ExecuteScalar();
The m_sqlCmd has been fed an SQLParameter with direction set to output. When the stored proc returns, I want to test it. Now when there IS a row it returns the ID ok. When the row doesn't exist, in my watch I have:
m_sqlParam.SqlValue with value {Null}
I can't seem to work out how to test this value out. I've tried several things but none seem to work.
This line compiles ok, but the following runs into the IF statement as if the SqlValue is null??
if (m_sqlParam.SqlValue != null).... {
// I'm here!! I thought the watch says this is null??? }
Sorry if this is obvious, but I can't work this one out!!
Looks like there was a fix and then I read this fix is not a fix. Does anyone know how this can be rectified? Does it mean that only Windows authentiation is the only way it works. The Software is over 2 years old, there are no excuses.
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
I am getting this error: "Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails." -- But my value is not null. I did a response.write on it and it show the value. Of course, it would be nice if I could do a breakpoint but that doesn't seem to be working. I'll attach a couple of images below of my code, the error, and the breakpoint error.
Server Error in '/' Application.
Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.Source Error:
Line 89: sContact.Phone = sPhone.Text.Trim Line 90: sContact.Email = sEmail.Text.Trim Line 91: sContact.Save() Line 92: Line 93: Dim bContact As Contact = New Contact()Source File: F:InetpubwwwrootOutman KnifeCheckout.aspx.vb Line: 91 Stack Trace:
hi,my structure table in database:Amount float(53) not null default 0when i try to run his script:alter table ABC alter column Amount float(53) nullit can only set the Amount to allow null, but can't set the defaultvalue to empty.anyone know how to set the field to allow null and default set toempty, no value.thanks
I've built a sample CLR function with the following declaration....
CREATE FUNCTION GetManager(@DeptCode nvarchar(3)) RETURNS nvarchar(1000) WITH RETURNS NULL ON NULL INPUT AS EXTERNAL NAME Assembly1.[ClassLibrary1.MyVBClass].MyManager
It returns the value "Unknown" as it would have for any unknown DeptCode, as-programmed.
I'm of the theory it should have returned NULL without actually firing the function? Or is this only for non-CLR items... or stored procedures, not functions?
We have a case where in we should show date based on conditions for e.g if we had a column defined as
col varchar(10) then we would show col as 'NULL' for some condition and actual value when no condition
Normaly date values are stored here e.g under col 20150901 .
Case when col>'20150901' then 'NULL' else col end as Derivedcol
Note this is an extract process and we are presenting data by pumping the data in a table .
Now there is another similar column -colz varchar(10) which stores date but doesnt have case condition so whenever date has no value its shows null which is database null.
So whats the difference between database null and string null ?
How can we show database null for the case condition instead of string "null"?
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!