I am doing an update of MAINID column in a database table from excel. Now some of the MAINID data has string" NON CONVETBLE" and rest all are alpha Numeric. Now while i do the update these stiring " NON CONVERTBLE" is updated as NULL in the data ase table.
Now i dont want them to be upadated as NULL instead i want to put some Alpha Numeric string. such as "0000ABCD"
Please let me know how to do in this.
Cannot find an answer to this in previous posting, though there are similar topics.
My primary key "ID" requires a value (is not nullable), and not explictly providing it with one when I update a new record gives the following error:
Cannot insert the value NULL into column 'ID', table 'AdClub.mediaq.News'; 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 'ID', table 'AdClub.mediaq.News'; column does not allow nulls. INSERT fails.
However, trying to stuff that field with a recordCount+1 value (or any value), I get this error:
Violation of PRIMARY KEY constraint 'Primary Key'. Cannot insert duplicate key in object 'News'.
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: Violation of PRIMARY KEY constraint 'Primary Key'. Cannot insert duplicate key in object 'News'.
Cannot figure this one out. The value I'm providing for that field is known to be unique, but the SQL Server spits it out each time.
Is there a way to have the ID primary field automatically update with a new value when a new record is generated? This is how I used to do it in Access, but cannot find a similar feature in SQL Server. I'm sure I'm missing something simple, but right now I'm stuck in this "Catch-22" situation.
Please help!
If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE(
ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName)
VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
SELECT FirstName, LastName, NULL
FROM MYOTHERTABLE
Hi. I'm not such an expert in sql and I wanted to update a table's fields only if the value that im going to update it with is not null. How do I do that?
What I mean is something like:
---------------------------------------------------
Update database.dbo.table set
if(@newvalue !=null)
afield = @newvalue;
-------------------------------------------------
Really need your help on this. Thanks.
I am trying to update a table that has a null value and keep getting a return of zero. I know that there are null values but cannot get the script to rcognize it. Example below
Use SimplexWFM
update per_Personnel_Info
set LAC5 = 'Default'
where LAC5 = '<NULL>'
Can anyone help?
Here's my table:
----------------------------
Members
----------------------------
Subscriber | Name
----------------------------
Subscriber is a True/False value. However, some members have a NULL value in the Subscriber field. I'd like to go through all the members and update the Subscriber field to be 'False' if the value in there is NULL.
Using an SQL server and writing ASP code trying to set a numeric field to null.
ie ssql = "UPDATE users SET customer_id = null where name = 'Joe'"
Keeps on generating a syntax error
Hi.I have a update query:
UPDATE test1
SET testprice= ((unitprice*8)/100) + unitprice
it's good.but I dont want to change the testprice if the unitprice is NULL.what should I do?
Hi, 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 am having a problem with an update statement. I am using compareallvalues with a SQL data source. When one of the old values is null the update does not go through.
i.e. This does not work if something is NULL
UPDATE myTable SET something = @somethingWHERE ID = @o_ID AND something = @o_something
Thank You,Jason
I 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]....
Hello - I have a column in a table (SQL 2005 EE) with a Data Type of smalldatetime and a Default Value of getdate(). When I insert a record from my webpage the new record contains the correct date via getdate(). However if I update the record from my webpage the date of the record then becomes NULL. Is this normal? Is there anything I can do about this from sql server? I am inserting/updating via an formview and ODS, using standard insert/update methods.
Cheers
Marco
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
Hi guys, I have made several Access-based CMSs but now I am using SQL Server. I can read the records but my first attempts at writing are resulting in new records (with new ID) but all the fields are null.
I am posting the data from a form to the same page and an if /then statement catches the flag in the URL and runs the update script below. All the field names are correct.
if request.QueryString("add")<> "" then
Dim rsUpdateEntry
Set rsUpdateEntry = Server.CreateObject("ADODB.Recordset")
rsUpdateEntry.Open "SELECT * from generic_country_info" , oConn, 2, 3
rsUpdateEntry.AddNew
rsUpdateEntry.Fields("title1") = Request.Form("title1")
rsUpdateEntry.Fields("body1") = Request.Form("body1")
rsUpdateEntry.Fields("title2") = Request.Form("title2")
rsUpdateEntry.Fields("body2") = Request.Form("body2")
rsUpdateEntry.Fields("title3") = Request.Form("title3")
rsUpdateEntry.Fields("body3") = Request.Form("body3")
rsUpdateEntry.Fields("title4") = Request.Form("title4")
rsUpdateEntry.Fields("body4") = Request.Form("body4")
rsUpdateEntry.Fields("title5") = Request.Form("title5")
rsUpdateEntry.Fields("body5") = Request.Form("body5")
rsUpdateEntry.Fields("image1") = Request.Form("attach1")
rsUpdateEntry.Fields("image2") = Request.Form("attach2")
rsUpdateEntry.Fields("image3") = Request.Form("attach3")
rsUpdateEntry.Fields("image4") = Request.Form("attach4")
rsUpdateEntry.Fields("image5") = Request.Form("attach5")
rsUpdateEntry.Fields("country") = Request.Form("country")
rsUpdateEntry.Fields("dest_url") = Request.Form("dest_url")
rsUpdateEntry.Update
rsUpdateEntry.Close
Set rsUpdateEntry = Nothing
end if
Thanks
Mark
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] .....
Do I have to use condition split?, then union all?
if in script, I can use update from <tablename> Set column = isnull(column, 'NA'). It's so simple.
I'm also wondering can we run SQL Script against input dataset in a SSIS component?
Hi ,
I have 2 tables (Dept and Emp)
The columns in table Dept are Deptno and Deptname. Deptno is bigint and it is primary key. In Emp table, columns are Empno(PK) ,EmpName and Deptno(foreign key referring to Dept)
To Insert or Update record in Emp through application, value of Deptno is coming as 0(Zero). I want the value of Deptno to be inserted or updated as null if the value is Zero (0). How to do this in sql server 2005 by using trigger on table Emp
Thanks in advance
regards,
Srinivas Govada
Hi,I have an updatable DataGrid linked to a SQLDataSource in a web site developed using VS2005. Update works fine unless a value in the existing row is Null. Only one column in the database allows nulls.Putting a debug stop in the SqlDataSource1_Updating event, I checked the parameter value in the Immediate window and got the following result:?e.Command.Parameters(16){System.Data.SqlClient.SqlParameter}System.Data.SqlClient.SqlParameter: {System.Data.SqlClient.SqlParameter}DbType: Int32 {11}Direction: Input {1}IsNullable: FalseParameterName: "@original_FiresolveJobNo"Size: 0SourceColumn: ""SourceColumnNullMapping: FalseSourceVersion: Current {512}Value: NothingIs there a property I can set to generate a suitable Null check clause for the Update statement?Many Thanks,Keith.
View 3 Replies View RelatedI have a table with hundreds of millions of records and need to update an xml column to null. I do something like this:
UPDATE [Mydb].[MySchema].[MyTable]
SET [XMLColumn] = null
Currently it is taking around 6 hours.
Is there a quicker way to do this?
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
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
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've got a table with 6 fields :
EmployeeAccess
(MasterID, LoginID, AccessID, Storage1, Storage2, Storage3)
that needs to be updated using the data in the following spreadsheet
NewEmployeeAccessData
(ID, MasterID, AccessID1, LoginID1)
There is a 1:1 relationship between the two tables..I'm trying to code a pair of update statements on the EmployeeAccess table (1 for LoginID, 1 for AccessID) with the following logic:
If LoginID is NULL, then Update LoginID with new LoginID1 value,
If LoginID is not null, and Storage1 is NULL then Update Storage1 with New LoginID1 values
If LoginID is not null, and Storage1 is not NULL and Storage2 is NULL then Update Storage2 with New LoginID1 values
etc etc...
The same applies when trying to populate the AccessID column
If AccessID is NULL, then Update AccessID with new AccessID1 value,
If AccessID is not null, and Storage1 is NULL then Update Storage1 with New AccessID1 values
If AccessID is not null, and Storage1 is not NULL and Storage2 is NULL then Update Storage2 with New AccessID1 values
etc etc.
I have no control over the schema of this table so I'm trying to work the logic on how to update the columns in my table only if the corresponding column data is NULL, else update the next non NULL Storage column.
update table1 set field1=null where id=1; commit;
where I can look for the user,who execute this statment?
It was 6.02.2007 or 7.02.2007.
All users connect using SQL Serwer Authentication.
It is test database and from 1 to 3 users connect to it, and submit not more then 10 SQl statmant per day.
Here 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
How do u achieve this -- While SSIS Data Load Execution itself?
Update Col B with Col A value if Col B is NULL in the Data File?
Ok I have created a 2005 sql advanced database with text indexing. I have create the database like so
created a new database with text indexing enabled and the following table
create table support (problemId VARCHAR(50) NOT NULL PRIMARY KEY, problemTitle varchar(50) NOT NULL, problemBody text NOT NULL, linkOne varchar(50), linkTwo varchar(50), linkThree varchar(50), linkFour varchar(50), ftid int NOT NULL)
next
create fulltext catalog remoteSupportCatalog
create unique index ui_remotesupport ON support (ftid)
then
create fulltext index on support(problemBody)key index PK__support__7C8480AE on remoteSupportCatalog
--------
I then populated some rows and issues a quesry
Select * from support where freetext(problemBody, 'test database')
it works pulls back all the data I expected it to pull back
In my asp page I created a database connection with the folling select command
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:rsdb2ConnectionString2 %>"
SelectCommand="SELECT * FROM support WHERE FREETEXT(problemBody, @srchBox)">
created the search parameter<SelectParameters>
<asp:ControlParameter ControlID="srchBox" PropertyName="Text" Type="String" Name="srchBox" /> //this is a text box that is searchable with a button
</SelectParameters>
and it doesnt give me back an error or data it does nothing. What am I missing?????
I hate making complecated queries....but this doesn't seem too hard and I cannot figure out where my error is. The message is "Incorrect syntax near the keyword 'FROM'."
The query is:
SELECT tblUsers.lUserID GROUP BY tblUsers.lUserID HAVING Count(tblDLHistory.lDownloadID) = 0 FROM tblUsers LEFT JOIN tblDLHistory ON tblUsers.lUserID = tblDLHistory.lUserID WHERE tblDLHistory.lVersionID = 5
The tables are
tblUsers
_______
lUserID
tblDLHistory
__________
lDownloadID
lUserID
lVersionID
What on earth am I doing wrong?
I need to search the database and pull up all customers who have a 'device' and their email address. I have watered down my select statement, but the following is the basics of it. I just cannot figure out how to also append the email. I have tried many different attempts and have come up dry.
Select a.company
From dbo.contact1 as a,
(
Select DISTINCT accountno
From dbo.contsupp
Where contact LIKE 'Product Inventory' AND contsupref LIKE '%device%'
) as b
Where a.accountno = b.accountno
Below are some sample databases to get an idea of some possibilities. There will not always be an entry in dbo.contsupp for an email address. There will not always be an entry in dbo.contsupp for a device.
------------------------
|dbo.contact1 |
|------------------------|
|accountno | company |
|------------------------|
|5123 | Alpha |
|4158 | Beta |
|2121 | Gamma |
|5555 | Omega |
------------------------
--------------------------------------------
|dbo.contsupp |
|--------------------------------------------|
|accountno | contact | contsupref |
|--------------------------------------------|
|5123 |Product Inventory|device 01 |
|2121 |product note |the note |
|2121 |Product Inventory|device 02 |
|5123 |Email Address |a@b.com |
|4158 |ladkafndkaldkfna |device stopped|
--------------------------------------------
**note: i am using MSSQL 2000
Hello,
I have a small table to manage orders in my company. When a new order is entered, the script makes use of the last row of the table to find out the last order, increments it and creates the new order number. The problem is, a few days ago the last row got stuck. New insertions to the table all got the same order number and are placed above the last row. Anybody has any idea what's going on?
Hi
I am completely stuck trying to make a query
Heres is a shorten downed example of what I am trying to achieve. I have four tables. The first being OrderHeader, the second OrderLines, the third StockCategory. The OrderHeader table contains basic order details, which in turn is linked to the OrderLines table which show if the order in the OrderHeader has a single or multiple order lines. The StockCategory table shows what stock group the item in each order line is associated to.
Here is an example of the tables in a shorten downed version (in both data and fields):
OrderHeader:
Sales Order Ref, Order Date,
1, 01/05/2008
2, 01/05/2008
3, 02/05/2008
4, 02/05/2008
OrderLines:
Sales Order Ref, Part Number
1, 222
1, 234
1, 333
2, 222
2, 555
2, 444
3, 333
3, 111
4, 222
StockCategory
Stock Category, Part Number,
A, 222
B, 234
C, 333
D, 444
E, 111
Stock Group:
Stock Group, Stock Category, Priority
Berr, A, 1
Berr, B, 1
OGD, C, 2
OGD, D, 2
DFID , E, 3
The thing I am trying to do is assign each of the orders to a Stock Group which I can do. The thing that has baffled me is if an two of the order lines in one order are assigned to different stock groups. If this occurs I want to assign the order to the order to the stock group with the highest priority (1 being highest, 3 being lowest) for example if one order line in the order was assigned to Berr (priority 1) and the other to DFID (priority 3) the order would be assigned to Berr.
I am using the following query:
DECLARE @Period DATETIME
DECLARE @BeginDate DATETIME
DECLARE @EndDate DATETIME
SET @Period =’2008-05-01’
SELECT @BeginDate = [BeginDate] FROM DatePeriods AS dp WHERE dp.ID = @Period
SELECT @EndDate = [EndDate] FROM DatePeriods AS dp WHERE dp.ID = @Period
SELECT
COUNT(oh.[Sales Order Reference])
FROM dbo.OrderHeaders AS oh
LEFT JOIN dbo.OrderLines AS ol ON oh.[Sales Order Reference] = ol.[Sales Order Reference]
LEFT JOIN dbo.StockCategories AS sc ON ol.[Part Number] = sc.[Part Number]
WHERE oh.[Order Date] BETWEEN @BeginDate AND @EndDate
AND sc.[Stock Group] IN (SELECT sg.[Stock Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'Berr')
AND ol.[Sales Order Reference] NOT IN (SELECT col.[Sales Order Reference]
FROM dbo.CancelledOrderLines AS col
WHERE col.[Part Number] = ol.[Part Number])
GROUP BY oh.[Sales Order Reference]
Is this possible to do?
Thanking you in advance!!!
Hi,
Been spinning on this for whole weekend I can't seem to get what you I want. I have the following xml result from my query. As you notice one of the child elements has the tag identifier VJobs, how can I make it so it says 'task' instead?
<task id="2" name="Saw 1" color="#99ccff" expand="true" />
<task id="3" name="Saw 2" color="#99ccff" expand="true" />
<VJobs id="3" name="Layout#" color="#99ccff">
<customproperty taskproperty-id="tpc0" value="Unknown" />
<customproperty taskproperty-id="tpc1" value="17.938 " />
<customproperty taskproperty-id="tpc2" value="Unknown" />
<customproperty taskproperty-id="tpc3" value="0" />
<customproperty taskproperty-id="tpc4" value="Operator Unknown" />
</VJobs>
</task>
Here is the query, which details jobs to be done on different equipments
SELECT EquipmentID + 1 as id,
EquipmentDescr as [name],
'#99ccff' AS color,
'true' AS [expand],
(SELECT JobID + 2AS id,
'Layout#' AS [name],
'#99ccff' AS color,
(SELECT [taskproperty-id] AS [taskproperty-id],
[value] AS [value]
FROM dbo.JobDetails customproperty
WHERE customproperty.JobID = VJobs.JobID
FOR XML AUTO, TYPE)
FROM VJobs
WHERE VJobs.EquipmentID = task.EquipmentID
FOR XML AUTO, TYPE)
FROM VEquipments task
ORDER BY EquipmentDescr
FOR XML AUTO, TYPE