adding record to sql db

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.

* error while uploading original data. 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 = "* error while uploading original data. " &
objError.Message & objError.Source

Exit Sub

Finally

If dbConn.State = ConnectionState.Open Then

dbConn.Close()

End If

End Try

End Sub

End Class

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

did you try to just use the integer 1 or 0 with no quotes?

"Bryan" <carvalho2181@rogers.com> wrote in message
news:5ydTb.90473$9Ce1.32801@news04.bloor.is.net.ca ble.rogers.com...[color=blue]
> hello all...
>
> im trying to add a record to an sql db on ms sql server 2000, using[/color]
vb.net.[color=blue]
>
> 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[/color]
1[color=blue]
> 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.
>
> * error while uploading original data. 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,[/color]
18))[color=blue]
>
> 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 =[/color]
RegCreditcardnumberTXT.Text[color=blue]
>
> 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 = "* error while uploading original data. " &
> objError.Message & objError.Source
>
> Exit Sub
>
> Finally
>
> If dbConn.State = ConnectionState.Open Then
>
> dbConn.Close()
>
> End If
>
> End Try
>
> End Sub
>
>
>
> End Class
>
> --------------------------------------------------------------------------[/color]
--[color=blue]
> ---
>
>
>[/color]

did you try to just use the integer 1 or 0 with no quotes?

"Bryan" <carvalho2181@rogers.com> wrote in message
news:5ydTb.90473$9Ce1.32801@news04.bloor.is.net.ca ble.rogers.com...[color=blue]
> hello all...
>
> im trying to add a record to an sql db on ms sql server 2000, using[/color]
vb.net.[color=blue]
>
> 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[/color]
1[color=blue]
> 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.
>
> * error while uploading original data. 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,[/color]
18))[color=blue]
>
> 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 =[/color]
RegCreditcardnumberTXT.Text[color=blue]
>
> 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 = "* error while uploading original data. " &
> objError.Message & objError.Source
>
> Exit Sub
>
> Finally
>
> If dbConn.State = ConnectionState.Open Then
>
> dbConn.Close()
>
> End If
>
> End Try
>
> End Sub
>
>
>
> End Class
>
> --------------------------------------------------------------------------[/color]
--[color=blue]
> ---
>
>
>[/color]

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" <carvalho2181@rogers.com> wrote in message
news:5ydTb.90473$9Ce1.32801@news04.bloor.is.net.ca ble.rogers.com...[color=blue]
> hello all...
>
> im trying to add a record to an sql db on ms sql server 2000, using[/color]
vb.net.[color=blue]
>
> 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[/color]
1[color=blue]
> 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.
>
> * error while uploading original data. 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,[/color]
18))[color=blue]
>
> 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 =[/color]
RegCreditcardnumberTXT.Text[color=blue]
>
> 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 = "* error while uploading original data. " &
> objError.Message & objError.Source
>
> Exit Sub
>
> Finally
>
> If dbConn.State = ConnectionState.Open Then
>
> dbConn.Close()
>
> End If
>
> End Try
>
> End Sub
>
>
>
> End Class
>
> --------------------------------------------------------------------------[/color]
--[color=blue]
> ---
>
>
>[/color]

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" <carvalho2181@rogers.com> wrote in message
news:5ydTb.90473$9Ce1.32801@news04.bloor.is.net.ca ble.rogers.com...[color=blue]
> hello all...
>
> im trying to add a record to an sql db on ms sql server 2000, using[/color]
vb.net.[color=blue]
>
> 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[/color]
1[color=blue]
> 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.
>
> * error while uploading original data. 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,[/color]
18))[color=blue]
>
> 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 =[/color]
RegCreditcardnumberTXT.Text[color=blue]
>
> 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 = "* error while uploading original data. " &
> objError.Message & objError.Source
>
> Exit Sub
>
> Finally
>
> If dbConn.State = ConnectionState.Open Then
>
> dbConn.Close()
>
> End If
>
> End Try
>
> End Sub
>
>
>
> End Class
>
> --------------------------------------------------------------------------[/color]
--[color=blue]
> ---
>
>
>[/color]

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:O9pvtDR6DHA.2748@TK2MSFTNGP09.phx.gbl...[color=blue]
> did you try to just use the integer 1 or 0 with no quotes?
>
>
> "Bryan" <carvalho2181@rogers.com> wrote in message
> news:5ydTb.90473$9Ce1.32801@news04.bloor.is.net.ca ble.rogers.com...[color=green]
> > hello all...
> >
> > im trying to add a record to an sql db on ms sql server 2000, using[/color]
> vb.net.[color=green]
> >
> > 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.[/color][/color]
the[color=blue][color=green]
> > field is login_status.
> >
> > ive tried in quotes and not, giving it an integer variable with the[/color][/color]
number[color=blue]
> 1[color=green]
> > 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.
> >
> > * error while uploading original data. String or binary data
> > would be truncated.The statement has been terminated..Net SqlClient Data
> > Provider
> >
> >
> >
> >[/color]
>
> --------------------------------------------------------------------------[color=green]
> > here is the code:[/color]
>
> --------------------------------------------------------------------------[color=green]
> > 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,[/color]
> 18))[color=green]
> >
> > cmd.Parameters.Add(New SqlParameter("@credit_card_expiry_date",
> > SqlDbType.DateTime))
> >
> > cmd.Parameters.Add(New SqlParameter("@mailing_address",[/color][/color]
SqlDbType.VarChar,[color=blue][color=green]
> > 100))
> >
> > cmd.Parameters.Add(New SqlParameter("@phone_nbr", SqlDbType.Char, 14))
> >
> > cmd.Parameters.Add(New SqlParameter("@email_address", SqlDbType.Char,[/color][/color]
50))[color=blue][color=green]
> >
> > 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 =[/color]
> RegCreditcardnumberTXT.Text[color=green]
> >
> > 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 =[/color][/color]
RegCreditcardnameTXT.Text[color=blue][color=green]
> >
> > 'opent the connection to the database
> >
> > dbConn.Open()
> >
> > 'execute sql statement
> >
> > cmd.ExecuteNonQuery()
> >
> > dbConn.Close()
> >
> > Catch objError As Exception
> >
> > dberror.Text = "* error while uploading original data. " &
> > objError.Message & objError.Source
> >
> > Exit Sub
> >
> > Finally
> >
> > If dbConn.State = ConnectionState.Open Then
> >
> > dbConn.Close()
> >
> > End If
> >
> > End Try
> >
> > End Sub
> >
> >
> >
> > End Class
> >[/color]
>
> --------------------------------------------------------------------------
> --[color=green]
> > ---
> >
> >
> >[/color]
>
>[/color]

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:O9pvtDR6DHA.2748@TK2MSFTNGP09.phx.gbl...[color=blue]
> did you try to just use the integer 1 or 0 with no quotes?
>
>
> "Bryan" <carvalho2181@rogers.com> wrote in message
> news:5ydTb.90473$9Ce1.32801@news04.bloor.is.net.ca ble.rogers.com...[color=green]
> > hello all...
> >
> > im trying to add a record to an sql db on ms sql server 2000, using[/color]
> vb.net.[color=green]
> >
> > 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.[/color][/color]
the[color=blue][color=green]
> > field is login_status.
> >
> > ive tried in quotes and not, giving it an integer variable with the[/color][/color]
number[color=blue]
> 1[color=green]
> > 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.
> >
> > * error while uploading original data. String or binary data
> > would be truncated.The statement has been terminated..Net SqlClient Data
> > Provider
> >
> >
> >
> >[/color]
>
> --------------------------------------------------------------------------[color=green]
> > here is the code:[/color]
>
> --------------------------------------------------------------------------[color=green]
> > 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,[/color]
> 18))[color=green]
> >
> > cmd.Parameters.Add(New SqlParameter("@credit_card_expiry_date",
> > SqlDbType.DateTime))
> >
> > cmd.Parameters.Add(New SqlParameter("@mailing_address",[/color][/color]
SqlDbType.VarChar,[color=blue][color=green]
> > 100))
> >
> > cmd.Parameters.Add(New SqlParameter("@phone_nbr", SqlDbType.Char, 14))
> >
> > cmd.Parameters.Add(New SqlParameter("@email_address", SqlDbType.Char,[/color][/color]
50))[color=blue][color=green]
> >
> > 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 =[/color]
> RegCreditcardnumberTXT.Text[color=green]
> >
> > 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 =[/color][/color]
RegCreditcardnameTXT.Text[color=blue][color=green]
> >
> > 'opent the connection to the database
> >
> > dbConn.Open()
> >
> > 'execute sql statement
> >
> > cmd.ExecuteNonQuery()
> >
> > dbConn.Close()
> >
> > Catch objError As Exception
> >
> > dberror.Text = "* error while uploading original data. " &
> > objError.Message & objError.Source
> >
> > Exit Sub
> >
> > Finally
> >
> > If dbConn.State = ConnectionState.Open Then
> >
> > dbConn.Close()
> >
> > End If
> >
> > End Try
> >
> > End Sub
> >
> >
> >
> > End Class
> >[/color]
>
> --------------------------------------------------------------------------
> --[color=green]
> > ---
> >
> >
> >[/color]
>
>[/color]

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" <bill@NoSp4m.devbuzz.com> wrote in message
news:eAbi9FR6DHA.3896@TK2MSFTNGP11.phx.gbl...[color=blue]
> 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[/color]
it's[color=blue]
> 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[/color]
do[color=blue]
> it for you.
>
> HTH,
>
> Bill
> "Bryan" <carvalho2181@rogers.com> wrote in message
> news:5ydTb.90473$9Ce1.32801@news04.bloor.is.net.ca ble.rogers.com...[color=green]
> > hello all...
> >
> > im trying to add a record to an sql db on ms sql server 2000, using[/color]
> vb.net.[color=green]
> >
> > 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.[/color][/color]
the[color=blue][color=green]
> > field is login_status.
> >
> > ive tried in quotes and not, giving it an integer variable with the[/color][/color]
number[color=blue]
> 1[color=green]
> > 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.
> >
> > * error while uploading original data. String or binary data
> > would be truncated.The statement has been terminated..Net SqlClient Data
> > Provider
> >
> >
> >
> >[/color]
>
> --------------------------------------------------------------------------[color=green]
> > here is the code:[/color]
>
> --------------------------------------------------------------------------[color=green]
> > 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,[/color]
> 18))[color=green]
> >
> > cmd.Parameters.Add(New SqlParameter("@credit_card_expiry_date",
> > SqlDbType.DateTime))
> >
> > cmd.Parameters.Add(New SqlParameter("@mailing_address",[/color][/color]
SqlDbType.VarChar,[color=blue][color=green]
> > 100))
> >
> > cmd.Parameters.Add(New SqlParameter("@phone_nbr", SqlDbType.Char, 14))
> >
> > cmd.Parameters.Add(New SqlParameter("@email_address", SqlDbType.Char,[/color][/color]
50))[color=blue][color=green]
> >
> > 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 =[/color]
> RegCreditcardnumberTXT.Text[color=green]
> >
> > 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 =[/color][/color]
RegCreditcardnameTXT.Text[color=blue][color=green]
> >
> > 'opent the connection to the database
> >
> > dbConn.Open()
> >
> > 'execute sql statement
> >
> > cmd.ExecuteNonQuery()
> >
> > dbConn.Close()
> >
> > Catch objError As Exception
> >
> > dberror.Text = "* error while uploading original data. " &
> > objError.Message & objError.Source
> >
> > Exit Sub
> >
> > Finally
> >
> > If dbConn.State = ConnectionState.Open Then
> >
> > dbConn.Close()
> >
> > End If
> >
> > End Try
> >
> > End Sub
> >
> >
> >
> > End Class
> >[/color]
>
> --------------------------------------------------------------------------
> --[color=green]
> > ---
> >
> >
> >[/color]
>
>[/color]

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" <bill@NoSp4m.devbuzz.com> wrote in message
news:eAbi9FR6DHA.3896@TK2MSFTNGP11.phx.gbl...[color=blue]
> 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[/color]
it's[color=blue]
> 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[/color]
do[color=blue]
> it for you.
>
> HTH,
>
> Bill
> "Bryan" <carvalho2181@rogers.com> wrote in message
> news:5ydTb.90473$9Ce1.32801@news04.bloor.is.net.ca ble.rogers.com...[color=green]
> > hello all...
> >
> > im trying to add a record to an sql db on ms sql server 2000, using[/color]
> vb.net.[color=green]
> >
> > 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.[/color][/color]
the[color=blue][color=green]
> > field is login_status.
> >
> > ive tried in quotes and not, giving it an integer variable with the[/color][/color]
number[color=blue]
> 1[color=green]
> > 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.
> >
> > * error while uploading original data. String or binary data
> > would be truncated.The statement has been terminated..Net SqlClient Data
> > Provider
> >
> >
> >
> >[/color]
>
> --------------------------------------------------------------------------[color=green]
> > here is the code:[/color]
>
> --------------------------------------------------------------------------[color=green]
> > 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,[/color]
> 18))[color=green]
> >
> > cmd.Parameters.Add(New SqlParameter("@credit_card_expiry_date",
> > SqlDbType.DateTime))
> >
> > cmd.Parameters.Add(New SqlParameter("@mailing_address",[/color][/color]
SqlDbType.VarChar,[color=blue][color=green]
> > 100))
> >
> > cmd.Parameters.Add(New SqlParameter("@phone_nbr", SqlDbType.Char, 14))
> >
> > cmd.Parameters.Add(New SqlParameter("@email_address", SqlDbType.Char,[/color][/color]
50))[color=blue][color=green]
> >
> > 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 =[/color]
> RegCreditcardnumberTXT.Text[color=green]
> >
> > 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 =[/color][/color]
RegCreditcardnameTXT.Text[color=blue][color=green]
> >
> > 'opent the connection to the database
> >
> > dbConn.Open()
> >
> > 'execute sql statement
> >
> > cmd.ExecuteNonQuery()
> >
> > dbConn.Close()
> >
> > Catch objError As Exception
> >
> > dberror.Text = "* error while uploading original data. " &
> > objError.Message & objError.Source
> >
> > Exit Sub
> >
> > Finally
> >
> > If dbConn.State = ConnectionState.Open Then
> >
> > dbConn.Close()
> >
> > End If
> >
> > End Try
> >
> > End Sub
> >
> >
> >
> > End Class
> >[/color]
>
> --------------------------------------------------------------------------
> --[color=green]
> > ---
> >
> >
> >[/color]
>
>[/color]

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" <carvalho2181@rogers.com> wrote in message
news:GefTb.91569$9Ce1.56231@news04.bloor.is.net.ca ble.rogers.com...[color=blue]
> 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" <bill@NoSp4m.devbuzz.com> wrote in message
> news:eAbi9FR6DHA.3896@TK2MSFTNGP11.phx.gbl...[color=green]
> > Bryan:
> >
> > What is the datatype of the field in SqlServer? Bit or Binary? The[/color][/color]
Param[color=blue][color=green]
> > type is specified as Bit but by the Exception message, I'm wondering if[/color]
> it's[color=green]
> > not Binary in the DB. If so, I believe changing the db type in the[/color][/color]
client[color=blue][color=green]
> > code to SqlDbType.Binary or changing the data type server side to Bit[/color][/color]
may[color=blue]
> do[color=green]
> > it for you.
> >
> > HTH,
> >
> > Bill
> > "Bryan" <carvalho2181@rogers.com> wrote in message
> > news:5ydTb.90473$9Ce1.32801@news04.bloor.is.net.ca ble.rogers.com...[color=darkred]
> > > hello all...
> > >
> > > im trying to add a record to an sql db on ms sql server 2000, using[/color]
> > vb.net.[color=darkred]
> > >
> > > 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.[/color][/color]
> the[color=green][color=darkred]
> > > field is login_status.
> > >
> > > ive tried in quotes and not, giving it an integer variable with the[/color][/color]
> number[color=green]
> > 1[color=darkred]
> > > in it, true/false statements.. ive tried a bunch of stuff... has me[/color][/color][/color]
real[color=blue][color=green][color=darkred]
> > > stumped..
> > >
> > > have any ideas?
> > >
> > > thanks
> > >
> > >
> > > here is the error it spits out.
> > >
> > > * error while uploading original data. String or binary[/color][/color][/color]
data[color=blue][color=green][color=darkred]
> > > would be truncated.The statement has been terminated..Net SqlClient[/color][/color][/color]
Data[color=blue][color=green][color=darkred]
> > > Provider
> > >
> > >
> > >
> > >[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green][color=darkred]
> > > here is the code:[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green][color=darkred]
> > > 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,[/color][/color][/color]
27))[color=blue][color=green][color=darkred]
> > >
> > > cmd.Parameters.Add(New SqlParameter("@password", SqlDbType.Char, 7))
> > >
> > > cmd.Parameters.Add(New SqlParameter("@credit_card_nbr",[/color][/color][/color]
SqlDbType.Char,[color=blue][color=green]
> > 18))[color=darkred]
> > >
> > > cmd.Parameters.Add(New SqlParameter("@credit_card_expiry_date",
> > > SqlDbType.DateTime))
> > >
> > > cmd.Parameters.Add(New SqlParameter("@mailing_address",[/color][/color]
> SqlDbType.VarChar,[color=green][color=darkred]
> > > 100))
> > >
> > > cmd.Parameters.Add(New SqlParameter("@phone_nbr", SqlDbType.Char, 14))
> > >
> > > cmd.Parameters.Add(New SqlParameter("@email_address", SqlDbType.Char,[/color][/color]
> 50))[color=green][color=darkred]
> > >
> > > cmd.Parameters.Add(New SqlParameter("@login_status", SqlDbType.Bit))
> > >
> > > cmd.Parameters.Add(New SqlParameter("@credit_card_name",[/color][/color][/color]
SqlDbType.Char,[color=blue][color=green][color=darkred]
> > > 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 =[/color]
> > RegCreditcardnumberTXT.Text[color=darkred]
> > >
> > > 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 =[/color][/color]
> RegCreditcardnameTXT.Text[color=green][color=darkred]
> > >
> > > 'opent the connection to the database
> > >
> > > dbConn.Open()
> > >
> > > 'execute sql statement
> > >
> > > cmd.ExecuteNonQuery()
> > >
> > > dbConn.Close()
> > >
> > > Catch objError As Exception
> > >
> > > dberror.Text = "* error while uploading original data. " &
> > > objError.Message & objError.Source
> > >
> > > Exit Sub
> > >
> > > Finally
> > >
> > > If dbConn.State = ConnectionState.Open Then
> > >
> > > dbConn.Close()
> > >
> > > End If
> > >
> > > End Try
> > >
> > > End Sub
> > >
> > >
> > >
> > > End Class
> > >[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green]
> > --[color=darkred]
> > > ---
> > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

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" <carvalho2181@rogers.com> wrote in message
news:GefTb.91569$9Ce1.56231@news04.bloor.is.net.ca ble.rogers.com...[color=blue]
> 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" <bill@NoSp4m.devbuzz.com> wrote in message
> news:eAbi9FR6DHA.3896@TK2MSFTNGP11.phx.gbl...[color=green]
> > Bryan:
> >
> > What is the datatype of the field in SqlServer? Bit or Binary? The[/color][/color]
Param[color=blue][color=green]
> > type is specified as Bit but by the Exception message, I'm wondering if[/color]
> it's[color=green]
> > not Binary in the DB. If so, I believe changing the db type in the[/color][/color]
client[color=blue][color=green]
> > code to SqlDbType.Binary or changing the data type server side to Bit[/color][/color]
may[color=blue]
> do[color=green]
> > it for you.
> >
> > HTH,
> >
> > Bill
> > "Bryan" <carvalho2181@rogers.com> wrote in message
> > news:5ydTb.90473$9Ce1.32801@news04.bloor.is.net.ca ble.rogers.com...[color=darkred]
> > > hello all...
> > >
> > > im trying to add a record to an sql db on ms sql server 2000, using[/color]
> > vb.net.[color=darkred]
> > >
> > > 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.[/color][/color]
> the[color=green][color=darkred]
> > > field is login_status.
> > >
> > > ive tried in quotes and not, giving it an integer variable with the[/color][/color]
> number[color=green]
> > 1[color=darkred]
> > > in it, true/false statements.. ive tried a bunch of stuff... has me[/color][/color][/color]
real[color=blue][color=green][color=darkred]
> > > stumped..
> > >
> > > have any ideas?
> > >
> > > thanks
> > >
> > >
> > > here is the error it spits out.
> > >
> > > * error while uploading original data. String or binary[/color][/color][/color]
data[color=blue][color=green][color=darkred]
> > > would be truncated.The statement has been terminated..Net SqlClient[/color][/color][/color]
Data[color=blue][color=green][color=darkred]
> > > Provider
> > >
> > >
> > >
> > >[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green][color=darkred]
> > > here is the code:[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green][color=darkred]
> > > 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,[/color][/color][/color]
27))[color=blue][color=green][color=darkred]
> > >
> > > cmd.Parameters.Add(New SqlParameter("@password", SqlDbType.Char, 7))
> > >
> > > cmd.Parameters.Add(New SqlParameter("@credit_card_nbr",[/color][/color][/color]
SqlDbType.Char,[color=blue][color=green]
> > 18))[color=darkred]
> > >
> > > cmd.Parameters.Add(New SqlParameter("@credit_card_expiry_date",
> > > SqlDbType.DateTime))
> > >
> > > cmd.Parameters.Add(New SqlParameter("@mailing_address",[/color][/color]
> SqlDbType.VarChar,[color=green][color=darkred]
> > > 100))
> > >
> > > cmd.Parameters.Add(New SqlParameter("@phone_nbr", SqlDbType.Char, 14))
> > >
> > > cmd.Parameters.Add(New SqlParameter("@email_address", SqlDbType.Char,[/color][/color]
> 50))[color=green][color=darkred]
> > >
> > > cmd.Parameters.Add(New SqlParameter("@login_status", SqlDbType.Bit))
> > >
> > > cmd.Parameters.Add(New SqlParameter("@credit_card_name",[/color][/color][/color]
SqlDbType.Char,[color=blue][color=green][color=darkred]
> > > 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 =[/color]
> > RegCreditcardnumberTXT.Text[color=darkred]
> > >
> > > 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 =[/color][/color]
> RegCreditcardnameTXT.Text[color=green][color=darkred]
> > >
> > > 'opent the connection to the database
> > >
> > > dbConn.Open()
> > >
> > > 'execute sql statement
> > >
> > > cmd.ExecuteNonQuery()
> > >
> > > dbConn.Close()
> > >
> > > Catch objError As Exception
> > >
> > > dberror.Text = "* error while uploading original data. " &
> > > objError.Message & objError.Source
> > >
> > > Exit Sub
> > >
> > > Finally
> > >
> > > If dbConn.State = ConnectionState.Open Then
> > >
> > > dbConn.Close()
> > >
> > > End If
> > >
> > > End Try
> > >
> > > End Sub
> > >
> > >
> > >
> > > End Class
> > >[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green]
> > --[color=darkred]
> > > ---
> > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

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" <bill@NoSp4m.devbuzz.com> wrote in message
news:OrB%23tVR6DHA.360@TK2MSFTNGP12.phx.gbl...[color=blue]
> 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[/color]
to[color=blue]
> VarBinary and that was ultimately the cause.
>
> Can you post the table script and the SQL Statement you are using? I'll[/color]
be[color=blue]
> glad to create a table and give it a try...
>
> Bill
> "Bryan" <carvalho2181@rogers.com> wrote in message
> news:GefTb.91569$9Ce1.56231@news04.bloor.is.net.ca ble.rogers.com...[color=green]
> > I noticed that also, yet the datatypes are teh same in sql and in teh[/color][/color]
vb[color=blue][color=green]
> > code. this is driving me nuts.
> >
> > thanks again.
> > bry
> >
> >
> > "William Ryan eMVP" <bill@NoSp4m.devbuzz.com> wrote in message
> > news:eAbi9FR6DHA.3896@TK2MSFTNGP11.phx.gbl...[color=darkred]
> > > Bryan:
> > >
> > > What is the datatype of the field in SqlServer? Bit or Binary? The[/color][/color]
> Param[color=green][color=darkred]
> > > type is specified as Bit but by the Exception message, I'm wondering[/color][/color][/color]
if[color=blue][color=green]
> > it's[color=darkred]
> > > not Binary in the DB. If so, I believe changing the db type in the[/color][/color]
> client[color=green][color=darkred]
> > > code to SqlDbType.Binary or changing the data type server side to Bit[/color][/color]
> may[color=green]
> > do[color=darkred]
> > > it for you.
> > >
> > > HTH,
> > >
> > > Bill
> > > "Bryan" <carvalho2181@rogers.com> wrote in message
> > > news:5ydTb.90473$9Ce1.32801@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[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > > database is a bit datatype, and though i get no syntax errors when
> > > > compiling, i get an error indicated that the data would be[/color][/color][/color]
truncated.[color=blue][color=green]
> > the[color=darkred]
> > > > field is login_status.
> > > >
> > > > ive tried in quotes and not, giving it an integer variable with the[/color]
> > number[color=darkred]
> > > 1
> > > > in it, true/false statements.. ive tried a bunch of stuff... has me[/color][/color]
> real[color=green][color=darkred]
> > > > stumped..
> > > >
> > > > have any ideas?
> > > >
> > > > thanks
> > > >
> > > >
> > > > here is the error it spits out.
> > > >
> > > > * error while uploading original data. String or binary[/color][/color]
> data[color=green][color=darkred]
> > > > would be truncated.The statement has been terminated..Net SqlClient[/color][/color]
> Data[color=green][color=darkred]
> > > > Provider
> > > >
> > > >
> > > >
> > > >
> > >[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green][color=darkred]
> > > > here is the code:
> > >[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green][color=darkred]
> > > > 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[/color][/color][/color]
EventArgs)[color=blue][color=green][color=darkred]
> > > >
> > > > 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,[/color][/color][/color]
19))[color=blue][color=green][color=darkred]
> > > >
> > > > cmd.Parameters.Add(New SqlParameter("@fullname", SqlDbType.VarChar,[/color][/color]
> 27))[color=green][color=darkred]
> > > >
> > > > cmd.Parameters.Add(New SqlParameter("@password", SqlDbType.Char, 7))
> > > >
> > > > cmd.Parameters.Add(New SqlParameter("@credit_card_nbr",[/color][/color]
> SqlDbType.Char,[color=green][color=darkred]
> > > 18))
> > > >
> > > > cmd.Parameters.Add(New SqlParameter("@credit_card_expiry_date",
> > > > SqlDbType.DateTime))
> > > >
> > > > cmd.Parameters.Add(New SqlParameter("@mailing_address",[/color]
> > SqlDbType.VarChar,[color=darkred]
> > > > 100))
> > > >
> > > > cmd.Parameters.Add(New SqlParameter("@phone_nbr", SqlDbType.Char,[/color][/color][/color]
14))[color=blue][color=green][color=darkred]
> > > >
> > > > cmd.Parameters.Add(New SqlParameter("@email_address",[/color][/color][/color]
SqlDbType.Char,[color=blue][color=green]
> > 50))[color=darkred]
> > > >
> > > > cmd.Parameters.Add(New SqlParameter("@login_status", SqlDbType.Bit))
> > > >
> > > > cmd.Parameters.Add(New SqlParameter("@credit_card_name",[/color][/color]
> SqlDbType.Char,[color=green][color=darkred]
> > > > 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 =[/color]
> > RegCreditcardnameTXT.Text[color=darkred]
> > > >
> > > > 'opent the connection to the database
> > > >
> > > > dbConn.Open()
> > > >
> > > > 'execute sql statement
> > > >
> > > > cmd.ExecuteNonQuery()
> > > >
> > > > dbConn.Close()
> > > >
> > > > Catch objError As Exception
> > > >
> > > > dberror.Text = "* error while uploading original data. "[/color][/color][/color]
&[color=blue][color=green][color=darkred]
> > > > objError.Message & objError.Source
> > > >
> > > > Exit Sub
> > > >
> > > > Finally
> > > >
> > > > If dbConn.State = ConnectionState.Open Then
> > > >
> > > > dbConn.Close()
> > > >
> > > > End If
> > > >
> > > > End Try
> > > >
> > > > End Sub
> > > >
> > > >
> > > >
> > > > End Class
> > > >
> > >[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green][color=darkred]
> > > --
> > > > ---
> > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

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" <bill@NoSp4m.devbuzz.com> wrote in message
news:OrB%23tVR6DHA.360@TK2MSFTNGP12.phx.gbl...[color=blue]
> 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[/color]
to[color=blue]
> VarBinary and that was ultimately the cause.
>
> Can you post the table script and the SQL Statement you are using? I'll[/color]
be[color=blue]
> glad to create a table and give it a try...
>
> Bill
> "Bryan" <carvalho2181@rogers.com> wrote in message
> news:GefTb.91569$9Ce1.56231@news04.bloor.is.net.ca ble.rogers.com...[color=green]
> > I noticed that also, yet the datatypes are teh same in sql and in teh[/color][/color]
vb[color=blue][color=green]
> > code. this is driving me nuts.
> >
> > thanks again.
> > bry
> >
> >
> > "William Ryan eMVP" <bill@NoSp4m.devbuzz.com> wrote in message
> > news:eAbi9FR6DHA.3896@TK2MSFTNGP11.phx.gbl...[color=darkred]
> > > Bryan:
> > >
> > > What is the datatype of the field in SqlServer? Bit or Binary? The[/color][/color]
> Param[color=green][color=darkred]
> > > type is specified as Bit but by the Exception message, I'm wondering[/color][/color][/color]
if[color=blue][color=green]
> > it's[color=darkred]
> > > not Binary in the DB. If so, I believe changing the db type in the[/color][/color]
> client[color=green][color=darkred]
> > > code to SqlDbType.Binary or changing the data type server side to Bit[/color][/color]
> may[color=green]
> > do[color=darkred]
> > > it for you.
> > >
> > > HTH,
> > >
> > > Bill
> > > "Bryan" <carvalho2181@rogers.com> wrote in message
> > > news:5ydTb.90473$9Ce1.32801@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[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > > database is a bit datatype, and though i get no syntax errors when
> > > > compiling, i get an error indicated that the data would be[/color][/color][/color]
truncated.[color=blue][color=green]
> > the[color=darkred]
> > > > field is login_status.
> > > >
> > > > ive tried in quotes and not, giving it an integer variable with the[/color]
> > number[color=darkred]
> > > 1
> > > > in it, true/false statements.. ive tried a bunch of stuff... has me[/color][/color]
> real[color=green][color=darkred]
> > > > stumped..
> > > >
> > > > have any ideas?
> > > >
> > > > thanks
> > > >
> > > >
> > > > here is the error it spits out.
> > > >
> > > > * error while uploading original data. String or binary[/color][/color]
> data[color=green][color=darkred]
> > > > would be truncated.The statement has been terminated..Net SqlClient[/color][/color]
> Data[color=green][color=darkred]
> > > > Provider
> > > >
> > > >
> > > >
> > > >
> > >[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green][color=darkred]
> > > > here is the code:
> > >[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green][color=darkred]
> > > > 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[/color][/color][/color]
EventArgs)[color=blue][color=green][color=darkred]
> > > >
> > > > 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,[/color][/color][/color]
19))[color=blue][color=green][color=darkred]
> > > >
> > > > cmd.Parameters.Add(New SqlParameter("@fullname", SqlDbType.VarChar,[/color][/color]
> 27))[color=green][color=darkred]
> > > >
> > > > cmd.Parameters.Add(New SqlParameter("@password", SqlDbType.Char, 7))
> > > >
> > > > cmd.Parameters.Add(New SqlParameter("@credit_card_nbr",[/color][/color]
> SqlDbType.Char,[color=green][color=darkred]
> > > 18))
> > > >
> > > > cmd.Parameters.Add(New SqlParameter("@credit_card_expiry_date",
> > > > SqlDbType.DateTime))
> > > >
> > > > cmd.Parameters.Add(New SqlParameter("@mailing_address",[/color]
> > SqlDbType.VarChar,[color=darkred]
> > > > 100))
> > > >
> > > > cmd.Parameters.Add(New SqlParameter("@phone_nbr", SqlDbType.Char,[/color][/color][/color]
14))[color=blue][color=green][color=darkred]
> > > >
> > > > cmd.Parameters.Add(New SqlParameter("@email_address",[/color][/color][/color]
SqlDbType.Char,[color=blue][color=green]
> > 50))[color=darkred]
> > > >
> > > > cmd.Parameters.Add(New SqlParameter("@login_status", SqlDbType.Bit))
> > > >
> > > > cmd.Parameters.Add(New SqlParameter("@credit_card_name",[/color][/color]
> SqlDbType.Char,[color=green][color=darkred]
> > > > 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 =[/color]
> > RegCreditcardnameTXT.Text[color=darkred]
> > > >
> > > > 'opent the connection to the database
> > > >
> > > > dbConn.Open()
> > > >
> > > > 'execute sql statement
> > > >
> > > > cmd.ExecuteNonQuery()
> > > >
> > > > dbConn.Close()
> > > >
> > > > Catch objError As Exception
> > > >
> > > > dberror.Text = "* error while uploading original data. "[/color][/color][/color]
&[color=blue][color=green][color=darkred]
> > > > objError.Message & objError.Source
> > > >
> > > > Exit Sub
> > > >
> > > > Finally
> > > >
> > > > If dbConn.State = ConnectionState.Open Then
> > > >
> > > > dbConn.Close()
> > > >
> > > > End If
> > > >
> > > > End Try
> > > >
> > > > End Sub
> > > >
> > > >
> > > >
> > > > End Class
> > > >
> > >[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green][color=darkred]
> > > --
> > > > ---
> > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

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" <carvalho2181@rogers.com> wrote in message
news:BJfTb.92051$9Ce1.22229@news04.bloor.is.net.ca ble.rogers.com...[color=blue]
> 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[/color]
BPurchaseOrderCA[color=blue]
> 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[/color]
not[color=blue]
> 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[/color]
BPurchaseOrderCA[color=blue]
> 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[/color]
not[color=blue]
> 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[/color]
exists.'[color=blue]
> 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[/color]
*/[color=blue]
> 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[/color]
exists.'[color=blue]
> 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[/color]
*/[color=blue]
> 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[/color]
BOrderItemCA[color=blue]
> 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[/color]
BOrderItemCA[color=blue]
> 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" <bill@NoSp4m.devbuzz.com> wrote in message
> news:OrB%23tVR6DHA.360@TK2MSFTNGP12.phx.gbl...[color=green]
> > Hmm, do you have any binary fields in the DB? I'm hesitant to admit[/color][/color]
this,[color=blue][color=green]
> > but I had this problem once and I realized I had defined a Varchar field[/color]
> to[color=green]
> > VarBinary and that was ultimately the cause.
> >
> > Can you post the table script and the SQL Statement you are using? I'll[/color]
> be[color=green]
> > glad to create a table and give it a try...
> >
> > Bill
> > "Bryan" <carvalho2181@rogers.com> wrote in message
> > news:GefTb.91569$9Ce1.56231@news04.bloor.is.net.ca ble.rogers.com...[color=darkred]
> > > I noticed that also, yet the datatypes are teh same in sql and in teh[/color][/color]
> vb[color=green][color=darkred]
> > > code. this is driving me nuts.
> > >
> > > thanks again.
> > > bry
> > >
> > >
> > > "William Ryan eMVP" <bill@NoSp4m.devbuzz.com> wrote in message
> > > news:eAbi9FR6DHA.3896@TK2MSFTNGP11.phx.gbl...
> > > > Bryan:
> > > >
> > > > What is the datatype of the field in SqlServer? Bit or Binary? The[/color]
> > Param[color=darkred]
> > > > type is specified as Bit but by the Exception message, I'm wondering[/color][/color]
> if[color=green][color=darkred]
> > > it's
> > > > not Binary in the DB. If so, I believe changing the db type in the[/color]
> > client[color=darkred]
> > > > code to SqlDbType.Binary or changing the data type server side to[/color][/color][/color]
Bit[color=blue][color=green]
> > may[color=darkred]
> > > do
> > > > it for you.
> > > >
> > > > HTH,
> > > >
> > > > Bill
> > > > "Bryan" <carvalho2181@rogers.com> wrote in message
> > > > news:5ydTb.90473$9Ce1.32801@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,[/color][/color][/color]
using[color=blue][color=green][color=darkred]
> > > > vb.net.
> > > > >
> > > > > seems to be working.. except for one thing, one of the columns in[/color][/color]
> the[color=green][color=darkred]
> > > > > database is a bit datatype, and though i get no syntax errors when
> > > > > compiling, i get an error indicated that the data would be[/color][/color]
> truncated.[color=green][color=darkred]
> > > the
> > > > > field is login_status.
> > > > >
> > > > > ive tried in quotes and not, giving it an integer variable with[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > number
> > > > 1
> > > > > in it, true/false statements.. ive tried a bunch of stuff... has[/color][/color][/color]
me[color=blue][color=green]
> > real[color=darkred]
> > > > > stumped..
> > > > >
> > > > > have any ideas?
> > > > >
> > > > > thanks
> > > > >
> > > > >
> > > > > here is the error it spits out.
> > > > >
> > > > > * error while uploading original data. String or[/color][/color][/color]
binary[color=blue][color=green]
> > data[color=darkred]
> > > > > would be truncated.The statement has been terminated..Net[/color][/color][/color]
SqlClient[color=blue][color=green]
> > Data[color=darkred]
> > > > > Provider
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > >[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green][color=darkred]
> > > > > here is the code:
> > > >
> > >[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green][color=darkred]
> > > > > 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[/color][/color]
> EventArgs)[color=green][color=darkred]
> > > > >
> > > > > 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,[/color][/color]
> 19))[color=green][color=darkred]
> > > > >
> > > > > cmd.Parameters.Add(New SqlParameter("@fullname",[/color][/color][/color]
SqlDbType.VarChar,[color=blue][color=green]
> > 27))[color=darkred]
> > > > >
> > > > > cmd.Parameters.Add(New SqlParameter("@password", SqlDbType.Char,[/color][/color][/color]
7))[color=blue][color=green][color=darkred]
> > > > >
> > > > > cmd.Parameters.Add(New SqlParameter("@credit_card_nbr",[/color]
> > SqlDbType.Char,[color=darkred]
> > > > 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,[/color][/color]
> 14))[color=green][color=darkred]
> > > > >
> > > > > cmd.Parameters.Add(New SqlParameter("@email_address",[/color][/color]
> SqlDbType.Char,[color=green][color=darkred]
> > > 50))
> > > > >
> > > > > cmd.Parameters.Add(New SqlParameter("@login_status",[/color][/color][/color]
SqlDbType.Bit))[color=blue][color=green][color=darkred]
> > > > >
> > > > > cmd.Parameters.Add(New SqlParameter("@credit_card_name",[/color]
> > SqlDbType.Char,[color=darkred]
> > > > > 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 = "* error while uploading original data.<br[/color][/color][/color]
/>"[color=blue]
> &[color=green][color=darkred]
> > > > > objError.Message & objError.Source
> > > > >
> > > > > Exit Sub
> > > > >
> > > > > Finally
> > > > >
> > > > > If dbConn.State = ConnectionState.Open Then
> > > > >
> > > > > dbConn.Close()
> > > > >
> > > > > End If
> > > > >
> > > > > End Try
> > > > >
> > > > > End Sub
> > > > >
> > > > >
> > > > >
> > > > > End Class
> > > > >
> > > >
> > >[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green][color=darkred]
> > > > --
> > > > > ---
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

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" <bill@NoSp4m.devbuzz.com> wrote in message
news:OV$ZItR6DHA.2720@TK2MSFTNGP09.phx.gbl...[color=blue]
> 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[/color]
text[color=blue]
> 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" <carvalho2181@rogers.com> wrote in message
> news:BJfTb.92051$9Ce1.22229@news04.bloor.is.net.ca ble.rogers.com...[color=green]
> > here is the sql script to create the database, at the end is the[/color][/color]
procedure[color=blue][color=green]
> > 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[/color]
> BPurchaseOrderCA[color=green]
> > 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[/color]
> not[color=green]
> > 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[/color]
> BPurchaseOrderCA[color=green]
> > 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[/color]
> not[color=green]
> > 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[/color]
> exists.'[color=green]
> > 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 =[/color][/color]
BManufacturerCA.manufacturer_id[color=blue]
> */[color=green]
> > 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[/color]
> exists.'[color=green]
> > 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 =[/color][/color]
BManufacturerCA.manufacturer_id[color=blue]
> */[color=green]
> > 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[/color]
> BOrderItemCA[color=green]
> > 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[/color]
> BOrderItemCA[color=green]
> > 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,[/color][/color]
@phone_nbr,[color=blue][color=green]
> > @email_address, @login_status, @credit_card_name )
> > GO
> >
> > SET QUOTED_IDENTIFIER OFF
> > GO
> >
> > SET ANSI_NULLS ON
> > GO
> >
> >
> > ----------------------------------------------------------
> >
> >
> > "William Ryan eMVP" <bill@NoSp4m.devbuzz.com> wrote in message
> > news:OrB%23tVR6DHA.360@TK2MSFTNGP12.phx.gbl...[color=darkred]
> > > Hmm, do you have any binary fields in the DB? I'm hesitant to admit[/color][/color]
> this,[color=green][color=darkred]
> > > but I had this problem once and I realized I had defined a Varchar[/color][/color][/color]
field[color=blue][color=green]
> > to[color=darkred]
> > > VarBinary and that was ultimately the cause.
> > >
> > > Can you post the table script and the SQL Statement you are using?[/color][/color][/color]
I'll[color=blue][color=green]
> > be[color=darkred]
> > > glad to create a table and give it a try...
> > >
> > > Bill
> > > "Bryan" <carvalho2181@rogers.com> wrote in message
> > > news:GefTb.91569$9Ce1.56231@news04.bloor.is.net.ca ble.rogers.com...
> > > > I noticed that also, yet the datatypes are teh same in sql and in[/color][/color][/color]
teh[color=blue][color=green]
> > vb[color=darkred]
> > > > code. this is driving me nuts.
> > > >
> > > > thanks again.
> > > > bry
> > > >
> > > >
> > > > "William Ryan eMVP" <bill@NoSp4m.devbuzz.com> wrote in message
> > > > news:eAbi9FR6DHA.3896@TK2MSFTNGP11.phx.gbl...
> > > > > Bryan:
> > > > >
> > > > > What is the datatype of the field in SqlServer? Bit or Binary?[/color][/color][/color]
The[color=blue][color=green][color=darkred]
> > > Param
> > > > > type is specified as Bit but by the Exception message, I'm[/color][/color][/color]
wondering[color=blue][color=green]
> > if[color=darkred]
> > > > it's
> > > > > not Binary in the DB. If so, I believe changing the db type in[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > client
> > > > > code to SqlDbType.Binary or changing the data type server side to[/color][/color]
> Bit[color=green][color=darkred]
> > > may
> > > > do
> > > > > it for you.
> > > > >
> > > > > HTH,
> > > > >
> > > > > Bill
> > > > > "Bryan" <carvalho2181@rogers.com> wrote in message
> > > > >[/color][/color][/color]
news:5ydTb.90473$9Ce1.32801@news04.bloor.is.net.ca ble.rogers.com...[color=blue][color=green][color=darkred]
> > > > > > hello all...
> > > > > >
> > > > > > im trying to add a record to an sql db on ms sql server 2000,[/color][/color]
> using[color=green][color=darkred]
> > > > > vb.net.
> > > > > >
> > > > > > seems to be working.. except for one thing, one of the columns[/color][/color][/color]
in[color=blue][color=green]
> > the[color=darkred]
> > > > > > database is a bit datatype, and though i get no syntax errors[/color][/color][/color]
when[color=blue][color=green][color=darkred]
> > > > > > compiling, i get an error indicated that the data would be[/color]
> > truncated.[color=darkred]
> > > > the
> > > > > > field is login_status.
> > > > > >
> > > > > > ive tried in quotes and not, giving it an integer variable with[/color][/color]
> the[color=green][color=darkred]
> > > > number
> > > > > 1
> > > > > > in it, true/false statements.. ive tried a bunch of stuff... has[/color][/color]
> me[color=green][color=darkred]
> > > real
> > > > > > stumped..
> > > > > >
> > > > > > have any ideas?
> > > > > >
> > > > > > thanks
> > > > > >
> > > > > >
> > > > > > here is the error it spits out.
> > > > > >
> > > > > > * error while uploading original data. String or[/color][/color]
> binary[color=green][color=darkred]
> > > data
> > > > > > would be truncated.The statement has been terminated..Net[/color][/color]
> SqlClient[color=green][color=darkred]
> > > Data
> > > > > > Provider
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > >
> > >[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green][color=darkred]
> > > > > > here is the code:
> > > > >
> > > >
> > >[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green][color=darkred]
> > > > > > 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[/color]
> > EventArgs)[color=darkred]
> > > > > >
> > > > > > 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,[/color]
> > 19))[color=darkred]
> > > > > >
> > > > > > cmd.Parameters.Add(New SqlParameter("@fullname",[/color][/color]
> SqlDbType.VarChar,[color=green][color=darkred]
> > > 27))
> > > > > >
> > > > > > cmd.Parameters.Add(New SqlParameter("@password", SqlDbType.Char,[/color][/color]
> 7))[color=green][color=darkred]
> > > > > >
> > > > > > 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",[/color][/color][/color]
SqlDbType.Char,[color=blue][color=green]
> > 14))[color=darkred]
> > > > > >
> > > > > > cmd.Parameters.Add(New SqlParameter("@email_address",[/color]
> > SqlDbType.Char,[color=darkred]
> > > > 50))
> > > > > >
> > > > > > cmd.Parameters.Add(New SqlParameter("@login_status",[/color][/color]
> SqlDbType.Bit))[color=green][color=darkred]
> > > > > >
> > > > > > 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 =[/color][/color][/color]
RegAddressTXT.Text[color=blue][color=green][color=darkred]
> > > > > >
> > > > > > 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 = "* error while uploading original data.<br[/color][/color]
> />"[color=green]
> > &[color=darkred]
> > > > > > objError.Message & objError.Source
> > > > > >
> > > > > > Exit Sub
> > > > > >
> > > > > > Finally
> > > > > >
> > > > > > If dbConn.State = ConnectionState.Open Then
> > > > > >
> > > > > > dbConn.Close()
> > > > > >
> > > > > > End If
> > > > > >
> > > > > > End Try
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > >
> > > > > >
> > > > > > End Class
> > > > > >
> > > > >
> > > >
> > >[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green][color=darkred]
> > > > > --
> > > > > > ---
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

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" <carvalho2181@rogers.com> wrote...
[color=blue]
> cmd.Parameters.Item("@login_status").Value = 1[/color]

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

* error while uploading original data. String or binary data
would be truncated.The statement has been terminated..Net SqlClient Data
Provider

"Tom Leylan" <gee@iamtiredofspam.com> wrote in message
news:%23WvsoFS6DHA.2488@TK2MSFTNGP09.phx.gbl...[color=blue]
> 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" <carvalho2181@rogers.com> wrote...
>[color=green]
> > cmd.Parameters.Item("@login_status").Value = 1[/color]
>
>[/color]

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" <carvalho2181@rogers.com> wrote in message
news:3QgTb.92854$9Ce1.45858@news04.bloor.is.net.ca ble.rogers.com...[color=blue]
> tried that.. really wished it worked.. gave me this.
>
> * error while uploading original data. String or binary data
> would be truncated.The statement has been terminated..Net SqlClient Data
> Provider
>
>
> "Tom Leylan" <gee@iamtiredofspam.com> wrote in message
> news:%23WvsoFS6DHA.2488@TK2MSFTNGP09.phx.gbl...[color=green]
> > Bryan,
> >
> > Try Convert.ToBoolean(1) on your assignment. If the error is being[/color][/color]
caused[color=blue][color=green]
> > by the bit field I assume it is because an integer value 1 won't fit.
> >
> > Tom
> >
> > "Bryan" <carvalho2181@rogers.com> wrote...
> >[color=darkred]
> > > cmd.Parameters.Item("@login_status").Value = 1[/color]
> >
> >[/color]
>
>[/color]

Ehhh, just a thought, do you set the value to "1" or do you set it to '1'?

Ronny

"Bryan" <carvalho2181@rogers.com> wrote in message
news:cmgTb.92519$9Ce1.49335@news04.bloor.is.net.ca ble.rogers.com...[color=blue]
> 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[/color]
fields[color=blue]
> 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[/color]
this[color=blue]
> be what is happening?
>
> eternally gratefull,
> bryan
>
>
>
>
> "William Ryan eMVP" <bill@NoSp4m.devbuzz.com> wrote in message
> news:OV$ZItR6DHA.2720@TK2MSFTNGP09.phx.gbl...[color=green]
> > Ok, if you write a Debug.Assert for each line ie asserting that the[/color][/color]
lenght[color=blue][color=green]
> > of textboxWhatever.text.Length <= FieldSize...see what happens. is[/color][/color]
there[color=blue][color=green]
> > 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[/color]
> text[color=green]
> > 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[/color][/color]
would[color=blue][color=green]
> > be smaller than field size.
> >
> > Let me play with it some more though and hopefully I can narrow it down.
> >
> > "Bryan" <carvalho2181@rogers.com> wrote in message
> > news:BJfTb.92051$9Ce1.22229@news04.bloor.is.net.ca ble.rogers.com...[color=darkred]
> > > here is the sql script to create the database, at the end is the[/color][/color]
> procedure[color=green][color=darkred]
> > > 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[/color]
> > BPurchaseOrderCA[color=darkred]
> > > 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[/color][/color][/color]
does[color=blue][color=green]
> > not[color=darkred]
> > > 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[/color]
> > BPurchaseOrderCA[color=darkred]
> > > 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[/color][/color][/color]
does[color=blue][color=green]
> > not[color=darkred]
> > > 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[/color]
> > exists.'[color=darkred]
> > > 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 =[/color][/color]
> BManufacturerCA.manufacturer_id[color=green]
> > */[color=darkred]
> > > 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[/color]
> > exists.'[color=darkred]
> > > 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 =[/color][/color]
> BManufacturerCA.manufacturer_id[color=green]
> > */[color=darkred]
> > > 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[/color]
> > BOrderItemCA[color=darkred]
> > > 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[/color]
> > BOrderItemCA[color=darkred]
> > > 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,[/color][/color]
> @phone_nbr,[color=green][color=darkred]
> > > @email_address, @login_status, @credit_card_name )
> > > GO
> > >
> > > SET QUOTED_IDENTIFIER OFF
> > > GO
> > >
> > > SET ANSI_NULLS ON
> > > GO
> > >
> > >
> > > ----------------------------------------------------------
> > >
> > >
> > > "William Ryan eMVP" <bill@NoSp4m.devbuzz.com> wrote in message
> > > news:OrB%23tVR6DHA.360@TK2MSFTNGP12.phx.gbl...
> > > > Hmm, do you have any binary fields in the DB? I'm hesitant to admit[/color]
> > this,[color=darkred]
> > > > but I had this problem once and I realized I had defined a Varchar[/color][/color]
> field[color=green][color=darkred]
> > > to
> > > > VarBinary and that was ultimately the cause.
> > > >
> > > > Can you post the table script and the SQL Statement you are using?[/color][/color]
> I'll[color=green][color=darkred]
> > > be
> > > > glad to create a table and give it a try...
> > > >
> > > > Bill
> > > > "Bryan" <carvalho2181@rogers.com> wrote in message
> > > > news:GefTb.91569$9Ce1.56231@news04.bloor.is.net.ca ble.rogers.com...
> > > > > I noticed that also, yet the datatypes are teh same in sql and in[/color][/color]
> teh[color=green][color=darkred]
> > > vb
> > > > > code. this is driving me nuts.
> > > > >
> > > > > thanks again.
> > > > > bry
> > > > >
> > > > >
> > > > > "William Ryan eMVP" <bill@NoSp4m.devbuzz.com> wrote in message
> > > > > news:eAbi9FR6DHA.3896@TK2MSFTNGP11.phx.gbl...
> > > > > > Bryan:
> > > > > >
> > > > > > What is the datatype of the field in SqlServer? Bit or Binary?[/color][/color]
> The[color=green][color=darkred]
> > > > Param
> > > > > > type is specified as Bit but by the Exception message, I'm[/color][/color]
> wondering[color=green][color=darkred]
> > > if
> > > > > it's
> > > > > > not Binary in the DB. If so, I believe changing the db type in[/color][/color]
> the[color=green][color=darkred]
> > > > client
> > > > > > code to SqlDbType.Binary or changing the data type server side[/color][/color][/color]
to[color=blue][color=green]
> > Bit[color=darkred]
> > > > may
> > > > > do
> > > > > > it for you.
> > > > > >
> > > > > > HTH,
> > > > > >
> > > > > > Bill
> > > > > > "Bryan" <carvalho2181@rogers.com> wrote in message
> > > > > >[/color][/color]
> news:5ydTb.90473$9Ce1.32801@news04.bloor.is.net.ca ble.rogers.com...[color=green][color=darkred]
> > > > > > > hello all...
> > > > > > >
> > > > > > > im trying to add a record to an sql db on ms sql server 2000,[/color]
> > using[color=darkred]
> > > > > > vb.net.
> > > > > > >
> > > > > > > seems to be working.. except for one thing, one of the columns[/color][/color]
> in[color=green][color=darkred]
> > > the
> > > > > > > database is a bit datatype, and though i get no syntax errors[/color][/color]
> when[color=green][color=darkred]
> > > > > > > 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[/color][/color][/color]
with[color=blue][color=green]
> > the[color=darkred]
> > > > > number
> > > > > > 1
> > > > > > > in it, true/false statements.. ive tried a bunch of stuff...[/color][/color][/color]
has[color=blue][color=green]
> > me[color=darkred]
> > > > real
> > > > > > > stumped..
> > > > > > >
> > > > > > > have any ideas?
> > > > > > >
> > > > > > > thanks
> > > > > > >
> > > > > > >
> > > > > > > here is the error it spits out.
> > > > > > >
> > > > > > > * error while uploading original data. String or[/color]
> > binary[color=darkred]
> > > > data
> > > > > > > would be truncated.The statement has been terminated..Net[/color]
> > SqlClient[color=darkred]
> > > > Data
> > > > > > > Provider
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green][color=darkred]
> > > > > > > here is the code:
> > > > > >
> > > > >
> > > >
> > >[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green][color=darkred]
> > > > > > > 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[/color][/color][/color]
System.Data.SqlClient.SqlConnection(dbConnStr)[color=blue][color=green][color=darkred]
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > Try
> > > > > > >
> > > > > > > Dim cmd As New SqlCommand("addCustomerSQL", dbConn)
> > > > > > >
> > > > > > > cmd.CommandType = CommandType.StoredProcedure
> > > > > > >
> > > > > > > cmd.Parameters.Add(New SqlParameter("@username",[/color][/color][/color]
SqlDbType.Char,[color=blue][color=green][color=darkred]
> > > 19))
> > > > > > >
> > > > > > > cmd.Parameters.Add(New SqlParameter("@fullname",[/color]
> > SqlDbType.VarChar,[color=darkred]
> > > > 27))
> > > > > > >
> > > > > > > cmd.Parameters.Add(New SqlParameter("@password",[/color][/color][/color]
SqlDbType.Char,[color=blue][color=green]
> > 7))[color=darkred]
> > > > > > >
> > > > > > > cmd.Parameters.Add(New SqlParameter("@credit_card_nbr",
> > > > SqlDbType.Char,
> > > > > > 18))
> > > > > > >
> > > > > > > cmd.Parameters.Add(New[/color][/color][/color]
SqlParameter("@credit_card_expiry_date",[color=blue][color=green][color=darkred]
> > > > > > > SqlDbType.DateTime))
> > > > > > >
> > > > > > > cmd.Parameters.Add(New SqlParameter("@mailing_address",
> > > > > SqlDbType.VarChar,
> > > > > > > 100))
> > > > > > >
> > > > > > > cmd.Parameters.Add(New SqlParameter("@phone_nbr",[/color][/color]
> SqlDbType.Char,[color=green][color=darkred]
> > > 14))
> > > > > > >
> > > > > > > cmd.Parameters.Add(New SqlParameter("@email_address",
> > > SqlDbType.Char,
> > > > > 50))
> > > > > > >
> > > > > > > cmd.Parameters.Add(New SqlParameter("@login_status",[/color]
> > SqlDbType.Bit))[color=darkred]
> > > > > > >
> > > > > > > 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 =[/color][/color]
> RegAddressTXT.Text[color=green][color=darkred]
> > > > > > >
> > > > > > > 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 = "* error while uploading original[/color][/color][/color]
data.<br[color=blue][color=green]
> > />"[color=darkred]
> > > &
> > > > > > > objError.Message & objError.Source
> > > > > > >
> > > > > > > Exit Sub
> > > > > > >
> > > > > > > Finally
> > > > > > >
> > > > > > > If dbConn.State = ConnectionState.Open Then
> > > > > > >
> > > > > > > dbConn.Close()
> > > > > > >
> > > > > > > End If
> > > > > > >
> > > > > > > End Try
> > > > > > >
> > > > > > > End Sub
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > End Class
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >[/color]
> >[/color]
>
> --------------------------------------------------------------------------[color=green][color=darkred]
> > > > > > --
> > > > > > > ---
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

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" <gee@iamtiredofspam.com> wrote in message
news:ubdpqcU6DHA.1852@TK2MSFTNGP10.phx.gbl...[color=blue]
> 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" <carvalho2181@rogers.com> wrote in message
> news:3QgTb.92854$9Ce1.45858@news04.bloor.is.net.ca ble.rogers.com...[color=green]
> > tried that.. really wished it worked.. gave me this.
> >
> > * error while uploading original data. String or binary data
> > would be truncated.The statement has been terminated..Net SqlClient Data
> > Provider
> >
> >
> > "Tom Leylan" <gee@iamtiredofspam.com> wrote in message
> > news:%23WvsoFS6DHA.2488@TK2MSFTNGP09.phx.gbl...[color=darkred]
> > > Bryan,
> > >
> > > Try Convert.ToBoolean(1) on your assignment. If the error is being[/color][/color]
> caused[color=green][color=darkred]
> > > by the bit field I assume it is because an integer value 1 won't fit.
> > >
> > > Tom
> > >
> > > "Bryan" <carvalho2181@rogers.com> wrote...
> > >
> > > > cmd.Parameters.Item("@login_status").Value = 1
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

"Bryan" <carvalho2181@rogers.com> wrote in message news:<5ydTb.90473$9Ce1.32801@news04.bloor.is.net.c able.rogers.com>...[color=blue]
> hello all...
>
> im trying to add a record to an sql db on ms sql server 2000, using vb.net.
>[/color]

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

adding record to sql db

re: adding record to sql db

re: adding record to sql db

re: adding record to sql db

re: adding record to sql db

re: adding record to sql db

re: adding record to sql db

re: adding record to sql db

re: adding record to sql db

re: adding record to sql db

re: adding record to sql db

re: adding record to sql db

re: adding record to sql db

re: adding record to sql db

re: adding record to sql db

re: adding record to sql db

re: adding record to sql db

re: adding record to sql db

re: adding record to sql db

re: adding record to sql db

re: adding record to sql db

Similar Visual Basic .NET bytes

/bytes/development

/bytes/it

/bytes/about