|
Hi all,
how to validate the range of values in a table.No duplicate entries are accept.
ex : In a table, having cards serial number from 10 to 20, but again i try to insert 11 to 19 serial number cards, it won't be accept.how to validate this type of range validation.
table fields are :
vndrCode :v001
serialFrom:10
SerialTo:20
IssueDate:23-mar-2007
pls help me.
|
|
|
|
|
to validate data before inserting to a column can be done using CHECK constraint.
but, you are looking for comparing data from multiple columns and then validate the same. I've not sure whether is possible using constraint. however you can write a TRIGGER for validating.
Regards
KP
|
|
|
|
|
I am posting the code.....please check if it works or not......here the logic is........
check whether duplicate record exist or not before inserting records in table.....
i am only posting the code to check the duplicate record in table.....
this SQL will find record if the number ,you are going to insert, exist in table between fromSerial and toSerial
Dim fS as Integer = 10
Dim tS as Integer = 20<br />
Dim myConnection As System.Data.OleDb.OleDbConnection<br />
Dim ds As DataSet<br />
Dim da As OleDbDataAdapter <br />
<br />
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" <br />
& myDatabaseath & ";Persist Security Info=False" <br />
<br />
Dim sqlDuplicate As String = "SELECT vndrCode FROM myTable WHERE <br />
"serialFrom > " & fS & " " & _<br />
"And serialFrom < " & tS & " " & _<br />
"Or serialTo > " & fS & " " & _<br />
"And serialTo < " & tS & " " & _<br />
"Or serialFrom < " & fS & " " & _<br />
"And serialTo > " & tS & " " & _<br />
"Or serialFrom < " & fS & " " & _<br />
"And serialTo > " & tS & " " & _<br />
"Or serialFrom < " & fS & " " & _<br />
"And serialTo > " & fS & " " & _<br />
"Or serialFrom < " & tS & " " & _<br />
"And serialTo > " & tS & " )"<br />
<br />
<br />
// Set up connection<br />
<br />
If Not myConnection Is Nothing Then<br />
If myConnection.State = ConnectionState.Open Then <br />
myConnection.Close()<br />
myConnection = New OleDbConnection(strConn)<br />
myConnection.Open()<br />
Else<br />
myConnection = New OleDbConnection(strConn)<br />
myConnection.Open()<br />
End If<br />
<br />
// Bring records<br />
<br />
da = New OleDbDataAdapter(sqlDuplicate , myConnection)<br />
ds = New DataSet<br />
da.Fill(ds, "Tablename")<br />
<br />
// Check if duplicate record exist <br />
<br />
If ds.Tables(0).Rows.Count = 0 Then : Return 0 //Duplicate Record doesn't exist<br />
Else : Return 1 //Duplicate Record exists<br />
End If
Last of all I dont think this code is very smart .....looking for better answer
Tirtha
"A man can ride on your back only when it is bent....."
|
|
|
|
|
Can some one let me know what would be the best way to authenticate a client that runs on a different AD domain from the domain where the SQL server runs..
Is there some way to use Trusted connection in this scenario
NaReN
|
|
|
|
|
If you are talking about a web app, yes there is. You set the identity of the app pool your web app is running in as a domain user account that has the correct rights in sql server. Then your connection string for your web app can use trusted connection.
If you are talking about a windows app, I am not sure if it is possible or not.
Hope that helps.
Ben
|
|
|
|
|
how to run a program in sqlserver automatically without human intervention
|
|
|
|
|
When u would to run and which kind of program.
Parwej Ahamad
g_parwez@rediffmail.com
|
|
|
|
|
My actual requirement is to send automatically mails regarding birthday wishes to employees by checking dob from database without human intervention
|
|
|
|
|
You can make a service
and Write there business login for sending mail
Start one time service on server.
Or Write and CLR stored procedure and Schedule in sql server job scheduler.
Parwej Ahamad
g_parwez@rediffmail.com
|
|
|
|
|
for Scheduled Jobs ...
in SQL Server Enterprise Manager
Managenemt -> SQL Server Agent - > Jobs.
To my knowledge it is possible to schedule and run SQL commands only.
for executing other applications on scheduled interavals ...
1. write an application and schedule it in windows scheduler so that it
executes on regular intervals
or
2. write a service and configure for automatic startup.
Regards
KP
|
|
|
|
|
Hi
In my database I have two tables with the same structure (columns, data types, etc).
How would I copy or move specific records from the one table to the other one using Transact-SQL in a stored procedure?
Thanks.
Kobus
|
|
|
|
|
do u mean to copy the data from one table to another table ?or u want to move only specific records?
|
|
|
|
|
I would just like to be able to copy/move specific records, for example those records where UserIndex = 123.
Kobus
|
|
|
|
|
INSERT INTO tblA SELECT * FROM tblB WHERE userIndex = 123
Regards
KP
|
|
|
|
|
Thanks KP
Exactly what I was looking for.
Kobus
|
|
|
|
|
|
INSERT INTO <table1> SELECT * FROM <table2> [WHERE <condition>]
this will allow to select data from one table and insert into another.
Regards
KP
|
|
|
|
|
I get several EDI Files from vendors that have small square at the en of the file on the last line, if I don’t delete them before running my stored procedure I get unexpected end of file error. Anyone know how to make the procedure ignore this?
Brian
|
|
|
|
|
Hi Brian
Most text editors show a small square to indicate that there is a non-printable control character.
If you can identify what the control-character is (I normally use a Unix hex dump) then you can use
REPLACE(MyColumn, CHAR(n), '') to get rid of it (where "n" is the character number). The most likely control-characters are CHAR(0) [ASCII NUL] and CHAR(26) [ASCII EOF].
Hope that helps.
|
|
|
|
|
Thanks for the help but Im not sure how I use the replace statement in this content this is the code that fails due to the control character. Can I put the replace in with the bulk insert and how would I do this?
Thanks
declare @File varchar(1000)
declare @cmd varchar(2000)
declare @dealermark money
create table ##Import (prodnum varchar(20), lblabr varchar(4), lbl varchar(20), vendorname varchar(20), catnum varchar(20),config varchar(2),artist varchar(50),title varchar(50),mcat1 int,mcat2 int,mcat3 int,vidrate varchar(5),sellprice money,listprice money,UPC varchar(20),oldupc varchar(12),oldprod varchar(20),rlsdate datetime,disdate datetime, lastret datetime,specord varchar(1), modflag varchar(1), bioinfo varchar(1000))
select @File = @pFileName
select @cmd = 'bulk insert'
select @cmd = @cmd + ' ##Import'
select @cmd = @cmd + ' from'
select @cmd = @cmd + ' ''' + replace(@File,'"','') + ''''
select @cmd = @cmd + ' with (MAXERRORS = 1000, FIELDTERMINATOR=''","'''
select @cmd = @cmd + ',ROWTERMINATOR = ''' + char(10) + ''')'
truncate table ##Import
-- import the data
exec (@cmd)
|
|
|
|
|
Do you have an error message? I use the same sort of code in my application and it doesn't complain about CHAR(0) characters.
Otherwise, is it possible for you to clean the file using C# before loading it in?
|
|
|
|
|
In Access these four items would be sorted like this:
EIGHTY
'EIN EL-JARBA
EL-AJJUL, TELL
ELAM, ELAMITES
But in SQL Server they get sorted like this:
'EIN EL_JARBA
EIGHTY
ELAM, ELAMITES
EL-AJJUL, TELL
Is there a way to make SQL server sort like the Access database.
|
|
|
|
|
I kept waiting, hoping someone would reply, cause I don't know nothin about SQL Server; but since no one is stepping up to the plate...
What colation order are you using in Access? Look under Tools -> Options -> General -> "New database sort order".
My guess is that you have it set to "General", which I think uses the Western European Code Page. Try the following link:
http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarsqlsg/html/msdn_interntl.asp[^]
which discusses code pages etc. You're probably going to have to reset your code page within SQL Server to get it to work like Access; probably to Western European.
|
|
|
|
|
Hi All,
Few days back I posted a problem related to Pivot Table, and with help of people here I solved it. Thanks a lot. Unfortunately the script had some lack of generalization. Lest see the code first, -
<br />
CREATE PROCEDURE dbo.spDayBranchSummary<br />
<br />
@RepDate DATETIME<br />
<br />
AS<br />
<br />
SELECT <br />
-- 1.A & 1.B Branch Code and Name<br />
BCode, BName, <br />
<br />
-- 2.A Total Quantity of HH sold on that day<br />
((SELECT SUM(SaleLine.Qty)<br />
FROM SaleLine INNER JOIN (Sale INNER JOIN Vendor ON <br />
Sale.VCode = Vendor.VCode AND<br />
Vendor.BCode = Branch.BCode)<br />
ON SaleLine.SaleCode = Sale.SaleCode AND<br />
Sale.SaleDate = @RepDate AND SaleLine.PCode = 'HH'<br />
)) AS HH,<br />
<br />
-- 2.B Total Quantity of HT sold on that day<br />
((SELECT SUM(SaleLine.Qty)<br />
FROM SaleLine INNER JOIN (Sale INNER JOIN Vendor ON <br />
Sale.VCode = Vendor.VCode AND<br />
Vendor.BCode = Branch.BCode)<br />
ON SaleLine.SaleCode = Sale.SaleCode AND<br />
Sale.SaleDate = @RepDate AND SaleLine.PCode = 'HT'<br />
)) AS HT,<br />
<br />
-- 3 Sales on that day<br />
dbo.NZ((SELECT SUM(SaleLine.Qty * SaleLine.Price)<br />
FROM SaleLine INNER JOIN (Sale INNER JOIN Vendor ON <br />
Sale.VCode = Vendor.VCode AND<br />
Vendor.BCode = Branch.BCode)<br />
ON SaleLine.SaleCode = Sale.SaleCode AND<br />
Sale.SaleDate = @RepDate), 0) AS [Net Value],<br />
<br />
-- 4.A Total Payment on that day<br />
dbo.NZ((SELECT SUM(VPH.CashAmount)<br />
FROM VendorPaymentHistory VPH INNER JOIN <br />
Vendor <br />
ON VPH.VCode = Vendor.VCode AND <br />
Vendor.BCode = Branch.BCode AND <br />
VPH.PaymentDate = @RepDate), 0) AS Cash,<br />
<br />
-- 4.B Total Coupons returned on that day<br />
dbo.NZ((SELECT SUM(VCH.Quantity*VCH.Price)<br />
FROM VendorCouponHistory VCH INNER JOIN<br />
Vendor<br />
ON VCH.VCode = Vendor.VCode AND <br />
Vendor.BCode = Branch.BCode AND <br />
VCH.[Date] = @RepDate), 0) AS Coupon, <br />
<br />
-- 5 Total of 4.A and 4.B<br />
-- (Cash + Coupon) AS [Total Value]<br />
<br />
-- 6 Day's Balance<br />
-- ([Net Value] - Cash - Coupon) AS [Day Balance]<br />
<br />
-- 7 Previous Outstanding<br />
-- Previous Outstanding = [Previous Sale] - [Previous Cash] <br />
-- - [Previous Coupon] + [Total Opening Balance]<br />
-- 7.A Previous Sale<br />
dbo.NZ((SELECT SUM(SaleLine.Qty * SaleLine.Price)<br />
FROM SaleLine INNER JOIN <br />
(Sale INNER JOIN <br />
Vendor ON Sale.VCode = Vendor.VCode AND <br />
Vendor.BCode = Branch.BCode)<br />
ON SaleLine.SaleCode = Sale.SaleCode AND<br />
Sale.SaleDate < @RepDate), 0) AS [Previous Sale],<br />
<br />
-- 7.B Previous Cash<br />
dbo.NZ((SELECT SUM(VPH.CashAmount)<br />
FROM VendorPaymentHistory VPH INNER JOIN <br />
Vendor <br />
ON VPH.VCode = Vendor.VCode AND <br />
Vendor.BCode = Branch.BCode AND <br />
VPH.PaymentDate < @RepDate), 0) AS [Previous Cash],<br />
<br />
-- 7.C Previous Coupon<br />
dbo.NZ((SELECT SUM(VCH.Quantity*VCH.Price)<br />
FROM VendorCouponHistory VCH INNER JOIN<br />
Vendor<br />
ON VCH.VCode = Vendor.VCode AND <br />
Vendor.BCode = Branch.BCode AND <br />
VCH.[Date] < @RepDate), 0) AS [Previous Coupon],<br />
<br />
-- 7.D Opening Balance<br />
dbo.NZ((SELECT SUM(Vendor.OpeningBalance)<br />
FROM Vendor <br />
WHERE Vendor.BCode = Branch.BCode), 0) AS [Opening Balance]<br />
FROM Branch<br />
ORDER BY BCode<br />
GO<br />
Now in this stored procedure 2.A and 2.B are rows in another table and taken as columns here. In future suppose rows in that table increase I will have to add lines here for new rows. Can this be automated? So that there is no need to add new lines for new rows?
Thanks in advanced
Kumar
|
|
|
|
|
Phew - that's one long procedure. If I were you, I would introduce a table that contained the codes that you were interested in, and then in your select statement show the code and the total. The link is a simple inner join, so
((SELECT SUM(SaleLine.Qty)
FROM SaleLine INNER JOIN (Sale INNER JOIN Vendor ON
Sale.VCode = Vendor.VCode AND
Vendor.BCode = Branch.BCode)
ON SaleLine.SaleCode = Sale.SaleCode AND
Sale.SaleDate = @RepDate AND SaleLine.PCode = 'HT'
)) AS HT, becomes
LookupTable.Code, ((SELECT SUM(SaleLine.Qty)
FROM SaleLine INNER JOIN (Sale INNER JOIN Vendor ON
Sale.VCode = Vendor.VCode AND
Vendor.BCode = Branch.BCode)
ON SaleLine.SaleCode = Sale.SaleCode AND
Sale.SaleDate = @RepDate AND SaleLine.PCode = LookupTable.Code
)) AS SumOfValues
Deja View - the feeling that you've seen this post before.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
