adding record to sql db

  • Thread starter Thread starter Bryan
  • Start date Start date
B

Bryan

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.SqlClient





Public Class WebForm1

Inherits System.Web.UI.Page










Public Sub Register_User(ByVal sender As Object, ByVal E As EventArgs)

Dim dbConnStr As String = "user id=**(blanked for
security)***;password=**(blanked for
security)**;database=BCdb;server=localhost"

Dim dbConn As New System.Data.SqlClient.SqlConnection(dbConnStr)







Try

Dim cmd As New SqlCommand("addCustomerSQL", dbConn)

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add(New SqlParameter("@username", SqlDbType.Char, 19))

cmd.Parameters.Add(New SqlParameter("@fullname", SqlDbType.VarChar, 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_expiry_date",
SqlDbType.DateTime))

cmd.Parameters.Add(New SqlParameter("@mailing_address", SqlDbType.VarChar,
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_name", SqlDbType.Char,
27))

cmd.Parameters.Item("@username").Value = RegUsrnameTXT.Text

cmd.Parameters.Item("@fullname").Value = RegFullnameTXT.Text

cmd.Parameters.Item("@password").Value = RegPasswordTXT.Text

cmd.Parameters.Item("@credit_card_nbr").Value = RegCreditcardnumberTXT.Text

cmd.Parameters.Item("@credit_card_expiry_date").Value =
RegCreditcardexpiryTXT.Text

cmd.Parameters.Item("@mailing_address").Value = RegAddressTXT.Text

cmd.Parameters.Item("@email_address").Value = RegEmailTXT.Text

cmd.Parameters.Item("@phone_nbr").Value = RegPhoneTXT.Text

cmd.Parameters.Item("@login_status").Value = 1

cmd.Parameters.Item("@credit_card_name").Value = RegCreditcardnameTXT.Text

'opent the connection to the database

dbConn.Open()

'execute sql statement

cmd.ExecuteNonQuery()

dbConn.Close()

Catch objError As Exception

dberror.Text = "<b>* error while uploading original data</b>.<br />" &
objError.Message & objError.Source

Exit Sub

Finally

If dbConn.State = ConnectionState.Open Then

dbConn.Close()

End If

End Try

End Sub



End Class
 
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.Binary or changing the data type server side to Bit may do
it for you.

HTH,

Bill
 
yes i tried with and without quotes.
i even tried creating an integer variable, assigning it the value 1 and
putting that variable in its place.. no go either.

thanks for the response,, have any other ideas?

cheers
bry
 
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
 
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
 
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_expiry_date datetime NULL,
mailing__address varchar(100) NULL,
phone_nbr char(14) NULL,
email_address char(50) NULL,
login_status bit NULL,
credit_card_name char(27) NULL
)
go


ALTER TABLE BCustomerCA
ADD PRIMARY KEY NONCLUSTERED (username)
go


CREATE TABLE BManufacturerCA (
manufacturer_id char(14) NOT NULL,
manufacturer_name 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_id)
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 BPurchaseOrderCA (
po_nbr char(18) NOT NULL,
po_timetable datetime NULL,
isprocessed bit NULL,
username char(19) NULL
)
go


ALTER TABLE BPurchaseOrderCA
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 BPurchaseOrderCA
go


ALTER TABLE BOrderItemCA
ADD FOREIGN KEY (part_id)
REFERENCES BPartCA
go


ALTER TABLE BPartCA
ADD FOREIGN KEY (manufacturer_id)
REFERENCES BManufacturerCA
go


ALTER TABLE BPurchaseOrderCA
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 BPurchaseOrderCA ON PARENT DELETE SET NULL */
update BPurchaseOrderCA
set
/* BPurchaseOrderCA.username = NULL */
BPurchaseOrderCA.username = NULL
from BPurchaseOrderCA,deleted
where
/* BPurchaseOrderCA.username = deleted.username */
BPurchaseOrderCA.username = deleted.username


/* 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 BPurchaseOrderCA ON PARENT UPDATE SET NULL */
if
/* update(username) */
update(username)
begin
update BPurchaseOrderCA
set
/* BPurchaseOrderCA.username = NULL */
BPurchaseOrderCA.username = NULL
from BPurchaseOrderCA,deleted
where
/* BPurchaseOrderCA.username = deleted.username */
BPurchaseOrderCA.username = deleted.username
end


/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go

create trigger tD_BManufacturerCA 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.manufacturer_id = NULL */
BPartCA.manufacturer_id = NULL
from BPartCA,deleted
where
/* BPartCA.manufacturer_id = deleted.manufacturer_id */
BPartCA.manufacturer_id = deleted.manufacturer_id


/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go

create trigger tU_BManufacturerCA 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,
@insmanufacturer_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(manufacturer_id) */
update(manufacturer_id)
begin
update BPartCA
set
/* BPartCA.manufacturer_id = NULL */
BPartCA.manufacturer_id = NULL
from BPartCA,deleted
where
/* BPartCA.manufacturer_id = deleted.manufacturer_id */
BPartCA.manufacturer_id = deleted.manufacturer_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 */
/* BPurchaseOrderCA R/3 BOrderItemCA ON CHILD INSERT RESTRICT */
if
/* update(po_nbr) */
update(po_nbr)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,BPurchaseOrderCA
where
/* inserted.po_nbr = BPurchaseOrderCA.po_nbr */
inserted.po_nbr = BPurchaseOrderCA.po_nbr
/* */

if @validcnt + @nullcnt != @numrows
begin
select @errno = 30002,
@errmsg = 'Cannot INSERT BOrderItemCA because BPurchaseOrderCA
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,BPartCA
where
/* inserted.part_id = BPartCA.part_id */
inserted.part_id = 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 */
/* BPurchaseOrderCA R/3 BOrderItemCA ON CHILD UPDATE RESTRICT */
if
/* update(po_nbr) */
update(po_nbr)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,BPurchaseOrderCA
where
/* inserted.po_nbr = BPurchaseOrderCA.po_nbr */
inserted.po_nbr = BPurchaseOrderCA.po_nbr
/* */

if @validcnt + @nullcnt != @numrows
begin
select @errno = 30007,
@errmsg = 'Cannot UPDATE BOrderItemCA because BPurchaseOrderCA
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,BPartCA
where
/* inserted.part_id = BPartCA.part_id */
inserted.part_id = 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,BOrderItemCA
where
/* BOrderItemCA.part_id = deleted.part_id */
BOrderItemCA.part_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(manufacturer_id) */
update(manufacturer_id)
begin
update BPartCA
set
/* BPartCA.manufacturer_id = NULL */
BPartCA.manufacturer_id = NULL
from BPartCA,inserted
where
/* */

not exists (
select * from BManufacturerCA
where
/* inserted.manufacturer_id = BManufacturerCA.manufacturer_id */
inserted.manufacturer_id = BManufacturerCA.manufacturer_id
)
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,BOrderItemCA
where
/* BOrderItemCA.part_id = deleted.part_id */
BOrderItemCA.part_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(manufacturer_id) */
update(manufacturer_id)
begin
update BPartCA
set
/* BPartCA.manufacturer_id = NULL */
BPartCA.manufacturer_id = NULL
from BPartCA,inserted
where
/* */

not exists (
select * from BManufacturerCA
where
/* inserted.manufacturer_id = BManufacturerCA.manufacturer_id */
inserted.manufacturer_id = BManufacturerCA.manufacturer_id
)
end


/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go

create trigger tD_BPurchaseOrderCA on BPurchaseOrderCA for DELETE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* DELETE trigger on BPurchaseOrderCA */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BPurchaseOrderCA R/3 BOrderItemCA ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,BOrderItemCA
where
/* BOrderItemCA.po_nbr = deleted.po_nbr */
BOrderItemCA.po_nbr = deleted.po_nbr
)
begin
select @errno = 30001,
@errmsg = 'Cannot DELETE BPurchaseOrderCA 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_BPurchaseOrderCA on BPurchaseOrderCA for INSERT as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* INSERT trigger on BPurchaseOrderCA */
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 BPurchaseOrderCA ON CHILD INSERT SET NULL */
if
/* update(username) */
update(username)
begin
update BPurchaseOrderCA
set
/* BPurchaseOrderCA.username = NULL */
BPurchaseOrderCA.username = NULL
from BPurchaseOrderCA,inserted
where
/* */

not exists (
select * from BCustomerCA
where
/* inserted.username = BCustomerCA.username */
inserted.username = BCustomerCA.username
)
end


/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go

create trigger tU_BPurchaseOrderCA on BPurchaseOrderCA for UPDATE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* UPDATE trigger on BPurchaseOrderCA */
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 */
/* BPurchaseOrderCA R/3 BOrderItemCA ON PARENT UPDATE RESTRICT */
if
/* update(po_nbr) */
update(po_nbr)
begin
if exists (
select * from deleted,BOrderItemCA
where
/* BOrderItemCA.po_nbr = deleted.po_nbr */
BOrderItemCA.po_nbr = deleted.po_nbr
)
begin
select @errno = 30005,
@errmsg = 'Cannot UPDATE BPurchaseOrderCA because BOrderItemCA
exists.'
goto error
end
end

/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BCustomerCA R/5 BPurchaseOrderCA ON CHILD UPDATE SET NULL */
if
/* update(username) */
update(username)
begin
update BPurchaseOrderCA
set
/* BPurchaseOrderCA.username = NULL */
BPurchaseOrderCA.username = NULL
from BPurchaseOrderCA,inserted
where
/* */

not exists (
select * from BCustomerCA
where
/* inserted.username = BCustomerCA.username */
inserted.username = BCustomerCA.username
)
end


/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go


SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE addCustomerSQL
(

@username char(19),
@fullname varchar(27),
@password char(7),
@credit_card_nbr char(18),
@credit_card_expiry_date datetime,
@mailing_address varchar(100),
@phone_nbr char(14),
@email_address char(50),
@login_status bit,
@credit_card_name char(27)

)
AS
INSERT INTO BCustomerCA VALUES (@username , @fullname, @password,
@credit_card_nbr, @credit_card_expiry_date, @mailing_address, @phone_nbr,
@email_address, @login_status, @credit_card_name )
GO

SET QUOTED_IDENTIFIER OFF
GO

SET ANSI_NULLS ON
GO
 
Ok, if you write a Debug.Assert for each line ie asserting that the lenght
of textboxWhatever.text.Length <= FieldSize...see what happens. is there
any chance that your inputs are longer than the field size?

If you don't want to write out all of the Assertions, just use a
Debug.WriteLine(textBoxWhatever.text.Length.ToString()) for each of the text
box lenghts and then compare them to the allowed field size.

I've done this directly with the parent table (excluding triggers and
children ) and I cna get an insert to work but I've used data I knew would
be smaller than field size.

Let me play with it some more though and hopefully I can narrow it down.
 
Im new to asp and have a little experience with java, so some concepts are
foreign to me. from what i understand of your last post, some of the fields
being sent are too long? is that not avoided by the following statement?

cmd.Parameters.Add(New SqlParameter("@username", SqlDbType.Char, 19))

also, could the number "1" that im sending be sent in as a character as
opposed to a bit? if im correct a character one would be 8 bits. could this
be what is happening?

eternally gratefull,
bryan
 
Bryan,

Try Convert.ToBoolean(1) on your assignment. If the error is being caused
by the bit field I assume it is because an integer value 1 won't fit.

Tom
 
tried that.. really wished it worked.. gave me this.

<b>* error while uploading original data</b>.<br />String or binary data
would be truncated.The statement has been terminated..Net SqlClient Data
Provider
 
Bryan,

Are you quite certain it is the bit column that is generating the error?
Create a sample that only tries to update that column.
 
i managed a work around.

to avoid the bit problems, i redesignated it as an integer in the asp.net
code, sent the integer "1" and upon recieving it, sql appropriately enters
it as a bit "1" as opposed to recieving some incorrectly formatted bit.

thank you for all your help, it is much appreciated.

bryan
 
Bryan said:
hello all...

im trying to add a record to an sql db on ms sql server 2000, using vb.net.
Click to expand...

Bryan,

I noticed two things. First you are allowing null values in the bit
field, this is bad form as well as bad logic. Second, the next line
(from your code) seems to be missing a value:

cmd.Parameters.Add(New SqlParameter("@login_status", SqlDbType.Bit))

HTH,

Charlie
 
Back
Top