INSERT + CHECK CONSTRAINT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

INSERT + CHECK CONSTRAINT

Author

Topic

EINAT
Starting Member

3 Posts

Posted - 2003-02-02 : 04:32:31

HI,

I TRIED TO INSERT VALUE USING VB TO COLOUM THAT HAVE CHECK CONSTRAIN
IN THE TABEL DISSINE .

THE DATABAS IS SQL 7.

THE CODE IS:Public stud_conn As New ADODB.Connection
Public stud_rs As New ADODB.Recordset

Private Sub Command1_Click()
stud_conn.Close
End Sub

Private Sub Command2_Click()
Dim a As String
a = Trim(Text2.Text)
stud_conn.Execute "insert into stud_info(city)values (' " & a & " ')"
End Sub

Private Sub Form_Load()
stud_conn.Open "Provider=SQLOLEDB.1;Persist Security Info=false;" _
& "User ID=einat;Initial Catalog=STUD_2;Data Source=oemcomputer"
If stud_conn.State = adStateOpen Then

L1.Caption = "WWELL CONN"

Else
L1.Caption = "NO CONN"
End If
End Sub

THE ERROR MESSEGE IS:
RUNTIME ERROR -2147217900(80040E14)
INSERT STAEMENT CONFICTED WITH TABLE CHECK CONSTRAINT
'CK_STUD_INFO'

WHERE IS MY MISTAK??
THANKS
EINAT

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-02-02 : 11:22:28

What is the value of Text2.Text or a when you got the error?

What constraints do you have on the city column? Is it you're primary key and you're inserting a duplicate or is it supposed to be unique and you're trying to insert a duplicate???

Edited by - ValterBorges on 02/02/2003 11:23:34

revital
Starting Member

2 Posts

Posted - 2003-02-03 : 03:01:48

quote:

What is the value of Text2.Text or a when you got the error?

What constraints do you have on the city column? Is it you're primary key and you're inserting a duplicate or is it supposed to be unique and you're trying to insert a duplicate???

Edited by - ValterBorges on 02/02/2003 11:23:34

quote:

in text2.text i insert value like ny that has been define in chack constraint, on this way: [city]='ny' or [city]='wt'
on the coloum city.
city is not index ' key , duplicate or others,
it can be even null/
when i use in the insert conmmand the value 'ny', its go
withuot error , but when i use to send the data using textbox ther
is runtime error.

thanks.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-02-04 : 19:54:10

Use SQL Profiler and start a trace then watch for the statements being executed against the database.

or

Write the sql to a string variable first and then debug your code by placing a watch on the line where the string is created.
Then you can either identify the problem if it's obvious by looking at the string or by cutting and pasting into query analyzer.

The mod would be something like
Dim a As String
Dim strSQL As String
a = Trim(Text2.Text)
If UCase(a) <> 'NY' or UCase(a) <> 'WT' Then
MsgBox "Error in input"
Exit Sub
End If
strSQL = "insert into stud_info(city) values ('" & a & "')"
stud_conn.Execute strSQL

Now you can place a breakpoint at strSQL and watch the value or use a Debug.Print or MsgBox to show you the value before execution.

You need to intercept the command your executing.

Edited by - ValterBorges on 02/04/2003 19:54:24

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources