stored procedure help..... - 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

General SQL Server Forums

New to SQL Server Programming

stored procedure help.....

Author

Topic

wormz666
Posting Yak Master

110 Posts

Posted - 2008-10-07 : 06:03:55

im using a ms sql server 2000
i found difficulty on my back end and front end
i want to use the msgbox on the ms sql server 2000
i dont want to duplicate anytime important field.....
my front end is vb 6.0

declare @posno varchar(10)
declare @descript varchar(50)
if not exists(Select * from table1 where posno=@posno or descript=@descript)
begin
insert into table1
(posno,descript)
values(@posno,@descript)
end
else
begin
raiserror 13401 'Invalid dates'
rollback transaction
end

thank in advance......

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 06:08:06

whats the probelm you're facing?code looks fine. You could simply set unique composite key on columns (posno,descript) instead of above check.

wormz666
Posting Yak Master

110 Posts

Posted - 2008-10-07 : 06:17:57

the problem is one the back....i dont know why it debug.....
the msgbox appear with the ff. runtime error -2147217900(80040e14)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 06:44:09

quote:
Originally posted by wormz666

the problem is one the back....i dont know why it debug.....
the msgbox appear with the ff. runtime error -2147217900(80040e14)

have you tried executing stored procedure in query analyser. does it work fine?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-07 : 06:46:23

Do you pass NULL value to non-NULL column?

Madhivanan

Failing to plan is Planning to fail

wormz666
Posting Yak Master

110 Posts

Posted - 2008-10-07 : 06:53:45

its not null its value be pass by front end....
im having problem with the msgbox of the ms sql server 2000 to use as
my msgbox on my front end.....plzzzzzzzzzzzzzz help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-07 : 06:58:57

quote:
Originally posted by wormz666

its not null its value be pass by front end....
im having problem with the msgbox of the ms sql server 2000 to use as
my msgbox on my front end.....plzzzzzzzzzzzzzz help

Can you post the VB code?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 07:05:56

quote:
Originally posted by wormz666

its not null its value be pass by front end....
im having problem with the msgbox of the ms sql server 2000 to use as
my msgbox on my front end.....plzzzzzzzzzzzzzz help

sql server does not have msgbox. the problem is with value you're passing from your front end msgbox i guess. how are you passing value in your front end?

wormz666
Posting Yak Master

110 Posts

Posted - 2008-10-07 : 21:14:48

Set cm = New ADODB.Connection
With cm
.ActiveConnection = cn
.CommandType = adCmdStoredProc
.CommandText = "sp_insert"
.Parameters.Append .CreateParameter("@posno", adVarChar, adParamInput, 10, strvalue1)
.Parameters.Append .CreateParameter("@descript", adVarChar, adParamInput, 50, strvalue2)
.Execute
End With
Set cm = Nothing

wormz666
Posting Yak Master

110 Posts

Posted - 2008-10-07 : 21:24:35

when i pass a value that does not duplicate it will not debug...........
but when i pass a value that will duplicate....it will debug.....
my problem is on the database.....

please help me solve this problem..........thank you in advance..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 23:37:07

what do you mean by debug? debug application or debug procedure?

wormz666
Posting Yak Master

110 Posts

Posted - 2008-10-08 : 01:20:15

debug on stored procedure......................

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 01:24:59

what were values you passed when it worked correctly? what do you mean by values that duplicate? do you mean those which returns more than 1 record from table?

wormz666
Posting Yak Master

110 Posts

Posted - 2008-10-08 : 01:29:41

yes, but more than 0 values.... so that the data on the table will not have a duplicate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 01:33:52

quote:
Originally posted by wormz666

yes, but more than 0 values.... so that the data on the table will not have a duplicate

sorry you're not making much sense here. more than 0 values? can you illustrate your problem with some sample data as given in below link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources