Increment An ID Field In Sql Query String
Mar 3, 2006
Hello..
can anyone help me with this query string?
String SQL = "INSERT Employee(Employee ID, UserName, JobRole, Department, Level, Email)(SELECT max(EmployeeID) + 1 FROM Employee) AS Employee ID, VALUES(EmployeeID, '" + newUserName + "', '" + newJobRole + "', '" + newDept + "', '" + newLevel + "', '" + newEmail + "')";
I am trying to insert values into a table, but i have an Employee ID field, which needs incrementing. How can i do this through my SQL query string? Is this possible? As it can't accept a NULL value.
Thanks, Sandy
View 4 Replies
ADVERTISEMENT
Feb 13, 2006
We have the following two tables :
Link ( GroupID int , MemberID int )
Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in
multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated
Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to
fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID,
then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
View 1 Replies
View Related
Feb 28, 2007
Hi,
I have a SQL server 2005 database with a series of multiple fields. One of the fields has a array of strings seperated by semi-colons like so: Red;Green;Blue
My question is, how can i run a query on all of the fields that have the value of say Green in it. Note that these values vary in different order and numbers.
Thanks
View 2 Replies
View Related
Dec 22, 2006
I'm very new to SQL server and can use some help. MyTable has ColumnA, which contains strings composed of 1 to 4 numeric characters (0 thru 9) followed by alphabetic characters. For example, "53ASDF". In my query, I need to create ColumnB, which takes the numeric prefix from ColumnA's string and prepends it with zeros, if necessary, to create a string of exactly 4 numeric characters. For example, I could get the following result:
ColA ColB
"6abc" "0006"
"457def" "0457"
"7232hij" "7232"
I have implemented a temporary solution using a CASE statement:
SELECT ColA, ColB =
CASE
WHEN ISNUMERIC(LEFT(ColA, 4)) = 1 THEN (LEFT(ColA, 4))
WHEN ISNUMERIC(LEFT(ColA, 3)) = 1 THEN '0' + (LEFT(ColA, 3))
WHEN ISNUMERIC(LEFT(ColA, 2)) = 1 THEN '00' + (LEFT(ColA, 2))
WHEN ISNUMERIC(LEFT(ColA, 1)) = 1 THEN '000' + (LEFT(ColA, 1))
ELSE ''
END
FROM MyTable
Because of additional complexities, I need to implement the solution with a loop instead of a CASE statement. Can someone please describe such a solution?
I'm very confused about how variables work in SQL Server, but made an attempt to implement a solution. Hopefully, someone can make corrections and describe how to use it with a SELECT statement. I would greatly appreciate any suggestions. This is what I started with:
DECLARE @ColBstring char(4)
DECLARE @num int
SET @ColBstring = ''
SET num = 1;
-- Get the numeric prefix from ColumnA's string
WHILE(isnumeric(substring(colA, 1, num)) = 1)
@ColBstring = (substring(colA, 1, num)
num = num + 1
-- Prepend the ColumnB string with zeros
WHILE(LEN(@ColBstring) < 4)
@ColBstring = '0' + @ColBstring
Thanks for any help,
Mike
View 1 Replies
View Related
Mar 7, 2008
I need to search a nvarchar field based on the format of the text. This field holds values in two formats: 000 000 000 000 and 000000. I only want to search through the records that are in the 000 000 000 000 format. Can anyone give me direction on how to go about doing this or give me some key words to search for on Google? Fixing this problem is not an option. This is a county tax DB from a poor county with almost a million records in it.
Thanks for the help!
View 2 Replies
View Related
Oct 26, 2007
I have FeaturedClassifiedsCount field, which I would like to update each time record is selected. How do I do it in stored procedure on SQL 2005?
This is my existing code:alter PROCEDURE dbo.SP_FeaturedClassifieds
@PageIndex INT,
@NumRows INT,
@FeaturedClassifiedsCount INT OUTPUT
AS
BEGIN
select @FeaturedClassifiedsCount = (Select Count(*) From classifieds_Ads Where AdStatus=100 And Adlevel=50 )
Declare @startRowIndex INT;
Set @startRowIndex = (@PageIndex * @NumRows) + 1;
With FeaturedClassifieds as (Select ROW_NUMBER() OVER (Order By FeaturedDisplayedCount * (1-(Weight-1)/100) ASC) as
Row, Id, PreviewImageId, Title, DateCreated, FeaturedDisplayedCountFrom
classifieds_Ads
WhereAdStatus=100 And AdLevel=50
)
SelectId, PreviewImageId, Title, DateCreated, FeaturedDisplayedCount
From
FeaturedClassifieds
Where
Row between@startRowIndex And @startRowIndex+@NumRows-1
END
View 1 Replies
View Related
Nov 15, 2007
HiI have a field containing numbers. I want to do some simple arithmetics with it, say value=value+1 or value=value-1 or or even value+2. What is to be done, is fixed at design time. I think this could be done by loading the row or record to my program and doing the calculations there. And then storing the record back. But this seems too complicated.Is there a single query doing that in data table.
View 3 Replies
View Related
Nov 27, 2007
Hi,
I have one Auto increment field in the table.
I have a problem that suppose I have 10 Records in the table, Now If someone delete record no 3 and 5 then it has 8 records.
But The field will give 11 no to new record I want it first fill 3 and 5 then give 11 to the new record.
Is it possible with the auto increment field in sql server 2005.
View 1 Replies
View Related
Apr 5, 2008
Hey,
Im building a site within Visual Studio 2005 and im using the SQL database system it provides. I currently have a number of tables each with a primary key that is set to the 'int' field type and also set to auto increment 1, 2, 3 etc etc. My question is it there a way to make this field increment automatically, providing a unique value but also automatically placing a couple of pre-defined letters before the number? Obviously it can't be an 'int' anymore because it'll be holding something like MEM1, MEM2 etc. Is there a way to do this is Visual Studio?
Many Thanks!
View 3 Replies
View Related
Oct 27, 2014
I am having a
Source Table: #test1(studID, FIrstNAme,LasteName)
Target Table: #Test2(StudID, UserName)
Now I want to create usernames from #test1 by considering first character of first name and last name and if same combination found then append with 01.
Example if #test1 contains data as below:
1,Abhas, Pawar
2, Arun, Pawar
3, Ashis, Panday
Then i want to create username like:
apawar
apawar01
apanday02
but if same username exists in #test2 then i want to inser records as below:
first apawar will check in #test2, if not exists insert as it is:
if apawar01 exists in #test2 then, cretae apawar02 instead of apawar01
for next create apawar03 and insert and so on...
In brief I want to check created username eith #table2 and if same exists then first check if lower value available if not then create with lower value and insert.
View 5 Replies
View Related
Oct 8, 2006
I was just wondering on a very simple database table with lets say a primary key set to columb ID and another columb lets say products, can you make the primary key automaticly increment its self whenever a new entry has been put in?For instance say I have this table set up with ID Being the primary KEY, Columb 1 = ID( INT ), Columb 2 = Products ( VarChar(50) ), and have the fields ID = 1, and products = my product.....and if a user inserts a new record say from a gridview or some sort of data entry the second ID Feild will automaticly be 2 and the products gets updated per user input.......I'm very sorry but I'm having a hard time putting this into words for some reason..umm basicly user adds something into the products feild and the ID field automaticly increments one number higher from the last one?ThanksAdam.
View 4 Replies
View Related
Nov 4, 2014
declare @kk int
set @kk=0
insert into tblSSAppsOrgEntityToEmployerMapDiffer
(Id,
OrgEntityCode,
EmployerId,
[Default],
[Code] ...
In above example Id is PK for Differ tbl and Temp tbl not having field related to this. thats why i have to take and increment that Id value manually.... but like above way i m getting error ..........
View 5 Replies
View Related
Apr 25, 2008
Hi All,
I am trying to write a query that takes the max recordID on table A, and increment it by 1 for every record that is inserted into table A. The recordID field does not identity field property turned on.
Can you give me some help in getting this done? Is what I am trying to do even possible?
Thanks in Advance for your help.
View 9 Replies
View Related
Nov 11, 2006
Greetings all,
I have a bit of brainteaser that's going to take some serious thought.
I'm importing information from .xls files into a SQL table. The problem is I need to check for dupes and increment certain fields on success of dupe find and then not insert or delete the dupes.
For example, if I have Adam, Turner, 32, 50 already in the table and someone tries to insert Adam, Turner, 32, 50...I need it to increment to read Adam, Turner, 64, 100 and not insert the record. (Notice 2 fields were incremented.)
With that, I have created an INSERT trigger as follows:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Trigger [dbo].[trgInsertCheck]
ON [dbo].[MyTable]
FOR INSERT
AS
BEGIN
EXEC sp_UpdateDupes
EXEC sp_DeleteDupes
END
The first stored procedure checks for dupes and updates if any dupes are found as follows:
--------------------------------------------------------------------------------------------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[sp_UpdateDupes] AS
DECLARE @FirstName varchar(20), @LastName varchar(20), @Age int, @Widgets int
DECLARE c1 CURSOR FOR
SELECT FirstName, LastName, Age, Widgets
FROM MyTable
GROUP BY FirstName, LastName, Age, Widgets
HAVING COUNT(*) > 1
OPEN c1
FETCH NEXT FROM c1
INTO @FirstName, @LastName, @Age, @Widgets
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE MyTable set Widgets = Widgets + @Widgets, Age = Age + @Age
WHERE FirstName = @FirstName AND LastName = @LastName
FETCH NEXT FROM c1
INTO @FirstName, @LastName, @Age, @Widgets
END
CLOSE c1
DEALLOCATE c1
Lastly, it finds all dupes, deletes them and inserts one row back in as follows:
--------------------------------------------------------------------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[sp_DeleteDupes] AS
DECLARE @FirstName varchar(20), @LastName varchar(20), @Age int, @Widgets int --declare all fields in table
DECLARE c1 CURSOR FOR
SELECT FirstName, LastName, Age, Widgets
FROM MyTable
GROUP BY FirstName, LastName, Age, Widgets
HAVING COUNT(*) > 1
OPEN c1
FETCH NEXT FROM c1
INTO @FirstName, @LastName, @Age, @Widgets
WHILE @@FETCH_STATUS = 0
BEGIN
--Delete all dupes...the cursor remembers the current record
DELETE FROM MyTable
WHERE FirstName IN (SELECT FirstName FROM MyTable GROUP BY FirstName HAVING COUNT(FirstName) > 1)
AND LastName IN (SELECT LastName FROM MyTable GROUP BY LastName HAVING COUNT(LastName) > 1)
AND Age IN (SELECT Age FROM MyTable GROUP BY Age HAVING COUNT(Age) > 1)
AND Widgets IN (SELECT Widgets FROM MyTable GROUP BY Widgets HAVING COUNT(Widgets) > 1)
--insert the current record back into the table
INSERT INTO MyTable(FirstName, LastName, Age, Widgets) VALUES(@FirstName, @LastName, @Age, @Widgets)
FETCH NEXT FROM c1
INTO @FirstName, @LastName, @Age, @Widgets
END
CLOSE c1
DEALLOCATE c1
Is there an easier way to do this?
(I know Age doesn't make much sense in this example but just replace it with a field would logically be incremented such as wadgets.)
Adamus
View 7 Replies
View Related
Apr 11, 2003
CREATE FUNCTION fctisnumericex(@c varchar(1))
RETURNS int AS
BEGIN
RETURN CASE WHEN ASCII(@c)>=ASCII('0') AND ASCII(@c)<=ASCII('9') THEN 1 ELSE 0 END END
CREATE FUNCTION fctstringincrement (@string varchar(255),@maxlen int)
RETURNS varchar(255) AS
BEGIN
DECLARE @@posr int
DECLARE @@posl int
DECLARE @@c varchar(1)
DECLARE @@token1 varchar(255)
DECLARE @@token varchar(255)
DECLARE @@token3 varchar(255)
DECLARE @@i int
/* emulates parts of the behaviour of s_modformatting::substringincrement */
/* 1. find the place where the numeric token starts from the right */
/* if we didn't find any non-numeric part then it might well be that the rightmost digit is already numeric */
IF dbo.fctisnumericex(SUBSTRING(@string,DATALENGTH(@string),1))=1
BEGIN
SELECT @@posr=DATALENGTH(@string)
END ELSE BEGIN
SELECT @@i=DATALENGTH(@string)
SELECT @@c=SUBSTRING(@string,@@i,1)
WHILE dbo.fctisnumericex(@@c)!=1 BEGIN
SELECT @@i=@@i-1
IF @@i<1 BEGIN BREAK END
SELECT @@c=SUBSTRING(@string,@@i,1)
END
SELECT @@posr=@@i
END
/* so have we got any numeric part inside that string? */
IF @@posr>0 BEGIN
/* yep. see how long it lasts */
SELECT @@i=@@posr
SELECT @@c=SUBSTRING(@string,@@i,1)
WHILE dbo.fctisnumericex(@@c)=1 BEGIN
SELECT @@posl=@@i
SELECT @@i=@@i-1
IF @@i<1 BEGIN BREAK END
SELECT @@c=SUBSTRING(@string,@@i,1)
END
/* separate now the parts of the string */
IF @@posl>1 BEGIN SELECT @@token1=SUBSTRING(@string,1,@@posl-1) END ELSE BEGIN SELECT @@token1='' END
SELECT @@token=SUBSTRING(@string,@@posl,@@posr-@@posl+1)
IF @@posr<DATALENGTH(@string) BEGIN SELECT
@@token3=SUBSTRING(@string,@@posr+1,DATALENGTH(@string)-@@posr) END ELSE BEGIN SELECT @@token3='' END
/* increment the numeric part */
SELECT @@token=convert(varchar(255),convert(int,@@token)+1)
END ELSE BEGIN
/* no numeric part at all. start with 1 at the end */
SELECT @@token1=@string
SELECT @@token='1'
SELECT @@token3=''
END
/* recompose the string and trim to max length if necessary */
RETURN SUBSTRING(@@token1+@@token+@@token3,1,@maxlen)
END
View 4 Replies
View Related
May 28, 2015
I want to update a field and in this field insert a increment count, for example:
When I make, "Select * from Users order by User" displays:
User1 | NULL
User1 | NULL
User1 | NULL
User2 | NULL
User2 | NULL
and I want to do this:
User1 | 1
User1 | 2
User1 | 3
User2 | 1
User2 | 2
how to do this?
View 7 Replies
View Related
Dec 30, 2003
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable
mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))
option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End
Please help
View 6 Replies
View Related
Mar 20, 2014
We have some URLs within a bulk block of text some of which are very long. I need to identify rows where such urls exceed say 100 characters in length in amongst other text.So the rule would be return a record if within the string there is a string (without spaces) longer than 100 characters.
View 9 Replies
View Related
Dec 11, 2007
Hello,
I Have a table that needs to have 2 unique number.
detail_id and detail_print_id.
detail_id is already an IDENTITY.
both fields need to be different, because when importing, it imports the same data into a table twice, with only a slight data change (and id is not one of the changes).
So I thought i could do the following:
detail_id INT NOT NULL IDENTITY(1,2),
detail_print_id INT NOT NULL IDENTITY(2,2),
--blah blah
that way, the detail_id will always be odd, and the detail_print_id will always be even. however SQL Server 2005 only allows 1 identity per table, and both these fields need to be auto generated when the field is inserted, so as to prevent double data.
is there anyway I can create a int column to auto increment, without the column being an IDENTITY??
also, I would prefer to not have to create a second table with a single column just for this work.
Thanks,
Justin
View 5 Replies
View Related
Feb 17, 2014
I have two tables with this info:
TABLE 1
COL1 COL2 COL3
AAA BBB CCC
QQQ WWW EEE
AAA SSS DDD
WWW EEE RRR
BBB BBB BBB
TABLE 2
COL1 COL2 COL3 COL4
b b b 343
a a a 344
c c c 345
d d d 346
e e e 347
I want to insert TABLE 1 into TABLE 2 with a query that will auto increment to COL4 looking like this:
COL1 COL2 COL3 COL4
b b b 343
a a a 344
c c c 345
d d d 346
e e e 347
AAA BBB CCC 348
QQQ WWW EEE 349
AAA SSS DDD 350
WWW EEE RRR 351
BBB BBB BBB 352
I know this can be done easily by just altering the column to have an auto-increment datatype, but I currently cannot do that at this moment.
View 5 Replies
View Related
Jan 23, 2004
I have an MS SQL Server table with a Job Number field I need this field to start at a certain number then auto increment from there. Is there a way to do this programatically or within MSDE?
Thanks, Justin.
View 3 Replies
View Related
May 22, 2007
Hello to all,
I have a problem with ms sql query. I hope that somebody can help me.
i have a table "Relationships". There are two Fields (IDMember und RelationshipIDs) in this table. IDMember is the Owner ID (type: integer) und RelationshipIDs saves all partners of this Owner ( type: varchar(1000)). Example Datas for Table Relationships: IDMember Relationships .
3387 (2345, 2388,4567,....)
4567 (8990, 7865, 3387...)
i wirte a query to check if there is Relationship between two members.
Query:
Declare @IDM int; Declare @IDO int; Set @IDM = 3387, @IDO = 4567;
select *
from Relationship where (IDMember = @IDM) and ( cast(@ID0 as char(100)) in
(select Relationship .[RelationshipIDs] from Relationship where IDMember = @IDM))
But I get nothing by this query.
Can Someone tell me where is the problem? Thanks
Best Regards
Pinsha
View 9 Replies
View Related
Mar 17, 2008
I am running a converted DTS package which executes a stored procedure which extracts fields from a SQL database table and puts them to a flat file. Two of the fields are datetime fields. The stored procedure does not convert the dates, and under SQL 2000 DTS the fields extract as strings in format yyyy-mm-dd hh:mms. When this gave me an error in SSIS, I added a data conversion task between the Ole DB Source and the Text file destination to convert the two fields to DT_STR format and used the converted fields in my mapping for the text file. I am still getting an error on this:
-1071636319,0x,Data conversion failed. The data conversion for column "mm_hdr_process_date" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page."
The target code page is 1252 (ANSI) and I have tried several variations of length, and I still get this error.
Any suggestions? I would prefer not to have to change every stored procedure we have which may do this.
Thanks for any help.
MaryKate
View 6 Replies
View Related
Sep 7, 2007
Hi,
I have some columns that can be either a Number or Text.
and I have to sum all the value if that column is a Number, therefore in my Table footer i have:
=IIF(IsNumeric(Fields!Col1.Value),Sum(Fields!Col1.Value),"")
However this will give me #ERROR if the columns is Text. It works for Number.
Any help is appreciated, thanks
Jon
View 7 Replies
View Related
Jun 8, 2007
Hi!
Is there any way to store UTF-8 encoded string in Nvarchar field? Or strings can be stored only in Unicode?
View 3 Replies
View Related
Jan 17, 2008
Hi All ,
I have one field in SQL Server Report Called Status which is text field.
that status can be "Abesent" , Late In", "Early Out" , "Early Out and Late in"
, its in table Like this.
Emp no | Name | Date | Time | Status
1 Kaisar 1-1-07 7:15 Late In
1 Kaisar 1-2-07 17:15 Early Out
1 Kaisar 1-4-07 - Absent
1 Kaisar 1-5-07 - Absent
1 Kaisar 1-6-07 - Absent
And So On......
----------------------------------------------------------------
i want to get count some thing like this
Total Of Absent : 3
Total Of Late in : 4
Total Of Early Out :5
any idea how can i do this ?
View 5 Replies
View Related
Dec 10, 2007
I am trying to append some text to a full text field in SQL Server 2000. I tried this; but, it didn't work:
UPDATE DefendantEventPros SET EventComment=EventComment + ' This event was completed on "& Date() &".' WHERE EventNumber="& eventnumber &"
I get an error saying:
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid operator for data type. Operator equals add, type equals text.
How can I append some text to the end of a field? Thanks for any help!
View 14 Replies
View Related
Mar 29, 2004
hi
i have connected my ms sql 2000 with C using ODBC
can u help me to return the utf 8 string from nvarchar field ??
how should i do it
please help!!!!!!!
View 6 Replies
View Related
Mar 20, 2007
Stuart writes "Hi being new to this game
I have have an error when trying to inseet string into a table with datetime field.
the date is not that important its the time that I use in later steps
I am creating a global temp table and then inserting values into it
below is the code
-- create the temp table
Execute ( 'create table ##progsch0
([Time] [DateTime] , '
+ '[' + @day7 + '] [varchar](100) ,'
+ '[' + @day1 + '] [varchar](100) ,'
+ '[' + @day2 + '] [varchar](100) ,'
+ '[' + @day3 + '] [varchar](100) ,'
+ '[' + @day4 + '] [varchar](100) ,'
+ '[' + @day5 + '] [varchar](100) ,'
+ '[' + @day6 + '] [varchar](100) )')
set @Starttime = 'JUL 21,2006 5:30am'
I am doing the insert in this manor becuase the @Starttime
in code actually changes time and a new record in inserted into the temp table.
Set @SQL = 'Insert into ##progsch0 (Time)
Values(convert(varchar,Convert(datetime,'+ @Starttime +'),100))'
PRINT @SQL
execute sp_executesql @SQL
I may to doing this in the completely wrong manor.
Any help would be greatful "
View 1 Replies
View Related
Jul 23, 2005
Hello All,I'm trying to parse for a numeric string from a column in a table. WhatI'm looking for is a numeric string of a fixed length of 8.The column is a comments field and can contain the numeric string inany positionHere's an example of the values in the column1) Fri KX 3-21-98 5:48 P.M. arrival Cxled ATRI #27068935 3-17-982) wed.kx10/26 Netrez 95860536Now I need to parse through these lines and return only the 8 digitnumbers in itThe result set should be2706893595860536This is what I've done so farDeclare @tmp table(Comments_Txt varchar(255))Insert into @tmpselect Comments_Txt from Reservationselect * FROM @tmp where Comments_Txtlike ('%[0-9][0-9][0-9][0-9][0-9][0**9]%')But it returns the entire comments field in the result set. What I needis a way to return just those 8 digits.Any Ideas??Thanks in advance!!!
View 2 Replies
View Related
Jul 23, 2005
Hi!I got a column, with different dates in it (Ddata type "nvarchar")...when running a SELECT on this column, I'm trying to filter thoserecordsets out, WHERE this column is NULL (I checked the table, thereare "empty" fields in the column):"SELECT bla FROM bla WHERE myColumn NOT NULL"but I still receive those "empty" fields in my resultset ...so I tried it with:SELECT bla FROM bla WHERE myColumn <> ''again, "empty" fields in my resultset ....what's going wrong there? is there a possibility to check what kind ofvalue I got in my column!?Thanks!Peter
View 3 Replies
View Related
Oct 9, 2007
We have account numbers that are string values, looking like: "01.02.02.00.0040.000.000".
We need to parse individual segments and pull out a range of values treating the segment like an integer. For example we would like all accounts that have the fifth segment ranging in values from .0040. through .0060. inclusive.
We've been trying to do something with the like clause: LIKE '__.__.__.__.[0-0][0-0][4-6][0-0]'.
We want 40 through 60 but we're getting 40, 50, and 60. If we change the last bracket to [0-9] we get 40 through 69.
Does anyone have any suggestions as to either fix our like clause or another approach?
Thanks.
John
View 4 Replies
View Related
Jul 25, 2006
I have a few columns in table with default value defined as zero length string (''). I want to insert record from DetailsView which uses SqlDataSource as DataSource. In the ItemInserting event, if the data is not valid, I want to use zero length string for the column. But I always get Null instead of zero length string. The code in ItemInserting event looks like this:
If objddl.SelectedIndex > 0 Then e.Values("myFld") = objddl.SelectedItem.ValueElse e.Values("myFld") = ""End If
The line: e.Values("myFld") = "" put Null in the column.
How can I set a column as zero length string using the SqlDataSource?
Any help is appreciated.
Thanks.
View 3 Replies
View Related