No one else knows...

[dvst8]

I've posted the following in other forums, but no one seems to know....

I have an SQL database, which has a table that has constraints on the data that can go in. (ie constrain is that numbers must be between 4000-4999).

what i would like to do, is find out the contraint programatically from ASP. can this be done?

the reason i want to do this, is so that if the user enters bad data, i don't want a bad ADO error thrown...

and i don't want to do client side validation, because the constraint may change....

please let me know if/how it can be done!

dvst8

[AKA]

I am sure you can do it but they way to succed my differ from DBengine to DBengine.

Dianne Siebold wrote VB Programmer's Journal (No.7) about accessing SQL Server Metadata. For others you might use ODBC to get the Metadata.

When you recive the Metadata just parse it and there you have it.

Not an easy task but usefull.

Best luck, AKA

[dvst8]

VB Programmer's Journal is a magazine?

I don't know anything about accessing metadata...I've searched the web and come up empty-handed. Do you know where I could find such info?

Thanks.

dvst8

[Clunietp]

Is this a trigger that you have? I'm assuming by "SQL Database" you mean SQL Server

[dvst8]

yes SQL server.

it's not a trigger, it's a constraint.

[AKA]

I have tested a littlie and here is my result, but be aware that I have not gone to the bottom of the problem and that this is a start for you.

I made a CHECK constraint named X on a table with the column Id that I want to check that it is bigger than 0.

I made this SQL statement in the query analyzer

select sc.text from syscomments sc,sysobjects so where so.id=sc.id and so.name="X"

and got this result

text
------------------------------------------------------
([id] > 0)

I think that you can go on from here. I will leave for a three weeks vacation today so dont sitt up wating for more help from me ;) AKA

[dvst8]

thank you AKA.

it was very frustrating not being able to find anything in my reference resources about this.

you have given me a starting point, from which I should be able to solve my problem.

i shall post the entire solution when i get it going.

dvst8

[dvst8]

for any who care, here is how i solved my problem.
many thanks to AKA for directing me to the proper tables where to find the data.



<%

function removespace(myString)
mx = Len(myString)
for i = 1 to mx
cur = Mid(myString, i, 1)
if cur <> " " then
good = good & cur
end if
next
removespace = good

end function

function num(myString, operator)
mx = Len(myString)

for i = 1 to mx-1
cur = Mid(myString,i,2)
if cur = operator then
idx = i + 2
nextChar = Mid(myString,idx,1)
result = ""
do while nextChar >= chr(48) and nextChar <= chr(57)
result = result & nextChar
idx = idx + 1
nextChar = Mid(myString,idx,1)
loop
num = result
exit function
end if
next
end function


stmSQL = "SELECT syscomments.text as constr FROM syscomments INNER JOIN sysobjects ON syscomments.id = sysobjects.id WHERE sysobjects.name = 'CK_CodeID'"
set oRS = Server.CreateObject("ADODB.recordset")
oRS.open stmSQL,"DSN=labdata"

oRS.MoveFirst
constraint = removespace(oRS("constr"))
Response.Write constraint & "<br>"
Response.Write "The minimum number is "&num(constraint, ">=") & "<br>"
Response.Write "The maximum number is "&num(constraint, "<=") & "<br>"


%>




dvst8