|
Hi
We are using SQL Server 2005. In a table we want to add a Unique constraint to ensure that only one record can be added for a student per day. The two columns for the constraint will be StudentIndex and IssueDate. We cannot use the whole IssueDate value because of the hour, min, seconds, milliseconds parts. Is it possible to use DateParts as part of a Unique constraint?
The following does not work:
CONSTRAINT UQ_Issues
UNIQUE ( DATEPART(dd, IssueDate), DATEPART(mm, IssueDate), DATEPART(yy, IssueDate), StudentIndex )
Any ideas will be appreciated.
Thanks
Kobus
|
|
|
|
|
Hi Kobus,
If you are not able to add a unique constraint to datepart, you can use a trigger to enforce this rule.
Regards,
Mehroz
|
|
|
|
|
Hi,
I'm using a custom membership provider and I am a bit confused.
I am currently work on the ValidateUser method which was overridden. The first stored procedure that I call is aspnet_Membership_GetPasswordWithFormat to get the password. The parameters that I need to supply is:
@ApplicationName
@UserName
@UpdateLastLoginActivityDate
@CurrentTimeUtc
I'm not sure when to make @UpdateLastLoginActivityDate true or false. Please can someone explain.
If the user tries to login, and the he/she supplied the incorrect password, then I call the aspnet_Membership_UpdateUserInfo to update the information, and again there are a couple of parameters that I do not understand what to set them to, namely:
@UpdateLastLoginActivityDate
@CurrentTimeUtc
@LastLoginDate
@LastActivityDate
I hope to hear from someone soon.
Thanks
Brendan
|
|
|
|
|
|
How do i run SQL server stored procedure in visual basic 6 to INSERT records to a table from the form (frmsuppliers)?
the properties are bundled into an array for insertion in the stored procedure as: -
Public Sub SaveSupplier(ByVal vntSupplierInfo As Variant)
'Declarations
Dim strSQL As String
Dim command As New ADODB.command
If vntSupplierInfo(0, 0) = 1 Then '
Dim a(0 To 7) As Variant
a(1) = vntSupplierInfo(2, 0)
a(2) = vntSupplierInfo(3, 0)
a(3) = vntSupplierInfo(4, 0)
a(4) = vntSupplierInfo(5, 0)
a(5) = vntSupplierInfo(6, 0)
a(6) = vntSupplierInfo(7, 0)
a(7) = vntSupplierInfo(0, 0)
'Create a connection in the database
command.ActiveConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Asset;Data Source=AMKILA"
command.CommandText = "sp_SaveSupplier" 'find the insert stored procedure for that language
command.Execute , a() 'Execute the the array of a's into the stored procedure and run the SP
The error i get is:
Run-time error "-2147217904(80040e10)';
Procedure 'sp_savesupplier' expects parameter '@supplierID, which was not supplied.
Here is the procedure which i want to run in order to INSERT RECORDS in table called suppliers in the database called Asset.
CREATE PROCEDURE SP_SaveSupplier
@SupplierID smallint,
@SupplierCode varchar(10),
@CompanyName Varchar(100),
@Address Varchar(50),
@Telephone1 Varchar(20),
@Faxnumber Varchar(20),
@ContactPerson Varchar(30),
@Email Varchar(50)
AS
BEGIN
INSERT INTO Suppliers(SupplierID, Supplier_Code, Company_Name, Address, Telephone1, Faxnumber, Contact_Person, Email_Address)values(@supplierID, @SupplierCode, @CompanyName, @Address, @Telephone1, @Faxnumber, @ContactPerson, @Email )
END
GO
Kindly assist as soon as possible
Amkila 
|
|
|
|
|
I haven't used VB6 since I started working with C# 6 years ago, so I don't have it installed and can't give the exact syntax. But you need to define the command parameters and pass the values of your array to the appropriate paramters. Something like this:
'define paramsters
command.Parameters.Add("@SupplierId", ....... )
command.Parameters.Add("@SupplierCode", ....... )
command.Parameters.Add("@CompanyName", ....... )
command.Parameters.Add("@Address", ....... )
' ... add other parameters ...
'set parameter values
command.Parameters("@SupplierId").Value = a(1)
command.Parameters("@SupplierCode").Value = a(2)
command.Parameters("@CompanyName").Value = a(3)
command.Parameters("@Address").Value = a(4)
' ... set other parameters ...
command.Execute()
|
|
|
|
|
Hello : I've seen defferent post about tthis issue but no real fix
The problem:
If you add a new row with an autoincrement key in the database, using sqlcommand builder, it seems that the newly created key is not updated in the DS
So if just after that add you try to delete the row you get the message :
Concurrency violation: the DeleteCommand affected 0 of the expected 1 records."
One yay I've found to fix that is
1- Tho the update
1- clear the dataset ds.clear()
2- refill the ds using adapter adapter.fill(ds)
But it sounds a little bit odd
Any other Idea ?
|
|
|
|
|
I've never used the command builder because I'd rather control the SQL than have it written for me. But I assume the command builder isn't including the correct statement to retrieve the autoincrement value. Here's an msdn article on how to update the autoincrement value when you call DataAdapter.Update.
http://msdn2.microsoft.com/en-us/library/ks9f57t0(VS.80).aspx[^]
|
|
|
|
|
Thank you
Yes te article describe the problem.
For now what I was doing is to clear the dataset and refill it
I will try to find if I can get the returned record from the adapter
I'm using MySql connector
|
|
|
|
|
Hi,
I have two table like this:
quote_tb:
ID item vendor quote
11 a v1 100
11 a v2 200
11 b v1 400
11 b v2 300
11 c v1 555
11 c v2 777
22 a v1 122
22 a v2 222
22 b v1 322
22 b v2 422
22 c v1 555
22 c v2 777
vendor_tb
name phone# fax# address#
v1 1111 2222 3, x street
v2 1212 2323 4, m street
I am trying to write an sql that given me the following result:
lowestQ_tb
ID item vendor quote phone# fax#
11 a v1 100 1111 2222
11 b v2 300 1212 2323
11 c v1 555 1111 2222
i.e for each of the items a,b,c having the id(11) select the vendors that have the lease quote and then get the phone and fax numbers of the vendors form the table vendor_tb.
To get the first part i.e. lowest quote for each item, this is the sql I use:
SELECT f.item, f.vendor, f.quote
FROM (SELECT item, min(quote) AS minprice FROM quote_tb WHERE ID='11' GROUP BY item)
AS x INNER JOIN quote_tb AS f ON f.item = x.item AND f.quote = x.minprice
which gives me the result:
ID item vendor quote
11 a v1 100
11 b v2 300
11 c v1 555
I don't know how to take it further form here to get lowestQ_tb. Please can you tell me how to get the above desired result i.e tabele lowestQ_tb. Is it possible to do such a thing?
Thanks,
Tara
|
|
|
|
|
How about:
select Q.ID, Q.item, Q.vendor, Q.Quote, V.phone, V.fax
from (
select ID, item, min(quote) AS MinQuote
from quote_tb
where ID = 11
group by ID, item
) AS MinQ
inner join quote_tb Q
on Q.ID = MinQ.ID
and Q.item = MinQ.item
and Q.quote = MinQ.quote
inner join vendor_tb AS V
on V.name = Q.vendor
order by Q.ID, Q.item Note that if two different suppliers give the same quote for the same item then they will both be displayed. You could correct that using:
...
from (
select Q.ID, Q.item, min(vendor) AS MinVendor
from (
select ID, item, min(quote) AS MinQuote
from quote_tb
where ID = 11
group by ID, item
) AS MinQ
inner join quote_tb AS Q
on Q.ID = MinQ.ID
and Q.item = MinQ.item
and Q.quote = MinQ.quote
group by Q.ID, Q.item
) AS MinS
inner join quote_tb Q
on Q.ID = MinS.ID
and Q.item = MinS.item
and Q.vendor = MinS.MinVendor
...It may help you to run each sub-query in turn to see how it is built-up. If you are using SQL-Server 2005 then you may be able to rewrite this more concisley using the windowing-functions.
Regards
Andy
|
|
|
|
|
Thank you for the reply.
I am basically connecting to MS Access through my VC++ code. I tried the code that you have given and it works with a few parenthesis added. Thank you.
Thanks,
Tara
|
|
|
|
|
See my addition in bold, I hope this will help you.
SELECT f.item, f.vendor, f.quote, v.phone, v.fax
FROM (SELECT item, min(quote) AS minprice FROM quote_tb WHERE ID='11' GROUP BY item)
AS x INNER JOIN quote_tb AS f ON f.item = x.item AND f.quote = x.minprice
inner join vendor_tb as v on f.vendor = v.name
Regards,
Mehroz
|
|
|
|
|
Thank you for the reply.
But for some reason, whatever way I try it doesn't seem to be working.
I am connecting to MS Access database through VC++. In whatever way I try to add an extra 'inner join' it gives me the error : "Syntax error(missing operator) in query expression". If I remove the extra 'inner join' its all fine.
Can't understand why. Is it something to do with the formatting?
Thanks,
Tara
Thanks,
Tara
|
|
|
|
|
I got it. Just needed to place a parenthesis.
SELECT f.item, f.vendor, f.quote, v.phone, v.fax
FROM ((SELECT item, min(quote) AS minprice FROM quote_tb WHERE ID='11' GROUP BY item)
AS x INNER JOIN quote_tb AS f ON f.item = x.item AND f.quote = x.minprice)
inner join vendor_tb as v on f.vendor = v.name
Thank you for the help.
Thanks,
Tara
|
|
|
|
|
Hi Everyone,
Is it possible to detect the expiry of password in oracle without being an DBA?. Can anyone help me in this regard.
Regards,
Manowj
|
|
|
|
|
Thanks for looking
I have this query:
select * from Table1 where field1 in (SELECT Value FROM fnSplitString ('a,b,c', ','))
Ive also tryed casting Value:
select * from Table1 where field1 in (SELECT cast(Value as varchar(50)) FROM fnSplitString ('a,b,c', ','))
Field1 has <database default> as collation and is of type varchar(50)
fnSplitString returns a table, in this case, it would return:
Value
a
b
c
of type varchar
Im getting error: Cannot resolve collation conflict for equal to operation.
And i dont know why
I am able to use this function but using it with integer values:
select * from table where ID in (SELECT Value FROM fnSplitString ('1,2,3', ','))
Ill appeciate any help
Code of the function
<br />
CREATE function dbo.fnSplitString(<br />
@String varchar (4000),<br />
@Delimiter varchar (10)<br />
)<br />
returns @ValueTable table ([Value] varchar(4000))<br />
begin<br />
declare @NextString varchar(100)<br />
declare @Pos int<br />
declare @NextPos int<br />
declare @CommaCheck varchar(1)<br />
<br />
--Initialize<br />
set @NextString = ''<br />
set @CommaCheck = right(@String,1) <br />
<br />
--Check for trailing Comma, if not exists, INSERT<br />
--if (@CommaCheck <> @Delimiter )<br />
set @String = @String + @Delimiter<br />
<br />
--Get position of first Comma<br />
set @Pos = charindex(@Delimiter,@String)<br />
set @NextPos = 1<br />
<br />
--Loop while there is still a comma in the String of levels<br />
while (@pos <> 0) <br />
begin<br />
set @NextString = substring(@String,1,@Pos - 1)<br />
<br />
insert into @ValueTable ( [Value]) Values (@NextString)<br />
<br />
set @String = substring(@String,@pos +1,len(@String))<br />
<br />
set @NextPos = @Pos<br />
set @pos = charindex(@Delimiter,@String)<br />
end<br />
<br />
return<br />
end<br />
<br />
Alexei Rodriguez
|
|
|
|
|
OK i "fixed it"
This works:
select * from Table1 where (Field1 COLLATE Traditional_Spanish_CI_AI) in (SELECT Value FROM fnSplitString ('a,b,c', ','))
This also woks if i use Field1 COLLATE Traditional_Spanish_CI_AI
But whats wrong with my original query??
Is there any other way to make this work?
Thanks
Alexei Rodriguez
|
|
|
|
|
Hi!!!
I'm creating a program to build a DataTable and I want to export it to a .dbf file.
One of the result is this line:
Create Table Results.dbf ( NumerodeIndividuosouNinhos number(4,0), Quadricula Char(5))
Unfortunely, it gave me a OleBdException and the error was "Field name is a duplicate or invalid.".
But where is the error here? It isn't a duplicate field...if it's invalid, why is invalid?
Thank You
|
|
|
|
|
I believe creating a DBF this way requires that Column names be limited to 10 chars.
|
|
|
|
|
Hi There!!!
Thank You, perryf_00. That worked well.  Now I've to modify that Column names to 10 chars max.
|
|
|
|
|
Anybody know how to define a NOCHECK add constraint (or equivalent) in a DataSet?
I see the DataRelation and ForeignKeyConstraint classes, but I don't see anything that I can use to set a constraint in the DataSet as a nocheck constraint.
Any help is greatly appreciated.
Thanks!
Charles.
|
|
|
|
|
It's been a while since I worked with this, but I remember having the same issue. I believe adding a relation adds both a check constraint and a foreign key constraint. You should be able to loop through the constraints collection and remove the check constraints and leave the foreign key constraints in place.
|
|
|
|
|
Thanks for the reply.
Any idea how I distinguish them? I expected a Type property or something like that on the Constraint object. Or, checking the constraint type (i.e., ForeignKeyConstraint)...
Charles.
|
|
|
|
|
You can use GetType() and check the type of the object. If you're using C# you could use :
if(constraint is ForeignKeyConstraint)
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
