Adding a constraint question

Can you add a constraint using the below syntax but to check whether a field
has only numeric digits?
So that if Threshold was updated to say 'marc1' it would fail.
ALTER TABLE misre_threshold WITH NOCHECK
ADD CONSTRAINT Threshold CHECK (Threshold in ('Y','N'))Something like this?
ALTER TABLE misre_threshold WITH NOCHECK
ADD CONSTRAINT Threshold_Const CHECK (isnumeric(Threshold) = 1)
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"marcmc" wrote:

> Can you add a constraint using the below syntax but to check whether a fie
ld
> has only numeric digits?
> So that if Threshold was updated to say 'marc1' it would fail.
> ALTER TABLE misre_threshold WITH NOCHECK
> ADD CONSTRAINT Threshold CHECK (Threshold in ('Y','N'))|||Try this:
...check (<column name> not like '%[^0-9]%')
ML
http://milambda.blogspot.com/|||thanks guys BUT
I tried the following:
but the Threshold Column is dataType money
ALTER TABLE misre_threshold WITH NOCHECK
ADD CONSTRAINT Threshold_Const CHECK (isnumeric(convert(money,Threshold)) =
1)
ALTER TABLE misre_threshold WITH NOCHECK
ADD CONSTRAINT Threshold_Const CHECK (Threshold not like '%[^0-9]%')
Server: Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type money to varchar is not allowed. Use the
CONVERT function to run this query.|||Aha, I assumed it was a character column (you haven't mentioned the actual
data type).
Use CAST in the constraint:
ALTER TABLE misre_threshold WITH NOCHECK
ADD CONSTRAINT Threshold_Const CHECK (cast(Threshold as varchar(64)) not
like '%[^0-9]%')
ML
http://milambda.blogspot.com/|||By the way - have you checked out this nice article regarding the ISNUMERIC
function?
http://www.aspfaq.com/show.asp?id=2390
ML
http://milambda.blogspot.com/|||thanks that does build it but my .net application runs as follows and so
encounters an error, should i change my .net app? I would rather not.|||Thanks for that..
Kewl article.. But I am using 2000 SP4 and the results are different. But
some misses nevertheless.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"ML" wrote:

> By the way - have you checked out this nice article regarding the ISNUMERI
C
> function?
> http://www.aspfaq.com/show.asp?id=2390
>
> ML
> --
> http://milambda.blogspot.com/|||Whenever a constraint is violated the SQL Server returns an error saying so,
and the error should be handled by the calling application. Isn't the
constraint already implemented on the application tier? Why not?
What exactly do you need?
An alternative is to use a trigger and simply rollback any transactions that
would otherwise violate the constraint, but this is IMHO a kludge, since the
data would not be inserted and the user would not be notified of this - in m
y
view - vital consequence.
ML
http://milambda.blogspot.com/|||I see your point.
I don't know what your exposure is to validating data on a datagrid within
.net but it aint pretty.
sample ddl,
What I want is the first update to break the constraint, the second should
not.
create table marc(
ThresholdType varchar(6),
Threshold money)
insert into marc values('RecEst', 20000.00)
ALTER TABLE marc WITH NOCHECK
ADD CONSTRAINT Threshold CHECK (cast(Threshold as varchar(64)) not
like '%[^0-9]%')
select * from marc
update marc set Threshold = 'marc1' where ThresholdType = 'RecEst'
update marc set Threshold = 20001.00 where ThresholdType = 'RecEst'

Add values to a table based on values in another table

When a record is inserted or updated records in Table1 I want a record to be inserted into table3 for each record ID that is in table2 if the table2.id does not already exist in table3. What is the best way to do this? Could this be done with a trigger? If it could how would I write such a trigger. I have never written one before and this sounds like a hand full for my first effort. Your help will be greatly appreciated.

Thanks

You could do it in a trigger. They're really not hard. It's just SQL you want to run whenever a certain action happens on your table. Pretty straightforward. Another option is to use stored procedures to do all of your inserts and updates. Then, just code what could go in the trigger directly into your stored procedure.

Pete

Add New Row Problems

This code runs with no errors.
The problem is nothing actually gets updated.

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
Dim mEmp As New YessClass1
Dim EmpID As Long
Dim Conn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))

'Dim mRdg As YessClass1 = New YessClass1
Dim lngID As Long
'This is to save a record to tblFeedback
Dim strSelect As String = "SELECT * FROM tblFeedback"
'now create the data adapter object and connect it to
'sql string and the connection
Dim dscmd As New SqlDataAdapter(strSelect, Conn)
' Load a data set.
ds = New DataSet
'data Adapter fill with dataset, name of dataset
dscmd.Fill(ds, "Feedback")
SqlConnection1.Close()
'get the EmployeeID and the Rounding RecordID
Try
mEmp = CType(Session("objEmp"), YessClass1)
EmpID = mEmp.Employee
Try
Dim dt As DataTable = ds.Tables.Item("FeedBack")
Dim rowFeedback As DataRow = dt.NewRow

With rowFeedback
.Item("RoundingID") = RdgID
.Item("QuestionID") = Me.lstQuestion.SelectedItem.Value
.Item("ResponseActionID") = Me.lstAction.SelectedItem.Value
.Item("EmployeeID") = Me.lstDirector.SelectedItem.Value
.Item("ActivityID") = Me.lstActivity.SelectedItem.Value
.Item("Feedback") = Me.txtFeedback.Text
If Me.ckReq.Checked = True Then
.Item("Acknowledgement") = 1
.Item("AckDate") = Now
End If
End With

dt.Rows.Add(rowFeedback)

Catch ex As Exception

Response.Write("Error occured" & ex.Message)
End Try
Catch ex As Exception
Response.Write("ERROR: " & ex.Message)
End Try
End SubWhere are you actually updating the database? Adding the row to the DataTable does not do it. You need to call .Update somewhere on the DataSet.

Add a column to a view

hello,
I am trying to add a column to a view. This is a generic column not pulling
from the other tables but will be updated based on the values of the other
columns.
I thought I simply altering the view and add a column and set a value, if
the other values were found true. However, it is not allowing me to add a
column unless it is a integer value. Any suggestions?you cannot add a column here.
You use alter view to change the vew definition. You will have to give the
full sql stament with the computed column (if I am not wrong) in the view.
use it this way. Its very similar to create view just the keyword create is
replaced by alter. Hope this helps.
alter view vname
as
select .... col1 + col2 as new_col
from table1
where <conditions>|||> I thought I simply altering the view and add a column and set a value, if
> the other values were found true. However, it is not allowing me to add a
> column unless it is a integer value.
What does "not allowing" mean? Can you show the DDL for the table
referenced in the view, the existing code for the view, the change you are
attempting, and the exact text of the error? A lot of the people here are
pretty smart, but not many are psychic.
A|||Well, I tried to alter the view but it not giving me what I want.
Currently, I am pulling all the columns from other tables. However, I want
to create a new column called columnD and set it to 1 if columnA, columnB an
d
columnC are 1 else set it to 0.
"Omnibuzz" wrote:

> you cannot add a column here.
> You use alter view to change the vew definition. You will have to give the
> full sql stament with the computed column (if I am not wrong) in the view.
> use it this way. Its very similar to create view just the keyword create i
s
> replaced by alter. Hope this helps.
> alter view vname
> as
> select .... col1 + col2 as new_col
> from table1
> where <conditions>|||Are you using Query Analyzer or the view editor in Enterprise Manager? What
code are you trying to run? This sounds like it needs a CASE expression,
and the ability to understand CASE is a serious limitation in Enterprise
Manager. As I asked before, if you can provide *SPECIFIC* information, we
may be able to help.
"Sonya" <Sonya@.discussions.microsoft.com> wrote in message
news:9240DFD4-B5A0-482B-B64E-AE13F3B31E4E@.microsoft.com...
> Well, I tried to alter the view but it not giving me what I want.
> Currently, I am pulling all the columns from other tables. However, I want
> to create a new column called columnD and set it to 1 if columnA, columnB
> and
> columnC are 1 else set it to 0.
> "Omnibuzz" wrote:
>|||>I have it sort of working but it is not totally correct. So i am sing a
> better way in adding a column to the view based on the below criteria.
And I'd like my car to run better! But if I can't be bothered to give my
mechanic more information, he's going to tell me to go jump off a bridge...|||Since you haven't given the defn, I have created a sample. See if this is
what you wnted. Hope this helps.
create table tbl (a int,b int,c int)
go
insert into tbl values (1,1,1)
insert into tbl values (1,1,0)
create view vw1
as
select a,b,c from tbl
select * from vw1
-- till above was your setup...
--now do this
alter view vw1
as
select a,b,c, case when a=b and b=c and c=1 then 1 else 0 end as d
from tbl
select * from vw1|||Initially,
I wrote to add columnD
alter view sam_generic
AS
select columnD, a.columnA, a.columnB,a. columnC, m.member, v.history,
u.category, c.content, l.letter
from tableA AS a INNER JOIN
tableU on a.category = u.category LEFT OUTER JOIN
tableC on a.id = c.id LEFT OUTER JOIN
tableL on a.id = l.id LEFT OUTER JOIN
tableM on a.id = m.id LEFT OUTER JOIN
tableV on a.id =v.id
Set columnD = 1 Where Exists (a.columnA = 1) AND (if a.columnB = 1) AND (if
(a. columnC=1)
"Aaron Bertrand [SQL Server MVP]" wrote:

> What does "not allowing" mean? Can you show the DDL for the table
> referenced in the view, the existing code for the view, the change you are
> attempting, and the exact text of the error? A lot of the people here are
> pretty smart, but not many are psychic.
> A
>
>|||I was using query analyzer. I didn't think about using case statements. I'll
try that.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Are you using Query Analyzer or the view editor in Enterprise Manager? Wh
at
> code are you trying to run? This sounds like it needs a CASE expression,
> and the ability to understand CASE is a serious limitation in Enterprise
> Manager. As I asked before, if you can provide *SPECIFIC* information, we
> may be able to help.
>
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:9240DFD4-B5A0-482B-B64E-AE13F3B31E4E@.microsoft.com...
>
>|||I was submiting the information that you requested. I was working on multipl
e
tasks, so give me a minute to send the information to you.
"Aaron Bertrand [SQL Server MVP]" wrote:

> And I'd like my car to run better! But if I can't be bothered to give my
> mechanic more information, he's going to tell me to go jump off a bridge..
.
>
>