Converting Boolean to Bit...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I and pretty new to .Net. I am trying to save a value back to a SQL Server
database from a Windows form. I keep getting this error?

A first chance exception of type 'System.FormatException' occurred in
mscorlib.dll
Additional information: Input string was not in a correct format.

This line turns green

Dim p8 As New SqlParameter("InspectionRequired", Data.SqlDbType.Bit,
("InspectionRequired"))

Now the field in the database is of type bit.


Any Ideas?
 
Try
Dim p8 As New SqlParameter("InspectionRequired", SqlDbType.Bit)
p8.value = InspectionRequired
(assuming that InspectionRequired is declared as Boolean)

Jav
 
Jav,

That worked Why?

Leo

Jav said:
Try
Dim p8 As New SqlParameter("InspectionRequired", SqlDbType.Bit)
p8.value = InspectionRequired
(assuming that InspectionRequired is declared as Boolean)

Jav
Click to expand...
 
Jav,

I am now getting a different error. I was wondering if you could help me
with this one?

A first chance exception of type 'System.Data.SqlClient.SqlException'
occurred in system.data.dll

Additional information: System error.

I get it in both the UpdateSub and the Insert Sub?

I get it on the ExecuteNonQuery???

I appreciate your help.

Private Sub updateJobLoss()

Dim cmdUpdate As New SqlCommand

cmdUpdate.Connection = SqlConnection1()
cmdUpdate.CommandType = CommandType.Text
cmdUpdate.CommandText = "UPDATE JobAccident SET JobNumber = ?,
AmountofLoss = ? DateofAccident = ? ,RepairType = ?, Employee = ?,
DateofReport = ?, ReportedBy = ?, InspectionRequired = ?, BackCharge = ?,
Description = ?, AttachmentPath = ? WHERE JobAccident_ID = ?"

Dim p1 As New SqlParameter("JobNumber", Data.SqlDbType.NVarChar,
250, "JobNumber")
Dim p2 As New SqlParameter("LossAmount", Data.SqlDbType.Decimal)
Dim p3 As New SqlParameter("DateofAccident",
Data.SqlDbType.NVarChar, 50, "DateofAccident")
Dim p4 As New SqlParameter("RepairType", Data.SqlDbType.NVarChar,
50, "RepairType")
Dim p5 As New SqlParameter("Employee", Data.SqlDbType.NVarChar, 50,
"Employee")
Dim p6 As New SqlParameter("DateofReport", Data.SqlDbType.NVarChar,
50, "DateofReport")
Dim p7 As New SqlParameter("ReportedBy", Data.SqlDbType.NVarChar,
50, "ReportedBy")
Dim p8 As New SqlParameter("InspectionRequired", Data.SqlDbType.Bit)
Dim p9 As New SqlParameter("BackCharge", Data.SqlDbType.Bit)
Dim p10 As New SqlParameter("Description", Data.SqlDbType.NVarChar,
250, "Description")
Dim p11 As New SqlParameter("AttachmentPath",
Data.SqlDbType.NVarChar, 50, "AttachmentPath")

p1.Value = m_JobNumber
p2.Value = m_LossAmount
p3.Value = m_DateofAccident
p4.Value = m_RepairType
p5.Value = m_Employee
p6.Value = m_DateofReport
p7.Value = m_ReportedBy
p8.Value = m_InspectionRequired
p9.Value = m_BackCharge
p10.Value = m_Description
p11.Value = m_AttachmentPath


cmdUpdate.Parameters.Add(p1)
cmdUpdate.Parameters.Add(p2)
cmdUpdate.Parameters.Add(p3)
cmdUpdate.Parameters.Add(p4)
cmdUpdate.Parameters.Add(p5)
cmdUpdate.Parameters.Add(p6)
cmdUpdate.Parameters.Add(p7)
cmdUpdate.Parameters.Add(p8)
cmdUpdate.Parameters.Add(p9)
cmdUpdate.Parameters.Add(p10)
cmdUpdate.Parameters.Add(p11)


SqlConnection1().Open()

Dim rowsAffected As Integer
rowsAffected = cmdUpdate.ExecuteNonQuery

SqlConnection1().Close()

End Sub

Private Sub insertJobLoss()

cmdInsert.Parameters("@JobNumber").Value = m_JobNumber
cmdInsert.Parameters("@AmountofLoss").Value = m_LossAmount
cmdInsert.Parameters("@DateOfAccident").Value = m_DateofAccident
cmdInsert.Parameters("@RepairType").Value = m_RepairType
cmdInsert.Parameters("@employee").Value = m_Employee
cmdInsert.Parameters("@DateofReport").Value = m_DateofReport
cmdInsert.Parameters("@ReportedBy").Value = m_ReportedBy
cmdInsert.Parameters("@InspectionRequired").Value =
m_InspectionRequired
cmdInsert.Parameters("@BackCharge").Value = m_BackCharge
cmdInsert.Parameters("@Description").Value = m_Description
cmdInsert.Parameters("@AttachmentPath").Value = m_AttachmentPath

SqlConnection1().Open()

Dim rowsAffected As Integer
rowsAffected = cmdInsert.ExecuteNonQuery

SqlConnection1().Close()

End Sub
 
Hi Leo,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you're receiving a system error when
trying to update a record. If there is any misunderstanding, please feel
free to let me know.

Based on my research, I think there are something wrong with the UPDATE
statement and the parameters.

1. The parameters have to be named in the UPDATE statement when you're
working on a SQL server database. We add a @ at the beginning of the
parameter name. For example,

UPDATE JobAccident SET JobNumber = @JobNumber, AmountofLoss = @AmountofLoss
DateofAccident = @DateofAccident.

2. When you're working on a single record update with ExecuteNonQuery, you
needn't specify the source column name as the fourth argument. Also, we
have to name the parameter according to the parameter name in the command
text.

Dim p1 As New SqlParameter("@JobNumber", Data.SqlDbType.NVarChar,
250)

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Kevin,

You are correct all of my updates are getting this erorr.

I changed the Update to look like this: (I am still getting the error. )

Private Sub updateJobLoss()

Dim cmdUpdate As New SqlCommand

cmdUpdate.Connection = SqlConnection1()
cmdUpdate.CommandType = CommandType.Text
cmdUpdate.CommandText = "UPDATE JobAccident SET AmountofLoss =
@LossAmount DateofAccident = @dateofAccident ,RepairType = @RepairType,
Employee = @Employee, DateofReport = @DateofReport, ReportedBy = @ReportedBy,
InspectionRequired = @InspectionRequired, BackCharge = @BackCharge,
Description = @Description, AttachmentPath = @AttachmentPath WHERE JobNumber
= @JobNumber"

Dim p1 As New SqlParameter("JobNumber", Data.SqlDbType.NVarChar, 250)
Dim p2 As New SqlParameter("LossAmount", Data.SqlDbType.Decimal)
Dim p3 As New SqlParameter("DateofAccident",
Data.SqlDbType.NVarChar, 50)
Dim p4 As New SqlParameter("RepairType", Data.SqlDbType.NVarChar, 50)
Dim p5 As New SqlParameter("Employee", Data.SqlDbType.NVarChar, 50)
Dim p6 As New SqlParameter("DateofReport", Data.SqlDbType.NVarChar,
50)
Dim p7 As New SqlParameter("ReportedBy", Data.SqlDbType.NVarChar, 50)
Dim p8 As New SqlParameter("InspectionRequired", Data.SqlDbType.Bit)
Dim p9 As New SqlParameter("BackCharge", Data.SqlDbType.Bit)
Dim p10 As New SqlParameter("Description", Data.SqlDbType.NVarChar,
250)
Dim p11 As New SqlParameter("AttachmentPath",
Data.SqlDbType.NVarChar, 50)

p1.Value = m_JobNumber
p2.Value = m_LossAmount
p3.Value = m_DateofAccident
p4.Value = m_RepairType
p5.Value = m_Employee
p6.Value = m_DateofReport
p7.Value = m_ReportedBy
p8.Value = m_InspectionRequired
p9.Value = m_BackCharge
p10.Value = m_Description
p11.Value = m_AttachmentPath


cmdUpdate.Parameters.Add(p1)
cmdUpdate.Parameters.Add(p2)
cmdUpdate.Parameters.Add(p3)
cmdUpdate.Parameters.Add(p4)
cmdUpdate.Parameters.Add(p5)
cmdUpdate.Parameters.Add(p6)
cmdUpdate.Parameters.Add(p7)
cmdUpdate.Parameters.Add(p8)
cmdUpdate.Parameters.Add(p9)
cmdUpdate.Parameters.Add(p10)
cmdUpdate.Parameters.Add(p11)


SqlConnection1().Open()

Dim rowsAffected As Integer
rowsAffected = cmdUpdate.ExecuteNonQuery

SqlConnection1().Close()
 
Hi Leo,

Please try the following. The SqlParameters has to be named according to
the command text. @ has to be added. HTH.

Private Sub updateJobLoss()

Dim cmdUpdate As New SqlCommand

cmdUpdate.Connection = SqlConnection1()
cmdUpdate.CommandType = CommandType.Text
cmdUpdate.CommandText = "UPDATE JobAccident SET AmountofLoss =
@LossAmount DateofAccident = @dateofAccident ,RepairType = @RepairType,
Employee = @Employee, DateofReport = @DateofReport, ReportedBy =
@ReportedBy,
InspectionRequired = @InspectionRequired, BackCharge = @BackCharge,
Description = @Description, AttachmentPath = @AttachmentPath WHERE
JobNumber
= @JobNumber"

Dim p1 As New SqlParameter("@JobNumber", Data.SqlDbType.NVarChar,
250)
Dim p2 As New SqlParameter("@LossAmount", Data.SqlDbType.Decimal)
Dim p3 As New SqlParameter("@DateofAccident",
Data.SqlDbType.NVarChar, 50)
Dim p4 As New SqlParameter("@RepairType", Data.SqlDbType.NVarChar,
50)
Dim p5 As New SqlParameter("@Employee", Data.SqlDbType.NVarChar, 50)
Dim p6 As New SqlParameter("@DateofReport",
Data.SqlDbType.NVarChar,
50)
Dim p7 As New SqlParameter("@ReportedBy", Data.SqlDbType.NVarChar,
50)
Dim p8 As New SqlParameter("@InspectionRequired",
Data.SqlDbType.Bit)
Dim p9 As New SqlParameter("@BackCharge", Data.SqlDbType.Bit)
Dim p10 As New SqlParameter("@Description",
Data.SqlDbType.NVarChar,
250)
Dim p11 As New SqlParameter("@AttachmentPath",
Data.SqlDbType.NVarChar, 50)

p1.Value = m_JobNumber
p2.Value = m_LossAmount
p3.Value = m_DateofAccident
p4.Value = m_RepairType
p5.Value = m_Employee
p6.Value = m_DateofReport
p7.Value = m_ReportedBy
p8.Value = m_InspectionRequired
p9.Value = m_BackCharge
p10.Value = m_Description
p11.Value = m_AttachmentPath


cmdUpdate.Parameters.Add(p1)
cmdUpdate.Parameters.Add(p2)
cmdUpdate.Parameters.Add(p3)
cmdUpdate.Parameters.Add(p4)
cmdUpdate.Parameters.Add(p5)
cmdUpdate.Parameters.Add(p6)
cmdUpdate.Parameters.Add(p7)
cmdUpdate.Parameters.Add(p8)
cmdUpdate.Parameters.Add(p9)
cmdUpdate.Parameters.Add(p10)
cmdUpdate.Parameters.Add(p11)


SqlConnection1().Open()

Dim rowsAffected As Integer
rowsAffected = cmdUpdate.ExecuteNonQuery

SqlConnection1().Close()

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Hello Kevin,

It's still doing the same thing. It blows up and turns this line green.

rowsAffected = cmdUpdate.ExecuteNonQuery

Any other Ideas?

Thanks, Leo
 
Hi Leo,

If the code fails to execute, when the exception thown, there will be
exception messages available. Could you let me know the exception message
and what kind of exception it is?

The code seems to be all right. You can also check to see if the m_
variables matchs the parameter type mapping. For a complete list of data
types mapping from .NET Framework data provider data types to .NET
Framework data types, please check the following link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/htm
l/cpconmappingnetdataproviderdatatypestonetframeworkdatatypes.asp

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top