By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,905 Members | 902 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,905 IT Pros & Developers. It's quick & easy.

adding record to sql db

P: n/a
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").Va lue =
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

----------------------------------------------------------------------------
---

Nov 20 '05 #1
Share this Question
Share on Google+
20 Replies


P: n/a
did you try to just use the integer 1 or 0 with no quotes?
"Bryan" <ca**********@rogers.com> wrote in message
news:5y********************@news04.bloor.is.net.ca ble.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.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").Va lue =
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

-------------------------------------------------------------------------- -- ---

Nov 20 '05 #2

P: n/a
did you try to just use the integer 1 or 0 with no quotes?
"Bryan" <ca**********@rogers.com> wrote in message
news:5y********************@news04.bloor.is.net.ca ble.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.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").Va lue =
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

-------------------------------------------------------------------------- -- ---

Nov 20 '05 #3

P: n/a
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
"Bryan" <ca**********@rogers.com> wrote in message
news:5y********************@news04.bloor.is.net.ca ble.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.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").Va lue =
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

-------------------------------------------------------------------------- -- ---

Nov 20 '05 #4

P: n/a
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
"Bryan" <ca**********@rogers.com> wrote in message
news:5y********************@news04.bloor.is.net.ca ble.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.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").Va lue =
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

-------------------------------------------------------------------------- -- ---

Nov 20 '05 #5

P: n/a
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

"Brian Henry" <brianiup[nospam]@adelphia.net> wrote in message
news:O9**************@TK2MSFTNGP09.phx.gbl...
did you try to just use the integer 1 or 0 with no quotes?
"Bryan" <ca**********@rogers.com> wrote in message
news:5y********************@news04.bloor.is.net.ca ble.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.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").Va lue =
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


--------------------------------------------------------------------------
--
---


Nov 20 '05 #6

P: n/a
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

"Brian Henry" <brianiup[nospam]@adelphia.net> wrote in message
news:O9**************@TK2MSFTNGP09.phx.gbl...
did you try to just use the integer 1 or 0 with no quotes?
"Bryan" <ca**********@rogers.com> wrote in message
news:5y********************@news04.bloor.is.net.ca ble.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.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").Va lue =
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


--------------------------------------------------------------------------
--
---


Nov 20 '05 #7

P: n/a
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.devbuzz.com> wrote in message
news:eA**************@TK2MSFTNGP11.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.Binary or changing the data type server side to Bit may do it for you.

HTH,

Bill
"Bryan" <ca**********@rogers.com> wrote in message
news:5y********************@news04.bloor.is.net.ca ble.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.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").Va lue =
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


--------------------------------------------------------------------------
--
---


Nov 20 '05 #8

P: n/a
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.devbuzz.com> wrote in message
news:eA**************@TK2MSFTNGP11.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.Binary or changing the data type server side to Bit may do it for you.

HTH,

Bill
"Bryan" <ca**********@rogers.com> wrote in message
news:5y********************@news04.bloor.is.net.ca ble.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.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").Va lue =
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


--------------------------------------------------------------------------
--
---


Nov 20 '05 #9

P: n/a
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**********@rogers.com> wrote in message
news:Ge********************@news04.bloor.is.net.ca ble.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.devbuzz.com> wrote in message
news:eA**************@TK2MSFTNGP11.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.Binary or changing the data type server side to Bit may do
it for you.

HTH,

Bill
"Bryan" <ca**********@rogers.com> wrote in message
news:5y********************@news04.bloor.is.net.ca ble.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.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").Va lue =
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


--------------------------------------------------------------------------
--
---



Nov 20 '05 #10

P: n/a
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**********@rogers.com> wrote in message
news:Ge********************@news04.bloor.is.net.ca ble.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.devbuzz.com> wrote in message
news:eA**************@TK2MSFTNGP11.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.Binary or changing the data type server side to Bit may do
it for you.

HTH,

Bill
"Bryan" <ca**********@rogers.com> wrote in message
news:5y********************@news04.bloor.is.net.ca ble.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.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").Va lue =
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


--------------------------------------------------------------------------
--
---



Nov 20 '05 #11

P: n/a
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
----------------------------------------------------------
"William Ryan eMVP" <bi**@NoSp4m.devbuzz.com> wrote in message
news:Or***************@TK2MSFTNGP12.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**********@rogers.com> wrote in message
news:Ge********************@news04.bloor.is.net.ca ble.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.devbuzz.com> wrote in message
news:eA**************@TK2MSFTNGP11.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.Binary or changing the data type server side to Bit may
do
it for you.

HTH,

Bill
"Bryan" <ca**********@rogers.com> wrote in message
news:5y********************@news04.bloor.is.net.ca ble.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.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").Va lue =
> 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
>


-------------------------------------------------------------------------- --
> ---
>
>
>



Nov 20 '05 #12

P: n/a
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
----------------------------------------------------------
"William Ryan eMVP" <bi**@NoSp4m.devbuzz.com> wrote in message
news:Or***************@TK2MSFTNGP12.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**********@rogers.com> wrote in message
news:Ge********************@news04.bloor.is.net.ca ble.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.devbuzz.com> wrote in message
news:eA**************@TK2MSFTNGP11.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.Binary or changing the data type server side to Bit may
do
it for you.

HTH,

Bill
"Bryan" <ca**********@rogers.com> wrote in message
news:5y********************@news04.bloor.is.net.ca ble.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.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").Va lue =
> 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
>


-------------------------------------------------------------------------- --
> ---
>
>
>



Nov 20 '05 #13

P: n/a
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.ToStri ng()) 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.

"Bryan" <ca**********@rogers.com> wrote in message
news:BJ********************@news04.bloor.is.net.ca ble.rogers.com...
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
----------------------------------------------------------
"William Ryan eMVP" <bi**@NoSp4m.devbuzz.com> wrote in message
news:Or***************@TK2MSFTNGP12.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**********@rogers.com> wrote in message
news:Ge********************@news04.bloor.is.net.ca ble.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.devbuzz.com> wrote in message
news:eA**************@TK2MSFTNGP11.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.Binary or changing the data type server side to Bit may
do
> it for you.
>
> HTH,
>
> Bill
> "Bryan" <ca**********@rogers.com> wrote in message
> news:5y********************@news04.bloor.is.net.ca ble.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.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").Va lue =
> > 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
> >
>


--------------------------------------------------------------------------
> --
> > ---
> >
> >
> >
>
>



Nov 20 '05 #14

P: n/a
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


"William Ryan eMVP" <bi**@NoSp4m.devbuzz.com> wrote in message
news:OV**************@TK2MSFTNGP09.phx.gbl...
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.ToStri ng()) 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.

"Bryan" <ca**********@rogers.com> wrote in message
news:BJ********************@news04.bloor.is.net.ca ble.rogers.com...
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
----------------------------------------------------------
"William Ryan eMVP" <bi**@NoSp4m.devbuzz.com> wrote in message
news:Or***************@TK2MSFTNGP12.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**********@rogers.com> wrote in message
news:Ge********************@news04.bloor.is.net.ca ble.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.devbuzz.com> wrote in message
> news:eA**************@TK2MSFTNGP11.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.Binary or changing the data type server side to

Bit may
> do
> > it for you.
> >
> > HTH,
> >
> > Bill
> > "Bryan" <ca**********@rogers.com> wrote in message
> > news:5y********************@news04.bloor.is.net.ca ble.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.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").Va lue =
> > > 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
> > >
> >
>


-------------------------------------------------------------------------- > > --
> > > ---
> > >
> > >
> > >
> >
> >
>
>



Nov 20 '05 #15

P: n/a
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

"Bryan" <ca**********@rogers.com> wrote...
cmd.Parameters.Item("@login_status").Value = 1

Nov 20 '05 #16

P: n/a
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
"Tom Leylan" <ge*@iamtiredofspam.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
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

"Bryan" <ca**********@rogers.com> wrote...
cmd.Parameters.Item("@login_status").Value = 1


Nov 20 '05 #17

P: n/a
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.

"Bryan" <ca**********@rogers.com> wrote in message
news:3Q********************@news04.bloor.is.net.ca ble.rogers.com...
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
"Tom Leylan" <ge*@iamtiredofspam.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
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

"Bryan" <ca**********@rogers.com> wrote...
cmd.Parameters.Item("@login_status").Value = 1



Nov 20 '05 #18

P: n/a
R
Ehhh, just a thought, do you set the value to "1" or do you set it to '1'?

Ronny

"Bryan" <ca**********@rogers.com> wrote in message
news:cm********************@news04.bloor.is.net.ca ble.rogers.com...
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


"William Ryan eMVP" <bi**@NoSp4m.devbuzz.com> wrote in message
news:OV**************@TK2MSFTNGP09.phx.gbl...
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.ToStri ng()) 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.

"Bryan" <ca**********@rogers.com> wrote in message
news:BJ********************@news04.bloor.is.net.ca ble.rogers.com...
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
----------------------------------------------------------
"William Ryan eMVP" <bi**@NoSp4m.devbuzz.com> wrote in message
news:Or***************@TK2MSFTNGP12.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**********@rogers.com> wrote in message
> news:Ge********************@news04.bloor.is.net.ca ble.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.devbuzz.com> wrote in message
> > news:eA**************@TK2MSFTNGP11.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.Binary or changing the data type server side
to Bit
> may
> > do
> > > it for you.
> > >
> > > HTH,
> > >
> > > Bill
> > > "Bryan" <ca**********@rogers.com> wrote in message
> > > news:5y********************@news04.bloor.is.net.ca ble.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.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").Va lue =
> > > > 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
> > > >
> > >
> >
>


-------------------------------------------------------------------------- > > > --
> > > > ---
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>



Nov 20 '05 #19

P: n/a
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

"Tom Leylan" <ge*@iamtiredofspam.com> wrote in message
news:ub**************@TK2MSFTNGP10.phx.gbl...
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.

"Bryan" <ca**********@rogers.com> wrote in message
news:3Q********************@news04.bloor.is.net.ca ble.rogers.com...
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
"Tom Leylan" <ge*@iamtiredofspam.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
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

"Bryan" <ca**********@rogers.com> wrote...

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



Nov 20 '05 #20

P: n/a
"Bryan" <ca**********@rogers.com> wrote in message news:<5y********************@news04.bloor.is.net.c able.rogers.com>...
hello all...

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


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
Nov 20 '05 #21

This discussion thread is closed

Replies have been disabled for this discussion.