NULL Values To Sequential Number (The Field Is Nvarchar Datatype)
Jun 16, 2012
Ok I have upgraded my works database from a poorly designed Access database to a SQL database. The previous system allowed NULL values and duplicates to be inserted into a field that should NOT ALLOW NULL Values or duplicates. Therefore, this issue has now been moved across to my new system as I cannot set these constraints on the field that has multiple NULL values.
My solution would be to use a sequential operator, so whatever = NULL would be changed to a sequential number that us as administrators would know was a bogus number starting at something like = 999999900 counting up from that. There are only 250 records that would require updating.
To make things more interesting this field is not a integer type, its a Nvarchar type as its a Hardware ID. Both numerical and characters are require.
View 1 Replies
ADVERTISEMENT
Dec 9, 2013
I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?
CREATE TABLE [dbo].[Import_CustomerSales](
 [CustomerId] [nvarchar](50) NULL,
 [CustomeName] [nvarchar](50) NULL,
 [CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]
View 5 Replies
View Related
Mar 20, 2008
Hi,
I am facing problem while inserting a Null value into a smalldatetime datatype field in sql server 2000 using code in vb 6.0
Error as : Type mismatch.
Kindly let me know how to insert Null or blank (dtDate = "") into a column.
Regards,
Srinivas Alwala
View 1 Replies
View Related
May 7, 2015
I am trying to change a column from a decimal(18,2) to a decimal(18,3). What is the SQL command to alter this table?
SQL Command:
alter table TableName
alter column ColumnName decimal(18,3) [null]
the above command is right ?
View 6 Replies
View Related
Aug 3, 2006
Hi,
When i try to insert a value in to a field of datatype float(8), it is storing the wrong values(approximate values).
For example, if i try to insert 2.62 , it takes it as 2.6200000000000001
But i can see the correct values displayed in the frontend.
Heard that there is a fix available for this issue.
Could any of you help me in getting the details about that fix?
Thanks in advance.
~ Chaitanya
View 4 Replies
View Related
Mar 14, 2008
Hi,
I imported a table from Accees to SQL 7 with data in it.
I need to modify one of the datatype columns to "datetime" from nvarchar.
I tried to convert it manually, in SQL Server Enterprise Manager tool, but it gave me an error.
I also tried, creating another column "DATE2-datatype:datetime" and updating the column with the old one.
UPDATE users SET DATE2 = DATE.. But it also faild,..
How can I modify the column?
Thank you.
View 10 Replies
View Related
Feb 3, 2008
Hi,
I have a table employee with 4 columns,
empno fname lname deptno
1 abc def 10
2 fff hhh 20
3 abc def NULL
4 abc def NULL
5 abc def 50
suppose i want to know the total number of null values in a particular column say deptno how shuld i write a query?
select count(deptno) from employee
where deptno IS NULL..
When i query this i get the result as 0..
View 8 Replies
View Related
Mar 1, 2007
I am importing an Access .mdb file into MS SQL server, and empty fields where the default value is "", change into NULL. This is a problem when I re-export a result set and have to apply a procedure to clean these values. Is there a way to eliminate this? . . . . and what have I missed?
View 2 Replies
View Related
Apr 22, 2008
Hi All,
I want to show 0, if the field contains NULL values.
I use the following expression.
=IIf(Fields!MTD_TotGrossBKCOAmt.Value = "NULL" , SUM(Fields!MTD_TotGrossBKCOAmt.Value), 0 )
But this works if the field contains only NULL values.
If it has a value, then it shows as #Error
Can anyone tell me how to make this work?
Thanks
View 7 Replies
View Related
May 9, 2005
I have a function that updates a table in sql server
If a field is left blank I want the database field set to Null. I use:sqlCmd.Parameter.Add("@somefield, someintvalue) to pass all my values to the database.
if someintvalue is null I want to set @somefield to dbnull
How can I do this? I get errors if I submit a null integer when there is a foreign key constraint. What is the best way to handle this? should I just make all parameters objects? so that I can set them to DBNull.Value?
Also on a side note, when you are populating fields from a datatable, is there a better way to set the values (i.e. of a textbox) than cheking for DBNull before assigning?
View 2 Replies
View Related
Aug 29, 2006
I cannot in the life of me understand what goes wrong here... In a webapplication (C#.NET) I traced an inability to retrieve existing records to SQL Server, where I cannot do the same either. The problem is that in the parameterized query, some fields can be null, and thus when the corresponding fields in the database record are null also, they should be selected. But this won't happen for some reason.
I wrote a test SQL statement that gives me the same bogus:
DECLARE @institution int, @collection int, @serialnr int, @subnr nvarchar(50)SET @institution = 1 SET @collection = 1SET @serialnr = 240 SET @subnr = NULLSELECT ID, Institution, Collection, SerialNumber, SubNumber, AccessionYear, Sagsnummer, DanekraeNr, TaxonIdentified, Stratigraphy, TypeStatus, PlacementRoom, PlacementCabinet, PlacementDrawer, UnitNotesFROM SpecimensWHERE (Institution = @institution) AND (Collection = @collection) AND (SerialNumber = @serialnr) AND (SubNumber = @subnr)
Now there is at least one row with corresponding fields values (1, 1, 240, null), but it won't be selected! What is wrong!?
View 4 Replies
View Related
Jul 18, 2006
Using c# in the compact framework, is there a way to do a parameterized query for counting the number of records where a specified column is null. I tried all of these, but none of them work:
cmd.Add(new SqlCeParameter("@IntColumn", SqlInt32.Null));
cmd.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";
cmd.Add(new SqlCeParameter("@IntColumn", DBNull.Value));
cmd.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";
cmd.Add(new SqlCeParameter("@IntColumn", SqlInt32.Null));
cmd.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn";
cmd.Add(new SqlCeParameter("@IntColumn", DBNull.Value));
cmd.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32));
cmd.Parameters["@IntColumn"].Value = SqlInt32.Null;
cmdGetNumRead.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32));
cmd.Parameters["@IntColumn"].Value = SqlInt32.Null;
cmdGetNumRead.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32));
cmd.Parameters["@IntColumn"].Value = DBNull.Value;
cmdGetNumRead.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32));
cmd.Parameters["@IntColumn"].Value = DBNull.Value;
cmdGetNumRead.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant));
cmd.Parameters["@IntColumn"].Value = SqlInt32.Null;
cmd.CommandText = "select count(*) from Meter where IntColumn is not @IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant));
cmd.Parameters["@IntColumn"].Value = SqlInt32.Null;
cmd.CommandText = "select count(*) from Meter where not IntColumn = @IntColumn";
md.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant));
cmd.Parameters["@IntColumn"].Value = DBNull.Value;
cmd.CommandText = "select count(*) from Meter where IntColumn is not @IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant));
cmd.Parameters["@IntColumn"].Value = DBNull.Value;
cmd.CommandText = "select count(*) from Meter where not IntColumn = @IntColumn";
Whenever I use a "is not" in the query I get an exception, otherwise it returns a count of 0 which is not accurate. Any help will be appreciated.
View 5 Replies
View Related
Feb 25, 2008
Hi guys..
i have so doubts in my mind and that i want to discuss with you guys... Can i use more then 5/6 fields in a table with datatype of Text as u know Text can store maximu data... ? acutally i am trying to store a very long strings values into the all fields. it's just popup into my mind that might be table structer would not able to store that my amount of data when u use more then 5/6 text datatypes...
and another thing... is which one is better to use as data type "Text" or "varchar(max)"... ?
if any article to read more about these thing,, can you refere to me...
Thanks and looking forward.-MALIK
View 5 Replies
View Related
Sep 22, 2014
I am facing problem with re numbering of field records in a table.I am having one table with below records.
sid(identity/primarykey) stickyId(Integer) UserId(integer)
102 0 171
103 1 171
104 2 171
105 3 171
here how to renumbering stickyId values when deleted particular stickyId from UI. Here stickyId field is Integer type only. not primarykey/identity field.
View 3 Replies
View Related
Feb 13, 2007
Hi
I have a really simple query which i can't figure out why its not working. I have a table called 'ADMIN' which has a datetime field called 'date_edited'. Because the majority of records have never been edited, i have allowed null values and they are filled with 'NULL' in each record. How ever, when i try:
SELECT * FROM ADMIN WHERE date_edited = NULL
I get no records, but i can see and know i have hundreds! I know i'm doing somthing really stupid, but for life of me can't figure it out! :eek:
thanks
View 10 Replies
View Related
Jun 18, 2004
I have a checkbox on my webform that is bound to a bit field in my SQL table. I'm fine as long as I've got the bit field set to 0 or 1, but if the field is NULL, the checkbox throws an exception during the databind.
Is there any way to handle this without removing the data binding and manually setting the value (ie: some way to intercept it before the exception gets thrown and then setting the field value in the dataset)?
Thanks!
View 1 Replies
View Related
Oct 17, 2012
We are integrating all our applications/databases into one application/database. During the transition phase, I need to create a number of views based on the new database that mimic the old tables of the old databases, so the old programs can continue to function until they are gradually replaced.
In one of the views, I need to generate a sequential number. The value is unimportant, as long as it is unique in the dataset; strictly spoken, it even doesn't need to be sequential:
eg:
SELECT * FROM myView
should give
Code:
col1col2...id
lalacar..1
bababike..2
....
zsrdpen..896
ghrtink..897
SELECT * FROM myView ORDER BY col2
should give
Code:
col1col2...id
bababike..1
lalacar..2
..
ghrtink..45
..
zsrdpen..396
....
The view is created based on a number of tables.
View 1 Replies
View Related
Oct 27, 2006
this is a slight change to a fequently asked question around here. Ihave a table which contains a "sortorder" column where a user canspecify some arbitrary order for records to be displayed in. Usershave sometimes ordered records the way we used to number lines in aBASIC program (10,20,30, etc.). I'd like to do an update query and fixthis so that every record is in sequential order. I found an examplein this newsgroup of how to do this.However, I have a slight problem! Sometimes, the users duplicated thesortorders. So for example, I might have two records were thesortorder is 20. The query I found in this newsgroup does not work inthat case. Here is some code so that you can see what I mean.create table sorttest (label char(5),sortorder int)goinsert sorttest values ('joe',20)insert sorttest values ('dan',10)insert sorttest values ('jim',44)insert sorttest values ('tom',20)insert sorttest values ('jan',50)-- data dumpselect label, sortorder from sorttest order by sortorder-- I'd like to fix all of the sortorder fields so that they aresequentialupdate sorttestset sortorder = (select count(*)from sorttest subquerywhere sorttest.sortorder <= subquery.sortorder)-- note that tom and joe BOTH HAVE SORTORDER = 4select label, sortorder from sorttest order by sortorderdrop table sorttestThanks in advance for any help.
View 19 Replies
View Related
Dec 5, 2005
Hello, I have a table where I'm deleting the contents before populating the table with new data. I have an ID column that is autogenerating a sequential number. I would like to reset this number back to 1 when I delete the contents of the table. How can this be accomplished?
View 3 Replies
View Related
Feb 17, 2005
Hello everyone,
I have a stored procedure that supplies rows for a front-end DataGrid that allows custom paging. The stored procedure must return the requested "page" of rows as identified by a sproc argument. Currently, I'm loading the the query's result set into a temporary table that has an identity column as primary key. I then run a second query against the temp table that uses the identity column value to strip out the requested "page" of rows and return them to the front-end DataGrid.
I'd like to eliminate the temporary table. To do so I would need to create the equivalent of an identity column in the query's sorted results and reference this value in the WHERE clause in order to return only the requested rows.
Does anyone know of a way to generate a sequential number (starting at 1) within a query (and after the rows have been sorted by the ORDER BY)? I don't think this can be done but I put it out for those who may know better.
Thanks for your help. If you know this is impossible, that would be helpful feedback as well.
BlackCatBone
View 3 Replies
View Related
Jul 20, 2005
I need to group records and assign a setid to the group. I have atable with data that looks like thisColA ColB94015 0106594016 0106594015 0108594015 0108633383 0091232601 00912I need to create a resultset using just sql to look like thisColA ColB GRP94015 01065 194016 01065 194015 01085 194015 01086 133383 00912 232601 00912 2The tricky part is resolving the many to many issue. A value in ColAcan belong to multiple values in ColB and a value in ColB can havemultiple values in ColA.
View 6 Replies
View Related
Oct 7, 2007
I have two tables
1. Po (Purchase Order)
2. PoDet (PoDetails)
Po Table is having a datatime field called "ShipDate", OrderNo and a field called PoNo (which is having number of PO).
PoDet is child table having PoNo and PoQty
Suppose following records are in both tables
Po:-
OrderNo : 1
PoNo :- Po No 1
ShipDate :- Oct 5, 2007
OrderNo : 1
PoNo :- Po No 2
ShipDate :- Dec 5, 2007
OrderNo : 1
PoNo :- Po No 3
ShipDate :- Oct 5, 2007
PoDet:
PoNo :- Po No 1
PoQty :- 2000
PoNo :- Po No 2
PoQty :- 3000
PoNo :- Po No 3
PoQty :- 4000
I want to generate a Delivery No. which will be generated in this way :-
1. Earlier Shipdates should be assigned a Lower Number
2. If Shipdates are same, like in case of PoNo 1 and PoNo3, the higher Qty will be assigned a lower number
So meeting the above two conditions, a single SQL should return
PoNo :- Po No 3
DelNo : 1 (As Dates are same for PoNo1 and PoNo3 but PoNo3 Qty is higher, so this will come first)
PoNo :- Po No 1
DelNo : 2
PoNo :- Po No 2
DelNo : 3
Can anybody help on this.
View 4 Replies
View Related
Jan 25, 2008
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
View 7 Replies
View Related
Jan 28, 2015
Need to know if the varchar datatype field will ingore leading zeros when compared with numeric datatype ?
create table #temp
(
code varchar(4) null,
id int not null
)
insert into #temp
[Code] .....
View 4 Replies
View Related
Feb 13, 2015
The below data come from table table1. Instead of below result Ex1: I need output similar to the ex2.
Ex1:
CaseNumberStart CaseNumberEndExported
15000013150000131
15000014150000141
15000504150005041
15000505150005051
Ex2:
CaseNumberStart CaseNumberEndExported
15000013150000142
15000504150005052
How to get the result similar to Ex2, instead of Ex1. (ie., case-number is in sequential order then no need to break), And it should suit large dataset, I will finetune, if any performance issue.
View 1 Replies
View Related
Sep 25, 2015
I have to increment sequential values for the following:
Current Data
Col1Â Â Â Â Â Â Â Â Â Â Col2Â Â Â Col3
12.345.678Â 0001Â Â 32
13.456.789Â 0002Â Â 43
Updated Data
Col1Â Â Â Â Â Â Â Â Â Â Col2Â Â Â Col3
12.345.678Â 0001Â Â 32
12.345.678Â 0002Â Â 32
12.345.678Â 0003Â Â 32
13.456.789Â 0002Â Â 43
13.456.789Â 0003Â Â 43
13.456.789Â 0004Â Â 43
What I need is: Increment up to 3 times the values in Col2. Need to identify the first number in Col2 and increase the number up to 3.
View 3 Replies
View Related
Mar 21, 2006
I can't seem to get nvarchar(max) to work with ADO 2.8 using sql native client.
I am creating a stored procedure and every time i attempt to add a parameter to the command object of type nVarChar(max) I receive the error
"Parameter object is improperly defined"
here is the code to add the parameter
cmd.Parameters.Append cmd.createparameter(@piComments,adLongVarWChar,adparaminput,,me.comments)
adLongVarWChar is the ado data type i am using to map to the new nVarChar(max) but it does not appear to be working.
Is this supported in ADO? I am using the sql native client connection to connect to the database as follows.
pubStrConnectionString = "Provider=SQLNCLI;" _
& "Server=.sqlExpress;" _
& "Database=MyDBName;" _
& "Integrated Security=SSPI;" _
& "DataTypeCompatibility=80;" _
& "MARS Connection=True;"
thanks
View 9 Replies
View Related
Sep 10, 2007
Hi, I have created a database using VWD to keep values of urls and have structured it as...
Prefix (http://, network name), address(www.name.com), and name (name of address), the address field has been defined as a nvarchar(MAX).
Most of the addresses updated into the address field work, except something like: www.java-scripts.net/javascripts/Image-Rollover-Script.phtml.
I get this error:
Cannot open user default database. Login failed.Login failed for user 'NETWORKNAMEASPNET'.
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 open user default database. Login failed.Login failed for user 'NETWORKNAMEASPNET'.Source Error:
Line 1176: if (((this.Adapter.InsertCommand.Connection.State & System.Data.ConnectionState.Open)
Line 1177: != System.Data.ConnectionState.Open)) {
Line 1178: this.Adapter.InsertCommand.Connection.Open();
Line 1179: }
Line 1180: try {
I can insert something like www.google.com into the addresses field without any errors. Any ideas why?If it is a nvarchar type it should be able to except all sorts of characters??
View 11 Replies
View Related
Mar 20, 2007
Hi,
trying to input
create table T (c1 nvarchar(max));
in MS SQL Server Manangement Studio Express results in an error :
Fehler beim Analysieren der Abfrage. [ Token line number = 1,Token line offset = 30,Token in error = max ]
create table T (c1 nvarchar(4000));
is processed w/o errors.
I´ve installed SQL Server 2005 Express Ed. SP2
Microsoft SQL Server Management Studio Express 9.00.3042.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft .NET Framework 2.0.50727.42
Betriebssystem 5.1.2600
Any ideas?
Thanks in advance
Werner
View 2 Replies
View Related
Apr 7, 2004
Hello!
Got a problem I hope some clever people can help me out with..
I have a web form that displays a set of records in a grid. The grid is "paged" according to a PageNum column, with a dropdown box to change pages and buttons allowing the items to be moved up or down a list within a page or moved between pages. So the backend table (simplified) looks something like this
PageNum ItemNum ItemDescription
----------- ----------- -------------------
1 1 aaaaaaa
1 2 bbbbbbb
1 3 cccccccc
2 1 ddddddd
2 2 eeeeeee
2 3 ffffffffffff
3 1 ggggggg
3 2 hhhhhhh
3 3 iiiiiiiiiiiiiiii
The problem is when I want to delete a page - I need the page numbers to automatically resequence themselves, so for example, If I delete "Page 2" (i.e. delete rows where PageNum = 2), all items on "Page 3" become "Page 2" (and any items on "Page 4" become "Page 3" etc).
This has proved straightforward to when deleting an item from a particular page, and can resequence ItemNum thanks to a clever bit of code found on SQLteam.com:
DECLARE @intCounter int
SET @intCounter = 0
UPDATE <Item Table>
@intCounter = ItemNum = @intCounter + 1
WHERE Pagenumber = <Currently Selected Page>
However I haven't been able to adapt this to resequence the Page number, as this involves resequencing blocks of numbers. The closest I can get is:
DECLARE @intCounter int
SET @intCounter = 1
UPDATE <Itemtable>
SET @intCounter = PageNum = CASE
WHEN @intCounter = PageNum - 1 THEN @intCounter + 1
WHEN @intCounter = PageNum - 2 THEN @intCounter + 1
ELSE @intCounter
END
But this doesn't quite work.
Anyone got any other ideas??
Thanks
Greg
View 3 Replies
View Related
Jul 14, 2014
I have a table that holds notes for item's. I'm want to do a select statement where one of my columns assigns a sequential value to each row based on the item number. Would like data to look like this where doc_no would be my row_number function:
item_no seq_no note doc_no
ABC 1 blah 1
ABC 2 blahh 1
ABC 3 bla3 1
XYZ 1 more n 2
XYZ 2 another 2
EFG 1 blahhh 3
View 2 Replies
View Related
Sep 25, 2015
I have to increment sequencial values for the following:
Current Data
Col1 Col2 Col3
12.345.678 0001 32
13.456.789 0002 43
Updated Data
Col1 Col2 Col3
12.345.678 0001 32
12.345.678 0002 32
12.345.678 0003 32
13.456.789 0002 43
13.456.789 0003 43
13.456.789 0004 43
What I need is: Increment up to 3 times the values in Col2. Need to identify the first number in Col2 and increase the number up to 3.
View 2 Replies
View Related
May 15, 2005
Hi there,I have a table named Action. This table has a column InPrice with datatypenvarchar(12). I want to change its datatype from nvarchar(12) to money. Ibrowsed through the values and removed any dots. Th column now has onlynumeric values (and commas for decimal values such as 105,8). When I try tochange the datatype from nvarchar to money, following mesage is displayed:ADO error: Cannot convert a char value to money. The char value hasincorrect syntax.How can I solve this problem? I cannot figure out which values are causingthis error.Thanks in advance,Burak
View 4 Replies
View Related
