String or binary would be trancated.

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

SQL Server 2000 Forums

Transact-SQL (2000)

String or binary would be trancated.

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-03-16 : 14:52:09

I'm stuck at Inserting to SQL database from asp.net page. It gives me this error:

"System.Data.SqlClient.SqlException: String or binary data would be truncated. The statement has been terminated."

I can however entered the same information from query analyzer and do an insert it from there, but somehow I can't seem to insert it from my asp.net page.

I did do a maxlength on all my textboxes so that the length matches what is allowed for each field.

Here's my asp.net code for inserts:

Dim strConnect as String = connectionString

Dim strInsert As String = "INSERT INTO QUOTE " _
& "(ID, CUSTOMER_ID, NAME, ADDR_1, ADDR_2, ADDR_3, CITY, STATE, ZIPCODE, COUNTRY, " _
& "CONTACT_FIRST_NAME, CONTACT_LAST_NAME, CONTACT_INITIAL, CONTACT_POSITION, " _
& "CONTACT_HONORIFIC, CONTACT_SALUTATION, CONTACT_PHONE, CONTACT_FAX, SALESREP_ID, TERRITORY, " _
& "STATUS, WON_LOSS_DATE, WON_LOSS_REASON, QUOTE_DATE,EXPIRATION_DATE, FOLLOWUP_DATE, " _
& "EXPECTED_WIN_DATE, WIN_PROBABILITY, SHIP_VIA, FREE_ON_BOARD, " _
& "TERMS_NET_TYPE,TERMS_NET_DAYS, TERMS_NET_DATE, TERMS_DISC_DAYS, TERMS_DISC_TYPE, TERMS_DISC_DATE, " _
& "TERMS_DISC_PERCENT, TERMS_DESCRIPTION, FREIGHT_TERMS, CREATE_DATE, USER_ID, QUOTED_LEADTIME, CURRENCY_ID, " _
& "ENTITY_ID, DISCOUNT_CODE,PRINTED_DATE, USER_1, USER_2, USER_3, USER_4, USER_5, USER_6, USER_7, USER_8, " _
& "USER_9, USER_10, UDF_LAYOUT_ID) " _
& "VALUES ('" & txbQuoteID.Text & "', '" & txbCustNum.Text & "', '" & txbName.Text & "','" & txbAddress1.Text & "', '" & txbAddress2.Text & "', '" & txbAddress3.Text & "', '" & txbCity.Text & "', " _
& "'" & txbState.Text & "', '" & txbZipCode.Text & "', '" & txbCountry.Text & "', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, " _
& "'A', NULL, NULL, GetDate(), NULL, NULL, GetDate(), '1', NULL, NULL, " _
& "'A', NULL, NULL, NULL, 'A', NULL, NULL, NULL, 'B', GetDate(), " _
& "'" & strAuthUsr & "', NULL, 'USD', '" & strDatabase & "', NULL, NULL, NULL, NULL, NULL, NULL, " _
& "NULL, NULL, NULL, NULL, NULL, NULL, NULL) "

Dim myConnection As New SqlConnection(strConnect)
Dim myCommand As New SqlCommand(strInsert, myConnection
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

Can anyone advise on why I can insert the same data within query analyzer and not my asp page? Thanks in advance.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-16 : 15:01:27

I thought we had agreed that you'd start using stored procedures for stuff like this ? Why are you trying to build an INSERT statement on the fly. Jsut create a stored proc, which accepts one parameter per text field you are passing in, and THAT stored proc does the INSERT. then there's no problems with the length of the items or odd characters. and that's the whole idea of using stored procs.

- Jeff

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-03-16 : 15:35:01

Jeff..I know, I know. But I wanted to test it out and see how it will turn out before creating the stored procedure.

I will go ahead and use the stored procedure and see if I still have this problem.

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-16 : 16:14:19

OK, now I need a drink..that hurt...

WHY ARE YOU PUTTING NULLS IN THE VALUE LIST?




CREATE PROC mySproc99
	  @txbQuoteID int (whatever...need to define all datatypes)
	, @txbCustNum int (whatever...need to define all datatypes)
	, @txbName int (whatever...need to define all datatypes)
	, @txbAddress1 int (whatever...need to define all datatypes)
	, @txbAddress2 int (whatever...need to define all datatypes)
	, @txbAddress3 int (whatever...need to define all datatypes)
	, @txbCity int (whatever...need to define all datatypes)
	, @txbState int (whatever...need to define all datatypes) 
	, @txbZipCode int (whatever...need to define all datatypes)
	, @txbCountry int (whatever...need to define all datatypes)
	, @strAuthUsr int (whatever...need to define all datatypes)
	, @strDatabase int (whatever...need to define all datatypes)

AS

DECLARE @ERRO int, @ROWCOUNT int

INSERT INTO QUOTE (
	  [ID]
	, CUSTOMER_ID
	, [NAME]
	, ADDR_1
	, ADDR_2
	, ADDR_3
	, CITY
	, STATE
	, ZIPCODE
	, COUNTRY
--	, CONTACT_FIRST_NAME
--	, CONTACT_LAST_NAME
--	, CONTACT_INITIAL
--	, CONTACT_POSITION
--	, CONTACT_HONORIFIC
--	, CONTACT_SALUTATION
--	, CONTACT_PHONE
--	, CONTACT_FAX
--	, SALESREP_ID
--	, TERRITORY
	, STATUS
--	, WON_LOSS_DATE
--	, WON_LOSS_REASON
	, QUOTE_DATE
--	, EXPIRATION_DATE
--	, FOLLOWUP_DATE
	, EXPECTED_WIN_DATE
	, WIN_PROBABILITY
--	, SHIP_VIA
--	, FREE_ON_BOARD
	, TERMS_NET_TYPE
--	, TERMS_NET_DAYS
--	, TERMS_NET_DATE
--	, TERMS_DISC_DAYS
	, TERMS_DISC_TYPE
--	, TERMS_DISC_DATE
--	, TERMS_DISC_PERCENT
--	, TERMS_DESCRIPTION
	, FREIGHT_TERMS
  	, CREATE_DATE
	, [USER_ID]
--	, QUOTED_LEADTIME
	, CURRENCY_ID
	, ENTITY_ID
--	, DISCOUNT_CODE
--	, PRINTED_DATE
--	, USER_1
--	, USER_2
--	, USER_3
--	, USER_4
--	, USER_5
--	, USER_6
--	, USER_7
--	, USER_8
--	, USER_9
--	, USER_10
--	, UDF_LAYOUT_ID)
SELECT    @txbQuoteID
	, @txbCustNum
	, @txbName
	, @txbAddress1
	, @txbAddress2
	, @txbAddress3
	, @txbCity
	, @txbState
	, @txbZipCode
	, @txbCountry
--	, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
	, 'A'
--	, NULL, NULL
	, GetDate()
--	, NULL, NULL
	, GetDate()
	, '1'
--	, NULL, NULL
	, 'A'
--	, NULL, NULL, NULL
	, 'A'
--	, NULL, NULL, NULL
	, 'B'
	, GetDate()
	, @strAuthUsr
--	, NULL
	, 'USD'
	, @strDatabase
--	, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL


SELECT @ROWCOUNT = @@ROWCOUNT, @ERROR = @@ERROR

IF @ERROR <> 0
  BEGIN
	-- ERROR Handle and Raise error
  END

IF @ROWCOUNT = 0 
  BEGIN
	-- Buisness Error, somthing screwed up Handle and RAISERERROR
  END

RETURN
GO

Brett

8-)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-16 : 16:15:38

Shhhh...Don't tell Jeff I told you this but:

capture the asp call in sql profiler, paste the call into a QA window, and run it. You'll get the same error but you'll be able to more easily track down which value is longer than the column allows.

This didn't come from me...

Be One with the Optimizer
TG

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-03-16 : 18:11:12

Thanks for the tip TG.

WOW!!!

Brett. I didn't know you can do that and leave it Null. Thanks for the example. I will look into it. Appreciate the help.

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-03-17 : 09:06:08

Brett,

For the part Select below, is that supposed to be Value? I get an error at that section.

SELECT @txbQuoteID
, @txbCustNum
, @txbName
, @txbAddress1
, @txbAddress2
, @txbAddress3
, @txbCity
, @txbState
, @txbZipCode
, @txbCountry
-- , NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
, 'A'

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-03-17 : 09:15:14

I got it now. It's the paranthesis which got commented out.

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-03-17 : 09:50:36

Brett can you help me add a transaction to this. If successful then continue if not then rollback. I got the example below but something seems missing. I wanted to use the Transaction because I am also adding an Update query with this stored procedure. If successful then I want to update the table NEXT_GEN_NUMBER for QuoteID to the next number. So the update example is to add the textbox QuoteID and increment it by 1.

Thanks.

Example:

UPDATE NEXT_GEN_NUMBER
SET NEXT_NUM = @txbQuoteID + '1'
WHERE TABLE_NAME = 'QUOTE'

Example:

CREATE PROC SP_NEWQUOTE
@txbQuoteID VARCHAR(15)
, @txbCustNum VARCHAR(50)
, @txbName VARCHAR(50)
, @txbAddress1 VARCHAR(50)
, @txbAddress2 VARCHAR(50)
, @txbAddress3 VARCHAR(50)
, @txbCity VARCHAR(30)
, @txbState VARCHAR(10)
, @txbZipCode VARCHAR(10)
, @txbCountry VARCHAR(50)
, @strAuthUsr VARCHAR(20)
, @strDatabase VARCHAR(10)

AS

BEGIN TRAN

DECLARE @ERROR int, @ROWCOUNT int

INSERT INTO QUOTE (
[ID]
, CUSTOMER_ID
, [NAME]
, ADDR_1
, ADDR_2
, ADDR_3
, CITY
, STATE
, ZIPCODE
, COUNTRY
-- , CONTACT_FIRST_NAME
-- , CONTACT_LAST_NAME
-- , CONTACT_INITIAL
-- , CONTACT_POSITION
-- , CONTACT_HONORIFIC
-- , CONTACT_SALUTATION
-- , CONTACT_PHONE
-- , CONTACT_FAX
-- , SALESREP_ID
-- , TERRITORY
, STATUS
-- , WON_LOSS_DATE
-- , WON_LOSS_REASON
, QUOTE_DATE
-- , EXPIRATION_DATE
-- , FOLLOWUP_DATE
, EXPECTED_WIN_DATE
, WIN_PROBABILITY
-- , SHIP_VIA
-- , FREE_ON_BOARD
, TERMS_NET_TYPE
-- , TERMS_NET_DAYS
-- , TERMS_NET_DATE
-- , TERMS_DISC_DAYS
, TERMS_DISC_TYPE
-- , TERMS_DISC_DATE
-- , TERMS_DISC_PERCENT
-- , TERMS_DESCRIPTION
, FREIGHT_TERMS
, CREATE_DATE
, [USER_ID]
-- , QUOTED_LEADTIME
, CURRENCY_ID
, ENTITY_ID
-- , DISCOUNT_CODE
-- , PRINTED_DATE
-- , USER_1
-- , USER_2
-- , USER_3
-- , USER_4
-- , USER_5
-- , USER_6
-- , USER_7
-- , USER_8
-- , USER_9
-- , USER_10
-- , UDF_LAYOUT_ID
)
SELECT @txbQuoteID
, @txbCustNum
, @txbName
, @txbAddress1
, @txbAddress2
, @txbAddress3
, @txbCity
, @txbState
, @txbZipCode
, @txbCountry
-- , NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
, 'A'
-- , NULL, NULL
, GetDate()
-- , NULL, NULL
, GetDate()
, '1'
-- , NULL, NULL
, 'A'
-- , NULL, NULL, NULL
, 'A'
-- , NULL, NULL, NULL
, 'B'
, GetDate()
, @strAuthUsr
-- , NULL
, 'USD'
, @strDatabase
-- , NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL

UPDATE NEXT_GEN_NUMBER
SET NEXT_NUM = @txbQuoteID + '1'
WHERE TABLE_NAME = 'QUOTE'

SELECT @ROWCOUNT = @@ROWCOUNT, @ERROR = @@ERROR

IF @ERROR <> 0
BEGIN
ROLLBACK TRAN
Return 10
END

IF @ROWCOUNT = 0
BEGIN
ROLLBACK TRAN
Return 11
END

RETURN

COMMIT TRAN

GO

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-17 : 10:11:48

First I would leave The return value alone.

If you want to check for a specific error in the code use an output variable.

And you want the whole unit of work to commit right? If not Rollback?

Anyway, try and use this model

http://weblogs.sqlteam.com/brettk/archive/2004/05/25/1378.aspx

For builing your sprocs...it'll take care of 99% of your error handlind needs...not to mention not having to type anymore

Brett

8-)

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-03-17 : 11:00:35

Thanks for the documentation. I will look at that for example.

Yes I want the entire work to commit and if any failed then rollback for both the insert and update.

With the example you provided, I'm getting this error.

System.Data.SqlClient.SqlException: Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

Don't I just use a Begin Transaction and Commit at the beginning and end?

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-17 : 13:32:23

You have an uncommited transaction mostl likely from a previous error.

Look at SELECT @@TRANCOUNT

And perform another ROLLBACK TRAN

Now, if you don't start using QA soon, your life will become hell

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-17 : 13:47:57

>>SET NEXT_NUM = @txbQuoteID + '1'

why is @txbQuoteID a varchar? you are concatenating a string value of '1' to the previous value, not adding the number 1 to the previous value mathematically.

- Jeff

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-03-17 : 15:08:05

Jeff,

I'm just grabbing whatever the next ID number is and trying to add a one to it and update the NEXT_GEN_NUMBER table. So do I need to set that as in Int instead of a varchar?

Brett,

I don't see where you are referring to. Being a newbie with SQL, I don't know where I would do another rollback.

Look at SELECT @@TRANCOUNT

And perform another ROLLBACK TRAN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources