here is the sql script to create the database, at the end is the procedure
im using.
i very much appreciate the help...
bryan
-----------------------------------------------------
CREATE TABLE BCustomerCA (
username char(19) NOT NULL,
fullname varchar(27) NULL,
credit_card_nbr char(18) NULL,
password char(7) NULL,
credit_card_exp iry_date datetime NULL,
mailing__addres s varchar(100) NULL,
phone_nbr char(14) NULL,
email_address char(50) NULL,
login_status bit NULL,
credit_card_nam e char(27) NULL
)
go
ALTER TABLE BCustomerCA
ADD PRIMARY KEY NONCLUSTERED (username)
go
CREATE TABLE BManufacturerCA (
manufacturer_id char(14) NOT NULL,
manufacturer_na me varchar(50) NULL,
email_address char(50) NULL,
contact_name varchar(27) NULL,
phone_nbr char(14) NULL
)
go
ALTER TABLE BManufacturerCA
ADD PRIMARY KEY NONCLUSTERED (manufacturer_i d)
go
CREATE TABLE BOrderItemCA (
part_id char(18) NOT NULL,
po_nbr char(18) NOT NULL,
item_qty int NULL
)
go
ALTER TABLE BOrderItemCA
ADD PRIMARY KEY NONCLUSTERED (part_id, po_nbr)
go
CREATE TABLE BPartCA (
part_id char(18) NOT NULL,
part_name varchar(50) NULL,
stock_size int NULL,
manufacturer_id char(14) NULL,
part_price decimal(10,2) NULL
)
go
ALTER TABLE BPartCA
ADD PRIMARY KEY NONCLUSTERED (part_id)
go
CREATE TABLE BPurchaseOrderC A (
po_nbr char(18) NOT NULL,
po_timetable datetime NULL,
isprocessed bit NULL,
username char(19) NULL
)
go
ALTER TABLE BPurchaseOrderC A
ADD PRIMARY KEY NONCLUSTERED (po_nbr)
go
CREATE TABLE BStaffCA (
username char(19) NOT NULL,
password char(7) NULL,
login_status bit NULL,
fullname varchar(27) NULL
)
go
ALTER TABLE BStaffCA
ADD PRIMARY KEY NONCLUSTERED (username)
go
ALTER TABLE BOrderItemCA
ADD FOREIGN KEY (po_nbr)
REFERENCES BPurchaseOrderC A
go
ALTER TABLE BOrderItemCA
ADD FOREIGN KEY (part_id)
REFERENCES BPartCA
go
ALTER TABLE BPartCA
ADD FOREIGN KEY (manufacturer_i d)
REFERENCES BManufacturerCA
go
ALTER TABLE BPurchaseOrderC A
ADD FOREIGN KEY (username)
REFERENCES BCustomerCA
go
create trigger tD_BCustomerCA on BCustomerCA for DELETE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* DELETE trigger on BCustomerCA */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BCustomerCA R/5 BPurchaseOrderC A ON PARENT DELETE SET NULL */
update BPurchaseOrderC A
set
/* BPurchaseOrderC A.username = NULL */
BPurchaseOrderC A.username = NULL
from BPurchaseOrderC A,deleted
where
/* BPurchaseOrderC A.username = deleted.usernam e */
BPurchaseOrderC A.username = deleted.usernam e
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_BCustomerCA on BCustomerCA for UPDATE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* UPDATE trigger on BCustomerCA */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insusername char(19),
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BCustomerCA R/5 BPurchaseOrderC A ON PARENT UPDATE SET NULL */
if
/* update(username ) */
update(username )
begin
update BPurchaseOrderC A
set
/* BPurchaseOrderC A.username = NULL */
BPurchaseOrderC A.username = NULL
from BPurchaseOrderC A,deleted
where
/* BPurchaseOrderC A.username = deleted.usernam e */
BPurchaseOrderC A.username = deleted.usernam e
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tD_BManufacture rCA on BManufacturerCA for DELETE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* DELETE trigger on BManufacturerCA */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BManufacturerCA R/4 BPartCA ON PARENT DELETE SET NULL */
update BPartCA
set
/* BPartCA.manufac turer_id = NULL */
BPartCA.manufac turer_id = NULL
from BPartCA,deleted
where
/* BPartCA.manufac turer_id = deleted.manufac turer_id */
BPartCA.manufac turer_id = deleted.manufac turer_id
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_BManufacture rCA on BManufacturerCA for UPDATE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* UPDATE trigger on BManufacturerCA */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insmanufacture r_id char(14),
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BManufacturerCA R/4 BPartCA ON PARENT UPDATE SET NULL */
if
/* update(manufact urer_id) */
update(manufact urer_id)
begin
update BPartCA
set
/* BPartCA.manufac turer_id = NULL */
BPartCA.manufac turer_id = NULL
from BPartCA,deleted
where
/* BPartCA.manufac turer_id = deleted.manufac turer_id */
BPartCA.manufac turer_id = deleted.manufac turer_id
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tI_BOrderItemCA on BOrderItemCA for INSERT as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* INSERT trigger on BOrderItemCA */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BPurchaseOrderC A R/3 BOrderItemCA ON CHILD INSERT RESTRICT */
if
/* update(po_nbr) */
update(po_nbr)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,BPurch aseOrderCA
where
/* inserted.po_nbr = BPurchaseOrderC A.po_nbr */
inserted.po_nbr = BPurchaseOrderC A.po_nbr
/* */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30002,
@errmsg = 'Cannot INSERT BOrderItemCA because BPurchaseOrderC A
does not exist.'
goto error
end
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BPartCA R/2 BOrderItemCA ON CHILD INSERT RESTRICT */
if
/* update(part_id) */
update(part_id)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,BPartC A
where
/* inserted.part_i d = BPartCA.part_id */
inserted.part_i d = BPartCA.part_id
/* */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30002,
@errmsg = 'Cannot INSERT BOrderItemCA because BPartCA does not
exist.'
goto error
end
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_BOrderItemCA on BOrderItemCA for UPDATE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* UPDATE trigger on BOrderItemCA */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@inspart_id char(18),
@inspo_nbr char(18),
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BPurchaseOrderC A R/3 BOrderItemCA ON CHILD UPDATE RESTRICT */
if
/* update(po_nbr) */
update(po_nbr)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,BPurch aseOrderCA
where
/* inserted.po_nbr = BPurchaseOrderC A.po_nbr */
inserted.po_nbr = BPurchaseOrderC A.po_nbr
/* */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30007,
@errmsg = 'Cannot UPDATE BOrderItemCA because BPurchaseOrderC A
does not exist.'
goto error
end
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BPartCA R/2 BOrderItemCA ON CHILD UPDATE RESTRICT */
if
/* update(part_id) */
update(part_id)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,BPartC A
where
/* inserted.part_i d = BPartCA.part_id */
inserted.part_i d = BPartCA.part_id
/* */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30007,
@errmsg = 'Cannot UPDATE BOrderItemCA because BPartCA does not
exist.'
goto error
end
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tD_BPartCA on BPartCA for DELETE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* DELETE trigger on BPartCA */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BPartCA R/2 BOrderItemCA ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,BOrderI temCA
where
/* BOrderItemCA.pa rt_id = deleted.part_id */
BOrderItemCA.pa rt_id = deleted.part_id
)
begin
select @errno = 30001,
@errmsg = 'Cannot DELETE BPartCA because BOrderItemCA exists.'
goto error
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tI_BPartCA on BPartCA for INSERT as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* INSERT trigger on BPartCA */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BManufacturerCA R/4 BPartCA ON CHILD INSERT SET NULL */
if
/* update(manufact urer_id) */
update(manufact urer_id)
begin
update BPartCA
set
/* BPartCA.manufac turer_id = NULL */
BPartCA.manufac turer_id = NULL
from BPartCA,inserte d
where
/* */
not exists (
select * from BManufacturerCA
where
/* inserted.manufa cturer_id = BManufacturerCA .manufacturer_i d */
inserted.manufa cturer_id = BManufacturerCA .manufacturer_i d
)
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_BPartCA on BPartCA for UPDATE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* UPDATE trigger on BPartCA */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@inspart_id char(18),
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BPartCA R/2 BOrderItemCA ON PARENT UPDATE RESTRICT */
if
/* update(part_id) */
update(part_id)
begin
if exists (
select * from deleted,BOrderI temCA
where
/* BOrderItemCA.pa rt_id = deleted.part_id */
BOrderItemCA.pa rt_id = deleted.part_id
)
begin
select @errno = 30005,
@errmsg = 'Cannot UPDATE BPartCA because BOrderItemCA exists.'
goto error
end
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BManufacturerCA R/4 BPartCA ON CHILD UPDATE SET NULL */
if
/* update(manufact urer_id) */
update(manufact urer_id)
begin
update BPartCA
set
/* BPartCA.manufac turer_id = NULL */
BPartCA.manufac turer_id = NULL
from BPartCA,inserte d
where
/* */
not exists (
select * from BManufacturerCA
where
/* inserted.manufa cturer_id = BManufacturerCA .manufacturer_i d */
inserted.manufa cturer_id = BManufacturerCA .manufacturer_i d
)
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tD_BPurchaseOrd erCA on BPurchaseOrderC A for DELETE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* DELETE trigger on BPurchaseOrderC A */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BPurchaseOrderC A R/3 BOrderItemCA ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,BOrderI temCA
where
/* BOrderItemCA.po _nbr = deleted.po_nbr */
BOrderItemCA.po _nbr = deleted.po_nbr
)
begin
select @errno = 30001,
@errmsg = 'Cannot DELETE BPurchaseOrderC A because BOrderItemCA
exists.'
goto error
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tI_BPurchaseOrd erCA on BPurchaseOrderC A for INSERT as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* INSERT trigger on BPurchaseOrderC A */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BCustomerCA R/5 BPurchaseOrderC A ON CHILD INSERT SET NULL */
if
/* update(username ) */
update(username )
begin
update BPurchaseOrderC A
set
/* BPurchaseOrderC A.username = NULL */
BPurchaseOrderC A.username = NULL
from BPurchaseOrderC A,inserted
where
/* */
not exists (
select * from BCustomerCA
where
/* inserted.userna me = BCustomerCA.use rname */
inserted.userna me = BCustomerCA.use rname
)
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_BPurchaseOrd erCA on BPurchaseOrderC A for UPDATE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* UPDATE trigger on BPurchaseOrderC A */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@inspo_nbr char(18),
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BPurchaseOrderC A R/3 BOrderItemCA ON PARENT UPDATE RESTRICT */
if
/* update(po_nbr) */
update(po_nbr)
begin
if exists (
select * from deleted,BOrderI temCA
where
/* BOrderItemCA.po _nbr = deleted.po_nbr */
BOrderItemCA.po _nbr = deleted.po_nbr
)
begin
select @errno = 30005,
@errmsg = 'Cannot UPDATE BPurchaseOrderC A because BOrderItemCA
exists.'
goto error
end
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BCustomerCA R/5 BPurchaseOrderC A ON CHILD UPDATE SET NULL */
if
/* update(username ) */
update(username )
begin
update BPurchaseOrderC A
set
/* BPurchaseOrderC A.username = NULL */
BPurchaseOrderC A.username = NULL
from BPurchaseOrderC A,inserted
where
/* */
not exists (
select * from BCustomerCA
where
/* inserted.userna me = BCustomerCA.use rname */
inserted.userna me = BCustomerCA.use rname
)
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
SET QUOTED_IDENTIFI ER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE addCustomerSQL
(
@username char(19),
@fullname varchar(27),
@password char(7),
@credit_card_nb r char(18),
@credit_card_ex piry_date datetime,
@mailing_addres s varchar(100),
@phone_nbr char(14),
@email_address char(50),
@login_status bit,
@credit_card_na me char(27)
)
AS
INSERT INTO BCustomerCA VALUES (@username , @fullname, @password,
@credit_card_nb r, @credit_card_ex piry_date, @mailing_addres s, @phone_nbr,
@email_address, @login_status, @credit_card_na me )
GO
SET QUOTED_IDENTIFI ER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------------------------------
"William Ryan eMVP" <bi**@NoSp4m.de vbuzz.com> wrote in message
news:Or******** *******@TK2MSFT NGP12.phx.gbl.. .
Hmm, do you have any binary fields in the DB? I'm hesitant to admit this,
but I had this problem once and I realized I had defined a Varchar field
to VarBinary and that was ultimately the cause.
Can you post the table script and the SQL Statement you are using? I'll
be glad to create a table and give it a try...
Bill
"Bryan" <ca**********@r ogers.com> wrote in message
news:Ge******** ************@ne ws04.bloor.is.n et.cable.rogers .com... I noticed that also, yet the datatypes are teh same in sql and in teh
vb code. this is driving me nuts.
thanks again.
bry
"William Ryan eMVP" <bi**@NoSp4m.de vbuzz.com> wrote in message
news:eA******** ******@TK2MSFTN GP11.phx.gbl... Bryan:
What is the datatype of the field in SqlServer? Bit or Binary? The Param type is specified as Bit but by the Exception message, I'm wondering
if it's not Binary in the DB. If so, I believe changing the db type in the client code to SqlDbType.Binar y or changing the data type server side to Bit may do it for you.
HTH,
Bill
"Bryan" <ca**********@r ogers.com> wrote in message
news:5y******** ************@ne ws04.bloor.is.n et.cable.rogers .com...
> hello all...
>
> im trying to add a record to an sql db on ms sql server 2000, using
vb.net.
>
> seems to be working.. except for one thing, one of the columns in
the > database is a bit datatype, and though i get no syntax errors when
> compiling, i get an error indicated that the data would be
truncated. the > field is login_status.
>
> ive tried in quotes and not, giving it an integer variable with the
number 1
> in it, true/false statements.. ive tried a bunch of stuff... has me real > stumped..
>
> have any ideas?
>
> thanks
>
>
> here is the error it spits out.
>
> <b>* error while uploading original data</b>.<br />String or binary data > would be truncated.The statement has been terminated..Net SqlClient Data > Provider
>
>
>
>
-------------------------------------------------------------------------- > here is the code:
-------------------------------------------------------------------------- > Imports System.Data
>
> Imports System.Data.Sql Client
>
>
>
>
>
> Public Class WebForm1
>
> Inherits System.Web.UI.P age
>
>
>
>
>
>
>
>
>
>
> Public Sub Register_User(B yVal sender As Object, ByVal E As
EventArgs) >
> Dim dbConnStr As String = "user id=**(blanked for
> security)***;pa ssword=**(blank ed for
> security)**;dat abase=BCdb;serv er=localhost"
>
> Dim dbConn As New System.Data.Sql Client.SqlConne ction(dbConnStr )
>
>
>
>
>
>
>
> Try
>
> Dim cmd As New SqlCommand("add CustomerSQL", dbConn)
>
> cmd.CommandType = CommandType.Sto redProcedure
>
> cmd.Parameters. Add(New SqlParameter("@ username", SqlDbType.Char,
19)) >
> cmd.Parameters. Add(New SqlParameter("@ fullname", SqlDbType.VarCh ar,
27)) >
> cmd.Parameters. Add(New SqlParameter("@ password", SqlDbType.Char, 7))
>
> cmd.Parameters. Add(New SqlParameter("@ credit_card_nbr ", SqlDbType.Char, 18))
>
> cmd.Parameters. Add(New SqlParameter("@ credit_card_exp iry_date",
> SqlDbType.DateT ime))
>
> cmd.Parameters. Add(New SqlParameter("@ mailing_address ",
SqlDbType.VarCh ar, > 100))
>
> cmd.Parameters. Add(New SqlParameter("@ phone_nbr", SqlDbType.Char,
14)) >
> cmd.Parameters. Add(New SqlParameter("@ email_address",
SqlDbType.Char, 50)) >
> cmd.Parameters. Add(New SqlParameter("@ login_status", SqlDbType.Bit))
>
> cmd.Parameters. Add(New SqlParameter("@ credit_card_nam e", SqlDbType.Char, > 27))
>
> cmd.Parameters. Item("@username ").Value = RegUsrnameTXT.T ext
>
> cmd.Parameters. Item("@fullname ").Value = RegFullnameTXT. Text
>
> cmd.Parameters. Item("@password ").Value = RegPasswordTXT. Text
>
> cmd.Parameters. Item("@credit_c ard_nbr").Value =
RegCreditcardnu mberTXT.Text
>
> cmd.Parameters. Item("@credit_c ard_expiry_date ").Value =
> RegCreditcardex piryTXT.Text
>
> cmd.Parameters. Item("@mailing_ address").Value = RegAddressTXT.T ext
>
> cmd.Parameters. Item("@email_ad dress").Value = RegEmailTXT.Tex t
>
> cmd.Parameters. Item("@phone_nb r").Value = RegPhoneTXT.Tex t
>
> cmd.Parameters. Item("@login_st atus").Value = 1
>
> cmd.Parameters. Item("@credit_c ard_name").Valu e =
RegCreditcardna meTXT.Text >
> 'opent the connection to the database
>
> dbConn.Open()
>
> 'execute sql statement
>
> cmd.ExecuteNonQ uery()
>
> dbConn.Close()
>
> Catch objError As Exception
>
> dberror.Text = "<b>* error while uploading original data</b>.<br />"
& > objError.Messag e & objError.Source
>
> Exit Sub
>
> Finally
>
> If dbConn.State = ConnectionState .Open Then
>
> dbConn.Close()
>
> End If
>
> End Try
>
> End Sub
>
>
>
> End Class
>
-------------------------------------------------------------------------- --
> ---
>
>
>
