|
You want to check before or after inserting?
below logic will help you.
Before Inserting
------------------
check whether there is any ZIPCode exist within the range
sql
---
Select @begin = count(ZIPBegin) from tblZIPCodes where ZIPBegin between 12345 and 12346
Select @end = count(ZIPEnd) from tblZIPCodes where ZIPEnd between 12345 and 12346
If @begin > 0 or @end > 0
-- then there is an entry within that range already exist
else
--perform the insertion
After Inserting
----------------
BEGIN TRANSACTION
--Insert the records
-- then use the same sql stmt written above to get the value of @begin and
--@end
If @begin > 1 or @end > 1
-- multiple entries has been made
ROLLBACK TRANSACTION
RAISERROR
RETURN
else
--perform the insertion
COMMIT TRANSACTION
Regards
John
|
|
|
|
|
I have a script of creating tables and stored procedure.
I want to check particular column is exists or not before dropping it or I just want to catch an exception while dropping particular column.
I cannot do so.
Thanks in advanced
Divyesh
|
|
|
|
|
Hi Mr. divyesh1432
this code to check if column exist or not
Exaple:
-------------------------------------------------------------
if exists(select column_name from information_schema.columns
where table_name='TableName' and column_name='ColumnName')
----------------------------------------------------------------------
Best regard
Rami Abd Alhalim
|
|
|
|
|
|
Mr. divyesh1432
if my answer correct plz tell me
thanks
Rami Abd Alhalim
|
|
|
|
|
|
in my application i use ado.net to connect the db and in ado.net when you connect the db it takes an instance of the relevant table and close the connection . but when the application runs it needs to access the db often and in order to do this do i have to connect the db each and every time the db is needed or keep the connection without closing . but the second option affects on concurrecy.
can you guys do some suggestions to this .
thank you ...
|
|
|
|
|
Hi,
I am createing a report in First.aspx page it may look like this with a report in table format using sql server rs
ID Name Salary
1 xxxx 10000
2 yyyy 20000
3 zzzz 30000
I want The ID Column as Link Column(i.e, values of ID 1,2,3 must reposnd to click events)
I did this By using Navigation Property of that feild, But It is asking another report To jump
In which event I have To right my Code-Behind
|
|
|
|
|
Is it possible to define a parameter for a stored procedure that is both an input and output value, in MS SQL Server 2005?
If so, how?
regards
Steve Jowett
-------------------------
Sometimes a man who deserves to be looked down upon because he is a fool, is only despised only because he is an 'I.T. Consultant'
|
|
|
|
|
Yes you can used that.
(
@In_Out int out, --this parameter it can set and get data
@In int --this parameter it can set data just
)
Rami Abd Alhalim
|
|
|
|
|
Simple when you know how.
Thanks
Steve Jowett
-------------------------
Sometimes a man who deserves to be looked down upon because he is a fool, is only despised only because he is an 'I.T. Consultant'
|
|
|
|
|
hi,
I'm having a fixed length file hvaing rows of data and some/all of the records can be grouped together using a rowid.
for ex:
xx1|uiy2jof7giu4|0012000|2007-06-29|AAX
xx3|uiy1jofh3iud|0012000|2007-06-29|AAX
xx4|uiygjofhgiud|0020100|2007-06-29|CUY
xx1|uiyg3vb4giud|0212000|2007-06-30|1HU
xx1|uiygjofhgiud|0002000|2007-06-21|ZXX
xx4|ui2ygj7fhgi9|0052000|2007-07-29|559
Is it possible to read the file into sql as xmldata type and sum a particular value in the row with same id and send to the front end.
Can any one help ?
Thanks and Rgds,
PLS
|
|
|
|
|
I am no expert, but I believe you will need to look at DTS (Data Transformation Services)
Steve Jowett
-------------------------
Sometimes a man who deserves to be looked down upon because he is a fool, is only despised only because he is an 'I.T. Consultant'
|
|
|
|
|
you want read XML file and saving on SQL server ??
|
|
|
|
|
no the source file is a fixed length text file. the data should be read into sql server as xml datatype and sorted and a particular numeric field should be added there and returned to a dataset.
|
|
|
|
|
There are better methods for diong this, but maybe this will help some.
***********************
declare @cmd varchar(300), @file varchar(255)
set @file = 'C:\temp.txt'
set @cmd = 'type ' + @file
create table #temp (f_All varchar(255))
create table #temp2 (f1 varchar(10), f2 varchar(50), f3 varchar(10), f4 datetime, f5 varchar (10) )
insert into #temp
EXEC master..xp_cmdshell @cmd
insert into #temp2
select
substring(f_all, 1, 3),
substring(f_all, 5, 12),
substring(f_all, 18, 7),
substring(f_all, 26, 10),
substring(f_all, 37, 3)
from #temp
where f_all is not null
select * from #temp2
-- GROUP, SORT, SUMMARIZE, AND USE IT HOWEVER YOU NEED IT.
drop table #temp
drop table #temp2
**********************
Tom Garth
Developer
R. L. Nelson and Associates, Inc., Virginia
|
|
|
|
|
I am using auto increment for the primary key in a table. For example,
table - (userID, UserName). Now the auto increment of last userId = 10.
If I would like to insert a userId = 111, I found that the next auto increment userId must be in 112, but it is not 11.
Is there any setting to change back the UserId to 11?
Thanks!
|
|
|
|
|
hi mimimimilaw
you must go to design table ,Set Identity Seed = 10 for column auto increment
|
|
|
|
|
It maybe 10, 20, 45 or any number. How can I using mssql statement to do this?
|
|
|
|
|
Hi mimimimilaw
You can use this statment
CHECKIDENT ('tablename',RESEED,10)
best Regard
Rami Abd Alhalim
|
|
|
|
|
Hi,
Try this
DBCC CHECKIDENT ('tablename',RESEED,110)
If u execute the above statement then the next number will be 111.
But you cannot set it back to 10, because already 111 exists which is greater than 10.
May I know the situation why you need the userid to be 111?
Senthil
|
|
|
|
|
If you need anything i am ready
RAmi
|
|
|
|
|
Thanks All,
I try to do what I want in other methods.;)
|
|
|
|
|
I got this error and I don;t know how to solve it even thought my parameters are matching.
'DHS_CostCenter' table saved successfully
'DHS_Employee' table
- Unable to create relationship 'FK_DHS_Employee_DHS_CostCenter'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_DHS_Employee_DHS_CostCenter". The conflict occurred in database "DHS2", table "dbo.DHS_CostCenter", column 'CostCenterID'.
User canceled out of save dialog
(MS Visual Database Tools).
Thanks for your help.
|
|
|
|
|
Check datatypes fields in relations tables
I Love SQL
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
