Can @@ROWCOUNT Return NULL?
Aug 29, 2005
SQL Server 2000 SP3.
Is it possible for the @@ROWCOUNT function to return NULL after a
statement? I am troubleshooting a relatively large stored procedure with
multiple SELECT statements and a couple of INSERTs into table variables.
Immediately after each statement I save the value returned by @@ROWCOUNT to
a local variable. That information eventually is passed back to the client
via one output parameter, for all statements in the procedure.
Occasionally, the value returned via that parameter is NULL. This cannot be
reproduced by re-running the SP with the same input parameters.
Before doing any further troubleshooting, I would like to rule out the
possibility that @@ROWCOUNT can actually return a NULL under some
circumstances. From searching the archives, it appears that in SQL Server
7.0 this could happen in the context of a DML query on a table with
triggers. This is not the case here - the only DML queries are INSERTs into
table variables, all other queries in the SP are SELECTs.
Any related information would be appreciated.
--
remove a 9 to reply by email
View 3 Replies
ADVERTISEMENT
Sep 20, 2006
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)
AND DR.languageid = isnull(@inlanguageid,null)
AND DR.[ID]= @ID
)
go
set ansi_nulls on
View 3 Replies
View Related
Sep 24, 2007
HiI'm using an sqldatasource control in my aspx page, and then executing it from my code behind page (SqlDataSource1.Insert()), how do i retrieve the number of rows (@@rowcount) which have been inserted into the database and display it in my aspx page. I am using a stored procedure. thanks
View 1 Replies
View Related
Mar 2, 2008
I have stored procedures which have an output parameter with a rowcount.I used to just access the stored procedure directly. However, I'm separating my data access into a method in my data access class, and the method returns a datatable.I'm not sure how I'm supposed to access my output parameter anymore.
View 4 Replies
View Related
Apr 4, 2007
i have 2 stored procedures: a delete and a select. the delete sp returns the rowcount properly. the select returns null. the code for both sp's is extremely simple and extremely similar. when i execute the select sp in server management studio the rowcount shows a 1 as expected. but the calling method gets null.
SP Code
ALTER PROCEDURE [dbo].[RetrieveEmployeeKeyFromAssignmentTable]
@assignmentPrimaryKey int,
@rowCount int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT employeePrimaryKey FROM assignmentTable WHERE primaryKey = @assignmentPrimaryKey;
SET @rowCount = @@RowCount;
END
c# code
SqlConnection conn = GetOpenSqlConnection();
if (conn == null) return true;
SqlDataReader reader = null;
SqlParameter p1 = new SqlParameter(); SqlParameter p2 = new SqlParameter();
try {
SqlCommand command = new SqlCommand();
command.CommandText = "RetrieveEmployeeKeyFromAssignmentTable";
command.CommandType = CommandType.StoredProcedure;
command.Connection = conn;
p1.ParameterName = "@assignmentPrimaryKey";
p1.Value = assignmentPrimaryKey;
p2.ParameterName = "@rowCount";
p2.Direction = ParameterDirection.Output;
p2.Value = 0;
command.Parameters.Add(p1); command.Parameters.Add(p2);
reader = command.ExecuteReader();
if (p2.Value == null) //always true
any suggestions would be appreciated.
thanks. matt
View 3 Replies
View Related
Jul 7, 2006
I have a vb.net application that executes a simple flat file to sql table dtsx package. I want to capture the rowcount to display back to the user to verify the number of rows that were inserted or updated to the table. I have a Row Count component placed between the flat file source(without errors) and the destination component. I have assigned a variable named RecordCount to the Row Count component. So far so good I hope : )
Now, I also use a variable to "feed" the package the flat file source. This works fine, but I cannot figure out how to retrieve the row count information and how to assign that to the variable RecordCount.
Also, if anyone has any insight on the way to work with the OnProgress method in SSIS I would appreciate that as well. In SQL 2000 using DTS I create a "PackageEventsSink" that I had found online, and it worked great for monitoring the progress of the DTS. Can't seem to figure out how to get it to work in SSIS.
Thanx,
Mike
View 11 Replies
View Related
Jul 25, 2007
In sql I perform the following
SELECT * FROM
xlsdci x LEFT OUTER JOIN fffenics f ON f.[derived deal code] = x.[manual dcd id]
which gives me a row count of 2709 rows
In SSIS I have a merge join component (left outer)
left input = xlsdci with a sort order of 1 ASC on [manual dcd id] (OLE DB source component)
right input = fffenics with a sort order of 1 ASC on [derived deal code] (OLE DB source component)
which when run in the IDE gives me a rowcount of only 2594 rows
Why is this so?
Also if I change the join to INNER in the merge join, the number of rows drops dramatically to only 802.
Fair enough, I hear you cry, maybe there are IDs in the 'xlsdci' table that are not in the 'fffenics' table. Ok. But the following SQL reveals that there are only 14 rows(IDs) in 'xlsdci' that are not in 'fffenics'
SELECT * FROM xlsdci
WHERE [manual dcd id] NOT IN (SELECT [derived deal code] FROM dbo.fffenics)
What is going on here?
View 5 Replies
View Related
Feb 2, 2007
i have query which does the following select x from y where t = "House"x is an integer. If no record is found, how do i get it to return 0 rather than null?
View 9 Replies
View Related
May 7, 2008
Hi I have an sql statement that when i put through the query builder and specify NULL it returns all the rows. if i put 0 it returns nothing.how can i change the "IS Null" part of the statement to work with 0. so when i specify 0 for all values it returns all the rows.so i want 0 to work like NULL does 1 SELECT LocationID, TypeID, Title, Description, PropertyID, Price,
2 (SELECT Location
3 FROM Location_Table
4 WHERE (Property_Table.LocationID = LocationID)) AS Location,
5 (SELECT TypeOfProperty
6 FROM Type_Table
7 WHERE (Property_Table.LocationID = TypeID)) AS TypeOfProperty
8 FROM Property_Table
9 WHERE (@MinPrice IS NULL) AND (@MaxPrice IS NULL) AND (@TypeID IS NULL OR
10 TypeID = @TypeID) AND (@LocationID IS NULL OR
11 LocationID = @LocationID) OR
12 (@MinPrice IS NULL) AND (@TypeID IS NULL OR
13 TypeID = @TypeID) AND (@LocationID IS NULL OR
14 LocationID = @LocationID) AND (Price <= @MaxPrice) OR
15 (@MaxPrice IS NULL) AND (@TypeID IS NULL OR
16 TypeID = @TypeID) AND (@LocationID IS NULL OR
View 10 Replies
View Related
Sep 6, 2005
I am working on a web application for a company that uses an existing company database. I just found out before the long weekend that a couple hundred of the items in the table aren't completely related.
I am mostly using 2 tables for the project: ItemVersion, ItemVersionRev
ItemVersion has fields: ItemVersion, ItemNumber, RecordID
ItemVersionRev has fields: RecordID, RevNumber
All of the items in the database have ItemNumbers (obviously) and most have revision numbers (RevNumber). One of the functions that I created for my application finds the MaxOfRevNumber. I ran a query on the database and saw that dreaded error screen that read "There is no row at position 0"
The query that I have now simply returns MaxOfRevNumber. Is there a way to program a query to check and see if an ItemNumber has a Revision and depending on the result, return a certain value. For example, if there is not a corresponding RevNumber, then return 0 as the RevNumber?
View 1 Replies
View Related
May 22, 2003
Hello all
I am trying to run a sql statement, (without having to run stored procedures), that will lookup a value stored in a Record.field., otherwise ignore the value if it is blank. I have a field tied to a Microsoft Access form - ItemLocation. If the user selects a unique ItemLocation the report will attempt to locate all values within that ItemLocation. If the user decides to leave the field blank, I would like for it to return all values for every ItemLocation. If anyone can help, I would really appreciate it. I will attach code. My problem is the last line of this SQL statement. If F.ITemLocation is null, I get no data. I would like for it to return everything. Thank you.
Code:
SELECT DISTINCT
B.BarCodeID,
A.ItemDescription,
A.ItemCategory,
A.TypeOfItem,
A.SerialNumber,
B.ItemLocation,
B.LocationID,
B.LastUpdate,
B.TrackItID,
B.UserID
FROM
tblMISFixedAssetTable A,
tblMISFixedAssetTable2 B
WHERE A.BarCodeID = B.BarCodeID
AND A.DisposalDate is null
AND B.LastUpdate = (Select Max(C.LastUpdate)
from tblMISFixedAssetTable2 C
Where B.BarCodeID = C.BarCodeID)
AND B.BarCodeID in (Select D.BarCodeID
from tblMISFixedAssetTable3 D
Where D.FiscalYear = (Select E.FiscalYear
from tblReportFY E))
AND B.ItemLocation = (Select F.ItemLocation
from tblReportFY F)
View 1 Replies
View Related
Apr 12, 2008
hi
i am trying to get the output of the select statements of sqldatasource :
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack) { DataView dv; dv = (DataView)(this.SqlDataSourcePictures.Select(DataSourceSelectArguments.Empty)); }
}
the problem is that dv returns null ?
and here is the sqldatasource definition in .aspx page
<asp:SqlDataSource ID="SqlDataSourcePictures" runat="server" ConnectionString="<%$ ConnectionString:con1%>"
SelectCommand="SELECT URL FROM SchoolPictures WHERE (School_Code = @School_Code) AND (SchoolPictureCategory = @SchoolPictureCategory)" OnSelecting="SqlDataSourcePictures_Selecting" OnSelected="SqlDataSourcePictures_Selected">
<SelectParameters>
<asp:QueryStringParameter Name="School_Code" QueryStringField="bid" Type="Int16" />
<asp:ControlParameter ControlID="ddlCat" Name="SchoolPictureCategory" PropertyName="SelectedValue"
Type="Int16" />
</SelectParameters>
</asp:SqlDataSource>
thanks for help
View 2 Replies
View Related
May 10, 2005
Hi,
I basically do not want to return a null value as a result of using a sum function (using sum against 0 rows).
Is there a common way to avoid this?
Thanx
View 5 Replies
View Related
Apr 14, 2005
hi,
can anyone tell me how i can replicate the Access function NZ please?
i want to use it with charindex when parsing text strings
many thanks
View 7 Replies
View Related
Jun 14, 2006
I have this program that I want to invoke from SQLServer funny thing is I can invoke this from command line but could not get it to work via 'xp_cmdshell' on this particular server. When I invoke run the following from SQLServer Query Analyser I always get 'NULL' returned.
I tried just running EXEC master..xp_cmdshell 'dir ' and it worked, so I am certain it is not a SQL rights problem.
FYI, I executed the same code on another machine and it worked. I just cannot figure out why it is not working on this one server?
Can anyone help? thanks
----
DECLARE @command varchar(500)
SET @command = 'c:ServerClientabc.exe BBB/XX B,AAAA'
EXEC master..xp_cmdshell @command
View 4 Replies
View Related
Mar 24, 2004
Hi,
I have this problem with MS SQL Server.
I have this table 'Request' in which there's 2 fields 'RequestName' and 'DateSubmitted'.
The datatype for 'DateSubmitted' is datetimn.
I have to write a query to extract all the 'RequestName' that falls under each month based on the 'DateSubmitted'.
For example, the table 'Request' has 12 rows, each row ('DateSubmitted') with the month of the year. Like row1 = January, row2 = February etc.
For each month, I have to extract all the 'RequestName' for that month based on 'DateSubmitted' field.
My query is like this:
select RequestName, datename(month, DateSubmitted)as month
from Request where datename(month, DateSubmitted) = 'March'.
By right it should return just one row right?
Instead, it returned me all 12 rows, only one row with value 'March' and the other 11 rows with value 'NULL'. Even if the other 11 rows have value of other months, when i run the above query, it will return me null.
Any help is appreciated.
thanks a million.
View 10 Replies
View Related
Apr 1, 2008
Hi all,
I have the situation below. I can't use dual. Thank you for your time.
Example Query:
select name
from employee
where id=1;
Result:
0 rows selected
Wanted Result:
1 row selected
View 5 Replies
View Related
Apr 19, 2014
I am trying to return all the names of employees and their managers
this query returns all the employees except for 1
SELECT E.FNAME,E.LNAME,M.FNAME,M.LNAME
FROM EMPLOYEE E,EMPLOYEE M
WHERE E.SUPERSSN=M.SSN
the one that isn't returned has a null SUPERSSN, but when I add in:
OR E.SUPERSSN IS NULL
it returns a row with the name of the employee whose SUPERSSN is null 8 times (where each time the M.FNAME,M.LNAME are other employee names)
How do I ammend the first query to return each employee and their respective manager once, the employee without a manager having null values for the manager name columns?
View 1 Replies
View Related
Feb 20, 2006
johnny writes "I am trying to return only the columns from multiple tables that are NOT NULL for a specific ID. Once I have the tables JOINED is there a way to only get those columns that are populated. Thanks."
View 2 Replies
View Related
Aug 24, 2015
I am new to XML file to SQL table update. I use sp_xml_preparedocument and openxml to extract the fields from xml file but it returns NULL highlight below. I also notice both sp_xml_preparedocument and sp_xml_removedocument store procedures are not in SQL system store procedure.
View 5 Replies
View Related
Apr 1, 2008
Hello,
I have a stored procedure that accepts a number of different input parameters that populate some variables in my stored procedure.
I want to have this stored procedure return nothing if some of these variables aren't filled out (they are populated by a search page the user fills out).
I'm not very familiar with writing stored procedures, so any help you can give me is appreciated.
Thanks!
View 2 Replies
View Related
Jul 4, 2007
I'm trying to ensure that only of the parameters is passed to my stored procedure.
BOL says that the IS [NOT] NULL operator (language construct?) will return a boolean. An IF statement takes an expression which results in a boolean so I was surprised to find that the below code doesn't parse.
CREATE PROC sp_OneParm(
@NumericVal float = null,
@StringVal nvarchar(200) = null,
@DateVal datetime = null,
@BitVal bit = null)
AS
DECLARE @ValCount tinyint
SET @ValCount = 0
-- Ensure we've only got one update value specified
IF @NumericVal IS NOT NULL @ValCount = @ValCount + 1
IF @StringVal IS NOT NULL @ValCount = @ValCount + 1
IF @DateVal IS NOT NULL @ValCount = @ValCount + 1
IF @BitVal IS NOT NULL @ValCount = @ValCount + 1
IF @ValCount > 1 RAISERROR ('Only one @*Val paramater may be specified when calling sp_OneParm()', 16, 1)
-- Other Stuff
GO
Am I missing something simple or do I need to restructure my code to achieve the logic I want?
View 2 Replies
View Related
Feb 18, 2012
Nz(variant [, valueifnull ] ) is the syntax.
If the value of variant isn't Null, then the Nz function returns the value of variant.
Can I return a custom value if the variant isnt null?
View 5 Replies
View Related
Mar 13, 2014
I am using the below query to calculate column values. But I need to return zero when a column values is empty or null.
select [Funding] [Fundings],
[Original] AS [Originals],
[Variance] = SUM([Previous_Year]-[Current_Year]),
[SumValue] = SUM([CurrentYear]/4),
[ActualValue] = SUM([Variance] * 0.75),
[FinanceYear],
[New Value] = SUM([Previous_Year]+[Current_Year])
from Finance
GROUP BY [Original], [FinanceYear]
View 1 Replies
View Related
Oct 24, 2006
In a new instalation of SQL Server 2005. I've made a restore from SQL Server 2000 database.
Solve some problem with the user, and it looks ok.
I'm using a asp web aplication, with vb dll, and change the sql provider from "SQL Server" to "SQL Native Client". It works, but in some recordsets the value a recive for the table primary key is null?????
If execute the querie in the database, the result has values in the primary key.
the same appends when i connect to an SQL Server 2000 using "SQL Native Client".
Please help, what can i do.
View 7 Replies
View Related
Jan 9, 2008
Hello:
I'm creating a 'dynamic where clause' with 15 parameters. I was using coalesce with the example below and it's working fine until it encounters a null value. I was trying to use nullif with coalesce to no avail. Can someone show me based on my example below how I can incorporate nullif with coalesce. Thanks in advance for you help.
Code Block
WHERE cs.DirID = Coalesce(@DirNum, cs.DirID)
View 12 Replies
View Related
Jul 11, 2007
Hi,
I have an assembly that contains the following function:
Public Class Lookup
<SqlFunction()> _
Public Shared Function MyTest() As Integer
Return System.Data.SqlTypes.SqlInt64.Null
End Function
End Class
Then in SSMS:
CREATE ASSEMBLY IRS_MyTest
FROM '\machine empmyAssembly.dll'
GO
CREATE FUNCTION dbo.MyTest() RETURNS INT
AS EXTERNAL NAME IRS_MyTest.[MyClass].MyTest
GO
when I run:
SELECT dbo.MyTest()
the following is returned:
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'MyTest':
System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
System.Data.SqlTypes.SqlNullValueException:
at System.Data.SqlTypes.SqlInt64.get_Value()
at System.Data.SqlTypes.SqlInt64.op_Explicit(SqlInt64 x)
at Informed.DCLG.IRS.SQL.IncidentTransform.Lookup.MyTest()
Can anyone please advise on how to return back a null value. Currently, my only other option is to return nothing (actually returns 0) and then wrap this up to convert the value to null - not ideal.
Thanks,
Jan.
View 1 Replies
View Related
May 16, 2005
If I try to run the code below, and even one of the values in the INNER
JOIN statements is NULL, the DataReader ends up with zero rows.
What I need is to see the results even if one or more of INNER JOIN
statements has a NULL value. For example, if I want info on
asset# 2104, and there's no value in the DriverID field, I need the
rest of the data to display and just have the lblDriverName by
blank. Is that possible?
<code>
Sub BindSearchGrid()
Dim searchUnitID As String
Dim searchQuery As String
searchUnitID = tbSearchUnitID.Text
lblIDNum.Text = searchUnitID
searchQuery = "SELECT * FROM Assets " & _
"INNER JOIN Condition ON Condition.ConditionID = Assets.ConditionID " & _
"INNER JOIN Drivers ON Drivers.DriverID = Assets.DriverID " & _
"INNER JOIN Departments ON Departments.DepartmentID = Assets.DepartmentID " & _
"INNER JOIN AssetCategories
ON AssetCategories.AssetCategoryID = Assets.AssetCategoryID " & _
"INNER JOIN Store ON
Store.[Store ID] = Assets.StoreID WHERE RTRIM(Assets.[Unit ID]) = '"
& searchUnitID & "'"
Dim myReader As SqlDataReader
myReader = Data.queryDB(searchQuery)
While myReader.Read
If
Not IsDBNull(myReader("Store Name")) Then lblStrID.Text =
myReader("Store Name")
If
Not IsDBNull(myReader("AssetCategory")) Then lblAsstCat.Text =
myReader("AssetCategory")
If
Not IsDBNull(myReader("Condition Description")) Then lblCondID.Text =
myReader("Condition Description")
If
Not IsDBNull(myReader("DepartmentName")) Then lblDepID.Text =
myReader("DepartmentName")
If
Not IsDBNull(myReader("Unit ID")) Then lblUnID.Text = myReader("Unit
ID")
If
Not IsDBNull(myReader("Year")) Then lblYr.Text = myReader("Year")
If
Not IsDBNull(myReader("Make")) Then lblMk.Text = myReader("Make")
If
Not IsDBNull(myReader("Model")) Then lblMod.Text = myReader("Model")
If
Not IsDBNull(myReader("Mileage")) Then lblMile.Text =
myReader("Mileage")
If
Not IsDBNull(myReader("Vin Number")) Then lblVinNum.Text =
myReader("Vin Number")
If
Not IsDBNull(myReader("License Number")) Then lblLicNum.Text =
myReader("License Number")
If
Not IsDBNull(myReader("Name")) Then lblDriverName.Text =
myReader("Name")
If
Not IsDBNull(myReader("DateAcquired")) Then lblDateAcq.Text =
myReader("DateAcquired")
If
Not IsDBNull(myReader("DateSold")) Then lblDtSld.Text =
myReader("DateSold")
If
Not IsDBNull(myReader("PurchasePrice")) Then lblPrPrice.Text =
myReader("PurchasePrice")
If
Not IsDBNull(myReader("NextSchedMaint")) Then lblNSM.Text =
myReader("NextSchedMaint")
If
Not IsDBNull(myReader("GVWR")) Then lblGrVWR.Text = myReader("GVWR")
If
Not IsDBNull(myReader("GVW")) Then lblGrVW.Text = myReader("GVW")
If
Not IsDBNull(myReader("Crane Capacity")) Then lblCrCap.Text =
myReader("Crane Capacity")
If
Not IsDBNull(myReader("Crane Certification")) Then lblCrCert.Text =
myReader("Crane Certification")
If
Not IsDBNull(myReader("Repair Cost")) Then lblRepCost.Text =
myReader("Repair Cost")
If
Not IsDBNull(myReader("Estimate Replacement")) Then lblEstRep.Text =
myReader("Estimate Replacement")
If
Not IsDBNull(myReader("SalvageValue")) Then lblSalVal.Text =
myReader("SalvageValue")
If
Not IsDBNull(myReader("CurrentValue")) Then lblCurVal.Text =
myReader("CurrentValue")
If
Not IsDBNull(myReader("Comments")) Then lblCom.Text =
myReader("Comments")
If
Not IsDBNull(myReader("Description")) Then lblDesc.Text =
myReader("Description")
End While
End Sub</code>
View 1 Replies
View Related
Nov 10, 2014
I recently ran into an issue with an issue with a query against our Data Warehouse. When attempting to sum revenue from a table, and using a WHERE clause on a field that contains NULL values, the records with the NULL values are suppressed (in addition to whatever the WHERE clause specified). I believe this is because a NULL value is unknown so SQL doesn't know if it does or doesn't fit the criteria of there WHERE clause so it is suppressed.
That being said, is there a way to avoid this instead of having to add an ISNULL function in the WHERE clause which is going to kill performance?
Code:
create table #nullTest (
name varchar(50)
,revenue int)
INSERT INTO #nullTest
Values ('Tim',100)
,('Andrew', 50)
,(null, 200)
SELECT sum(revenue) as Revenue FROM #nulltest WHERE name <> 'tim'
Ideally, I would want the SELECT statement above to return 250, not 50. The only way I can think to accomplish this is with this query:
Code:
SELECT sum(revenue) as Revenue FROM #nullTest WHERE isnull(name,'') <> 'tim'
View 4 Replies
View Related
Jul 18, 2015
Using TSQL, I have a table that holds filenames of Pictures for products. Different products can be using the same picture. I need to select the filenames for a single product only if it does not exists for a different product.I have tried Where Exists (select FileName From Tbl where
Prod_Id = @var) AND NOT EXISTS(select FileName From Tbl where
Prod_Id != @var) In the Select Statement.
View 6 Replies
View Related
May 16, 2007
Hi All!
I'm writing a custom component in c# for SSIS and I have a problem with AcquireConnection...
I wrote this code:
public override void AcquireConnections(object transaction)
{
if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null)
{
ConnectionManager cm = DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);
ConnectionManagerAdoNet cmAdo = cm.InnerObject as ConnectionManagerAdoNet;
if (cmAdo == null)
throw new Exception("The ConnectionManager " + cm.Name + " is not an ADO connection.");
this.conn = cmAdo.AcquireConnection(transaction) as OracleConnection;
}
but the 'conn' is ALWAYS null...
I tried
this.conn = ((IDTSConnectionManagerDatabaseParameters90)cmAdo).GetConnectionForSchema() as OracleConnection;
too, but no result: the 'conn' is null again...
If I use oledbconnection or sqlconnection instead of oracleconnection the method works fine... I really don't understand
could you help me plz?
View 6 Replies
View Related
Apr 4, 2007
Does SQL have a function that return "null" for records which don't exist? Per example in a FK relation ship, that not all records in the first table have a "child" in the second table, so it returns null records.
Thank you very much.
View 2 Replies
View Related
Oct 17, 2007
Hello,
I have this INNER JOIN that is fine to show all possible combinations. But I need to show only rows that have one or more Null values in tbIntersect.
Should I use nested LEFT JOINT? How?
This is the SQL statement:
sSQL = "SELECT DISTINCT tbCar100.Car100_ID, tbCar100.Description100 AS [Caractéristique 100], " & _
"tbCar200.Car200_ID, tbCar200.Description200 AS [Caractéristique 200], " & _
"tbCar300.Car300_ID, tbCar300.Description300 AS [Caractéristique 300], " & _
"tbCar400.Car400_ID, tbCar400.Description400 AS [Caractéristique 400], " & _
"tbCar500.Car500_ID, tbCar500.Description500 AS [Caractéristique 500], " & _
"tbCar600.Car600_ID, tbCar600.Description600 AS [Caractéristique 600], " & _
"tbCar700.Car700_ID, tbCar700.Description700 AS [Caractéristique 700], " & _
"tbProducts.Prod_ID, tbProducts.PartNumber AS [Part Number] , tbProducts.Description AS [Description] , tbProducts.DateAdded AS [Date] " & _
"FROM tbProducts INNER JOIN (tbCar700 INNER JOIN (tbCar600 INNER JOIN (tbCar500 INNER JOIN (tbCar400 INNER JOIN (tbCar300 INNER JOIN (tbCar100 INNER JOIN " & _
"(tbCar200 INNER JOIN tbIntersect ON tbCar200.Car200_ID = tbIntersect.Car200_ID) " & _
"ON tbCar100.Car100_ID = tbIntersect.Car100_ID) ON tbCar300.Car300_ID = tbIntersect.Car300_ID) ON tbCar400.Car400_ID = tbIntersect.Car400_ID) ON tbCar500.Car500_ID = tbIntersect.Car500_ID) ON tbCar600.Car600_ID = tbIntersect.Car600_ID) ON tbCar700.Car700_ID = tbIntersect.Car700_ID) ON tbProducts.Prod_ID = tbIntersect.Prod_ID " & _
";"
Here is the content of the tbIntersect table:
Car100_ID Car200_ID Car300_ID Car400_ID Car500_ID Car600_ID Car700_ID Prod_ID ID
1 1 1 1 1 1 1 1 1
1 2 1 1 1 1 1 19
1 3 1 1 1 1 1 20
I need to return the rows that have null data, ex: second row because Prod_ID is NULL and third row because Car300_ID is NULL. In fact I need the data from the other joint tables that correspond to these ID fields.
Thanks
View 5 Replies
View Related
