Linq Question: Select Returns A DBNULL For An Integer Column

Feb 21, 2008

I have quite a few tables which allow NULL values. I have to struggle a lot with DBnull Exceptions :|
example: col1,col2,... are all columns of type Integer and possibly NULL.
var query =
from person in table
select new { person.col1, test = (int?) person.col2, person.col3, person.col4, ...};

As soon as my result encounters a DBNull value.. the whole query fails. This is really bad.
How would I return the valid values.. and set the keys where there is no value to a null type? (e.g. int -> 0)
I tried using "(int?)" but I'm not *really* sure what it does :-) Anyway.. it has no effect :-)

View 1 Replies

Help Getting My If Statement To Reconize That My Stored Procedure Returns DBNULL

Mar 31, 2008

Ok well i have a stored procedure that returns an integer between 1 and 5. My problem now is i want to check to see if the table will return NULL and if so i don't want to assign the value to my variable otherwise it'll trow an error. My code is listed below but i keep getting the error "Conversion from type 'DBNull' to type 'Integer' is not valid." i've also tried If getoptionpicked.Parameters("@optionpicked").Value = Nothing ThenIf getoptionpicked.Parameters("@optionpicked").Value Is system.dbnull Then below is the rest of the code If getoptionpicked.Parameters("@optionpicked").Value Is Nothing Then Else optionpicked = getoptionpicked.Parameters("@optionpicked").Value If optionpicked = 1 Then option1.Checked = True ElseIf optionpicked = 2 Then option2.Checked = True ElseIf optionpicked = 3 Then option3.Checked = True ElseIf optionpicked = 4 Then option4.Checked = True ElseIf optionpicked = 5 Then option5.Checked = True Else End If End If

View 1 Replies View Related

Select Statement That Returns The Column Names And Keys

Jul 20, 2005

Does anyone know a select statement that would return the column namesand keys and indexes of a table?Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 3 Replies View Related

Why Is There A Parameter That Returns An Integer In My Stored Proc?

May 9, 2008

I was comparing the parameters for two stored procs that I made using the SQL Server 2005 express management studio. Both of these sprocs only inserted one field into a single table. These were both of the type varchar.

One of the sprocs had "nocount on" and the other did not. I thought I would see the returns integer parameter in the sproc that did not have "nocount" set to on. I thought this is what returns an integer to validate an insert. Obviously, I am confused about how this works.

Can anyone help me to understand that difference between nocount on and the parameter that returns an integer.

Any help is appreciated.

View 1 Replies View Related

Select For Cursor Returns 0 Same Select Highlighted Returns 2,000+

Jul 23, 2005

Grrr!I'm trying to run a script:print 'Declaring cursor'declare cInv cursor forward_only static forselectdistinctinv.company,inv.contact,inv.address1,inv.city,inv.state,inv.postalcode,inv.cmcompanyidfromdedupe.dbo.ln_invoice as invleft joindedupe.dbo.customerid as cidondbo.fnCleanString(inv.company) = cid.searchcowhere((inv.customerid is nulland cid.searchco is null)and (inv.date >= '01/01/2003' or (inv.date < '01/01/2003' andinv.outstanding > 0.01))and not inv.company is null)print 'Cursor declared'declare@contact varchar(75),@company varchar(50),@address1 varchar(75),@city varchar(30),@state varchar(20),@zip varchar(10),@cmcompanyid varchar(32),@iCount int,@FetchString varchar(512)open cInvprint 'cursor opened'fetch cInv into@company,@contact,@address1,@city,@state,@zip,@cmc ompanyidprint 'Cursor fetched @@Cursor_rows = ' + cast(@@cursor_rows asvarchar(5))All the prints are there to help me figure out what's going on!When I get to the Print 'Cursor fetched @@cursor_rows....the value is 0 and the script skips down to the close and deallocate.BUT, if I just highlight the Select...When section, I get over 2,000rows. What am I missing?Thanks.

View 6 Replies View Related

Adding A New Column Is DBNULL

Jun 2, 2007

Hello,

VS 2005

I am developing the database application. This is a live database and is being used by the customer. I am to release a new version and I had to add new columns to fit the requirements DateStarted (DateTime), and TotalHours(Int) into a database table.There are already over a 1000 rows in this table.

When the customer wants to look at a record in this table and insert the value into the text boxes (Front-end), when it gets to the DateStarted or TotalHours it comes up with a error message:"

The value for column 'DateStarted' in table 'IncidentTask' is DBNull."The method for inserting is:

Code Snippet

Me.dtDateStarted.Value = Me.DsIncidentsControl.IncidentTask(0).DateStarted

What are the possible solutions to this problem? Would it mean checking for a DBNULL before displaying in the textboxes? Or updating the new column rows with an date:

Code Snippet

UPDATE IncidentTask SET DateStarted = '1/1/2005' WHERE (DateStarted IS NULL)

Many thanks for any suggestions,

Steve

View 1 Replies View Related

How To Do Where In Select In Linq?

Mar 7, 2008

var query = from cloc in context.t_companylocs where (cloc.ref_company == companyid) && (cloc.street == attributes["Street"]) && (cloc.postalcode == attributes["Postalcode"]) && (cloc.country in countrylist) && (cloc.city in citylist) select cloc; attributes is a Dictionary<string, string> object.countrylist and citylist are List<string> objects.Of course the syntax above doesn't work. It's basically what I'm trying to achieve :-)A quick and dirty solution would be to just drop the two where constraints containing the "where in" statement and handle that part in the following foreach() loop.Can anyone please explain how you would do it properly?regards

View 1 Replies View Related

Analysis :: Calculated Column That Makes Integer In YYYYMMDD Format Form Date Column

Oct 12, 2015

I am trying to create a whole number DAX calculated column that is derived from a date column. Basically it gets the date from the source data column and outputs it as an integer in the YYYYMMDD format.So 01/OCT/2015 would become --> 20151001...I've been fidgeting with DAX but my problem is that I keep missing the leading zeroes for months and days. So 01/March/2015 becomes 201531 which is not what I want (I need 20150301 in this case).

View 2 Replies View Related

Select Where Value Is INTEGER

Feb 12, 2014

I am trying to select only values within a column are intergers.

My statement looks like this:

(I am using MSSQL querying into a Pervasive DB via a Linked Server)

SELECT Invoice, Invoice_Date, Description, INVOICE_Code_1
FROM API_PENDING__INVOICE
WHERE (API_PENDING__INVOICE.INVOICE_CODE_1) is integer???

Obviously, this is not working...

View 6 Replies View Related

Select Integer Only From Substring

Nov 19, 2014

I would like to select the integer only from the substring of parameter.

The query as below:

DECLARE @Capacity VARCHAR(100)
BEGIN
SET @Capacity = Substring('KK10eads-00l5b1', 2, 4)
print @Capacity
END

The result i get is K10e

However, i only want 10

View 2 Replies View Related

Integer Column With Min / Max Value

May 6, 2015

Can I put a constraint on an integer column that will only allow a certain range of numbers to be entered, or do I have to put that into the application layer only?

I'd like the range to be 0 to 30 as the only allowable values. The only thing that I could think of was to create another table and populate with 0,1,2...,30 and put a foreign key on the new column that wouldn't allow anything not in that list but I was wondering if there was a better way.

View 3 Replies View Related

'Select Top 10 * ...' Returns Data But 'Select * ..' Does Not

Dec 7, 2006

HiI have a query that is performing very strangely.I f I put a top statement in it returns rows,soSelect top 10 * from .......returns 10 rowsbut without it then no data is returnedSelect * from ..........returns 0 rows.

View 1 Replies View Related

Select Returns One Row From Two

Jul 20, 2005

Goodmorning,Could I have a SELECT statement that normally returns two rows,but that instead returns one row appending to the first row the secondone of the result ?For exampleQuery: "SELECT username from tab1 where year in (2001,2002)"Result:1� - "'John'"2� - "'Adam'"Instead I need:Result:"'John','Adam'"?I have Win2000 Pro , SqlServer2000.Thank You--Posted via Mailgate.ORG Server - http://www.Mailgate.ORG

View 3 Replies View Related

Could A Datatype Of A Column Be Like Integer Array?

Aug 7, 2004

Hi everyone....
I need a column in my table that its DataType should be as integer arrays how can I implemant it in my sqlserver table.
Any help appreciated.

View 5 Replies View Related

AVG Function On An Integer Column- Truncation

Oct 25, 2006

When I use the AVG Function on an integer column, the result is truncated

Example:

Select AVG(field1) from table1

Field1 is an int field and has 4 rows with the values 114,115,115 and 115. This will return 114.

I can get the correct result by using the following SELECT:

SELECT CAST(AVG(CAST (field1 as decimal(18,1)))+ .5 as int) from table1

Am I missing something here? Is there an simpler way to do this?

Any help will be appreciated.

Steve D.

View 6 Replies View Related

Select Sum() Returns Null

Jan 21, 2004

hi,

i have the following query to sum the total due balance for a customer:

select sum(outstanding)from out where customer = 'myvariable' the problem is when the customer has no outstanding it returns NULL is there a way to return 0 when there are no outstanding?

thanks

View 7 Replies View Related

Need Query To Select VarChar Rows That Can Convert To Integer

Jul 23, 2005

I have a table of zip codes, some with Canadian zips. I'd like to takea zip code and search for nearby zips. For example:Dim theZip As Integer = textbox1.text....Parameter.Add(@ziplow, SqlDbType.Int, 5).Value = (theZip - 10)....Parameter.Add(@ziphigh, SqlDbType.Int, 5).Value = (theZip + 10)SELECT * from ZIPCODES where Cast(zip_code as Integer) BETWEEN @lowzipAND @highzipProblem is the letters in the Canadian records cannot be cast asintegers for this process. I get this error:Syntax error converting the varchar value '53151 1' to a column of datatype int.Is there a SQL query that can exclude if no cast can be made to anInteger?Thanks!Anton

View 5 Replies View Related

Query: Getting AVG() Of An Integer Column As A Floating Point

Feb 12, 2004

I have the following query:

SELECT AVG(respondent_question.answer)

Now, the 'answer' column is an integer. However, I want the query to return a floating point number with one decimal place (i.e., 5.4, 2.3)

Since the column is an integer, the query returns only an integer by rounding to the nearest integer. How can I do this? Thanks!

View 1 Replies View Related

Adding An Identity-based Integer Column

Jul 3, 1999

I need to add a column that has the datatype of integer with a seed and identity increment to a table that already exists and has data in it - and can't all NULLs. I have heard that this not possible if the table already exists.

I have downloaded the demo's of SQL Programmer and a couple of Embarcadero programs to see if that would help, but have had no succes to date.

Thanks for any help,

Alan

View 2 Replies View Related

Display Output Where Integer Value Is Column Data

Dec 1, 2011

I want to display the following output where integer value is column data.

{[Advertiser].[AdvertiserKey].&[4000],
[Advertiser].[AdvertiserKey].&[4001],
[Advertiser].[AdvertiserKey].&[4002],
[Advertiser].[AdvertiserKey].&[4003],
[Advertiser].[AdvertiserKey].&[3661],
[Advertiser].[AdvertiserKey].&[3662]
}

View 4 Replies View Related

SQL 2012 :: Integer Column Shows 5.00000000000000000 As Value

Sep 4, 2015

INSERT WITH SELECT HAS THIS:
,Convert(Int,TARG_SALES) AS TARG_SALES

Treats the column as if it were formatted decimal !!

View 4 Replies View Related

Convert Column Of Data Alphanumeric To Integer

Nov 14, 2013

I need to convert a column of data from alpha numeric to integer.

I am only querying the tables i.e. i don't have access to actually change the data tables themselves.

CAST or CONVERT throws up an error. Are there any other commands i can use at the query stage?

The data I need to convert is always actually a number. i.e. even though it is recognised as alpha numeric, the figure is a number.

I just need it to be converted to an integer so i can SUM it etc.

View 8 Replies View Related

Duplicating Rows Based On A Integer Column

Dec 17, 2007

View 4 Replies View Related

Select * From Sysperfinfo Returns 0 Rows.

Aug 15, 2003

Hi,

I have DB monitoring jobs which use Sysperfinfo to monitor some of the counters. On One SQL 2K Server since few days the select on sysperfinfo returns 0.

Do you think I need to start any service from the OS side to enable this?

Your input is highly apppreciated.

Thanks,:confused:

View 2 Replies View Related

Select Anything From Table - Returns Values

Jan 30, 2014

I'm trying to understand why I can enter a query such as:

select 5,"random"
from customers;

and get two columns with 5 and "random" in every respective column field.Why don't I receive a syntax error ?

View 4 Replies View Related

Select Statement Returns No Results

Jun 6, 2006

I am using the following conditional select statement but it returns no results.

Declare @DepartmentName as varchar
Set @DepartmentName = null
Declare @status as bigint
Set @status = 4
IF (@DepartmentName = null) BEGIN

SELECT CallNumber AS [Call Number], Problem, Solution, Note
FROM AdminView
WHERE (Status = @status)
ORDER BY CallLoggedDT
END
ELSE IF (@DepartmentName <> null) Begin

SELECT CallNumber AS [Call Number], Problem, Solution, Note
FROM dbo.AdminView
WHERE (Status = @status) AND
(DepartmentName = @DepartmentName)
ORDER BY CallLoggedDT
end

when i run the 2nd half by itself it tells me to declare @status but not @departmentname. whats going on???

Chris Morton

View 3 Replies View Related

Transact SQL :: How To Identify When There Is A Break In Continuity Of Integer On A Column

Jul 16, 2015

I have two columns, one column has a document ID and a given document can have many pages. The second column has the pages. Now I want to find out when the page number is broken. For example, if doc ID 1 has 3 rows and each of the three has 1,2,3 and then the fourth row has document 1 but the value jumps from 3 to 7 and then goes to 8,9,10 and then jumps again and starts from 17, i want to have the ranges identified.

DocID Page Number
1 1
1 2
1 3
1 7
1 8
1 9
1 10
1 17
1 18
1 19
1 20

The result should look like :

DocID Page Number
1 1-3
1 7-10
1 17-20

View 4 Replies View Related

Select Distinct Returns Multiple Rows With Same Value

Apr 24, 2008

I have a Select Distinct myfield that returns multiple rows with same value for myfield when it should only one. Why is this happening?

View 4 Replies View Related

CLR Test Script SELECT Returns No Row Data

Sep 12, 2006

Hi,

The test.sql scripts I write to test CLR stored procedures run successfully, but when I want to display the resulting data in the database with a simple "SELECT * from Employee"

I get the result as:
Name Address
---- -------
No rows affected.
(1 row(s) returned)

But not the actual row is displayed whereas I would expect to see something like:

Name Address

---- -------

John Doe

No rows affected.

(1 row(s) returned)

I have another database project where doing the same thing displays the row information but there doesn't seem to be a lot different between the two.

Why no results in first case?

Thanks,
Bahadir

View 1 Replies View Related

DB Design :: Convert Integer Date (MMDDYY) And Integer Time (HHMMSS) To DateTime Format?

Jul 20, 2015

Working on a new database where the Date and Time are stored in a Date Time Field.

Then working on an OLDER database file within the same SQL Database contains these 2 items as integers:

transDate = "71615" (July 16, 2015)
transTime = "12345" (01:23:45 AM)

How do we convert both of them into a single SQL DateTime field such as "2015-07-16 01:23:45.000" so that it can be used in a join restricting to a date time in a different SQL File that properly has the DateTime in it?

This works well for converting the transDate Part in the select statement:

dbo.IntegerToDate(at.transDate) as transDate

* That returns: "2015-07-16 00:00:00.000"

* The resulting data must work directly in a Microsoft SQL Server Management Studio Query using either using the "on" statement or part of the "where" clause. In other words, NOT as a stored procedure!

Also must be able to be used as a date difference calculation when comparing the 2 files Within say + or - 5 seconds.

View 3 Replies View Related

Binary Column Always Returns 0 For ISNULL

Jan 29, 2007

Why don't i ever get return value of 1 when the following binary column (profSignature) is null?
RETURN SELECT ISNULL(profSignature, profSignature) FROM mpProfiles WHERE ApplicantID = CAST(@CID AS INT) AND ProfileID = CAST(@PID AS INT)

View 7 Replies View Related

Detecting Carriage Returns In A Column

Jun 12, 2006

Can anyone provide me with some SQL that will identify rows from a table where a varchar column named "Notes" contain Carriage Returns?

I know that with report writer SQR I can translate CR's to white space but I do not know of any Sybase function that will allow me to do the same, any ideas on this well would be appreciated.

View 3 Replies View Related

Concatenated Column Returns Null

Mar 7, 2008

Hi folks,I have an issue with a column I created in my query called Instance.SELECT Object_Id, Event_type, Audience, Subject, Provider, Academic_Year, Start_date, End_date, CONVERT(varchar, Academic_Year) + ' (' + CONVERT(varchar, Start_date, 103) + ') : ' + Event_type AS InstanceFROM EventsORDER BY Event_type Above is my query. The problem is because the start date column can be null, it also returns the Instance column as null for that row.I thought it would have just missed out the date and display the rest, but it doesn't.Is there any way I could get the Instance column to display a value, when the start date is null?ThanksEdit: Managed to sort it using ISNULL()

View 14 Replies View Related