I have built a SSIS package that bascially updates two columns in table A...the update statement reads;
update Table A
set Colum 1 = ?,('?' is a variable)
Column 2 = ?
In my SSIS package, I would like to be notified/capture, if one or both variables are null....How do I do that ?..error log ?
The package runs fine both ways (if variables are null or not)
Thank you
I am attempting to update a sql db using the update and parameter code in VB.net 2003 through MSDE for a web application. It updates changed data OK, but if the textbox value is deleted, the code does not update the sql db. I am new to this, and I'm sure it is something simple. Here is some sample code.
SqlConnection1.Open()
strSQLu = "UPDATE table1 " _
& "SET Field1Tag = @Field1Tag, Field2Tag = @Field2Tag " _
& "WHERE (Field1Tag = @Field1Tag) "
cmdCategoriesUpdate.CommandText = strSQLu
With cmdCategoriesUpdate
.Parameters("@Field1Tag").Value = txtFld1.Text
.Parameters("@Field2Tag").Value = txtFld2.Text
End With
cmdCategoriesUpdate.ExecuteNonQuery()
SqlConnection1.Close()
I need to convert a a string column to integer. Before converting, I need to check if it has blank values then convert it to NULL. Someone told me that its easier to convert it to NULL before converting to integer.
View 5 Replies View RelatedHere is the sample data for table.
create table #sample (emp varchar(max),data1 varchar(max),data2 varchar(max), Rdate date)
insert into #sample (emp,data1,data2,rdate)
values('john','','',getdate())
insert into #sample (emp,data1,data2,rdate)
values('tony','','',getdate())
[Code] ...
I need to update the data1, data2 from yesterday or before but they should be non blank and non null and update to todays data.
It means, I can't update to 2015-03-12 values to 2015-03-13, as they are blanks or nulls.
So I need to go 2015-03-11 and check the values, if they are not blank , i need to update with todays values.
Before the update :
empdata1data2Rdate
john3/13/2015
tony3/13/2015
john3/12/2015
tony3/12/2015
johncd3/11/2015
tonyab3/11/2015
After the update:
empdata1data2Rdate
johncd3/13/2015
tonyab3/13/2015
john3/12/2015
tony3/12/2015
johncd3/11/2015
tonyab3/11/2015
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
Hi.
I have an sql table which contains a number and a name. I would like to create a select statement that will display these two fields in the format :
"number | name", but if there is a null value in the number it will display only the name and vice versa.
How can I do it ?
Any help is appreciated.
Code below:
SELECT
NAME,
CASE WHEN PHONE IS NULL THEN '-' ELSE PHONE END AS PHONE
FROM
PATIENT
WHERE PHONE = '-'
If I am switching the NULL values to hyphens (-), why am I not getting any records when I look for hyphens in the WHERE clause? The script is not as simple as this or else I would just check for NULL values. I really need to switch NULL values to something I can pass on to a variable and query on it from Reporting Services.
Hello,Thanks for helping me with this... I really appreciate it.I have a table called tblPatientDemographics with a number of columns.I would like to count the number of NULL values per record within mytable.tblPatientDemographicsPatientID Age Weight Height Race1234567 20 155 <NULL> Caucasian8912345 21 <NULL> <NULL> <NULL>In the first example above I want to display '1'In the second example above I want to display '3'Any help would be very much appreciated.Thanks !Chad*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 3 Replies View RelatedHi, I am trying to use a formView with an update button to update individual records in an sql database. (when i click update it doesnt perform the update and just refreshes the page. ) One of the fields in my records is a NULL - this is also one of the fields that i need to update. When i manually go into the database and enter some data, and then go back to my form, it updates fine, but as soon as i delete the data from the field, it returns to NULL and im back to square one. Any Ideas on how to get around this problem?THanks
View 4 Replies View RelatedI have a grid with checkbox, where users can select multiple rows and edit at the same time and save it to the DB. Now I have used a Footer Template with textbox in the gridview. So if I want to put similar data's for some particular rows at the same time in the grid, I select the multiple rows and try to put values in the footer template textbox and when I click on save, it saves successfully.
UPDATE QUERY:
"UPDATE [Test] SET [Name]='" + Name + "',[Designation]= '" + Designation + "', [City]= '" + City + "' WHERE EmpID='" + EmpID + "'";
Now here is the challenge, but even when I enter null values in the footer template textbox it has to save with the old values of the rows and not null values. I tried it and couldn't make it happen. So anything like putting the case for each column and mentioning like if null accept the old value and not null accept new value.
i want to display the max Date in place of NULL when Indicator is "1"
INPUT
Emp Num
Date
Indicator
1
Null
1
[code]....
I have a stored procedure with a SELECT statement, that retrieves 1 row.
SELECT name FROM tblNames WHERE nameID = "1"
I want all the NULL values in that row to be change in some default values.
How do I do this?
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>
I use the merge statement in a sproc to insert, update and delete records from a staging table to a production table.
In the long sql, here is a part of it,
When Matched and
((Student.SchoolID <> esis.SchoolID
OR
Student.GradeLevel <> esis.GradeLevel
OR
Student.LegalName <> esis.LegalName
OR
Student.WithdrawDate <> esis.WithdrawDate
Student.SPEDFlag <> esis.SPEDFlag
OR
Student.MailingAddress <> esis.MailingAddress)
Then update
Set Student.Schoolid=esis.schoolid,
.....
My question is how about if the column has null values in it.for example
if schoolID is null in production table is null, but in staging table is not null, will the <> return true.or if either side of <> has a null value, will it return true.
I don't want it to omit some records and causing the students records not get updated.If not return true, how to fix this?
Hello everybody,
I can't perform an operation apparently very easy: set a field to a NULL value.
This is the db:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
This is the table:
CREATE TABLE [ProgettoTracce] (
[ID_Progetto] [int] NOT NULL ,
[MisDifDef] [real] NULL ,
[MisDifMeas] [real] NULL ,
[MisDifAna] [real] NULL ,
[MisDifID] [real] NULL ,
[MisDifCV] [real] NULL
) ON [PRIMARY]
GO
This is qry:
UPDATE ProgettoTracce
SET MisDifDef = NULL
WHERE ID_Progetto = 3444
The qry has been performed with no error. Then I execute SELECT * FROM ProgettoTracce WHERE ID_Progetto = 3444 and I find the value I tried to overwrite with NULL. If I update with 0 (for example) it works.
Obviously this happens on the production db, because on the development db the update with NULL works fine.
No transaction is called, db options are the same on dbs...
What's happen? Have I to call an exorcist???
Thanks in advance for any help!
Nicola
I wrote a select statement, I only want to see orders with max lastUpdatedOn date of 14 days and older. Now my results show dates with all orders of 14 days and older (which is OK), but all others are displayed in the "Uitgifte" column as "NULL". But those orders should not be displayed at all.
selectdistinct ProductionHeader.ProdHeaderOrdNr,
ProductionHeader.PartCode,
ProductionHeader.Description,
ProductionHeader.Qty,
(select max (ProdStatusLog.ProdStatusCode)
[code]...
We are just upgrading a server from 2000 to 2005 and we are getting the message below when we execute a sql statement against a table with a date field with null values:
"Error converting data type DBTYPE_DBTIMESTAMP to datetime."
I am attempting to update a table that drives a report. The report is at the order level. An order can have several types of demand (revenue $): Ship and/or Backordered. When I update one of these demand fields on the report table, I would expect those orders that have that type of demand to update, and those orders that don't have that type of demand to remain at their current value ($0 in this example which is the table default). But what is happening is that orders that don't have that type of demand are changing to NULL. What am i doing wrong with my update statement that is causing this?
Code:
CREATE TABLE #sales(
OrdNo int,
StatusGrp varchar(10),
Demand decimal(10,2)
)
INSERT INTO #sales
VALUES (1,'Ship',100.00)
[Code] .....
I have table Products and Orders that has the following columns:
table Products: ProductID, ProductName
table Orders: OrderID, ProductID, OrderDate, Quantity, Price
The Orders table contains orders placed on all the dates. I want to obtain a list of orders for a particular date, if there is no order for a product on the requested date, I want to return null values for the Quantity and Price fields.
I tried the following select statement:
select Products.ProductName, Orders.Quantity, Orders.Price from Products left join Orders on Products.ProductID = Orders.ProductID where Orders.OrderDate = '10/16/2004'
Where, there are a total of three products (A,B,C) in table Products. Product-C has no order on 10/16/2004, but I want it to return :
ProductName / Quantity / Price
Product-A 5 1.89
Product-B 6 2.43
Product-C null null
Obviously, my sql statement won't work becaue the where clause will filter out Product-C.
Could anyone help me figure out how to modify my sql code to get the resultset I want?
Thanks in advance.
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?
-- Ryan
Does anyone know the trick when inserting an empty variable into a MSSQL table for it to show as null and not a blank or a zero?
It is a php variable.
Thanks
so far, i'd read a lot of books and search from internet, i still don't very understand the difference between of NULL and balnk value.
can anybody help to explain on it?
Hi,My question is, is there any difference between a NULL and a Blank(Unknown, Not Applicable) field in MS SQL or are they the same?Awaiting your comments,Regards
View 4 Replies View RelatedI have two groupings defined in my table. I group by Owner and then by Priority. I'm struggling with the expression to capture the string values at the appropriate scope. Using the table below, I want to capture "Critical, High" for "Jack Daniels" and "Medium, Low, Informational" for "Jim Beam". The table has "grpOwner" and "grpPriority" defined on Fields!Owner.Value and Fields!Priority.Value respectively. Any help would be greatly appreciated!
Owner
Priority
Jack Daniels
Critical
High
Jim Beam
Medium
Low
Informational
If a database's collation is blank under enterprise manager and NULL with SELECT DATABASEPROPERTYEX('<Database_Name>', 'Collation') does this mean it has the same collation as the server?
The reason I ask is that I restored a database with a different collation to my server and so I expected the different collation name to show up.
Its showing as blank though...
I thought databases kept their original collation even if restored to another server.
(The database was restored as a new database).
I am passing few parameters to the sql function to do some calculations. If the input parameter is null or blank, then I want to set the input parameter to the value zero
View 1 Replies View Related
Hi, where I return the address details, in some of the fields they are blank, I need these blank fields as nulls, how do i do that ?
Thanks
Code Snippet
SELECT
a.[Account Name],
a.[Line Address 1],
a.[Line Address 2],
a.[Line Address 3],
a.[Line Address 4], b.*
FROM (
SELECT
abCUSA AS Company,
abCUSB AS Account,
0 AS DeliverTo,
abcnam AS [Account Name],
abcad1 AS [Line Address 1],
abcad2 AS [Line Address 2],
abcad3 AS [Line Address 3],
abcad4 AS [Line Address 4],
CONVERT(VARCHAR(20), abCUSA) + CAST(abCUSB AS VARCHAR(20)) AS [Customer Number]
FROM
LIVEAS400.S65C422B.WRFDTA.PARNADR
) a
Inner JOIN
(
SELECT
SLDATE as Date,
SLCUSA as [Company Number],
SLCUSB as [Customer Suffix],
SLDELN as [Deliber To Number],
SLTREF as [Trans Reference],
SLCNAM as [Deliver To Name],
CONVERT(VARCHAR(20), SLCUSA) + CAST(SLCUSB AS VARCHAR(20)) AS [Customer Number],
CONVERT(VARCHAR(20), SLCUSA) + CAST(SLCUSB AS VARCHAR(20)) + CAST(SLDELN AS VARCHAR(20)) AS [Full Deliver To Number],
-- SLTRT2 as [Trans type],
CASE WHEN SLTRT2 IN (92, 93, 94, 95, 98) THEN SLGDSV * -1 ELSE SLGDSV END as [Goods Value],
CASE WHEN SLTRT2 IN (92, 93, 94, 95, 98) THEN SLVATV * -1 ELSE SLVATV END as [VAT],
CASE WHEN SLTRT2 IN (92, 93, 94, 95, 98) THEN SLTOTV * -1 ELSE SLTOTV END as Total,
SLDESC as [Trans Description]
FROM LIVEAS400.S65C422B.WRFDTA.SQLSLDGR
) b ON a.[Customer Number] = b.[Customer Number]
I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.
The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column.
The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.
Below is the schema of the three tables
I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.
Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.
This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.
Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.
I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.
Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows
After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.
I tried to remove the Null value from the #DetailTable by running the update statement of analysis detail in a while loop however its not working.
DECLARE @categoryid INT = 8
DECLARE @DetailQuestionID INT = 1380
/*------- I need the query to run for the below three data.
Here i'm updating my planids that already exists in my database*/
DECLARE @planids VARCHAR(MAX) = '2,4,5'
[code].....
How can I enter NULL manually in one of the rows in Sql Server database. Those rows does not contain any data.
View 3 Replies View Related
I have created a report with a stored proc which takes a parameter whose default value is null. Now when I run my stored proc in Enterprise Manager without any params I get 4 rows otherwise with proper parameter I get 2 rows.
Now I set the params value to allow for Null and blank values. When I run the report if I provide parameter value it works fine but if i try to run it without any param it shows an empty report while the stored proc running without params shows 4 rows.
I have earlier sucessfully created reports where stored procs had 4 params out of which 3 where defaulted to null. And so I set the allow nulls, Allow Blanks values to be true for those params in Rep services. So when I selected value for 1st param and ran the report I got the desired report. But this time I have only one param which can be null. But its not working.
Thanks in advance.
Hi everyone,
The following snippet of code returns something like that: string;string1;string2
Up to here fine. I woner how to export such value to ssis variable??? That variable will contain the value needed for the FILEATTACHMENTS property (Send Mail Task)
Thanks a lot,
declare @anex as varchar(500)
declare @anex2 as varchar(700)
set @anex2 = ''
DECLARE anexos CURSOR FOR
SELECT [Ruta] + [Fichero] as ANEXO
FROM SVC_FICHEROS INNER JOIN SVC_ENVIOS ON SVC_FICHEROS.IDENVIO = SVC_ENVIOS.IDENVIO
WHERE ENVIADO = 0
OPEN anexos;
FETCH NEXT FROM anexos INTO @anex
WHILE @@FETCH_STATUS = 0
BEGIN
IF @anex2 = ''
begin
set @anex2 = @anex
end
else
begin
set @anex2 = @anex2 + ';' + @anex
end
FETCH NEXT FROM anexos INTO @anex
END
CLOSE anexos
DEALLOCATE anexos
I have been using return values to check the status of my stored procedures called from an application. But how does one read the new raiserror values that are passed with sql server 2005 and using ado.net 2.0. Are they returned as parameters or as a dataset or what?
Thanks
smHaig
Data from as400 imports into SQL with blank fields which is the way as400 outputs records. How can you insert previous record of data null or blank field. ex:
ONETWO
a1
2
3
b1
2
3
Would want:
ONETWO
a1
a2
a3
b1
b2
b3