hello all...
im trying to add a record to an sql db on ms sql server 2000, using vb.net.
seems to be working.. except for one thing, one of the columns in the
database is a bit datatype, and though i get no syntax errors when
compiling, i get an error indicated that the data would be truncated. the
field is login_status.
ive tried in quotes and not, giving it an integer variable with the number 1
in it, true/false statements.. ive tried a bunch of stuff... has me real
stumped..
have any ideas?
thanks
here is the error it spits out.
<b>* error while uploading original data</b>.<br />String or binary data
would be truncated.The statement has been terminated..Net SqlClient Data
Provider
--------------------------------------------------------------------------
here is the code:
--------------------------------------------------------------------------
Imports System.Data
Imports System.Data.SqlClient
Public Class WebForm1
Inherits System.Web.UI.Page
Public Sub Register_User(ByVal sender As Object, ByVal E As EventArgs)
Dim dbConnStr As String = "user id=**(blanked for
security)***;password=**(blanked for
security)**;database=BCdb;server=localhost"
Dim dbConn As New System.Data.SqlClient.SqlConnection(dbConnStr)
Try
Dim cmd As New SqlCommand("addCustomerSQL", dbConn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@username", SqlDbType.Char, 19))
cmd.Parameters.Add(New SqlParameter("@fullname", SqlDbType.VarChar, 27))
cmd.Parameters.Add(New SqlParameter("@password", SqlDbType.Char, 7))
cmd.Parameters.Add(New SqlParameter("@credit_card_nbr", SqlDbType.Char, 18))
cmd.Parameters.Add(New SqlParameter("@credit_card_expiry_date",
SqlDbType.DateTime))
cmd.Parameters.Add(New SqlParameter("@mailing_address", SqlDbType.VarChar,
100))
cmd.Parameters.Add(New SqlParameter("@phone_nbr", SqlDbType.Char, 14))
cmd.Parameters.Add(New SqlParameter("@email_address", SqlDbType.Char, 50))
cmd.Parameters.Add(New SqlParameter("@login_status", SqlDbType.Bit))
cmd.Parameters.Add(New SqlParameter("@credit_card_name", SqlDbType.Char,
27))
cmd.Parameters.Item("@username").Value = RegUsrnameTXT.Text
cmd.Parameters.Item("@fullname").Value = RegFullnameTXT.Text
cmd.Parameters.Item("@password").Value = RegPasswordTXT.Text
cmd.Parameters.Item("@credit_card_nbr").Value = RegCreditcardnumberTXT.Text
cmd.Parameters.Item("@credit_card_expiry_date").Va lue =
RegCreditcardexpiryTXT.Text
cmd.Parameters.Item("@mailing_address").Value = RegAddressTXT.Text
cmd.Parameters.Item("@email_address").Value = RegEmailTXT.Text
cmd.Parameters.Item("@phone_nbr").Value = RegPhoneTXT.Text
cmd.Parameters.Item("@login_status").Value = 1
cmd.Parameters.Item("@credit_card_name").Value = RegCreditcardnameTXT.Text
'opent the connection to the database
dbConn.Open()
'execute sql statement
cmd.ExecuteNonQuery()
dbConn.Close()
Catch objError As Exception
dberror.Text = "<b>* error while uploading original data</b>.<br />" &
objError.Message & objError.Source
Exit Sub
Finally
If dbConn.State = ConnectionState.Open Then
dbConn.Close()
End If
End Try
End Sub
End Class
----------------------------------------------------------------------------
--- 20 2068
did you try to just use the integer 1 or 0 with no quotes?
"Bryan" <ca**********@rogers.com> wrote in message
news:5y********************@news04.bloor.is.net.ca ble.rogers.com... hello all...
im trying to add a record to an sql db on ms sql server 2000, using
vb.net. seems to be working.. except for one thing, one of the columns in the database is a bit datatype, and though i get no syntax errors when compiling, i get an error indicated that the data would be truncated. the field is login_status.
ive tried in quotes and not, giving it an integer variable with the number
1 in it, true/false statements.. ive tried a bunch of stuff... has me real stumped..
have any ideas?
thanks
here is the error it spits out.
<b>* error while uploading original data</b>.<br />String or binary data would be truncated.The statement has been terminated..Net SqlClient Data Provider
-------------------------------------------------------------------------- here is the code: -------------------------------------------------------------------------- Imports System.Data
Imports System.Data.SqlClient
Public Class WebForm1
Inherits System.Web.UI.Page
Public Sub Register_User(ByVal sender As Object, ByVal E As EventArgs)
Dim dbConnStr As String = "user id=**(blanked for security)***;password=**(blanked for security)**;database=BCdb;server=localhost"
Dim dbConn As New System.Data.SqlClient.SqlConnection(dbConnStr)
Try
Dim cmd As New SqlCommand("addCustomerSQL", dbConn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@username", SqlDbType.Char, 19))
cmd.Parameters.Add(New SqlParameter("@fullname", SqlDbType.VarChar, 27))
cmd.Parameters.Add(New SqlParameter("@password", SqlDbType.Char, 7))
cmd.Parameters.Add(New SqlParameter("@credit_card_nbr", SqlDbType.Char,
18)) cmd.Parameters.Add(New SqlParameter("@credit_card_expiry_date", SqlDbType.DateTime))
cmd.Parameters.Add(New SqlParameter("@mailing_address", SqlDbType.VarChar, 100))
cmd.Parameters.Add(New SqlParameter("@phone_nbr", SqlDbType.Char, 14))
cmd.Parameters.Add(New SqlParameter("@email_address", SqlDbType.Char, 50))
cmd.Parameters.Add(New SqlParameter("@login_status", SqlDbType.Bit))
cmd.Parameters.Add(New SqlParameter("@credit_card_name", SqlDbType.Char, 27))
cmd.Parameters.Item("@username").Value = RegUsrnameTXT.Text
cmd.Parameters.Item("@fullname").Value = RegFullnameTXT.Text
cmd.Parameters.Item("@password").Value = RegPasswordTXT.Text
cmd.Parameters.Item("@credit_card_nbr").Value =
RegCreditcardnumberTXT.Text cmd.Parameters.Item("@credit_card_expiry_date").Va lue = RegCreditcardexpiryTXT.Text
cmd.Parameters.Item("@mailing_address").Value = RegAddressTXT.Text
cmd.Parameters.Item("@email_address").Value = RegEmailTXT.Text
cmd.Parameters.Item("@phone_nbr").Value = RegPhoneTXT.Text
cmd.Parameters.Item("@login_status").Value = 1
cmd.Parameters.Item("@credit_card_name").Value = RegCreditcardnameTXT.Text
'opent the connection to the database
dbConn.Open()
'execute sql statement
cmd.ExecuteNonQuery()
dbConn.Close()
Catch objError As Exception
dberror.Text = "<b>* error while uploading original data</b>.<br />" & objError.Message & objError.Source
Exit Sub
Finally
If dbConn.State = ConnectionState.Open Then
dbConn.Close()
End If
End Try
End Sub End Class
--------------------------------------------------------------------------
-- ---
did you try to just use the integer 1 or 0 with no quotes?
"Bryan" <ca**********@rogers.com> wrote in message
news:5y********************@news04.bloor.is.net.ca ble.rogers.com... hello all...
im trying to add a record to an sql db on ms sql server 2000, using
vb.net. seems to be working.. except for one thing, one of the columns in the database is a bit datatype, and though i get no syntax errors when compiling, i get an error indicated that the data would be truncated. the field is login_status.
ive tried in quotes and not, giving it an integer variable with the number
1 in it, true/false statements.. ive tried a bunch of stuff... has me real stumped..
have any ideas?
thanks
here is the error it spits out.
<b>* error while uploading original data</b>.<br />String or binary data would be truncated.The statement has been terminated..Net SqlClient Data Provider
-------------------------------------------------------------------------- here is the code: -------------------------------------------------------------------------- Imports System.Data
Imports System.Data.SqlClient
Public Class WebForm1
Inherits System.Web.UI.Page
Public Sub Register_User(ByVal sender As Object, ByVal E As EventArgs)
Dim dbConnStr As String = "user id=**(blanked for security)***;password=**(blanked for security)**;database=BCdb;server=localhost"
Dim dbConn As New System.Data.SqlClient.SqlConnection(dbConnStr)
Try
Dim cmd As New SqlCommand("addCustomerSQL", dbConn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@username", SqlDbType.Char, 19))
cmd.Parameters.Add(New SqlParameter("@fullname", SqlDbType.VarChar, 27))
cmd.Parameters.Add(New SqlParameter("@password", SqlDbType.Char, 7))
cmd.Parameters.Add(New SqlParameter("@credit_card_nbr", SqlDbType.Char,
18)) cmd.Parameters.Add(New SqlParameter("@credit_card_expiry_date", SqlDbType.DateTime))
cmd.Parameters.Add(New SqlParameter("@mailing_address", SqlDbType.VarChar, 100))
cmd.Parameters.Add(New SqlParameter("@phone_nbr", SqlDbType.Char, 14))
cmd.Parameters.Add(New SqlParameter("@email_address", SqlDbType.Char, 50))
cmd.Parameters.Add(New SqlParameter("@login_status", SqlDbType.Bit))
cmd.Parameters.Add(New SqlParameter("@credit_card_name", SqlDbType.Char, 27))
cmd.Parameters.Item("@username").Value = RegUsrnameTXT.Text
cmd.Parameters.Item("@fullname").Value = RegFullnameTXT.Text
cmd.Parameters.Item("@password").Value = RegPasswordTXT.Text
cmd.Parameters.Item("@credit_card_nbr").Value =
RegCreditcardnumberTXT.Text cmd.Parameters.Item("@credit_card_expiry_date").Va lue = RegCreditcardexpiryTXT.Text
cmd.Parameters.Item("@mailing_address").Value = RegAddressTXT.Text
cmd.Parameters.Item("@email_address").Value = RegEmailTXT.Text
cmd.Parameters.Item("@phone_nbr").Value = RegPhoneTXT.Text
cmd.Parameters.Item("@login_status").Value = 1
cmd.Parameters.Item("@credit_card_name").Value = RegCreditcardnameTXT.Text
'opent the connection to the database
dbConn.Open()
'execute sql statement
cmd.ExecuteNonQuery()
dbConn.Close()
Catch objError As Exception
dberror.Text = "<b>* error while uploading original data</b>.<br />" & objError.Message & objError.Source
Exit Sub
Finally
If dbConn.State = ConnectionState.Open Then
dbConn.Close()
End If
End Try
End Sub End Class
--------------------------------------------------------------------------
-- ---
Bryan:
What is the datatype of the field in SqlServer? Bit or Binary? The Param
type is specified as Bit but by the Exception message, I'm wondering if it's
not Binary in the DB. If so, I believe changing the db type in the client
code to SqlDbType.Binary or changing the data type server side to Bit may do
it for you.
HTH,
Bill
"Bryan" <ca**********@rogers.com> wrote in message
news:5y********************@news04.bloor.is.net.ca ble.rogers.com... hello all...
im trying to add a record to an sql db on ms sql server 2000, using
vb.net. seems to be working.. except for one thing, one of the columns in the database is a bit datatype, and though i get no syntax errors when compiling, i get an error indicated that the data would be truncated. the field is login_status.
ive tried in quotes and not, giving it an integer variable with the number
1 in it, true/false statements.. ive tried a bunch of stuff... has me real stumped..
have any ideas?
thanks
here is the error it spits out.
<b>* error while uploading original data</b>.<br />String or binary data would be truncated.The statement has been terminated..Net SqlClient Data Provider
-------------------------------------------------------------------------- here is the code: -------------------------------------------------------------------------- Imports System.Data
Imports System.Data.SqlClient
Public Class WebForm1
Inherits System.Web.UI.Page
Public Sub Register_User(ByVal sender As Object, ByVal E As EventArgs)
Dim dbConnStr As String = "user id=**(blanked for security)***;password=**(blanked for security)**;database=BCdb;server=localhost"
Dim dbConn As New System.Data.SqlClient.SqlConnection(dbConnStr)
Try
Dim cmd As New SqlCommand("addCustomerSQL", dbConn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@username", SqlDbType.Char, 19))
cmd.Parameters.Add(New SqlParameter("@fullname", SqlDbType.VarChar, 27))
cmd.Parameters.Add(New SqlParameter("@password", SqlDbType.Char, 7))
cmd.Parameters.Add(New SqlParameter("@credit_card_nbr", SqlDbType.Char,
18)) cmd.Parameters.Add(New SqlParameter("@credit_card_expiry_date", SqlDbType.DateTime))
cmd.Parameters.Add(New SqlParameter("@mailing_address", SqlDbType.VarChar, 100))
cmd.Parameters.Add(New SqlParameter("@phone_nbr", SqlDbType.Char, 14))
cmd.Parameters.Add(New SqlParameter("@email_address", SqlDbType.Char, 50))
cmd.Parameters.Add(New SqlParameter("@login_status", SqlDbType.Bit))
cmd.Parameters.Add(New SqlParameter("@credit_card_name", SqlDbType.Char, 27))
cmd.Parameters.Item("@username").Value = RegUsrnameTXT.Text
cmd.Parameters.Item("@fullname").Value = RegFullnameTXT.Text
cmd.Parameters.Item("@password").Value = RegPasswordTXT.Text
cmd.Parameters.Item("@credit_card_nbr").Value =
RegCreditcardnumberTXT.Text cmd.Parameters.Item("@credit_card_expiry_date").Va lue = RegCreditcardexpiryTXT.Text
cmd.Parameters.Item("@mailing_address").Value = RegAddressTXT.Text
cmd.Parameters.Item("@email_address").Value = RegEmailTXT.Text
cmd.Parameters.Item("@phone_nbr").Value = RegPhoneTXT.Text
cmd.Parameters.Item("@login_status").Value = 1
cmd.Parameters.Item("@credit_card_name").Value = RegCreditcardnameTXT.Text
'opent the connection to the database
dbConn.Open()
'execute sql statement
cmd.ExecuteNonQuery()
dbConn.Close()
Catch objError As Exception
dberror.Text = "<b>* error while uploading original data</b>.<br />" & objError.Message & objError.Source
Exit Sub
Finally
If dbConn.State = ConnectionState.Open Then
dbConn.Close()
End If
End Try
End Sub End Class
--------------------------------------------------------------------------
-- ---
Bryan:
What is the datatype of the field in SqlServer? Bit or Binary? The Param
type is specified as Bit but by the Exception message, I'm wondering if it's
not Binary in the DB. If so, I believe changing the db type in the client
code to SqlDbType.Binary or changing the data type server side to Bit may do
it for you.
HTH,
Bill
"Bryan" <ca**********@rogers.com> wrote in message
news:5y********************@news04.bloor.is.net.ca ble.rogers.com... hello all...
im trying to add a record to an sql db on ms sql server 2000, using
vb.net. seems to be working.. except for one thing, one of the columns in the database is a bit datatype, and though i get no syntax errors when compiling, i get an error indicated that the data would be truncated. the field is login_status.
ive tried in quotes and not, giving it an integer variable with the number
1 in it, true/false statements.. ive tried a bunch of stuff... has me real stumped..
have any ideas?
thanks
here is the error it spits out.
<b>* error while uploading original data</b>.<br />String or binary data would be truncated.The statement has been terminated..Net SqlClient Data Provider
-------------------------------------------------------------------------- here is the code: -------------------------------------------------------------------------- Imports System.Data
Imports System.Data.SqlClient
Public Class WebForm1
Inherits System.Web.UI.Page
Public Sub Register_User(ByVal sender As Object, ByVal E As EventArgs)
Dim dbConnStr As String = "user id=**(blanked for security)***;password=**(blanked for security)**;database=BCdb;server=localhost"
Dim dbConn As New System.Data.SqlClient.SqlConnection(dbConnStr)
Try
Dim cmd As New SqlCommand("addCustomerSQL", dbConn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@username", SqlDbType.Char, 19))
cmd.Parameters.Add(New SqlParameter("@fullname", SqlDbType.VarChar, 27))
cmd.Parameters.Add(New SqlParameter("@password", SqlDbType.Char, 7))
cmd.Parameters.Add(New SqlParameter("@credit_card_nbr", SqlDbType.Char,
18)) cmd.Parameters.Add(New SqlParameter("@credit_card_expiry_date", SqlDbType.DateTime))
cmd.Parameters.Add(New SqlParameter("@mailing_address", SqlDbType.VarChar, 100))
cmd.Parameters.Add(New SqlParameter("@phone_nbr", SqlDbType.Char, 14))
cmd.Parameters.Add(New SqlParameter("@email_address", SqlDbType.Char, 50))
cmd.Parameters.Add(New SqlParameter("@login_status", SqlDbType.Bit))
cmd.Parameters.Add(New SqlParameter("@credit_card_name", SqlDbType.Char, 27))
cmd.Parameters.Item("@username").Value = RegUsrnameTXT.Text
cmd.Parameters.Item("@fullname").Value = RegFullnameTXT.Text
cmd.Parameters.Item("@password").Value = RegPasswordTXT.Text
cmd.Parameters.Item("@credit_card_nbr").Value =
RegCreditcardnumberTXT.Text cmd.Parameters.Item("@credit_card_expiry_date").Va lue = RegCreditcardexpiryTXT.Text
cmd.Parameters.Item("@mailing_address").Value = RegAddressTXT.Text
cmd.Parameters.Item("@email_address").Value = RegEmailTXT.Text
cmd.Parameters.Item("@phone_nbr").Value = RegPhoneTXT.Text
cmd.Parameters.Item("@login_status").Value = 1
cmd.Parameters.Item("@credit_card_name").Value = RegCreditcardnameTXT.Text
'opent the connection to the database
dbConn.Open()
'execute sql statement
cmd.ExecuteNonQuery()
dbConn.Close()
Catch objError As Exception
dberror.Text = "<b>* error while uploading original data</b>.<br />" & objError.Message & objError.Source
Exit Sub
Finally
If dbConn.State = ConnectionState.Open Then
dbConn.Close()
End If
End Try
End Sub End Class
--------------------------------------------------------------------------
-- ---
yes i tried with and without quotes.
i even tried creating an integer variable, assigning it the value 1 and
putting that variable in its place.. no go either.
thanks for the response,, have any other ideas?
cheers
bry
"Brian Henry" <brianiup[nospam]@adelphia.net> wrote in message
news:O9**************@TK2MSFTNGP09.phx.gbl... did you try to just use the integer 1 or 0 with no quotes?
"Bryan" <ca**********@rogers.com> wrote in message news:5y********************@news04.bloor.is.net.ca ble.rogers.com... hello all...
im trying to add a record to an sql db on ms sql server 2000, using vb.net. seems to be working.. except for one thing, one of the columns in the database is a bit datatype, and though i get no syntax errors when compiling, i get an error indicated that the data would be truncated.
the field is login_status.
ive tried in quotes and not, giving it an integer variable with the
number 1 in it, true/false statements.. ive tried a bunch of stuff... has me real stumped..
have any ideas?
thanks
here is the error it spits out.
<b>* error while uploading original data</b>.<br />String or binary data would be truncated.The statement has been terminated..Net SqlClient Data Provider
-------------------------------------------------------------------------- here is the code:
-------------------------------------------------------------------------- Imports System.Data
Imports System.Data.SqlClient
Public Class WebForm1
Inherits System.Web.UI.Page
Public Sub Register_User(ByVal sender As Object, ByVal E As EventArgs)
Dim dbConnStr As String = "user id=**(blanked for security)***;password=**(blanked for security)**;database=BCdb;server=localhost"
Dim dbConn As New System.Data.SqlClient.SqlConnection(dbConnStr)
Try
Dim cmd As New SqlCommand("addCustomerSQL", dbConn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@username", SqlDbType.Char, 19))
cmd.Parameters.Add(New SqlParameter("@fullname", SqlDbType.VarChar, 27))
cmd.Parameters.Add(New SqlParameter("@password", SqlDbType.Char, 7))
cmd.Parameters.Add(New SqlParameter("@credit_card_nbr", SqlDbType.Char, 18)) cmd.Parameters.Add(New SqlParameter("@credit_card_expiry_date", SqlDbType.DateTime))
cmd.Parameters.Add(New SqlParameter("@mailing_address",
SqlDbType.VarChar, 100))
cmd.Parameters.Add(New SqlParameter("@phone_nbr", SqlDbType.Char, 14))
cmd.Parameters.Add(New SqlParameter("@email_address", SqlDbType.Char,
50)) cmd.Parameters.Add(New SqlParameter("@login_status", SqlDbType.Bit))
cmd.Parameters.Add(New SqlParameter("@credit_card_name", SqlDbType.Char, 27))
cmd.Parameters.Item("@username").Value = RegUsrnameTXT.Text
cmd.Parameters.Item("@fullname").Value = RegFullnameTXT.Text
cmd.Parameters.Item("@password").Value = RegPasswordTXT.Text
cmd.Parameters.Item("@credit_card_nbr").Value = RegCreditcardnumberTXT.Text cmd.Parameters.Item("@credit_card_expiry_date").Va lue = RegCreditcardexpiryTXT.Text
cmd.Parameters.Item("@mailing_address").Value = RegAddressTXT.Text
cmd.Parameters.Item("@email_address").Value = RegEmailTXT.Text
cmd.Parameters.Item("@phone_nbr").Value = RegPhoneTXT.Text
cmd.Parameters.Item("@login_status").Value = 1
cmd.Parameters.Item("@credit_card_name").Value =
RegCreditcardnameTXT.Text 'opent the connection to the database
dbConn.Open()
'execute sql statement
cmd.ExecuteNonQuery()
dbConn.Close()
Catch objError As Exception
dberror.Text = "<b>* error while uploading original data</b>.<br />" & objError.Message & objError.Source
Exit Sub
Finally
If dbConn.State = ConnectionState.Open Then
dbConn.Close()
End If
End Try
End Sub End Class
-------------------------------------------------------------------------- -- ---
yes i tried with and without quotes.
i even tried creating an integer variable, assigning it the value 1 and
putting that variable in its place.. no go either.
thanks for the response,, have any other ideas?
cheers
bry
"Brian Henry" <brianiup[nospam]@adelphia.net> wrote in message
news:O9**************@TK2MSFTNGP09.phx.gbl... did you try to just use the integer 1 or 0 with no quotes?
"Bryan" <ca**********@rogers.com> wrote in message news:5y********************@news04.bloor.is.net.ca ble.rogers.com... hello all...
im trying to add a record to an sql db on ms sql server 2000, using vb.net. seems to be working.. except for one thing, one of the columns in the database is a bit datatype, and though i get no syntax errors when compiling, i get an error indicated that the data would be truncated.
the field is login_status.
ive tried in quotes and not, giving it an integer variable with the
number 1 in it, true/false statements.. ive tried a bunch of stuff... has me real stumped..
have any ideas?
thanks
here is the error it spits out.
<b>* error while uploading original data</b>.<br />String or binary data would be truncated.The statement has been terminated..Net SqlClient Data Provider
-------------------------------------------------------------------------- here is the code:
-------------------------------------------------------------------------- Imports System.Data
Imports System.Data.SqlClient
Public Class WebForm1
Inherits System.Web.UI.Page
Public Sub Register_User(ByVal sender As Object, ByVal E As EventArgs)
Dim dbConnStr As String = "user id=**(blanked for security)***;password=**(blanked for security)**;database=BCdb;server=localhost"
Dim dbConn As New System.Data.SqlClient.SqlConnection(dbConnStr)
Try
Dim cmd As New SqlCommand("addCustomerSQL", dbConn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@username", SqlDbType.Char, 19))
cmd.Parameters.Add(New SqlParameter("@fullname", SqlDbType.VarChar, 27))
cmd.Parameters.Add(New SqlParameter("@password", SqlDbType.Char, 7))
cmd.Parameters.Add(New SqlParameter("@credit_card_nbr", SqlDbType.Char, 18)) cmd.Parameters.Add(New SqlParameter("@credit_card_expiry_date", SqlDbType.DateTime))
cmd.Parameters.Add(New SqlParameter("@mailing_address",
SqlDbType.VarChar, 100))
cmd.Parameters.Add(New SqlParameter("@phone_nbr", SqlDbType.Char, 14))
cmd.Parameters.Add(New SqlParameter("@email_address", SqlDbType.Char,
50)) cmd.Parameters.Add(New SqlParameter("@login_status", SqlDbType.Bit))
cmd.Parameters.Add(New SqlParameter("@credit_card_name", SqlDbType.Char, 27))
cmd.Parameters.Item("@username").Value = RegUsrnameTXT.Text
cmd.Parameters.Item("@fullname").Value = RegFullnameTXT.Text
cmd.Parameters.Item("@password").Value = RegPasswordTXT.Text
cmd.Parameters.Item("@credit_card_nbr").Value = RegCreditcardnumberTXT.Text cmd.Parameters.Item("@credit_card_expiry_date").Va lue = RegCreditcardexpiryTXT.Text
cmd.Parameters.Item("@mailing_address").Value = RegAddressTXT.Text
cmd.Parameters.Item("@email_address").Value = RegEmailTXT.Text
cmd.Parameters.Item("@phone_nbr").Value = RegPhoneTXT.Text
cmd.Parameters.Item("@login_status").Value = 1
cmd.Parameters.Item("@credit_card_name").Value =
RegCreditcardnameTXT.Text 'opent the connection to the database
dbConn.Open()
'execute sql statement
cmd.ExecuteNonQuery()
dbConn.Close()
Catch objError As Exception
dberror.Text = "<b>* error while uploading original data</b>.<br />" & objError.Message & objError.Source
Exit Sub
Finally
If dbConn.State = ConnectionState.Open Then
dbConn.Close()
End If
End Try
End Sub End Class
-------------------------------------------------------------------------- -- ---
I noticed that also, yet the datatypes are teh same in sql and in teh vb
code. this is driving me nuts.
thanks again.
bry
"William Ryan eMVP" <bi**@NoSp4m.devbuzz.com> wrote in message
news:eA**************@TK2MSFTNGP11.phx.gbl... Bryan:
What is the datatype of the field in SqlServer? Bit or Binary? The Param type is specified as Bit but by the Exception message, I'm wondering if
it's not Binary in the DB. If so, I believe changing the db type in the client code to SqlDbType.Binary or changing the data type server side to Bit may
do it for you.
HTH,
Bill "Bryan" <ca**********@rogers.com> wrote in message news:5y********************@news04.bloor.is.net.ca ble.rogers.com... hello all...
im trying to add a record to an sql db on ms sql server 2000, using vb.net. seems to be working.. except for one thing, one of the columns in the database is a bit datatype, and though i get no syntax errors when compiling, i get an error indicated that the data would be truncated.
the field is login_status.
ive tried in quotes and not, giving it an integer variable with the
number 1 in it, true/false statements.. ive tried a bunch of stuff... has me real stumped..
have any ideas?
thanks
here is the error it spits out.
<b>* error while uploading original data</b>.<br />String or binary data would be truncated.The statement has been terminated..Net SqlClient Data Provider
-------------------------------------------------------------------------- here is the code:
-------------------------------------------------------------------------- Imports System.Data
Imports System.Data.SqlClient
Public Class WebForm1
Inherits System.Web.UI.Page
Public Sub Register_User(ByVal sender As Object, ByVal E As EventArgs)
Dim dbConnStr As String = "user id=**(blanked for security)***;password=**(blanked for security)**;database=BCdb;server=localhost"
Dim dbConn As New System.Data.SqlClient.SqlConnection(dbConnStr)
Try
Dim cmd As New SqlCommand("addCustomerSQL", dbConn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@username", SqlDbType.Char, 19))
cmd.Parameters.Add(New SqlParameter("@fullname", SqlDbType.VarChar, 27))
cmd.Parameters.Add(New SqlParameter("@password", SqlDbType.Char, 7))
cmd.Parameters.Add(New SqlParameter("@credit_card_nbr", SqlDbType.Char, 18)) cmd.Parameters.Add(New SqlParameter("@credit_card_expiry_date", SqlDbType.DateTime))
cmd.Parameters.Add(New SqlParameter("@mailing_address",
SqlDbType.VarChar, 100))
cmd.Parameters.Add(New SqlParameter("@phone_nbr", SqlDbType.Char, 14))
cmd.Parameters.Add(New SqlParameter("@email_address", SqlDbType.Char,
50)) cmd.Parameters.Add(New SqlParameter("@login_status", SqlDbType.Bit))
cmd.Parameters.Add(New SqlParameter("@credit_card_name", SqlDbType.Char, 27))
cmd.Parameters.Item("@username").Value = RegUsrnameTXT.Text
cmd.Parameters.Item("@fullname").Value = RegFullnameTXT.Text
cmd.Parameters.Item("@password").Value = RegPasswordTXT.Text
cmd.Parameters.Item("@credit_card_nbr").Value = RegCreditcardnumberTXT.Text cmd.Parameters.Item("@credit_card_expiry_date").Va lue = RegCreditcardexpiryTXT.Text
cmd.Parameters.Item("@mailing_address").Value = RegAddressTXT.Text
cmd.Parameters.Item("@email_address").Value = RegEmailTXT.Text
cmd.Parameters.Item("@phone_nbr").Value = RegPhoneTXT.Text
cmd.Parameters.Item("@login_status").Value = 1
cmd.Parameters.Item("@credit_card_name").Value =
RegCreditcardnameTXT.Text 'opent the connection to the database
dbConn.Open()
'execute sql statement
cmd.ExecuteNonQuery()
dbConn.Close()
Catch objError As Exception
dberror.Text = "<b>* error while uploading original data</b>.<br />" & objError.Message & objError.Source
Exit Sub
Finally
If dbConn.State = ConnectionState.Open Then
dbConn.Close()
End If
End Try
End Sub End Class
-------------------------------------------------------------------------- -- ---
I noticed that also, yet the datatypes are teh same in sql and in teh vb
code. this is driving me nuts.
thanks again.
bry
"William Ryan eMVP" <bi**@NoSp4m.devbuzz.com> wrote in message
news:eA**************@TK2MSFTNGP11.phx.gbl... Bryan:
What is the datatype of the field in SqlServer? Bit or Binary? The Param type is specified as Bit but by the Exception message, I'm wondering if
it's not Binary in the DB. If so, I believe changing the db type in the client code to SqlDbType.Binary or changing the data type server side to Bit may
do it for you.
HTH,
Bill "Bryan" <ca**********@rogers.com> wrote in message news:5y********************@news04.bloor.is.net.ca ble.rogers.com... hello all...
im trying to add a record to an sql db on ms sql server 2000, using vb.net. seems to be working.. except for one thing, one of the columns in the database is a bit datatype, and though i get no syntax errors when compiling, i get an error indicated that the data would be truncated.
the field is login_status.
ive tried in quotes and not, giving it an integer variable with the
number 1 in it, true/false statements.. ive tried a bunch of stuff... has me real stumped..
have any ideas?
thanks
here is the error it spits out.
<b>* error while uploading original data</b>.<br />String or binary data would be truncated.The statement has been terminated..Net SqlClient Data Provider
-------------------------------------------------------------------------- here is the code:
-------------------------------------------------------------------------- Imports System.Data
Imports System.Data.SqlClient
Public Class WebForm1
Inherits System.Web.UI.Page
Public Sub Register_User(ByVal sender As Object, ByVal E As EventArgs)
Dim dbConnStr As String = "user id=**(blanked for security)***;password=**(blanked for security)**;database=BCdb;server=localhost"
Dim dbConn As New System.Data.SqlClient.SqlConnection(dbConnStr)
Try
Dim cmd As New SqlCommand("addCustomerSQL", dbConn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@username", SqlDbType.Char, 19))
cmd.Parameters.Add(New SqlParameter("@fullname", SqlDbType.VarChar, 27))
cmd.Parameters.Add(New SqlParameter("@password", SqlDbType.Char, 7))
cmd.Parameters.Add(New SqlParameter("@credit_card_nbr", SqlDbType.Char, 18)) cmd.Parameters.Add(New SqlParameter("@credit_card_expiry_date", SqlDbType.DateTime))
cmd.Parameters.Add(New SqlParameter("@mailing_address",
SqlDbType.VarChar, 100))
cmd.Parameters.Add(New SqlParameter("@phone_nbr", SqlDbType.Char, 14))
cmd.Parameters.Add(New SqlParameter("@email_address", SqlDbType.Char,
50)) cmd.Parameters.Add(New SqlParameter("@login_status", SqlDbType.Bit))
cmd.Parameters.Add(New SqlParameter("@credit_card_name", SqlDbType.Char, 27))
cmd.Parameters.Item("@username").Value = RegUsrnameTXT.Text
cmd.Parameters.Item("@fullname").Value = RegFullnameTXT.Text
cmd.Parameters.Item("@password").Value = RegPasswordTXT.Text
cmd.Parameters.Item("@credit_card_nbr").Value = RegCreditcardnumberTXT.Text cmd.Parameters.Item("@credit_card_expiry_date").Va lue = RegCreditcardexpiryTXT.Text
cmd.Parameters.Item("@mailing_address").Value = RegAddressTXT.Text
cmd.Parameters.Item("@email_address").Value = RegEmailTXT.Text
cmd.Parameters.Item("@phone_nbr").Value = RegPhoneTXT.Text
cmd.Parameters.Item("@login_status").Value = 1
cmd.Parameters.Item("@credit_card_name").Value =
RegCreditcardnameTXT.Text 'opent the connection to the database
dbConn.Open()
'execute sql statement
cmd.ExecuteNonQuery()
dbConn.Close()
Catch objError As Exception
dberror.Text = "<b>* error while uploading original data</b>.<br />" & objError.Message & objError.Source
Exit Sub
Finally
If dbConn.State = ConnectionState.Open Then
dbConn.Close()
End If
End Try
End Sub End Class
-------------------------------------------------------------------------- -- ---
Hmm, do you have any binary fields in the DB? I'm hesitant to admit this,
but I had this problem once and I realized I had defined a Varchar field to
VarBinary and that was ultimately the cause.
Can you post the table script and the SQL Statement you are using? I'll be
glad to create a table and give it a try...
Bill
"Bryan" <ca**********@rogers.com> wrote in message
news:Ge********************@news04.bloor.is.net.ca ble.rogers.com... I noticed that also, yet the datatypes are teh same in sql and in teh vb code. this is driving me nuts.
thanks again. bry
"William Ryan eMVP" <bi**@NoSp4m.devbuzz.com> wrote in message news:eA**************@TK2MSFTNGP11.phx.gbl... Bryan:
What is the datatype of the field in SqlServer? Bit or Binary? The
Param type is specified as Bit but by the Exception message, I'm wondering if it's not Binary in the DB. If so, I believe changing the db type in the
client code to SqlDbType.Binary or changing the data type server side to Bit
may do it for you.
HTH,
Bill "Bryan" <ca**********@rogers.com> wrote in message news:5y********************@news04.bloor.is.net.ca ble.rogers.com... hello all...
im trying to add a record to an sql db on ms sql server 2000, using vb.net. seems to be working.. except for one thing, one of the columns in the database is a bit datatype, and though i get no syntax errors when compiling, i get an error indicated that the data would be truncated.
the field is login_status.
ive tried in quotes and not, giving it an integer variable with the number 1 in it, true/false statements.. ive tried a bunch of stuff... has me
real stumped..
have any ideas?
thanks
here is the error it spits out.
<b>* error while uploading original data</b>.<br />String or binary
data would be truncated.The statement has been terminated..Net SqlClient
Data Provider
-------------------------------------------------------------------------- here is the code:
-------------------------------------------------------------------------- Imports System.Data
Imports System.Data.SqlClient
Public Class WebForm1
Inherits System.Web.UI.Page
Public Sub Register_User(ByVal sender As Object, ByVal E As EventArgs)
Dim dbConnStr As String = "user id=**(blanked for security)***;password=**(blanked for security)**;database=BCdb;server=localhost"
Dim dbConn As New System.Data.SqlClient.SqlConnection(dbConnStr)
Try
Dim cmd As New SqlCommand("addCustomerSQL", dbConn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@username", SqlDbType.Char, 19))
cmd.Parameters.Add(New SqlParameter("@fullname", SqlDbType.VarChar,
27)) cmd.Parameters.Add(New SqlParameter("@password", SqlDbType.Char, 7))
cmd.Parameters.Add(New SqlParameter("@credit_card_nbr",
SqlDbType.Char, 18)) cmd.Parameters.Add(New SqlParameter("@credit_card_expiry_date", SqlDbType.DateTime))
cmd.Parameters.Add(New SqlParameter("@mailing_address", SqlDbType.VarChar, 100))
cmd.Parameters.Add(New SqlParameter("@phone_nbr", SqlDbType.Char, 14))
cmd.Parameters.Add(New SqlParameter("@email_address", SqlDbType.Char, 50)) cmd.Parameters.Add(New SqlParameter("@login_status", SqlDbType.Bit))
cmd.Parameters.Add(New SqlParameter("@credit_card_name",
SqlDbType.Char, 27))
cmd.Parameters.Item("@username").Value = RegUsrnameTXT.Text
cmd.Parameters.Item("@fullname").Value = RegFullnameTXT.Text
cmd.Parameters.Item("@password").Value = RegPasswordTXT.Text
cmd.Parameters.Item("@credit_card_nbr").Value = RegCreditcardnumberTXT.Text cmd.Parameters.Item("@credit_card_expiry_date").Va lue = RegCreditcardexpiryTXT.Text
cmd.Parameters.Item("@mailing_address").Value = RegAddressTXT.Text
cmd.Parameters.Item("@email_address").Value = RegEmailTXT.Text
cmd.Parameters.Item("@phone_nbr").Value = RegPhoneTXT.Text
cmd.Parameters.Item("@login_status").Value = 1
cmd.Parameters.Item("@credit_card_name").Value =
RegCreditcardnameTXT.Text 'opent the connection to the database
dbConn.Open()
'execute sql statement
cmd.ExecuteNonQuery()
dbConn.Close()
Catch objError As Exception
dberror.Text = "<b>* error while uploading original data</b>.<br />" & objError.Message & objError.Source
Exit Sub
Finally
If dbConn.State = ConnectionState.Open Then
dbConn.Close()
End If
End Try
End Sub End Class
-------------------------------------------------------------------------- -- ---
Hmm, do you have any binary fields in the DB? I'm hesitant to admit this,
but I had this problem once and I realized I had defined a Varchar field to
VarBinary and that was ultimately the cause.
Can you post the table script and the SQL Statement you are using? I'll be
glad to create a table and give it a try...
Bill
"Bryan" <ca**********@rogers.com> wrote in message
news:Ge********************@news04.bloor.is.net.ca ble.rogers.com... I noticed that also, yet the datatypes are teh same in sql and in teh vb code. this is driving me nuts.
thanks again. bry
"William Ryan eMVP" <bi**@NoSp4m.devbuzz.com> wrote in message news:eA**************@TK2MSFTNGP11.phx.gbl... Bryan:
What is the datatype of the field in SqlServer? Bit or Binary? The
Param type is specified as Bit but by the Exception message, I'm wondering if it's not Binary in the DB. If so, I believe changing the db type in the
client code to SqlDbType.Binary or changing the data type server side to Bit
may do it for you.
HTH,
Bill "Bryan" <ca**********@rogers.com> wrote in message news:5y********************@news04.bloor.is.net.ca ble.rogers.com... hello all...
im trying to add a record to an sql db on ms sql server 2000, using vb.net. seems to be working.. except for one thing, one of the columns in the database is a bit datatype, and though i get no syntax errors when compiling, i get an error indicated that the data would be truncated.
the field is login_status.
ive tried in quotes and not, giving it an integer variable with the number 1 in it, true/false statements.. ive tried a bunch of stuff... has me
real stumped..
have any ideas?
thanks
here is the error it spits out.
<b>* error while uploading original data</b>.<br />String or binary
data would be truncated.The statement has been terminated..Net SqlClient
Data Provider
-------------------------------------------------------------------------- here is the code:
-------------------------------------------------------------------------- Imports System.Data
Imports System.Data.SqlClient
Public Class WebForm1
Inherits System.Web.UI.Page
Public Sub Register_User(ByVal sender As Object, ByVal E As EventArgs)
Dim dbConnStr As String = "user id=**(blanked for security)***;password=**(blanked for security)**;database=BCdb;server=localhost"
Dim dbConn As New System.Data.SqlClient.SqlConnection(dbConnStr)
Try
Dim cmd As New SqlCommand("addCustomerSQL", dbConn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@username", SqlDbType.Char, 19))
cmd.Parameters.Add(New SqlParameter("@fullname", SqlDbType.VarChar,
27)) cmd.Parameters.Add(New SqlParameter("@password", SqlDbType.Char, 7))
cmd.Parameters.Add(New SqlParameter("@credit_card_nbr",
SqlDbType.Char, 18)) cmd.Parameters.Add(New SqlParameter("@credit_card_expiry_date", SqlDbType.DateTime))
cmd.Parameters.Add(New SqlParameter("@mailing_address", SqlDbType.VarChar, 100))
cmd.Parameters.Add(New SqlParameter("@phone_nbr", SqlDbType.Char, 14))
cmd.Parameters.Add(New SqlParameter("@email_address", SqlDbType.Char, 50)) cmd.Parameters.Add(New SqlParameter("@login_status", SqlDbType.Bit))
cmd.Parameters.Add(New SqlParameter("@credit_card_name",
SqlDbType.Char, 27))
cmd.Parameters.Item("@username").Value = RegUsrnameTXT.Text
cmd.Parameters.Item("@fullname").Value = RegFullnameTXT.Text
cmd.Parameters.Item("@password").Value = RegPasswordTXT.Text
cmd.Parameters.Item("@credit_card_nbr").Value = RegCreditcardnumberTXT.Text cmd.Parameters.Item("@credit_card_expiry_date").Va lue = RegCreditcardexpiryTXT.Text
cmd.Parameters.Item("@mailing_address").Value = RegAddressTXT.Text
cmd.Parameters.Item("@email_address").Value = RegEmailTXT.Text
cmd.Parameters.Item("@phone_nbr").Value = RegPhoneTXT.Text
cmd.Parameters.Item("@login_status").Value = 1
cmd.Parameters.Item("@credit_card_name").Value =
RegCreditcardnameTXT.Text 'opent the connection to the database
dbConn.Open()
'execute sql statement
cmd.ExecuteNonQuery()
dbConn.Close()
Catch objError As Exception
dberror.Text = "<b>* error while uploading original data</b>.<br />" & objError.Message & objError.Source
Exit Sub
Finally
If dbConn.State = ConnectionState.Open Then
dbConn.Close()
End If
End Try
End Sub End Class
-------------------------------------------------------------------------- -- ---
here is the sql script to create the database, at the end is the procedure
im using.
i very much appreciate the help...
bryan
-----------------------------------------------------
CREATE TABLE BCustomerCA (
username char(19) NOT NULL,
fullname varchar(27) NULL,
credit_card_nbr char(18) NULL,
password char(7) NULL,
credit_card_expiry_date datetime NULL,
mailing__address varchar(100) NULL,
phone_nbr char(14) NULL,
email_address char(50) NULL,
login_status bit NULL,
credit_card_name char(27) NULL
)
go
ALTER TABLE BCustomerCA
ADD PRIMARY KEY NONCLUSTERED (username)
go
CREATE TABLE BManufacturerCA (
manufacturer_id char(14) NOT NULL,
manufacturer_name varchar(50) NULL,
email_address char(50) NULL,
contact_name varchar(27) NULL,
phone_nbr char(14) NULL
)
go
ALTER TABLE BManufacturerCA
ADD PRIMARY KEY NONCLUSTERED (manufacturer_id)
go
CREATE TABLE BOrderItemCA (
part_id char(18) NOT NULL,
po_nbr char(18) NOT NULL,
item_qty int NULL
)
go
ALTER TABLE BOrderItemCA
ADD PRIMARY KEY NONCLUSTERED (part_id, po_nbr)
go
CREATE TABLE BPartCA (
part_id char(18) NOT NULL,
part_name varchar(50) NULL,
stock_size int NULL,
manufacturer_id char(14) NULL,
part_price decimal(10,2) NULL
)
go
ALTER TABLE BPartCA
ADD PRIMARY KEY NONCLUSTERED (part_id)
go
CREATE TABLE BPurchaseOrderCA (
po_nbr char(18) NOT NULL,
po_timetable datetime NULL,
isprocessed bit NULL,
username char(19) NULL
)
go
ALTER TABLE BPurchaseOrderCA
ADD PRIMARY KEY NONCLUSTERED (po_nbr)
go
CREATE TABLE BStaffCA (
username char(19) NOT NULL,
password char(7) NULL,
login_status bit NULL,
fullname varchar(27) NULL
)
go
ALTER TABLE BStaffCA
ADD PRIMARY KEY NONCLUSTERED (username)
go
ALTER TABLE BOrderItemCA
ADD FOREIGN KEY (po_nbr)
REFERENCES BPurchaseOrderCA
go
ALTER TABLE BOrderItemCA
ADD FOREIGN KEY (part_id)
REFERENCES BPartCA
go
ALTER TABLE BPartCA
ADD FOREIGN KEY (manufacturer_id)
REFERENCES BManufacturerCA
go
ALTER TABLE BPurchaseOrderCA
ADD FOREIGN KEY (username)
REFERENCES BCustomerCA
go
create trigger tD_BCustomerCA on BCustomerCA for DELETE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* DELETE trigger on BCustomerCA */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BCustomerCA R/5 BPurchaseOrderCA ON PARENT DELETE SET NULL */
update BPurchaseOrderCA
set
/* BPurchaseOrderCA.username = NULL */
BPurchaseOrderCA.username = NULL
from BPurchaseOrderCA,deleted
where
/* BPurchaseOrderCA.username = deleted.username */
BPurchaseOrderCA.username = deleted.username
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_BCustomerCA on BCustomerCA for UPDATE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* UPDATE trigger on BCustomerCA */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insusername char(19),
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BCustomerCA R/5 BPurchaseOrderCA ON PARENT UPDATE SET NULL */
if
/* update(username) */
update(username)
begin
update BPurchaseOrderCA
set
/* BPurchaseOrderCA.username = NULL */
BPurchaseOrderCA.username = NULL
from BPurchaseOrderCA,deleted
where
/* BPurchaseOrderCA.username = deleted.username */
BPurchaseOrderCA.username = deleted.username
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tD_BManufacturerCA on BManufacturerCA for DELETE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* DELETE trigger on BManufacturerCA */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BManufacturerCA R/4 BPartCA ON PARENT DELETE SET NULL */
update BPartCA
set
/* BPartCA.manufacturer_id = NULL */
BPartCA.manufacturer_id = NULL
from BPartCA,deleted
where
/* BPartCA.manufacturer_id = deleted.manufacturer_id */
BPartCA.manufacturer_id = deleted.manufacturer_id
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_BManufacturerCA on BManufacturerCA for UPDATE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* UPDATE trigger on BManufacturerCA */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insmanufacturer_id char(14),
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BManufacturerCA R/4 BPartCA ON PARENT UPDATE SET NULL */
if
/* update(manufacturer_id) */
update(manufacturer_id)
begin
update BPartCA
set
/* BPartCA.manufacturer_id = NULL */
BPartCA.manufacturer_id = NULL
from BPartCA,deleted
where
/* BPartCA.manufacturer_id = deleted.manufacturer_id */
BPartCA.manufacturer_id = deleted.manufacturer_id
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tI_BOrderItemCA on BOrderItemCA for INSERT as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* INSERT trigger on BOrderItemCA */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BPurchaseOrderCA R/3 BOrderItemCA ON CHILD INSERT RESTRICT */
if
/* update(po_nbr) */
update(po_nbr)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,BPurchaseOrderCA
where
/* inserted.po_nbr = BPurchaseOrderCA.po_nbr */
inserted.po_nbr = BPurchaseOrderCA.po_nbr
/* */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30002,
@errmsg = 'Cannot INSERT BOrderItemCA because BPurchaseOrderCA
does not exist.'
goto error
end
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BPartCA R/2 BOrderItemCA ON CHILD INSERT RESTRICT */
if
/* update(part_id) */
update(part_id)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,BPartCA
where
/* inserted.part_id = BPartCA.part_id */
inserted.part_id = BPartCA.part_id
/* */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30002,
@errmsg = 'Cannot INSERT BOrderItemCA because BPartCA does not
exist.'
goto error
end
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_BOrderItemCA on BOrderItemCA for UPDATE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* UPDATE trigger on BOrderItemCA */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@inspart_id char(18),
@inspo_nbr char(18),
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BPurchaseOrderCA R/3 BOrderItemCA ON CHILD UPDATE RESTRICT */
if
/* update(po_nbr) */
update(po_nbr)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,BPurchaseOrderCA
where
/* inserted.po_nbr = BPurchaseOrderCA.po_nbr */
inserted.po_nbr = BPurchaseOrderCA.po_nbr
/* */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30007,
@errmsg = 'Cannot UPDATE BOrderItemCA because BPurchaseOrderCA
does not exist.'
goto error
end
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BPartCA R/2 BOrderItemCA ON CHILD UPDATE RESTRICT */
if
/* update(part_id) */
update(part_id)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,BPartCA
where
/* inserted.part_id = BPartCA.part_id */
inserted.part_id = BPartCA.part_id
/* */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30007,
@errmsg = 'Cannot UPDATE BOrderItemCA because BPartCA does not
exist.'
goto error
end
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tD_BPartCA on BPartCA for DELETE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* DELETE trigger on BPartCA */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BPartCA R/2 BOrderItemCA ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,BOrderItemCA
where
/* BOrderItemCA.part_id = deleted.part_id */
BOrderItemCA.part_id = deleted.part_id
)
begin
select @errno = 30001,
@errmsg = 'Cannot DELETE BPartCA because BOrderItemCA exists.'
goto error
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tI_BPartCA on BPartCA for INSERT as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* INSERT trigger on BPartCA */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BManufacturerCA R/4 BPartCA ON CHILD INSERT SET NULL */
if
/* update(manufacturer_id) */
update(manufacturer_id)
begin
update BPartCA
set
/* BPartCA.manufacturer_id = NULL */
BPartCA.manufacturer_id = NULL
from BPartCA,inserted
where
/* */
not exists (
select * from BManufacturerCA
where
/* inserted.manufacturer_id = BManufacturerCA.manufacturer_id */
inserted.manufacturer_id = BManufacturerCA.manufacturer_id
)
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_BPartCA on BPartCA for UPDATE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* UPDATE trigger on BPartCA */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@inspart_id char(18),
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BPartCA R/2 BOrderItemCA ON PARENT UPDATE RESTRICT */
if
/* update(part_id) */
update(part_id)
begin
if exists (
select * from deleted,BOrderItemCA
where
/* BOrderItemCA.part_id = deleted.part_id */
BOrderItemCA.part_id = deleted.part_id
)
begin
select @errno = 30005,
@errmsg = 'Cannot UPDATE BPartCA because BOrderItemCA exists.'
goto error
end
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BManufacturerCA R/4 BPartCA ON CHILD UPDATE SET NULL */
if
/* update(manufacturer_id) */
update(manufacturer_id)
begin
update BPartCA
set
/* BPartCA.manufacturer_id = NULL */
BPartCA.manufacturer_id = NULL
from BPartCA,inserted
where
/* */
not exists (
select * from BManufacturerCA
where
/* inserted.manufacturer_id = BManufacturerCA.manufacturer_id */
inserted.manufacturer_id = BManufacturerCA.manufacturer_id
)
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tD_BPurchaseOrderCA on BPurchaseOrderCA for DELETE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* DELETE trigger on BPurchaseOrderCA */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BPurchaseOrderCA R/3 BOrderItemCA ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,BOrderItemCA
where
/* BOrderItemCA.po_nbr = deleted.po_nbr */
BOrderItemCA.po_nbr = deleted.po_nbr
)
begin
select @errno = 30001,
@errmsg = 'Cannot DELETE BPurchaseOrderCA because BOrderItemCA
exists.'
goto error
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tI_BPurchaseOrderCA on BPurchaseOrderCA for INSERT as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* INSERT trigger on BPurchaseOrderCA */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BCustomerCA R/5 BPurchaseOrderCA ON CHILD INSERT SET NULL */
if
/* update(username) */
update(username)
begin
update BPurchaseOrderCA
set
/* BPurchaseOrderCA.username = NULL */
BPurchaseOrderCA.username = NULL
from BPurchaseOrderCA,inserted
where
/* */
not exists (
select * from BCustomerCA
where
/* inserted.username = BCustomerCA.username */
inserted.username = BCustomerCA.username
)
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_BPurchaseOrderCA on BPurchaseOrderCA for UPDATE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* UPDATE trigger on BPurchaseOrderCA */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@inspo_nbr char(18),
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BPurchaseOrderCA R/3 BOrderItemCA ON PARENT UPDATE RESTRICT */
if
/* update(po_nbr) */
update(po_nbr)
begin
if exists (
select * from deleted,BOrderItemCA
where
/* BOrderItemCA.po_nbr = deleted.po_nbr */
BOrderItemCA.po_nbr = deleted.po_nbr
)
begin
select @errno = 30005,
@errmsg = 'Cannot UPDATE BPurchaseOrderCA because BOrderItemCA
exists.'
goto error
end
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BCustomerCA R/5 BPurchaseOrderCA ON CHILD UPDATE SET NULL */
if
/* update(username) */
update(username)
begin
update BPurchaseOrderCA
set
/* BPurchaseOrderCA.username = NULL */
BPurchaseOrderCA.username = NULL
from BPurchaseOrderCA,inserted
where
/* */
not exists (
select * from BCustomerCA
where
/* inserted.username = BCustomerCA.username */
inserted.username = BCustomerCA.username
)
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE addCustomerSQL
(
@username char(19),
@fullname varchar(27),
@password char(7),
@credit_card_nbr char(18),
@credit_card_expiry_date datetime,
@mailing_address varchar(100),
@phone_nbr char(14),
@email_address char(50),
@login_status bit,
@credit_card_name char(27)
)
AS
INSERT INTO BCustomerCA VALUES (@username , @fullname, @password,
@credit_card_nbr, @credit_card_expiry_date, @mailing_address, @phone_nbr,
@email_address, @login_status, @credit_card_name )
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------------------------------
"William Ryan eMVP" <bi**@NoSp4m.devbuzz.com> wrote in message
news:Or***************@TK2MSFTNGP12.phx.gbl... Hmm, do you have any binary fields in the DB? I'm hesitant to admit this, but I had this problem once and I realized I had defined a Varchar field
to VarBinary and that was ultimately the cause.
Can you post the table script and the SQL Statement you are using? I'll
be glad to create a table and give it a try...
Bill "Bryan" <ca**********@rogers.com> wrote in message news:Ge********************@news04.bloor.is.net.ca ble.rogers.com... I noticed that also, yet the datatypes are teh same in sql and in teh
vb code. this is driving me nuts.
thanks again. bry
"William Ryan eMVP" <bi**@NoSp4m.devbuzz.com> wrote in message news:eA**************@TK2MSFTNGP11.phx.gbl... Bryan:
What is the datatype of the field in SqlServer? Bit or Binary? The Param type is specified as Bit but by the Exception message, I'm wondering
if it's not Binary in the DB. If so, I believe changing the db type in the client code to SqlDbType.Binary or changing the data type server side to Bit may do it for you.
HTH,
Bill "Bryan" <ca**********@rogers.com> wrote in message news:5y********************@news04.bloor.is.net.ca ble.rogers.com... > hello all... > > im trying to add a record to an sql db on ms sql server 2000, using vb.net. > > seems to be working.. except for one thing, one of the columns in
the > database is a bit datatype, and though i get no syntax errors when > compiling, i get an error indicated that the data would be
truncated. the > field is login_status. > > ive tried in quotes and not, giving it an integer variable with the number 1 > in it, true/false statements.. ive tried a bunch of stuff... has me real > stumped.. > > have any ideas? > > thanks > > > here is the error it spits out. > > <b>* error while uploading original data</b>.<br />String or binary data > would be truncated.The statement has been terminated..Net SqlClient Data > Provider > > > >
-------------------------------------------------------------------------- > here is the code:
-------------------------------------------------------------------------- > Imports System.Data > > Imports System.Data.SqlClient > > > > > > Public Class WebForm1 > > Inherits System.Web.UI.Page > > > > > > > > > > > Public Sub Register_User(ByVal sender As Object, ByVal E As
EventArgs) > > Dim dbConnStr As String = "user id=**(blanked for > security)***;password=**(blanked for > security)**;database=BCdb;server=localhost" > > Dim dbConn As New System.Data.SqlClient.SqlConnection(dbConnStr) > > > > > > > > Try > > Dim cmd As New SqlCommand("addCustomerSQL", dbConn) > > cmd.CommandType = CommandType.StoredProcedure > > cmd.Parameters.Add(New SqlParameter("@username", SqlDbType.Char,
19)) > > cmd.Parameters.Add(New SqlParameter("@fullname", SqlDbType.VarChar,
27)) > > cmd.Parameters.Add(New SqlParameter("@password", SqlDbType.Char, 7)) > > cmd.Parameters.Add(New SqlParameter("@credit_card_nbr", SqlDbType.Char, 18)) > > cmd.Parameters.Add(New SqlParameter("@credit_card_expiry_date", > SqlDbType.DateTime)) > > cmd.Parameters.Add(New SqlParameter("@mailing_address", SqlDbType.VarChar, > 100)) > > cmd.Parameters.Add(New SqlParameter("@phone_nbr", SqlDbType.Char,
14)) > > cmd.Parameters.Add(New SqlParameter("@email_address",
SqlDbType.Char, 50)) > > cmd.Parameters.Add(New SqlParameter("@login_status", SqlDbType.Bit)) > > cmd.Parameters.Add(New SqlParameter("@credit_card_name", SqlDbType.Char, > 27)) > > cmd.Parameters.Item("@username").Value = RegUsrnameTXT.Text > > cmd.Parameters.Item("@fullname").Value = RegFullnameTXT.Text > > cmd.Parameters.Item("@password").Value = RegPasswordTXT.Text > > cmd.Parameters.Item("@credit_card_nbr").Value = RegCreditcardnumberTXT.Text > > cmd.Parameters.Item("@credit_card_expiry_date").Va lue = > RegCreditcardexpiryTXT.Text > > cmd.Parameters.Item("@mailing_address").Value = RegAddressTXT.Text > > cmd.Parameters.Item("@email_address").Value = RegEmailTXT.Text > > cmd.Parameters.Item("@phone_nbr").Value = RegPhoneTXT.Text > > cmd.Parameters.Item("@login_status").Value = 1 > > cmd.Parameters.Item("@credit_card_name").Value = RegCreditcardnameTXT.Text > > 'opent the connection to the database > > dbConn.Open() > > 'execute sql statement > > cmd.ExecuteNonQuery() > > dbConn.Close() > > Catch objError As Exception > > dberror.Text = "<b>* error while uploading original data</b>.<br />"
& > objError.Message & objError.Source > > Exit Sub > > Finally > > If dbConn.State = ConnectionState.Open Then > > dbConn.Close() > > End If > > End Try > > End Sub > > > > End Class >
-------------------------------------------------------------------------- -- > --- > > >
here is the sql script to create the database, at the end is the procedure
im using.
i very much appreciate the help...
bryan
-----------------------------------------------------
CREATE TABLE BCustomerCA (
username char(19) NOT NULL,
fullname varchar(27) NULL,
credit_card_nbr char(18) NULL,
password char(7) NULL,
credit_card_expiry_date datetime NULL,
mailing__address varchar(100) NULL,
phone_nbr char(14) NULL,
email_address char(50) NULL,
login_status bit NULL,
credit_card_name char(27) NULL
)
go
ALTER TABLE BCustomerCA
ADD PRIMARY KEY NONCLUSTERED (username)
go
CREATE TABLE BManufacturerCA (
manufacturer_id char(14) NOT NULL,
manufacturer_name varchar(50) NULL,
email_address char(50) NULL,
contact_name varchar(27) NULL,
phone_nbr char(14) NULL
)
go
ALTER TABLE BManufacturerCA
ADD PRIMARY KEY NONCLUSTERED (manufacturer_id)
go
CREATE TABLE BOrderItemCA (
part_id char(18) NOT NULL,
po_nbr char(18) NOT NULL,
item_qty int NULL
)
go
ALTER TABLE BOrderItemCA
ADD PRIMARY KEY NONCLUSTERED (part_id, po_nbr)
go
CREATE TABLE BPartCA (
part_id char(18) NOT NULL,
part_name varchar(50) NULL,
stock_size int NULL,
manufacturer_id char(14) NULL,
part_price decimal(10,2) NULL
)
go
ALTER TABLE BPartCA
ADD PRIMARY KEY NONCLUSTERED (part_id)
go
CREATE TABLE BPurchaseOrderCA (
po_nbr char(18) NOT NULL,
po_timetable datetime NULL,
isprocessed bit NULL,
username char(19) NULL
)
go
ALTER TABLE BPurchaseOrderCA
ADD PRIMARY KEY NONCLUSTERED (po_nbr)
go
CREATE TABLE BStaffCA (
username char(19) NOT NULL,
password char(7) NULL,
login_status bit NULL,
fullname varchar(27) NULL
)
go
ALTER TABLE BStaffCA
ADD PRIMARY KEY NONCLUSTERED (username)
go
ALTER TABLE BOrderItemCA
ADD FOREIGN KEY (po_nbr)
REFERENCES BPurchaseOrderCA
go
ALTER TABLE BOrderItemCA
ADD FOREIGN KEY (part_id)
REFERENCES BPartCA
go
ALTER TABLE BPartCA
ADD FOREIGN KEY (manufacturer_id)
REFERENCES BManufacturerCA
go
ALTER TABLE BPurchaseOrderCA
ADD FOREIGN KEY (username)
REFERENCES BCustomerCA
go
create trigger tD_BCustomerCA on BCustomerCA for DELETE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* DELETE trigger on BCustomerCA */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BCustomerCA R/5 BPurchaseOrderCA ON PARENT DELETE SET NULL */
update BPurchaseOrderCA
set
/* BPurchaseOrderCA.username = NULL */
BPurchaseOrderCA.username = NULL
from BPurchaseOrderCA,deleted
where
/* BPurchaseOrderCA.username = deleted.username */
BPurchaseOrderCA.username = deleted.username
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_BCustomerCA on BCustomerCA for UPDATE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* UPDATE trigger on BCustomerCA */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insusername char(19),
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BCustomerCA R/5 BPurchaseOrderCA ON PARENT UPDATE SET NULL */
if
/* update(username) */
update(username)
begin
update BPurchaseOrderCA
set
/* BPurchaseOrderCA.username = NULL */
BPurchaseOrderCA.username = NULL
from BPurchaseOrderCA,deleted
where
/* BPurchaseOrderCA.username = deleted.username */
BPurchaseOrderCA.username = deleted.username
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tD_BManufacturerCA on BManufacturerCA for DELETE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* DELETE trigger on BManufacturerCA */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BManufacturerCA R/4 BPartCA ON PARENT DELETE SET NULL */
update BPartCA
set
/* BPartCA.manufacturer_id = NULL */
BPartCA.manufacturer_id = NULL
from BPartCA,deleted
where
/* BPartCA.manufacturer_id = deleted.manufacturer_id */
BPartCA.manufacturer_id = deleted.manufacturer_id
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_BManufacturerCA on BManufacturerCA for UPDATE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* UPDATE trigger on BManufacturerCA */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insmanufacturer_id char(14),
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BManufacturerCA R/4 BPartCA ON PARENT UPDATE SET NULL */
if
/* update(manufacturer_id) */
update(manufacturer_id)
begin
update BPartCA
set
/* BPartCA.manufacturer_id = NULL */
BPartCA.manufacturer_id = NULL
from BPartCA,deleted
where
/* BPartCA.manufacturer_id = deleted.manufacturer_id */
BPartCA.manufacturer_id = deleted.manufacturer_id
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tI_BOrderItemCA on BOrderItemCA for INSERT as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* INSERT trigger on BOrderItemCA */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BPurchaseOrderCA R/3 BOrderItemCA ON CHILD INSERT RESTRICT */
if
/* update(po_nbr) */
update(po_nbr)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,BPurchaseOrderCA
where
/* inserted.po_nbr = BPurchaseOrderCA.po_nbr */
inserted.po_nbr = BPurchaseOrderCA.po_nbr
/* */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30002,
@errmsg = 'Cannot INSERT BOrderItemCA because BPurchaseOrderCA
does not exist.'
goto error
end
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BPartCA R/2 BOrderItemCA ON CHILD INSERT RESTRICT */
if
/* update(part_id) */
update(part_id)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,BPartCA
where
/* inserted.part_id = BPartCA.part_id */
inserted.part_id = BPartCA.part_id
/* */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30002,
@errmsg = 'Cannot INSERT BOrderItemCA because BPartCA does not
exist.'
goto error
end
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_BOrderItemCA on BOrderItemCA for UPDATE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* UPDATE trigger on BOrderItemCA */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@inspart_id char(18),
@inspo_nbr char(18),
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BPurchaseOrderCA R/3 BOrderItemCA ON CHILD UPDATE RESTRICT */
if
/* update(po_nbr) */
update(po_nbr)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,BPurchaseOrderCA
where
/* inserted.po_nbr = BPurchaseOrderCA.po_nbr */
inserted.po_nbr = BPurchaseOrderCA.po_nbr
/* */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30007,
@errmsg = 'Cannot UPDATE BOrderItemCA because BPurchaseOrderCA
does not exist.'
goto error
end
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BPartCA R/2 BOrderItemCA ON CHILD UPDATE RESTRICT */
if
/* update(part_id) */
update(part_id)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,BPartCA
where
/* inserted.part_id = BPartCA.part_id */
inserted.part_id = BPartCA.part_id
/* */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30007,
@errmsg = 'Cannot UPDATE BOrderItemCA because BPartCA does not
exist.'
goto error
end
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tD_BPartCA on BPartCA for DELETE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* DELETE trigger on BPartCA */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BPartCA R/2 BOrderItemCA ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,BOrderItemCA
where
/* BOrderItemCA.part_id = deleted.part_id */
BOrderItemCA.part_id = deleted.part_id
)
begin
select @errno = 30001,
@errmsg = 'Cannot DELETE BPartCA because BOrderItemCA exists.'
goto error
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tI_BPartCA on BPartCA for INSERT as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* INSERT trigger on BPartCA */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BManufacturerCA R/4 BPartCA ON CHILD INSERT SET NULL */
if
/* update(manufacturer_id) */
update(manufacturer_id)
begin
update BPartCA
set
/* BPartCA.manufacturer_id = NULL */
BPartCA.manufacturer_id = NULL
from BPartCA,inserted
where
/* */
not exists (
select * from BManufacturerCA
where
/* inserted.manufacturer_id = BManufacturerCA.manufacturer_id */
inserted.manufacturer_id = BManufacturerCA.manufacturer_id
)
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_BPartCA on BPartCA for UPDATE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* UPDATE trigger on BPartCA */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@inspart_id char(18),
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BPartCA R/2 BOrderItemCA ON PARENT UPDATE RESTRICT */
if
/* update(part_id) */
update(part_id)
begin
if exists (
select * from deleted,BOrderItemCA
where
/* BOrderItemCA.part_id = deleted.part_id */
BOrderItemCA.part_id = deleted.part_id
)
begin
select @errno = 30005,
@errmsg = 'Cannot UPDATE BPartCA because BOrderItemCA exists.'
goto error
end
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BManufacturerCA R/4 BPartCA ON CHILD UPDATE SET NULL */
if
/* update(manufacturer_id) */
update(manufacturer_id)
begin
update BPartCA
set
/* BPartCA.manufacturer_id = NULL */
BPartCA.manufacturer_id = NULL
from BPartCA,inserted
where
/* */
not exists (
select * from BManufacturerCA
where
/* inserted.manufacturer_id = BManufacturerCA.manufacturer_id */
inserted.manufacturer_id = BManufacturerCA.manufacturer_id
)
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tD_BPurchaseOrderCA on BPurchaseOrderCA for DELETE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* DELETE trigger on BPurchaseOrderCA */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BPurchaseOrderCA R/3 BOrderItemCA ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,BOrderItemCA
where
/* BOrderItemCA.po_nbr = deleted.po_nbr */
BOrderItemCA.po_nbr = deleted.po_nbr
)
begin
select @errno = 30001,
@errmsg = 'Cannot DELETE BPurchaseOrderCA because BOrderItemCA
exists.'
goto error
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tI_BPurchaseOrderCA on BPurchaseOrderCA for INSERT as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* INSERT trigger on BPurchaseOrderCA */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BCustomerCA R/5 BPurchaseOrderCA ON CHILD INSERT SET NULL */
if
/* update(username) */
update(username)
begin
update BPurchaseOrderCA
set
/* BPurchaseOrderCA.username = NULL */
BPurchaseOrderCA.username = NULL
from BPurchaseOrderCA,inserted
where
/* */
not exists (
select * from BCustomerCA
where
/* inserted.username = BCustomerCA.username */
inserted.username = BCustomerCA.username
)
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_BPurchaseOrderCA on BPurchaseOrderCA for UPDATE as
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* UPDATE trigger on BPurchaseOrderCA */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@inspo_nbr char(18),
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BPurchaseOrderCA R/3 BOrderItemCA ON PARENT UPDATE RESTRICT */
if
/* update(po_nbr) */
update(po_nbr)
begin
if exists (
select * from deleted,BOrderItemCA
where
/* BOrderItemCA.po_nbr = deleted.po_nbr */
BOrderItemCA.po_nbr = deleted.po_nbr
)
begin
select @errno = 30005,
@errmsg = 'Cannot UPDATE BPurchaseOrderCA because BOrderItemCA
exists.'
goto error
end
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
/* BCustomerCA R/5 BPurchaseOrderCA ON CHILD UPDATE SET NULL */
if
/* update(username) */
update(username)
begin
update BPurchaseOrderCA
set
/* BPurchaseOrderCA.username = NULL */
BPurchaseOrderCA.username = NULL
from BPurchaseOrderCA,inserted
where
/* */
not exists (
select * from BCustomerCA
where
/* inserted.username = BCustomerCA.username */
inserted.username = BCustomerCA.username
)
end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE addCustomerSQL
(
@username char(19),
@fullname varchar(27),
@password char(7),
@credit_card_nbr char(18),
@credit_card_expiry_date datetime,
@mailing_address varchar(100),
@phone_nbr char(14),
@email_address char(50),
@login_status bit,
@credit_card_name char(27)
)
AS
INSERT INTO BCustomerCA VALUES (@username , @fullname, @password,
@credit_card_nbr, @credit_card_expiry_date, @mailing_address, @phone_nbr,
@email_address, @login_status, @credit_card_name )
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------------------------------
"William Ryan eMVP" <bi**@NoSp4m.devbuzz.com> wrote in message
news:Or***************@TK2MSFTNGP12.phx.gbl... Hmm, do you have any binary fields in the DB? I'm hesitant to admit this, but I had this problem once and I realized I had defined a Varchar field
to VarBinary and that was ultimately the cause.
Can you post the table script and the SQL Statement you are using? I'll
be glad to create a table and give it a try...
Bill "Bryan" <ca**********@rogers.com> wrote in message news:Ge********************@news04.bloor.is.net.ca ble.rogers.com... I noticed that also, yet the datatypes are teh same in sql and in teh
vb code. this is driving me nuts.
thanks again. bry
"William Ryan eMVP" <bi**@NoSp4m.devbuzz.com> wrote in message news:eA**************@TK2MSFTNGP11.phx.gbl... Bryan:
What is the datatype of the field in SqlServer? Bit or Binary? The Param type is specified as Bit but by the Exception message, I'm wondering
if it's not Binary in the DB. If so, I believe changing the db type in the client code to SqlDbType.Binary or changing the data type server side to Bit may do it for you.
HTH,
Bill "Bryan" <ca**********@rogers.com> wrote in message news:5y********************@news04.bloor.is.net.ca ble.rogers.com... > hello all... > > im trying to add a record to an sql db on ms sql server 2000, using vb.net. > > seems to be working.. except for one thing, one of the columns in
the > database is a bit datatype, and though i get no syntax errors when > compiling, i get an error indicated that the data would be
truncated. the > field is login_status. > > ive tried in quotes and not, giving it an integer variable with the number 1 > in it, true/false statements.. ive tried a bunch of stuff... has me real > stumped.. > > have any ideas? > > thanks > > > here is the error it spits out. > > <b>* error while uploading original data</b>.<br />String or binary data > would be truncated.The statement has been terminated..Net SqlClient Data > Provider > > > >
-------------------------------------------------------------------------- > here is the code:
-------------------------------------------------------------------------- > Imports System.Data > > Imports System.Data.SqlClient > > > > > > Public Class WebForm1 > > Inherits System.Web.UI.Page > > > > > > > > > > > Public Sub Register_User(ByVal sender As Object, ByVal E As
EventArgs) > > Dim dbConnStr As String = "user id=**(blanked for > security)***;password=**(blanked for > security)**;database=BCdb;server=localhost" > > Dim dbConn As New System.Data.SqlClient.SqlConnection(dbConnStr) > > > > > > > > Try > > Dim cmd As New SqlCommand("addCustomerSQL", dbConn) > > cmd.CommandType = CommandType.StoredProcedure > > cmd.Parameters.Add(New SqlParameter("@username", SqlDbType.Char,
19)) > > cmd.Parameters.Add(New SqlParameter("@fullname", SqlDbType.VarChar,
27)) > > cmd.Parameters.Add(New SqlParameter("@password", SqlDbType.Char, 7)) > > cmd.Parameters.Add(New SqlParameter("@credit_card_nbr", SqlDbType.Char, 18)) > > cmd.Parameters.Add(New SqlParameter("@credit_card_expiry_date", > SqlDbType.DateTime)) > > cmd.Parameters.Add(New SqlParameter("@mailing_address", SqlDbType.VarChar, > 100)) > > cmd.Parameters.Add(New SqlParameter("@phone_nbr", SqlDbType.Char,
14)) > > cmd.Parameters.Add(New SqlParameter("@email_address",
SqlDbType.Char, 50)) > > cmd.Parameters.Add(New SqlParameter("@login_status", SqlDbType.Bit)) > > cmd.Parameters.Add(New SqlParameter("@credit_card_name", SqlDbType.Char, > 27)) > > cmd.Parameters.Item("@username").Value = RegUsrnameTXT.Text > > cmd.Parameters.Item("@fullname").Value = RegFullnameTXT.Text > > cmd.Parameters.Item("@password").Value = RegPasswordTXT.Text > > cmd.Parameters.Item("@credit_card_nbr").Value = RegCreditcardnumberTXT.Text > > cmd.Parameters.Item("@credit_card_expiry_date").Va lue = > RegCreditcardexpiryTXT.Text > > cmd.Parameters.Item("@mailing_address").Value = RegAddressTXT.Text > > cmd.Parameters.Item("@email_address").Value = RegEmailTXT.Text > > cmd.Parameters.Item("@phone_nbr").Value = RegPhoneTXT.Text > > cmd.Parameters.Item("@login_status").Value = 1 > > cmd.Parameters.Item("@credit_card_name").Value = RegCreditcardnameTXT.Text > > 'opent the connection to the database > > dbConn.Open() > > 'execute sql statement > > cmd.ExecuteNonQuery() > > dbConn.Close() > > Catch objError As Exception > > dberror.Text = "<b>* error while uploading original data</b>.<br />"
& > objError.Message & objError.Source > > Exit Sub > > Finally > > If dbConn.State = ConnectionState.Open Then > > dbConn.Close() > > End If > > End Try > > End Sub > > > > End Class >
-------------------------------------------------------------------------- -- > --- > > >
Ok, if you write a Debug.Assert for each line ie asserting that the lenght
of textboxWhatever.text.Length <= FieldSize...see what happens. is there
any chance that your inputs are longer than the field size?
If you don't want to write out all of the Assertions, just use a
Debug.WriteLine(textBoxWhatever.text.Length.ToStri ng()) for each of the text
box lenghts and then compare them to the allowed field size.
I've done this directly with the parent table (excluding triggers and
children ) and I cna get an insert to work but I've used data I knew would
be smaller than field size.
Let me play with it some more though and hopefully I can narrow it down.
"Bryan" <ca**********@rogers.com> wrote in message
news:BJ********************@news04.bloor.is.net.ca ble.rogers.com... here is the sql script to create the database, at the end is the procedure im using.
i very much appreciate the help...
bryan
----------------------------------------------------- CREATE TABLE BCustomerCA ( username char(19) NOT NULL, fullname varchar(27) NULL, credit_card_nbr char(18) NULL, password char(7) NULL, credit_card_expiry_date datetime NULL, mailing__address varchar(100) NULL, phone_nbr char(14) NULL, email_address char(50) NULL, login_status bit NULL, credit_card_name char(27) NULL ) go
ALTER TABLE BCustomerCA ADD PRIMARY KEY NONCLUSTERED (username) go
CREATE TABLE BManufacturerCA ( manufacturer_id char(14) NOT NULL, manufacturer_name varchar(50) NULL, email_address char(50) NULL, contact_name varchar(27) NULL, phone_nbr char(14) NULL ) go
ALTER TABLE BManufacturerCA ADD PRIMARY KEY NONCLUSTERED (manufacturer_id) go
CREATE TABLE BOrderItemCA ( part_id char(18) NOT NULL, po_nbr char(18) NOT NULL, item_qty int NULL ) go
ALTER TABLE BOrderItemCA ADD PRIMARY KEY NONCLUSTERED (part_id, po_nbr) go
CREATE TABLE BPartCA ( part_id char(18) NOT NULL, part_name varchar(50) NULL, stock_size int NULL, manufacturer_id char(14) NULL, part_price decimal(10,2) NULL ) go
ALTER TABLE BPartCA ADD PRIMARY KEY NONCLUSTERED (part_id) go
CREATE TABLE BPurchaseOrderCA ( po_nbr char(18) NOT NULL, po_timetable datetime NULL, isprocessed bit NULL, username char(19) NULL ) go
ALTER TABLE BPurchaseOrderCA ADD PRIMARY KEY NONCLUSTERED (po_nbr) go
CREATE TABLE BStaffCA ( username char(19) NOT NULL, password char(7) NULL, login_status bit NULL, fullname varchar(27) NULL ) go
ALTER TABLE BStaffCA ADD PRIMARY KEY NONCLUSTERED (username) go
ALTER TABLE BOrderItemCA ADD FOREIGN KEY (po_nbr) REFERENCES BPurchaseOrderCA go
ALTER TABLE BOrderItemCA ADD FOREIGN KEY (part_id) REFERENCES BPartCA go
ALTER TABLE BPartCA ADD FOREIGN KEY (manufacturer_id) REFERENCES BManufacturerCA go
ALTER TABLE BPurchaseOrderCA ADD FOREIGN KEY (username) REFERENCES BCustomerCA go
create trigger tD_BCustomerCA on BCustomerCA for DELETE as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* DELETE trigger on BCustomerCA */ begin declare @errno int, @errmsg varchar(255) /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BCustomerCA R/5 BPurchaseOrderCA ON PARENT DELETE SET NULL */ update BPurchaseOrderCA set /* BPurchaseOrderCA.username = NULL */ BPurchaseOrderCA.username = NULL from BPurchaseOrderCA,deleted where /* BPurchaseOrderCA.username = deleted.username */ BPurchaseOrderCA.username = deleted.username
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tU_BCustomerCA on BCustomerCA for UPDATE as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* UPDATE trigger on BCustomerCA */ begin declare @numrows int, @nullcnt int, @validcnt int, @insusername char(19), @errno int, @errmsg varchar(255)
select @numrows = @@rowcount /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BCustomerCA R/5 BPurchaseOrderCA ON PARENT UPDATE SET NULL */ if /* update(username) */ update(username) begin update BPurchaseOrderCA set /* BPurchaseOrderCA.username = NULL */ BPurchaseOrderCA.username = NULL from BPurchaseOrderCA,deleted where /* BPurchaseOrderCA.username = deleted.username */ BPurchaseOrderCA.username = deleted.username end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tD_BManufacturerCA on BManufacturerCA for DELETE as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* DELETE trigger on BManufacturerCA */ begin declare @errno int, @errmsg varchar(255) /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BManufacturerCA R/4 BPartCA ON PARENT DELETE SET NULL */ update BPartCA set /* BPartCA.manufacturer_id = NULL */ BPartCA.manufacturer_id = NULL from BPartCA,deleted where /* BPartCA.manufacturer_id = deleted.manufacturer_id */ BPartCA.manufacturer_id = deleted.manufacturer_id
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tU_BManufacturerCA on BManufacturerCA for UPDATE as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* UPDATE trigger on BManufacturerCA */ begin declare @numrows int, @nullcnt int, @validcnt int, @insmanufacturer_id char(14), @errno int, @errmsg varchar(255)
select @numrows = @@rowcount /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BManufacturerCA R/4 BPartCA ON PARENT UPDATE SET NULL */ if /* update(manufacturer_id) */ update(manufacturer_id) begin update BPartCA set /* BPartCA.manufacturer_id = NULL */ BPartCA.manufacturer_id = NULL from BPartCA,deleted where /* BPartCA.manufacturer_id = deleted.manufacturer_id */ BPartCA.manufacturer_id = deleted.manufacturer_id end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tI_BOrderItemCA on BOrderItemCA for INSERT as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* INSERT trigger on BOrderItemCA */ begin declare @numrows int, @nullcnt int, @validcnt int, @errno int, @errmsg varchar(255)
select @numrows = @@rowcount /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BPurchaseOrderCA R/3 BOrderItemCA ON CHILD INSERT RESTRICT */ if /* update(po_nbr) */ update(po_nbr) begin select @nullcnt = 0 select @validcnt = count(*) from inserted,BPurchaseOrderCA where /* inserted.po_nbr = BPurchaseOrderCA.po_nbr */ inserted.po_nbr = BPurchaseOrderCA.po_nbr /* */
if @validcnt + @nullcnt != @numrows begin select @errno = 30002, @errmsg = 'Cannot INSERT BOrderItemCA because
BPurchaseOrderCA does not exist.' goto error end end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BPartCA R/2 BOrderItemCA ON CHILD INSERT RESTRICT */ if /* update(part_id) */ update(part_id) begin select @nullcnt = 0 select @validcnt = count(*) from inserted,BPartCA where /* inserted.part_id = BPartCA.part_id */ inserted.part_id = BPartCA.part_id /* */
if @validcnt + @nullcnt != @numrows begin select @errno = 30002, @errmsg = 'Cannot INSERT BOrderItemCA because BPartCA does
not exist.' goto error end end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tU_BOrderItemCA on BOrderItemCA for UPDATE as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* UPDATE trigger on BOrderItemCA */ begin declare @numrows int, @nullcnt int, @validcnt int, @inspart_id char(18), @inspo_nbr char(18), @errno int, @errmsg varchar(255)
select @numrows = @@rowcount /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BPurchaseOrderCA R/3 BOrderItemCA ON CHILD UPDATE RESTRICT */ if /* update(po_nbr) */ update(po_nbr) begin select @nullcnt = 0 select @validcnt = count(*) from inserted,BPurchaseOrderCA where /* inserted.po_nbr = BPurchaseOrderCA.po_nbr */ inserted.po_nbr = BPurchaseOrderCA.po_nbr /* */
if @validcnt + @nullcnt != @numrows begin select @errno = 30007, @errmsg = 'Cannot UPDATE BOrderItemCA because
BPurchaseOrderCA does not exist.' goto error end end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BPartCA R/2 BOrderItemCA ON CHILD UPDATE RESTRICT */ if /* update(part_id) */ update(part_id) begin select @nullcnt = 0 select @validcnt = count(*) from inserted,BPartCA where /* inserted.part_id = BPartCA.part_id */ inserted.part_id = BPartCA.part_id /* */
if @validcnt + @nullcnt != @numrows begin select @errno = 30007, @errmsg = 'Cannot UPDATE BOrderItemCA because BPartCA does
not exist.' goto error end end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tD_BPartCA on BPartCA for DELETE as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* DELETE trigger on BPartCA */ begin declare @errno int, @errmsg varchar(255) /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BPartCA R/2 BOrderItemCA ON PARENT DELETE RESTRICT */ if exists ( select * from deleted,BOrderItemCA where /* BOrderItemCA.part_id = deleted.part_id */ BOrderItemCA.part_id = deleted.part_id ) begin select @errno = 30001, @errmsg = 'Cannot DELETE BPartCA because BOrderItemCA
exists.' goto error end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tI_BPartCA on BPartCA for INSERT as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* INSERT trigger on BPartCA */ begin declare @numrows int, @nullcnt int, @validcnt int, @errno int, @errmsg varchar(255)
select @numrows = @@rowcount /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BManufacturerCA R/4 BPartCA ON CHILD INSERT SET NULL */ if /* update(manufacturer_id) */ update(manufacturer_id) begin update BPartCA set /* BPartCA.manufacturer_id = NULL */ BPartCA.manufacturer_id = NULL from BPartCA,inserted where /* */
not exists ( select * from BManufacturerCA where /* inserted.manufacturer_id = BManufacturerCA.manufacturer_id
*/ inserted.manufacturer_id = BManufacturerCA.manufacturer_id ) end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tU_BPartCA on BPartCA for UPDATE as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* UPDATE trigger on BPartCA */ begin declare @numrows int, @nullcnt int, @validcnt int, @inspart_id char(18), @errno int, @errmsg varchar(255)
select @numrows = @@rowcount /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BPartCA R/2 BOrderItemCA ON PARENT UPDATE RESTRICT */ if /* update(part_id) */ update(part_id) begin if exists ( select * from deleted,BOrderItemCA where /* BOrderItemCA.part_id = deleted.part_id */ BOrderItemCA.part_id = deleted.part_id ) begin select @errno = 30005, @errmsg = 'Cannot UPDATE BPartCA because BOrderItemCA
exists.' goto error end end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BManufacturerCA R/4 BPartCA ON CHILD UPDATE SET NULL */ if /* update(manufacturer_id) */ update(manufacturer_id) begin update BPartCA set /* BPartCA.manufacturer_id = NULL */ BPartCA.manufacturer_id = NULL from BPartCA,inserted where /* */
not exists ( select * from BManufacturerCA where /* inserted.manufacturer_id = BManufacturerCA.manufacturer_id
*/ inserted.manufacturer_id = BManufacturerCA.manufacturer_id ) end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tD_BPurchaseOrderCA on BPurchaseOrderCA for DELETE as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* DELETE trigger on BPurchaseOrderCA */ begin declare @errno int, @errmsg varchar(255) /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BPurchaseOrderCA R/3 BOrderItemCA ON PARENT DELETE RESTRICT */ if exists ( select * from deleted,BOrderItemCA where /* BOrderItemCA.po_nbr = deleted.po_nbr */ BOrderItemCA.po_nbr = deleted.po_nbr ) begin select @errno = 30001, @errmsg = 'Cannot DELETE BPurchaseOrderCA because
BOrderItemCA exists.' goto error end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tI_BPurchaseOrderCA on BPurchaseOrderCA for INSERT as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* INSERT trigger on BPurchaseOrderCA */ begin declare @numrows int, @nullcnt int, @validcnt int, @errno int, @errmsg varchar(255)
select @numrows = @@rowcount /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BCustomerCA R/5 BPurchaseOrderCA ON CHILD INSERT SET NULL */ if /* update(username) */ update(username) begin update BPurchaseOrderCA set /* BPurchaseOrderCA.username = NULL */ BPurchaseOrderCA.username = NULL from BPurchaseOrderCA,inserted where /* */
not exists ( select * from BCustomerCA where /* inserted.username = BCustomerCA.username */ inserted.username = BCustomerCA.username ) end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tU_BPurchaseOrderCA on BPurchaseOrderCA for UPDATE as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* UPDATE trigger on BPurchaseOrderCA */ begin declare @numrows int, @nullcnt int, @validcnt int, @inspo_nbr char(18), @errno int, @errmsg varchar(255)
select @numrows = @@rowcount /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BPurchaseOrderCA R/3 BOrderItemCA ON PARENT UPDATE RESTRICT */ if /* update(po_nbr) */ update(po_nbr) begin if exists ( select * from deleted,BOrderItemCA where /* BOrderItemCA.po_nbr = deleted.po_nbr */ BOrderItemCA.po_nbr = deleted.po_nbr ) begin select @errno = 30005, @errmsg = 'Cannot UPDATE BPurchaseOrderCA because
BOrderItemCA exists.' goto error end end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BCustomerCA R/5 BPurchaseOrderCA ON CHILD UPDATE SET NULL */ if /* update(username) */ update(username) begin update BPurchaseOrderCA set /* BPurchaseOrderCA.username = NULL */ BPurchaseOrderCA.username = NULL from BPurchaseOrderCA,inserted where /* */
not exists ( select * from BCustomerCA where /* inserted.username = BCustomerCA.username */ inserted.username = BCustomerCA.username ) end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO
CREATE PROCEDURE addCustomerSQL (
@username char(19), @fullname varchar(27), @password char(7), @credit_card_nbr char(18), @credit_card_expiry_date datetime, @mailing_address varchar(100), @phone_nbr char(14), @email_address char(50), @login_status bit, @credit_card_name char(27)
) AS INSERT INTO BCustomerCA VALUES (@username , @fullname, @password, @credit_card_nbr, @credit_card_expiry_date, @mailing_address, @phone_nbr, @email_address, @login_status, @credit_card_name ) GO
SET QUOTED_IDENTIFIER OFF GO
SET ANSI_NULLS ON GO
----------------------------------------------------------
"William Ryan eMVP" <bi**@NoSp4m.devbuzz.com> wrote in message news:Or***************@TK2MSFTNGP12.phx.gbl... Hmm, do you have any binary fields in the DB? I'm hesitant to admit
this, but I had this problem once and I realized I had defined a Varchar field to VarBinary and that was ultimately the cause.
Can you post the table script and the SQL Statement you are using? I'll be glad to create a table and give it a try...
Bill "Bryan" <ca**********@rogers.com> wrote in message news:Ge********************@news04.bloor.is.net.ca ble.rogers.com... I noticed that also, yet the datatypes are teh same in sql and in teh vb code. this is driving me nuts.
thanks again. bry
"William Ryan eMVP" <bi**@NoSp4m.devbuzz.com> wrote in message news:eA**************@TK2MSFTNGP11.phx.gbl... > Bryan: > > What is the datatype of the field in SqlServer? Bit or Binary? The Param > type is specified as Bit but by the Exception message, I'm wondering if it's > not Binary in the DB. If so, I believe changing the db type in the client > code to SqlDbType.Binary or changing the data type server side to
Bit may do > it for you. > > HTH, > > Bill > "Bryan" <ca**********@rogers.com> wrote in message > news:5y********************@news04.bloor.is.net.ca ble.rogers.com... > > hello all... > > > > im trying to add a record to an sql db on ms sql server 2000,
using > vb.net. > > > > seems to be working.. except for one thing, one of the columns in the > > database is a bit datatype, and though i get no syntax errors when > > compiling, i get an error indicated that the data would be truncated. the > > field is login_status. > > > > ive tried in quotes and not, giving it an integer variable with
the number > 1 > > in it, true/false statements.. ive tried a bunch of stuff... has
me real > > stumped.. > > > > have any ideas? > > > > thanks > > > > > > here is the error it spits out. > > > > <b>* error while uploading original data</b>.<br />String or
binary data > > would be truncated.The statement has been terminated..Net
SqlClient Data > > Provider > > > > > > > > >
-------------------------------------------------------------------------- > > here is the code: >
-------------------------------------------------------------------------- > > Imports System.Data > > > > Imports System.Data.SqlClient > > > > > > > > > > > > Public Class WebForm1 > > > > Inherits System.Web.UI.Page > > > > > > > > > > > > > > > > > > > > > > Public Sub Register_User(ByVal sender As Object, ByVal E As EventArgs) > > > > Dim dbConnStr As String = "user id=**(blanked for > > security)***;password=**(blanked for > > security)**;database=BCdb;server=localhost" > > > > Dim dbConn As New System.Data.SqlClient.SqlConnection(dbConnStr) > > > > > > > > > > > > > > > > Try > > > > Dim cmd As New SqlCommand("addCustomerSQL", dbConn) > > > > cmd.CommandType = CommandType.StoredProcedure > > > > cmd.Parameters.Add(New SqlParameter("@username", SqlDbType.Char, 19)) > > > > cmd.Parameters.Add(New SqlParameter("@fullname",
SqlDbType.VarChar, 27)) > > > > cmd.Parameters.Add(New SqlParameter("@password", SqlDbType.Char,
7)) > > > > cmd.Parameters.Add(New SqlParameter("@credit_card_nbr",
SqlDbType.Char, > 18)) > > > > cmd.Parameters.Add(New SqlParameter("@credit_card_expiry_date", > > SqlDbType.DateTime)) > > > > cmd.Parameters.Add(New SqlParameter("@mailing_address", SqlDbType.VarChar, > > 100)) > > > > cmd.Parameters.Add(New SqlParameter("@phone_nbr", SqlDbType.Char, 14)) > > > > cmd.Parameters.Add(New SqlParameter("@email_address", SqlDbType.Char, 50)) > > > > cmd.Parameters.Add(New SqlParameter("@login_status",
SqlDbType.Bit)) > > > > cmd.Parameters.Add(New SqlParameter("@credit_card_name", SqlDbType.Char, > > 27)) > > > > cmd.Parameters.Item("@username").Value = RegUsrnameTXT.Text > > > > cmd.Parameters.Item("@fullname").Value = RegFullnameTXT.Text > > > > cmd.Parameters.Item("@password").Value = RegPasswordTXT.Text > > > > cmd.Parameters.Item("@credit_card_nbr").Value = > RegCreditcardnumberTXT.Text > > > > cmd.Parameters.Item("@credit_card_expiry_date").Va lue = > > RegCreditcardexpiryTXT.Text > > > > cmd.Parameters.Item("@mailing_address").Value = RegAddressTXT.Text > > > > cmd.Parameters.Item("@email_address").Value = RegEmailTXT.Text > > > > cmd.Parameters.Item("@phone_nbr").Value = RegPhoneTXT.Text > > > > cmd.Parameters.Item("@login_status").Value = 1 > > > > cmd.Parameters.Item("@credit_card_name").Value = RegCreditcardnameTXT.Text > > > > 'opent the connection to the database > > > > dbConn.Open() > > > > 'execute sql statement > > > > cmd.ExecuteNonQuery() > > > > dbConn.Close() > > > > Catch objError As Exception > > > > dberror.Text = "<b>* error while uploading original data</b>.<br
/>" & > > objError.Message & objError.Source > > > > Exit Sub > > > > Finally > > > > If dbConn.State = ConnectionState.Open Then > > > > dbConn.Close() > > > > End If > > > > End Try > > > > End Sub > > > > > > > > End Class > > >
-------------------------------------------------------------------------- > -- > > --- > > > > > > > >
Im new to asp and have a little experience with java, so some concepts are
foreign to me. from what i understand of your last post, some of the fields
being sent are too long? is that not avoided by the following statement?
cmd.Parameters.Add(New SqlParameter("@username", SqlDbType.Char, 19))
also, could the number "1" that im sending be sent in as a character as
opposed to a bit? if im correct a character one would be 8 bits. could this
be what is happening?
eternally gratefull,
bryan
"William Ryan eMVP" <bi**@NoSp4m.devbuzz.com> wrote in message
news:OV**************@TK2MSFTNGP09.phx.gbl... Ok, if you write a Debug.Assert for each line ie asserting that the lenght of textboxWhatever.text.Length <= FieldSize...see what happens. is there any chance that your inputs are longer than the field size?
If you don't want to write out all of the Assertions, just use a Debug.WriteLine(textBoxWhatever.text.Length.ToStri ng()) for each of the
text box lenghts and then compare them to the allowed field size.
I've done this directly with the parent table (excluding triggers and children ) and I cna get an insert to work but I've used data I knew would be smaller than field size.
Let me play with it some more though and hopefully I can narrow it down.
"Bryan" <ca**********@rogers.com> wrote in message news:BJ********************@news04.bloor.is.net.ca ble.rogers.com... here is the sql script to create the database, at the end is the
procedure im using.
i very much appreciate the help...
bryan
----------------------------------------------------- CREATE TABLE BCustomerCA ( username char(19) NOT NULL, fullname varchar(27) NULL, credit_card_nbr char(18) NULL, password char(7) NULL, credit_card_expiry_date datetime NULL, mailing__address varchar(100) NULL, phone_nbr char(14) NULL, email_address char(50) NULL, login_status bit NULL, credit_card_name char(27) NULL ) go
ALTER TABLE BCustomerCA ADD PRIMARY KEY NONCLUSTERED (username) go
CREATE TABLE BManufacturerCA ( manufacturer_id char(14) NOT NULL, manufacturer_name varchar(50) NULL, email_address char(50) NULL, contact_name varchar(27) NULL, phone_nbr char(14) NULL ) go
ALTER TABLE BManufacturerCA ADD PRIMARY KEY NONCLUSTERED (manufacturer_id) go
CREATE TABLE BOrderItemCA ( part_id char(18) NOT NULL, po_nbr char(18) NOT NULL, item_qty int NULL ) go
ALTER TABLE BOrderItemCA ADD PRIMARY KEY NONCLUSTERED (part_id, po_nbr) go
CREATE TABLE BPartCA ( part_id char(18) NOT NULL, part_name varchar(50) NULL, stock_size int NULL, manufacturer_id char(14) NULL, part_price decimal(10,2) NULL ) go
ALTER TABLE BPartCA ADD PRIMARY KEY NONCLUSTERED (part_id) go
CREATE TABLE BPurchaseOrderCA ( po_nbr char(18) NOT NULL, po_timetable datetime NULL, isprocessed bit NULL, username char(19) NULL ) go
ALTER TABLE BPurchaseOrderCA ADD PRIMARY KEY NONCLUSTERED (po_nbr) go
CREATE TABLE BStaffCA ( username char(19) NOT NULL, password char(7) NULL, login_status bit NULL, fullname varchar(27) NULL ) go
ALTER TABLE BStaffCA ADD PRIMARY KEY NONCLUSTERED (username) go
ALTER TABLE BOrderItemCA ADD FOREIGN KEY (po_nbr) REFERENCES BPurchaseOrderCA go
ALTER TABLE BOrderItemCA ADD FOREIGN KEY (part_id) REFERENCES BPartCA go
ALTER TABLE BPartCA ADD FOREIGN KEY (manufacturer_id) REFERENCES BManufacturerCA go
ALTER TABLE BPurchaseOrderCA ADD FOREIGN KEY (username) REFERENCES BCustomerCA go
create trigger tD_BCustomerCA on BCustomerCA for DELETE as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* DELETE trigger on BCustomerCA */ begin declare @errno int, @errmsg varchar(255) /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BCustomerCA R/5 BPurchaseOrderCA ON PARENT DELETE SET NULL */ update BPurchaseOrderCA set /* BPurchaseOrderCA.username = NULL */ BPurchaseOrderCA.username = NULL from BPurchaseOrderCA,deleted where /* BPurchaseOrderCA.username = deleted.username */ BPurchaseOrderCA.username = deleted.username
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tU_BCustomerCA on BCustomerCA for UPDATE as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* UPDATE trigger on BCustomerCA */ begin declare @numrows int, @nullcnt int, @validcnt int, @insusername char(19), @errno int, @errmsg varchar(255)
select @numrows = @@rowcount /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BCustomerCA R/5 BPurchaseOrderCA ON PARENT UPDATE SET NULL */ if /* update(username) */ update(username) begin update BPurchaseOrderCA set /* BPurchaseOrderCA.username = NULL */ BPurchaseOrderCA.username = NULL from BPurchaseOrderCA,deleted where /* BPurchaseOrderCA.username = deleted.username */ BPurchaseOrderCA.username = deleted.username end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tD_BManufacturerCA on BManufacturerCA for DELETE as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* DELETE trigger on BManufacturerCA */ begin declare @errno int, @errmsg varchar(255) /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BManufacturerCA R/4 BPartCA ON PARENT DELETE SET NULL */ update BPartCA set /* BPartCA.manufacturer_id = NULL */ BPartCA.manufacturer_id = NULL from BPartCA,deleted where /* BPartCA.manufacturer_id = deleted.manufacturer_id */ BPartCA.manufacturer_id = deleted.manufacturer_id
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tU_BManufacturerCA on BManufacturerCA for UPDATE as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* UPDATE trigger on BManufacturerCA */ begin declare @numrows int, @nullcnt int, @validcnt int, @insmanufacturer_id char(14), @errno int, @errmsg varchar(255)
select @numrows = @@rowcount /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BManufacturerCA R/4 BPartCA ON PARENT UPDATE SET NULL */ if /* update(manufacturer_id) */ update(manufacturer_id) begin update BPartCA set /* BPartCA.manufacturer_id = NULL */ BPartCA.manufacturer_id = NULL from BPartCA,deleted where /* BPartCA.manufacturer_id = deleted.manufacturer_id */ BPartCA.manufacturer_id = deleted.manufacturer_id end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tI_BOrderItemCA on BOrderItemCA for INSERT as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* INSERT trigger on BOrderItemCA */ begin declare @numrows int, @nullcnt int, @validcnt int, @errno int, @errmsg varchar(255)
select @numrows = @@rowcount /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BPurchaseOrderCA R/3 BOrderItemCA ON CHILD INSERT RESTRICT */ if /* update(po_nbr) */ update(po_nbr) begin select @nullcnt = 0 select @validcnt = count(*) from inserted,BPurchaseOrderCA where /* inserted.po_nbr = BPurchaseOrderCA.po_nbr */ inserted.po_nbr = BPurchaseOrderCA.po_nbr /* */
if @validcnt + @nullcnt != @numrows begin select @errno = 30002, @errmsg = 'Cannot INSERT BOrderItemCA because BPurchaseOrderCA does not exist.' goto error end end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BPartCA R/2 BOrderItemCA ON CHILD INSERT RESTRICT */ if /* update(part_id) */ update(part_id) begin select @nullcnt = 0 select @validcnt = count(*) from inserted,BPartCA where /* inserted.part_id = BPartCA.part_id */ inserted.part_id = BPartCA.part_id /* */
if @validcnt + @nullcnt != @numrows begin select @errno = 30002, @errmsg = 'Cannot INSERT BOrderItemCA because BPartCA does not exist.' goto error end end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tU_BOrderItemCA on BOrderItemCA for UPDATE as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* UPDATE trigger on BOrderItemCA */ begin declare @numrows int, @nullcnt int, @validcnt int, @inspart_id char(18), @inspo_nbr char(18), @errno int, @errmsg varchar(255)
select @numrows = @@rowcount /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BPurchaseOrderCA R/3 BOrderItemCA ON CHILD UPDATE RESTRICT */ if /* update(po_nbr) */ update(po_nbr) begin select @nullcnt = 0 select @validcnt = count(*) from inserted,BPurchaseOrderCA where /* inserted.po_nbr = BPurchaseOrderCA.po_nbr */ inserted.po_nbr = BPurchaseOrderCA.po_nbr /* */
if @validcnt + @nullcnt != @numrows begin select @errno = 30007, @errmsg = 'Cannot UPDATE BOrderItemCA because BPurchaseOrderCA does not exist.' goto error end end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BPartCA R/2 BOrderItemCA ON CHILD UPDATE RESTRICT */ if /* update(part_id) */ update(part_id) begin select @nullcnt = 0 select @validcnt = count(*) from inserted,BPartCA where /* inserted.part_id = BPartCA.part_id */ inserted.part_id = BPartCA.part_id /* */
if @validcnt + @nullcnt != @numrows begin select @errno = 30007, @errmsg = 'Cannot UPDATE BOrderItemCA because BPartCA does not exist.' goto error end end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tD_BPartCA on BPartCA for DELETE as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* DELETE trigger on BPartCA */ begin declare @errno int, @errmsg varchar(255) /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BPartCA R/2 BOrderItemCA ON PARENT DELETE RESTRICT */ if exists ( select * from deleted,BOrderItemCA where /* BOrderItemCA.part_id = deleted.part_id */ BOrderItemCA.part_id = deleted.part_id ) begin select @errno = 30001, @errmsg = 'Cannot DELETE BPartCA because BOrderItemCA exists.' goto error end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tI_BPartCA on BPartCA for INSERT as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* INSERT trigger on BPartCA */ begin declare @numrows int, @nullcnt int, @validcnt int, @errno int, @errmsg varchar(255)
select @numrows = @@rowcount /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BManufacturerCA R/4 BPartCA ON CHILD INSERT SET NULL */ if /* update(manufacturer_id) */ update(manufacturer_id) begin update BPartCA set /* BPartCA.manufacturer_id = NULL */ BPartCA.manufacturer_id = NULL from BPartCA,inserted where /* */
not exists ( select * from BManufacturerCA where /* inserted.manufacturer_id =
BManufacturerCA.manufacturer_id */ inserted.manufacturer_id = BManufacturerCA.manufacturer_id ) end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tU_BPartCA on BPartCA for UPDATE as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* UPDATE trigger on BPartCA */ begin declare @numrows int, @nullcnt int, @validcnt int, @inspart_id char(18), @errno int, @errmsg varchar(255)
select @numrows = @@rowcount /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BPartCA R/2 BOrderItemCA ON PARENT UPDATE RESTRICT */ if /* update(part_id) */ update(part_id) begin if exists ( select * from deleted,BOrderItemCA where /* BOrderItemCA.part_id = deleted.part_id */ BOrderItemCA.part_id = deleted.part_id ) begin select @errno = 30005, @errmsg = 'Cannot UPDATE BPartCA because BOrderItemCA exists.' goto error end end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BManufacturerCA R/4 BPartCA ON CHILD UPDATE SET NULL */ if /* update(manufacturer_id) */ update(manufacturer_id) begin update BPartCA set /* BPartCA.manufacturer_id = NULL */ BPartCA.manufacturer_id = NULL from BPartCA,inserted where /* */
not exists ( select * from BManufacturerCA where /* inserted.manufacturer_id =
BManufacturerCA.manufacturer_id */ inserted.manufacturer_id = BManufacturerCA.manufacturer_id ) end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tD_BPurchaseOrderCA on BPurchaseOrderCA for DELETE as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* DELETE trigger on BPurchaseOrderCA */ begin declare @errno int, @errmsg varchar(255) /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BPurchaseOrderCA R/3 BOrderItemCA ON PARENT DELETE RESTRICT */ if exists ( select * from deleted,BOrderItemCA where /* BOrderItemCA.po_nbr = deleted.po_nbr */ BOrderItemCA.po_nbr = deleted.po_nbr ) begin select @errno = 30001, @errmsg = 'Cannot DELETE BPurchaseOrderCA because BOrderItemCA exists.' goto error end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tI_BPurchaseOrderCA on BPurchaseOrderCA for INSERT as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* INSERT trigger on BPurchaseOrderCA */ begin declare @numrows int, @nullcnt int, @validcnt int, @errno int, @errmsg varchar(255)
select @numrows = @@rowcount /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BCustomerCA R/5 BPurchaseOrderCA ON CHILD INSERT SET NULL */ if /* update(username) */ update(username) begin update BPurchaseOrderCA set /* BPurchaseOrderCA.username = NULL */ BPurchaseOrderCA.username = NULL from BPurchaseOrderCA,inserted where /* */
not exists ( select * from BCustomerCA where /* inserted.username = BCustomerCA.username */ inserted.username = BCustomerCA.username ) end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tU_BPurchaseOrderCA on BPurchaseOrderCA for UPDATE as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* UPDATE trigger on BPurchaseOrderCA */ begin declare @numrows int, @nullcnt int, @validcnt int, @inspo_nbr char(18), @errno int, @errmsg varchar(255)
select @numrows = @@rowcount /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BPurchaseOrderCA R/3 BOrderItemCA ON PARENT UPDATE RESTRICT */ if /* update(po_nbr) */ update(po_nbr) begin if exists ( select * from deleted,BOrderItemCA where /* BOrderItemCA.po_nbr = deleted.po_nbr */ BOrderItemCA.po_nbr = deleted.po_nbr ) begin select @errno = 30005, @errmsg = 'Cannot UPDATE BPurchaseOrderCA because BOrderItemCA exists.' goto error end end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BCustomerCA R/5 BPurchaseOrderCA ON CHILD UPDATE SET NULL */ if /* update(username) */ update(username) begin update BPurchaseOrderCA set /* BPurchaseOrderCA.username = NULL */ BPurchaseOrderCA.username = NULL from BPurchaseOrderCA,inserted where /* */
not exists ( select * from BCustomerCA where /* inserted.username = BCustomerCA.username */ inserted.username = BCustomerCA.username ) end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO
CREATE PROCEDURE addCustomerSQL (
@username char(19), @fullname varchar(27), @password char(7), @credit_card_nbr char(18), @credit_card_expiry_date datetime, @mailing_address varchar(100), @phone_nbr char(14), @email_address char(50), @login_status bit, @credit_card_name char(27)
) AS INSERT INTO BCustomerCA VALUES (@username , @fullname, @password, @credit_card_nbr, @credit_card_expiry_date, @mailing_address,
@phone_nbr, @email_address, @login_status, @credit_card_name ) GO
SET QUOTED_IDENTIFIER OFF GO
SET ANSI_NULLS ON GO
----------------------------------------------------------
"William Ryan eMVP" <bi**@NoSp4m.devbuzz.com> wrote in message news:Or***************@TK2MSFTNGP12.phx.gbl... Hmm, do you have any binary fields in the DB? I'm hesitant to admit this, but I had this problem once and I realized I had defined a Varchar
field to VarBinary and that was ultimately the cause.
Can you post the table script and the SQL Statement you are using?
I'll be glad to create a table and give it a try...
Bill "Bryan" <ca**********@rogers.com> wrote in message news:Ge********************@news04.bloor.is.net.ca ble.rogers.com... > I noticed that also, yet the datatypes are teh same in sql and in
teh vb > code. this is driving me nuts. > > thanks again. > bry > > > "William Ryan eMVP" <bi**@NoSp4m.devbuzz.com> wrote in message > news:eA**************@TK2MSFTNGP11.phx.gbl... > > Bryan: > > > > What is the datatype of the field in SqlServer? Bit or Binary?
The Param > > type is specified as Bit but by the Exception message, I'm
wondering if > it's > > not Binary in the DB. If so, I believe changing the db type in
the client > > code to SqlDbType.Binary or changing the data type server side to
Bit may > do > > it for you. > > > > HTH, > > > > Bill > > "Bryan" <ca**********@rogers.com> wrote in message > >
news:5y********************@news04.bloor.is.net.ca ble.rogers.com... > > > hello all... > > > > > > im trying to add a record to an sql db on ms sql server 2000, using > > vb.net. > > > > > > seems to be working.. except for one thing, one of the columns
in the > > > database is a bit datatype, and though i get no syntax errors
when > > > compiling, i get an error indicated that the data would be truncated. > the > > > field is login_status. > > > > > > ive tried in quotes and not, giving it an integer variable with the > number > > 1 > > > in it, true/false statements.. ive tried a bunch of stuff... has me real > > > stumped.. > > > > > > have any ideas? > > > > > > thanks > > > > > > > > > here is the error it spits out. > > > > > > <b>* error while uploading original data</b>.<br />String or binary data > > > would be truncated.The statement has been terminated..Net SqlClient Data > > > Provider > > > > > > > > > > > > > > >
-------------------------------------------------------------------------- > > > here is the code: > > >
-------------------------------------------------------------------------- > > > Imports System.Data > > > > > > Imports System.Data.SqlClient > > > > > > > > > > > > > > > > > > Public Class WebForm1 > > > > > > Inherits System.Web.UI.Page > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Public Sub Register_User(ByVal sender As Object, ByVal E As EventArgs) > > > > > > Dim dbConnStr As String = "user id=**(blanked for > > > security)***;password=**(blanked for > > > security)**;database=BCdb;server=localhost" > > > > > > Dim dbConn As New System.Data.SqlClient.SqlConnection(dbConnStr) > > > > > > > > > > > > > > > > > > > > > > > > Try > > > > > > Dim cmd As New SqlCommand("addCustomerSQL", dbConn) > > > > > > cmd.CommandType = CommandType.StoredProcedure > > > > > > cmd.Parameters.Add(New SqlParameter("@username", SqlDbType.Char, 19)) > > > > > > cmd.Parameters.Add(New SqlParameter("@fullname", SqlDbType.VarChar, 27)) > > > > > > cmd.Parameters.Add(New SqlParameter("@password", SqlDbType.Char, 7)) > > > > > > cmd.Parameters.Add(New SqlParameter("@credit_card_nbr", SqlDbType.Char, > > 18)) > > > > > > cmd.Parameters.Add(New SqlParameter("@credit_card_expiry_date", > > > SqlDbType.DateTime)) > > > > > > cmd.Parameters.Add(New SqlParameter("@mailing_address", > SqlDbType.VarChar, > > > 100)) > > > > > > cmd.Parameters.Add(New SqlParameter("@phone_nbr",
SqlDbType.Char, 14)) > > > > > > cmd.Parameters.Add(New SqlParameter("@email_address", SqlDbType.Char, > 50)) > > > > > > cmd.Parameters.Add(New SqlParameter("@login_status", SqlDbType.Bit)) > > > > > > cmd.Parameters.Add(New SqlParameter("@credit_card_name", SqlDbType.Char, > > > 27)) > > > > > > cmd.Parameters.Item("@username").Value = RegUsrnameTXT.Text > > > > > > cmd.Parameters.Item("@fullname").Value = RegFullnameTXT.Text > > > > > > cmd.Parameters.Item("@password").Value = RegPasswordTXT.Text > > > > > > cmd.Parameters.Item("@credit_card_nbr").Value = > > RegCreditcardnumberTXT.Text > > > > > > cmd.Parameters.Item("@credit_card_expiry_date").Va lue = > > > RegCreditcardexpiryTXT.Text > > > > > > cmd.Parameters.Item("@mailing_address").Value =
RegAddressTXT.Text > > > > > > cmd.Parameters.Item("@email_address").Value = RegEmailTXT.Text > > > > > > cmd.Parameters.Item("@phone_nbr").Value = RegPhoneTXT.Text > > > > > > cmd.Parameters.Item("@login_status").Value = 1 > > > > > > cmd.Parameters.Item("@credit_card_name").Value = > RegCreditcardnameTXT.Text > > > > > > 'opent the connection to the database > > > > > > dbConn.Open() > > > > > > 'execute sql statement > > > > > > cmd.ExecuteNonQuery() > > > > > > dbConn.Close() > > > > > > Catch objError As Exception > > > > > > dberror.Text = "<b>* error while uploading original data</b>.<br
/>" & > > > objError.Message & objError.Source > > > > > > Exit Sub > > > > > > Finally > > > > > > If dbConn.State = ConnectionState.Open Then > > > > > > dbConn.Close() > > > > > > End If > > > > > > End Try > > > > > > End Sub > > > > > > > > > > > > End Class > > > > > >
-------------------------------------------------------------------------- > > -- > > > --- > > > > > > > > > > > > > > >
Bryan,
Try Convert.ToBoolean(1) on your assignment. If the error is being caused
by the bit field I assume it is because an integer value 1 won't fit.
Tom
"Bryan" <ca**********@rogers.com> wrote... cmd.Parameters.Item("@login_status").Value = 1
tried that.. really wished it worked.. gave me this.
<b>* error while uploading original data</b>.<br />String or binary data
would be truncated.The statement has been terminated..Net SqlClient Data
Provider
"Tom Leylan" <ge*@iamtiredofspam.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl... Bryan,
Try Convert.ToBoolean(1) on your assignment. If the error is being caused by the bit field I assume it is because an integer value 1 won't fit.
Tom
"Bryan" <ca**********@rogers.com> wrote...
cmd.Parameters.Item("@login_status").Value = 1
Bryan,
Are you quite certain it is the bit column that is generating the error?
Create a sample that only tries to update that column.
"Bryan" <ca**********@rogers.com> wrote in message
news:3Q********************@news04.bloor.is.net.ca ble.rogers.com... tried that.. really wished it worked.. gave me this.
<b>* error while uploading original data</b>.<br />String or binary data would be truncated.The statement has been terminated..Net SqlClient Data Provider
"Tom Leylan" <ge*@iamtiredofspam.com> wrote in message news:%2****************@TK2MSFTNGP09.phx.gbl... Bryan,
Try Convert.ToBoolean(1) on your assignment. If the error is being
caused by the bit field I assume it is because an integer value 1 won't fit.
Tom
"Bryan" <ca**********@rogers.com> wrote...
cmd.Parameters.Item("@login_status").Value = 1
Ehhh, just a thought, do you set the value to "1" or do you set it to '1'?
Ronny
"Bryan" <ca**********@rogers.com> wrote in message
news:cm********************@news04.bloor.is.net.ca ble.rogers.com... Im new to asp and have a little experience with java, so some concepts are foreign to me. from what i understand of your last post, some of the
fields being sent are too long? is that not avoided by the following statement?
cmd.Parameters.Add(New SqlParameter("@username", SqlDbType.Char, 19))
also, could the number "1" that im sending be sent in as a character as opposed to a bit? if im correct a character one would be 8 bits. could
this be what is happening?
eternally gratefull, bryan
"William Ryan eMVP" <bi**@NoSp4m.devbuzz.com> wrote in message news:OV**************@TK2MSFTNGP09.phx.gbl... Ok, if you write a Debug.Assert for each line ie asserting that the
lenght of textboxWhatever.text.Length <= FieldSize...see what happens. is
there any chance that your inputs are longer than the field size?
If you don't want to write out all of the Assertions, just use a Debug.WriteLine(textBoxWhatever.text.Length.ToStri ng()) for each of the text box lenghts and then compare them to the allowed field size.
I've done this directly with the parent table (excluding triggers and children ) and I cna get an insert to work but I've used data I knew
would be smaller than field size.
Let me play with it some more though and hopefully I can narrow it down.
"Bryan" <ca**********@rogers.com> wrote in message news:BJ********************@news04.bloor.is.net.ca ble.rogers.com... here is the sql script to create the database, at the end is the procedure im using.
i very much appreciate the help...
bryan
----------------------------------------------------- CREATE TABLE BCustomerCA ( username char(19) NOT NULL, fullname varchar(27) NULL, credit_card_nbr char(18) NULL, password char(7) NULL, credit_card_expiry_date datetime NULL, mailing__address varchar(100) NULL, phone_nbr char(14) NULL, email_address char(50) NULL, login_status bit NULL, credit_card_name char(27) NULL ) go
ALTER TABLE BCustomerCA ADD PRIMARY KEY NONCLUSTERED (username) go
CREATE TABLE BManufacturerCA ( manufacturer_id char(14) NOT NULL, manufacturer_name varchar(50) NULL, email_address char(50) NULL, contact_name varchar(27) NULL, phone_nbr char(14) NULL ) go
ALTER TABLE BManufacturerCA ADD PRIMARY KEY NONCLUSTERED (manufacturer_id) go
CREATE TABLE BOrderItemCA ( part_id char(18) NOT NULL, po_nbr char(18) NOT NULL, item_qty int NULL ) go
ALTER TABLE BOrderItemCA ADD PRIMARY KEY NONCLUSTERED (part_id, po_nbr) go
CREATE TABLE BPartCA ( part_id char(18) NOT NULL, part_name varchar(50) NULL, stock_size int NULL, manufacturer_id char(14) NULL, part_price decimal(10,2) NULL ) go
ALTER TABLE BPartCA ADD PRIMARY KEY NONCLUSTERED (part_id) go
CREATE TABLE BPurchaseOrderCA ( po_nbr char(18) NOT NULL, po_timetable datetime NULL, isprocessed bit NULL, username char(19) NULL ) go
ALTER TABLE BPurchaseOrderCA ADD PRIMARY KEY NONCLUSTERED (po_nbr) go
CREATE TABLE BStaffCA ( username char(19) NOT NULL, password char(7) NULL, login_status bit NULL, fullname varchar(27) NULL ) go
ALTER TABLE BStaffCA ADD PRIMARY KEY NONCLUSTERED (username) go
ALTER TABLE BOrderItemCA ADD FOREIGN KEY (po_nbr) REFERENCES BPurchaseOrderCA go
ALTER TABLE BOrderItemCA ADD FOREIGN KEY (part_id) REFERENCES BPartCA go
ALTER TABLE BPartCA ADD FOREIGN KEY (manufacturer_id) REFERENCES BManufacturerCA go
ALTER TABLE BPurchaseOrderCA ADD FOREIGN KEY (username) REFERENCES BCustomerCA go
create trigger tD_BCustomerCA on BCustomerCA for DELETE as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* DELETE trigger on BCustomerCA */ begin declare @errno int, @errmsg varchar(255) /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BCustomerCA R/5 BPurchaseOrderCA ON PARENT DELETE SET NULL */ update BPurchaseOrderCA set /* BPurchaseOrderCA.username = NULL */ BPurchaseOrderCA.username = NULL from BPurchaseOrderCA,deleted where /* BPurchaseOrderCA.username = deleted.username */ BPurchaseOrderCA.username = deleted.username
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tU_BCustomerCA on BCustomerCA for UPDATE as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* UPDATE trigger on BCustomerCA */ begin declare @numrows int, @nullcnt int, @validcnt int, @insusername char(19), @errno int, @errmsg varchar(255)
select @numrows = @@rowcount /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BCustomerCA R/5 BPurchaseOrderCA ON PARENT UPDATE SET NULL */ if /* update(username) */ update(username) begin update BPurchaseOrderCA set /* BPurchaseOrderCA.username = NULL */ BPurchaseOrderCA.username = NULL from BPurchaseOrderCA,deleted where /* BPurchaseOrderCA.username = deleted.username */ BPurchaseOrderCA.username = deleted.username end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tD_BManufacturerCA on BManufacturerCA for DELETE as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* DELETE trigger on BManufacturerCA */ begin declare @errno int, @errmsg varchar(255) /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BManufacturerCA R/4 BPartCA ON PARENT DELETE SET NULL */ update BPartCA set /* BPartCA.manufacturer_id = NULL */ BPartCA.manufacturer_id = NULL from BPartCA,deleted where /* BPartCA.manufacturer_id = deleted.manufacturer_id */ BPartCA.manufacturer_id = deleted.manufacturer_id
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tU_BManufacturerCA on BManufacturerCA for UPDATE as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* UPDATE trigger on BManufacturerCA */ begin declare @numrows int, @nullcnt int, @validcnt int, @insmanufacturer_id char(14), @errno int, @errmsg varchar(255)
select @numrows = @@rowcount /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BManufacturerCA R/4 BPartCA ON PARENT UPDATE SET NULL */ if /* update(manufacturer_id) */ update(manufacturer_id) begin update BPartCA set /* BPartCA.manufacturer_id = NULL */ BPartCA.manufacturer_id = NULL from BPartCA,deleted where /* BPartCA.manufacturer_id = deleted.manufacturer_id */ BPartCA.manufacturer_id = deleted.manufacturer_id end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tI_BOrderItemCA on BOrderItemCA for INSERT as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* INSERT trigger on BOrderItemCA */ begin declare @numrows int, @nullcnt int, @validcnt int, @errno int, @errmsg varchar(255)
select @numrows = @@rowcount /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BPurchaseOrderCA R/3 BOrderItemCA ON CHILD INSERT RESTRICT */ if /* update(po_nbr) */ update(po_nbr) begin select @nullcnt = 0 select @validcnt = count(*) from inserted,BPurchaseOrderCA where /* inserted.po_nbr = BPurchaseOrderCA.po_nbr */ inserted.po_nbr = BPurchaseOrderCA.po_nbr /* */
if @validcnt + @nullcnt != @numrows begin select @errno = 30002, @errmsg = 'Cannot INSERT BOrderItemCA because BPurchaseOrderCA does not exist.' goto error end end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BPartCA R/2 BOrderItemCA ON CHILD INSERT RESTRICT */ if /* update(part_id) */ update(part_id) begin select @nullcnt = 0 select @validcnt = count(*) from inserted,BPartCA where /* inserted.part_id = BPartCA.part_id */ inserted.part_id = BPartCA.part_id /* */
if @validcnt + @nullcnt != @numrows begin select @errno = 30002, @errmsg = 'Cannot INSERT BOrderItemCA because BPartCA
does not exist.' goto error end end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tU_BOrderItemCA on BOrderItemCA for UPDATE as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* UPDATE trigger on BOrderItemCA */ begin declare @numrows int, @nullcnt int, @validcnt int, @inspart_id char(18), @inspo_nbr char(18), @errno int, @errmsg varchar(255)
select @numrows = @@rowcount /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BPurchaseOrderCA R/3 BOrderItemCA ON CHILD UPDATE RESTRICT */ if /* update(po_nbr) */ update(po_nbr) begin select @nullcnt = 0 select @validcnt = count(*) from inserted,BPurchaseOrderCA where /* inserted.po_nbr = BPurchaseOrderCA.po_nbr */ inserted.po_nbr = BPurchaseOrderCA.po_nbr /* */
if @validcnt + @nullcnt != @numrows begin select @errno = 30007, @errmsg = 'Cannot UPDATE BOrderItemCA because BPurchaseOrderCA does not exist.' goto error end end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BPartCA R/2 BOrderItemCA ON CHILD UPDATE RESTRICT */ if /* update(part_id) */ update(part_id) begin select @nullcnt = 0 select @validcnt = count(*) from inserted,BPartCA where /* inserted.part_id = BPartCA.part_id */ inserted.part_id = BPartCA.part_id /* */
if @validcnt + @nullcnt != @numrows begin select @errno = 30007, @errmsg = 'Cannot UPDATE BOrderItemCA because BPartCA
does not exist.' goto error end end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tD_BPartCA on BPartCA for DELETE as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* DELETE trigger on BPartCA */ begin declare @errno int, @errmsg varchar(255) /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BPartCA R/2 BOrderItemCA ON PARENT DELETE RESTRICT */ if exists ( select * from deleted,BOrderItemCA where /* BOrderItemCA.part_id = deleted.part_id */ BOrderItemCA.part_id = deleted.part_id ) begin select @errno = 30001, @errmsg = 'Cannot DELETE BPartCA because BOrderItemCA exists.' goto error end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tI_BPartCA on BPartCA for INSERT as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* INSERT trigger on BPartCA */ begin declare @numrows int, @nullcnt int, @validcnt int, @errno int, @errmsg varchar(255)
select @numrows = @@rowcount /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BManufacturerCA R/4 BPartCA ON CHILD INSERT SET NULL */ if /* update(manufacturer_id) */ update(manufacturer_id) begin update BPartCA set /* BPartCA.manufacturer_id = NULL */ BPartCA.manufacturer_id = NULL from BPartCA,inserted where /* */
not exists ( select * from BManufacturerCA where /* inserted.manufacturer_id = BManufacturerCA.manufacturer_id */ inserted.manufacturer_id = BManufacturerCA.manufacturer_id ) end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tU_BPartCA on BPartCA for UPDATE as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* UPDATE trigger on BPartCA */ begin declare @numrows int, @nullcnt int, @validcnt int, @inspart_id char(18), @errno int, @errmsg varchar(255)
select @numrows = @@rowcount /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BPartCA R/2 BOrderItemCA ON PARENT UPDATE RESTRICT */ if /* update(part_id) */ update(part_id) begin if exists ( select * from deleted,BOrderItemCA where /* BOrderItemCA.part_id = deleted.part_id */ BOrderItemCA.part_id = deleted.part_id ) begin select @errno = 30005, @errmsg = 'Cannot UPDATE BPartCA because BOrderItemCA exists.' goto error end end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BManufacturerCA R/4 BPartCA ON CHILD UPDATE SET NULL */ if /* update(manufacturer_id) */ update(manufacturer_id) begin update BPartCA set /* BPartCA.manufacturer_id = NULL */ BPartCA.manufacturer_id = NULL from BPartCA,inserted where /* */
not exists ( select * from BManufacturerCA where /* inserted.manufacturer_id = BManufacturerCA.manufacturer_id */ inserted.manufacturer_id = BManufacturerCA.manufacturer_id ) end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tD_BPurchaseOrderCA on BPurchaseOrderCA for DELETE as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* DELETE trigger on BPurchaseOrderCA */ begin declare @errno int, @errmsg varchar(255) /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BPurchaseOrderCA R/3 BOrderItemCA ON PARENT DELETE RESTRICT */ if exists ( select * from deleted,BOrderItemCA where /* BOrderItemCA.po_nbr = deleted.po_nbr */ BOrderItemCA.po_nbr = deleted.po_nbr ) begin select @errno = 30001, @errmsg = 'Cannot DELETE BPurchaseOrderCA because BOrderItemCA exists.' goto error end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tI_BPurchaseOrderCA on BPurchaseOrderCA for INSERT as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* INSERT trigger on BPurchaseOrderCA */ begin declare @numrows int, @nullcnt int, @validcnt int, @errno int, @errmsg varchar(255)
select @numrows = @@rowcount /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BCustomerCA R/5 BPurchaseOrderCA ON CHILD INSERT SET NULL */ if /* update(username) */ update(username) begin update BPurchaseOrderCA set /* BPurchaseOrderCA.username = NULL */ BPurchaseOrderCA.username = NULL from BPurchaseOrderCA,inserted where /* */
not exists ( select * from BCustomerCA where /* inserted.username = BCustomerCA.username */ inserted.username = BCustomerCA.username ) end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
create trigger tU_BPurchaseOrderCA on BPurchaseOrderCA for UPDATE as /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* UPDATE trigger on BPurchaseOrderCA */ begin declare @numrows int, @nullcnt int, @validcnt int, @inspo_nbr char(18), @errno int, @errmsg varchar(255)
select @numrows = @@rowcount /* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BPurchaseOrderCA R/3 BOrderItemCA ON PARENT UPDATE RESTRICT */ if /* update(po_nbr) */ update(po_nbr) begin if exists ( select * from deleted,BOrderItemCA where /* BOrderItemCA.po_nbr = deleted.po_nbr */ BOrderItemCA.po_nbr = deleted.po_nbr ) begin select @errno = 30005, @errmsg = 'Cannot UPDATE BPurchaseOrderCA because BOrderItemCA exists.' goto error end end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ /* BCustomerCA R/5 BPurchaseOrderCA ON CHILD UPDATE SET NULL */ if /* update(username) */ update(username) begin update BPurchaseOrderCA set /* BPurchaseOrderCA.username = NULL */ BPurchaseOrderCA.username = NULL from BPurchaseOrderCA,inserted where /* */
not exists ( select * from BCustomerCA where /* inserted.username = BCustomerCA.username */ inserted.username = BCustomerCA.username ) end
/* ERwin Builtin Thu Jan 29 14:56:59 2004 */ return error: raiserror @errno @errmsg rollback transaction end go
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO
CREATE PROCEDURE addCustomerSQL (
@username char(19), @fullname varchar(27), @password char(7), @credit_card_nbr char(18), @credit_card_expiry_date datetime, @mailing_address varchar(100), @phone_nbr char(14), @email_address char(50), @login_status bit, @credit_card_name char(27)
) AS INSERT INTO BCustomerCA VALUES (@username , @fullname, @password, @credit_card_nbr, @credit_card_expiry_date, @mailing_address, @phone_nbr, @email_address, @login_status, @credit_card_name ) GO
SET QUOTED_IDENTIFIER OFF GO
SET ANSI_NULLS ON GO
----------------------------------------------------------
"William Ryan eMVP" <bi**@NoSp4m.devbuzz.com> wrote in message news:Or***************@TK2MSFTNGP12.phx.gbl... > Hmm, do you have any binary fields in the DB? I'm hesitant to admit this, > but I had this problem once and I realized I had defined a Varchar field to > VarBinary and that was ultimately the cause. > > Can you post the table script and the SQL Statement you are using? I'll be > glad to create a table and give it a try... > > Bill > "Bryan" <ca**********@rogers.com> wrote in message > news:Ge********************@news04.bloor.is.net.ca ble.rogers.com... > > I noticed that also, yet the datatypes are teh same in sql and in teh vb > > code. this is driving me nuts. > > > > thanks again. > > bry > > > > > > "William Ryan eMVP" <bi**@NoSp4m.devbuzz.com> wrote in message > > news:eA**************@TK2MSFTNGP11.phx.gbl... > > > Bryan: > > > > > > What is the datatype of the field in SqlServer? Bit or Binary? The > Param > > > type is specified as Bit but by the Exception message, I'm wondering if > > it's > > > not Binary in the DB. If so, I believe changing the db type in the > client > > > code to SqlDbType.Binary or changing the data type server side
to Bit > may > > do > > > it for you. > > > > > > HTH, > > > > > > Bill > > > "Bryan" <ca**********@rogers.com> wrote in message > > > news:5y********************@news04.bloor.is.net.ca ble.rogers.com... > > > > hello all... > > > > > > > > im trying to add a record to an sql db on ms sql server 2000, using > > > vb.net. > > > > > > > > seems to be working.. except for one thing, one of the columns in the > > > > database is a bit datatype, and though i get no syntax errors when > > > > compiling, i get an error indicated that the data would be truncated. > > the > > > > field is login_status. > > > > > > > > ive tried in quotes and not, giving it an integer variable
with the > > number > > > 1 > > > > in it, true/false statements.. ive tried a bunch of stuff...
has me > real > > > > stumped.. > > > > > > > > have any ideas? > > > > > > > > thanks > > > > > > > > > > > > here is the error it spits out. > > > > > > > > <b>* error while uploading original data</b>.<br />String or binary > data > > > > would be truncated.The statement has been terminated..Net SqlClient > Data > > > > Provider > > > > > > > > > > > > > > > > > > > > > >
-------------------------------------------------------------------------- > > > > here is the code: > > > > > >
-------------------------------------------------------------------------- > > > > Imports System.Data > > > > > > > > Imports System.Data.SqlClient > > > > > > > > > > > > > > > > > > > > > > > > Public Class WebForm1 > > > > > > > > Inherits System.Web.UI.Page > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Public Sub Register_User(ByVal sender As Object, ByVal E As EventArgs) > > > > > > > > Dim dbConnStr As String = "user id=**(blanked for > > > > security)***;password=**(blanked for > > > > security)**;database=BCdb;server=localhost" > > > > > > > > Dim dbConn As New
System.Data.SqlClient.SqlConnection(dbConnStr) > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Try > > > > > > > > Dim cmd As New SqlCommand("addCustomerSQL", dbConn) > > > > > > > > cmd.CommandType = CommandType.StoredProcedure > > > > > > > > cmd.Parameters.Add(New SqlParameter("@username",
SqlDbType.Char, 19)) > > > > > > > > cmd.Parameters.Add(New SqlParameter("@fullname",
SqlDbType.VarChar, > 27)) > > > > > > > > cmd.Parameters.Add(New SqlParameter("@password",
SqlDbType.Char, 7)) > > > > > > > > cmd.Parameters.Add(New SqlParameter("@credit_card_nbr", > SqlDbType.Char, > > > 18)) > > > > > > > > cmd.Parameters.Add(New
SqlParameter("@credit_card_expiry_date", > > > > SqlDbType.DateTime)) > > > > > > > > cmd.Parameters.Add(New SqlParameter("@mailing_address", > > SqlDbType.VarChar, > > > > 100)) > > > > > > > > cmd.Parameters.Add(New SqlParameter("@phone_nbr",
SqlDbType.Char, 14)) > > > > > > > > cmd.Parameters.Add(New SqlParameter("@email_address", SqlDbType.Char, > > 50)) > > > > > > > > cmd.Parameters.Add(New SqlParameter("@login_status", SqlDbType.Bit)) > > > > > > > > cmd.Parameters.Add(New SqlParameter("@credit_card_name", > SqlDbType.Char, > > > > 27)) > > > > > > > > cmd.Parameters.Item("@username").Value = RegUsrnameTXT.Text > > > > > > > > cmd.Parameters.Item("@fullname").Value = RegFullnameTXT.Text > > > > > > > > cmd.Parameters.Item("@password").Value = RegPasswordTXT.Text > > > > > > > > cmd.Parameters.Item("@credit_card_nbr").Value = > > > RegCreditcardnumberTXT.Text > > > > > > > > cmd.Parameters.Item("@credit_card_expiry_date").Va lue = > > > > RegCreditcardexpiryTXT.Text > > > > > > > > cmd.Parameters.Item("@mailing_address").Value = RegAddressTXT.Text > > > > > > > > cmd.Parameters.Item("@email_address").Value = RegEmailTXT.Text > > > > > > > > cmd.Parameters.Item("@phone_nbr").Value = RegPhoneTXT.Text > > > > > > > > cmd.Parameters.Item("@login_status").Value = 1 > > > > > > > > cmd.Parameters.Item("@credit_card_name").Value = > > RegCreditcardnameTXT.Text > > > > > > > > 'opent the connection to the database > > > > > > > > dbConn.Open() > > > > > > > > 'execute sql statement > > > > > > > > cmd.ExecuteNonQuery() > > > > > > > > dbConn.Close() > > > > > > > > Catch objError As Exception > > > > > > > > dberror.Text = "<b>* error while uploading original
data</b>.<br />" & > > > > objError.Message & objError.Source > > > > > > > > Exit Sub > > > > > > > > Finally > > > > > > > > If dbConn.State = ConnectionState.Open Then > > > > > > > > dbConn.Close() > > > > > > > > End If > > > > > > > > End Try > > > > > > > > End Sub > > > > > > > > > > > > > > > > End Class > > > > > > > > > >
-------------------------------------------------------------------------- > > > -- > > > > --- > > > > > > > > > > > > > > > > > > > > > > > >
i managed a work around.
to avoid the bit problems, i redesignated it as an integer in the asp.net
code, sent the integer "1" and upon recieving it, sql appropriately enters
it as a bit "1" as opposed to recieving some incorrectly formatted bit.
thank you for all your help, it is much appreciated.
bryan
"Tom Leylan" <ge*@iamtiredofspam.com> wrote in message
news:ub**************@TK2MSFTNGP10.phx.gbl... Bryan,
Are you quite certain it is the bit column that is generating the error? Create a sample that only tries to update that column.
"Bryan" <ca**********@rogers.com> wrote in message news:3Q********************@news04.bloor.is.net.ca ble.rogers.com... tried that.. really wished it worked.. gave me this.
<b>* error while uploading original data</b>.<br />String or binary data would be truncated.The statement has been terminated..Net SqlClient Data Provider
"Tom Leylan" <ge*@iamtiredofspam.com> wrote in message news:%2****************@TK2MSFTNGP09.phx.gbl... Bryan,
Try Convert.ToBoolean(1) on your assignment. If the error is being caused by the bit field I assume it is because an integer value 1 won't fit.
Tom
"Bryan" <ca**********@rogers.com> wrote...
> cmd.Parameters.Item("@login_status").Value = 1
"Bryan" <ca**********@rogers.com> wrote in message news:<5y********************@news04.bloor.is.net.c able.rogers.com>... hello all...
im trying to add a record to an sql db on ms sql server 2000, using vb.net.
Bryan,
I noticed two things. First you are allowing null values in the bit
field, this is bad form as well as bad logic. Second, the next line
(from your code) seems to be missing a value:
cmd.Parameters.Add(New SqlParameter("@login_status", SqlDbType.Bit))
HTH,
Charlie This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Jamie Fryatt |
last post by:
Hi everyone, here's what id like to do.
I have a table with 2 fields, name and value
I need to be able to add multiple records quickly, for example I need to add
name value
abc 1...
|
by: brenda.stow |
last post by:
error msg " An error occured while referencing the object. You tried to
run a visual basic procedure that improperly references a property or
method of an object"
This msg occurs everytime I add a...
|
by: Clayton Hamilton |
last post by:
I have a DataGrid on a webform bound to a Datasource and can
successfully use <ItemTemplate> to create edit/update/cancel
functionality for user maintenance of data. I use separate logic to
delete...
|
by: Jim Heavey |
last post by:
Trying to figure out the technique which should be used
to add rows to a datagrid. I am thinking that I would
want an "Add" button on the footer, but I am not quite
sure how to do that. Is that...
|
by: Greg |
last post by:
Binding Manager & dataset - won't add record
I've got an untyped dataset with controls bound through code. The user
can select a question number from a bound combobox, and the question
number and...
|
by: Rudy |
last post by:
Hi all,
I know this is easy, just can't seem to get it. I have a windows form, and
a text box, with a value already in it. I need to add that value to a table.
It's just one value, so the entire...
|
by: shumaker |
last post by:
I'm wondering if adding an autonumber primary key will improve the
performance of a multiuser access database on a network share. I have
a website that lists many tips for improving performance of...
|
by: john |
last post by:
In my form (table A) I have subform (table B (only 2 fieds: ID and App_name)
where table A -Table B are linked 1XM. To be able to add a record in the
subform I want to use a lookup form since the...
|
by: tonylove42 |
last post by:
HI, I have probelm adding, updating and deleteing a record. Here is my code for adding . The error I get is:
Operation must use an updateable query.
Line 135: MyCmd.ExecuteNonQuery()
...
|
by: ConfusedMay |
last post by:
Hi,
I'm working on a database that has a form which hold all of our products testing. each product can hold more than 1 complete test (complete test here means that product has to have before and...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |