Input string was not in a correct format.

Guest · Oct 28, 2005

Hi

I am trying to insert some value into SQL Server 2000 tables and I am keep
getting the "Input string was not in a correct format" error.

When user fills the Form it updates the table call tbl_04_Hellenic.
Then In that form some of data also goes and fills another table call
tbl_LEHTAR.

Here is my code
----------------------------------------------------------------------------------------------

'INSERT INTO TABLE: tbl_04_HELLENIC
Private Sub Update_DAHILI_HELLENIC()

Dim myConnection As SqlConnection
Dim cmdSQL As New SqlCommand

myConnection = New SqlConnection(clsConst.strConAccess)
cmdSQL.Connection = myConnection
cmdSQL.Connection.Open()

cmdSQL.CommandText = "INSERT INTO tbl_04_HELLENIC" & _

"(ID,CHQ_GIRIS_TARIHI,CHQ_AMIR,CHQ_AMIRKS_NO,CHQ_ATURKODU,CHQ_KAYIT_TARIHI,CHQ_BRANCH,CHQ_DOVIZCINSI,CHQ_DOVIZKURU,CHQ_DOVIZKURU_USD,CHQ_AMOUNT,CHQ_AMOUNT_YTL,CHQ_NUMBER,CHQ_SIRANO,CHQ_LEHTAR,CHQ_LEHTARKS_NO,CHQ_LTURKODU,CHQ_BANKA_ADI,CHQ_BANKA_ADRESI,CHQ_ACCNO,CHQ_BANKA_SWIFTKODU,CHQ_INFO,CHQ_PRINTEBY,CHQ_ULKEKODU,CHQ_TRANSACT_TYPE,CHQ_FOR,CHQ_RPT_TYPE)VALUES " & _

"(@mID,@mDate,@mAmir,@mAmirKSNO,@mAmirTurKodu,@mRegDate," & _

"@mBranchNo,@mDovizCinsi,@mDovizKuru,@mDovizKuruUSD,@mAmount,@mAmountYTL," & _

"@mChqNo,@mChqSiraNo,@mLehtar,@mLehtarKSNO,@mLehtarTurKodu," & _

"@mBankName,@mBankAdres,@mBankAccNo,@mBankSwiftKodu,@mInfo," & _

"@mPrintedBy,@mUlkeKodu,@mIslemTuru,@mFor,@mReportType)"

cmdSQL.Parameters.Add(New SqlParameter("@mID", SqlDbType.BigInt, 8,
mID))
cmdSQL.Parameters(0).Value = mID

cmdSQL.Parameters.Add(New SqlParameter("@mDate", SqlDbType.DateTime,
8, mDate))
cmdSQL.Parameters(1).Value = mDate

cmdSQL.Parameters.Add(New SqlParameter("@mAmir", SqlDbType.NChar, 50,
mAmir))
cmdSQL.Parameters(2).Value = mAmir

cmdSQL.Parameters.Add(New SqlParameter("@mAmirKSNO", SqlDbType.NChar,
7, mAmirKSNO))
cmdSQL.Parameters(3).Value = mAmirKSNO

cmdSQL.Parameters.Add(New SqlParameter("@mAmirTurKodu",
SqlDbType.NChar, 2, mAmirTurKodu))
cmdSQL.Parameters(4).Value = mAmirTurKodu

cmdSQL.Parameters.Add(New SqlParameter("@mRegDate",
SqlDbType.DateTime, 8, mRegDate))
cmdSQL.Parameters(5).Value = mRegDate

cmdSQL.Parameters.Add(New SqlParameter("@mBranchNo", SqlDbType.NChar,
2, mBranchNo))
cmdSQL.Parameters(6).Value = mBranchNo

cmdSQL.Parameters.Add(New SqlParameter("@mDovizCinsi",
SqlDbType.NVarChar, 4, mDovizCinsi))
cmdSQL.Parameters(7).Value = mDovizCinsi

cmdSQL.Parameters.Add(New SqlParameter("@mDovizKuru", SqlDbType.Real,
4, mDovizKuru))
cmdSQL.Parameters(8).Value = mDovizKuru

cmdSQL.Parameters.Add(New SqlParameter("@mDovizKuruUSD",
SqlDbType.Real, 4, mDovizKuruUSD))
cmdSQL.Parameters(9).Value = mDovizKuruUSD

cmdSQL.Parameters.Add(New SqlParameter("@mAmount", SqlDbType.Money, 8,
mAmount))
cmdSQL.Parameters(10).Value = mAmount

cmdSQL.Parameters.Add(New SqlParameter("@mAmountYTL", SqlDbType.Money,
8, mAmountYTL))
cmdSQL.Parameters(11).Value = mAmountYTL

cmdSQL.Parameters.Add(New SqlParameter("@mChqNo", SqlDbType.NChar, 10,
mChqNo))
cmdSQL.Parameters(12).Value = mChqNo

cmdSQL.Parameters.Add(New SqlParameter("@mChqSiraNo",
SqlDbType.BigInt, 8, mChqSiraNo))
cmdSQL.Parameters(13).Value = mChqSiraNo

cmdSQL.Parameters.Add(New SqlParameter("@mLehtar", SqlDbType.NChar,
50, mLehtar))
cmdSQL.Parameters(14).Value = mLehtar

cmdSQL.Parameters.Add(New SqlParameter("@mLehtarKSNO",
SqlDbType.NChar, 2, mLehtarKSNO))
cmdSQL.Parameters(15).Value = mLehtarKSNO

cmdSQL.Parameters.Add(New SqlParameter("@mLehtarTurKodu",
SqlDbType.NChar, 2, mLehtarTurKodu))
cmdSQL.Parameters(16).Value = mLehtarTurKodu

cmdSQL.Parameters.Add(New SqlParameter("@mBankName", SqlDbType.NChar,
50, mBankName))
cmdSQL.Parameters(17).Value = mBankName

cmdSQL.Parameters.Add(New SqlParameter("@mBankAdres", SqlDbType.NChar,
100, mBankAdres))
cmdSQL.Parameters(18).Value = mBankAdres

cmdSQL.Parameters.Add(New SqlParameter("@mBankAccNo", SqlDbType.NChar,
30, mBankAccNo))
cmdSQL.Parameters(19).Value = mBankAccNo

cmdSQL.Parameters.Add(New SqlParameter("@mBankSwiftKodu",
SqlDbType.NChar, 20, mBankSwiftKodu))
cmdSQL.Parameters(20).Value = mBankSwiftKodu

cmdSQL.Parameters.Add(New SqlParameter("@mInfo", SqlDbType.NChar, 30,
mInfo))
cmdSQL.Parameters(21).Value = mInfo

cmdSQL.Parameters.Add(New SqlParameter("@mPrintedBy", SqlDbType.NChar,
30, mPrintedBy))
cmdSQL.Parameters(22).Value = mPrintedBy

cmdSQL.Parameters.Add(New SqlParameter("@mUlkeKodu", SqlDbType.NChar,
3, mUlkeKodu))
cmdSQL.Parameters(23).Value = mUlkeKodu

cmdSQL.Parameters.Add(New SqlParameter("@mIslemTuru", SqlDbType.NChar,
6, mIslemTuru))
cmdSQL.Parameters(24).Value = mIslemTuru

cmdSQL.Parameters.Add(New SqlParameter("@mFor", SqlDbType.NChar, 50,
mFor))
cmdSQL.Parameters(25).Value = mFor

cmdSQL.Parameters.Add(New SqlParameter("@mReportType", SqlDbType.Int,
4, mReportType))
cmdSQL.Parameters(26).Value = mReportType

Try
cmdSQL.ExecuteNonQuery()
myConnection.Close()
Catch ex As FormatException
Dim msg As String = "Bilgiler DatabankasÄ±na aktarÄ±lamamÄ±ÅŸtÄ±r: " &
vbCrLf
Dim tmpSTR As String
tmpSTR = "TB-Ã‡EK PRINT Version: 1.0.0a" & vbCrLf & vbCrLf
tmpSTR = tmpSTR & msg & ex.Message.ToString & vbCrLf
MsgBox(tmpSTR, MsgBoxStyle.Critical, "TB-Ã‡EK PRINT")
Finally
myConnection.Close()
End Try

End Sub

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

Here is my other sub that it works

'INSERT INTO TABLE: tbl_AMIR
Private Sub Update_AMIR()
'Check if the Current Lehtar name in the SQL Data Base
Dim row As DataRow
For Each row In clsDATASET.tblAMIR.Rows
If mLehtar = Trim(row(1)) Then
Exit Sub
End If
Next

'SET ID NUMBER
Dim row1 As DataRow
Dim mySiraNo As String
For Each row In clsDATASET.tblAMIR.Rows
mySiraNo = row(0)
Next
mySiraNo = mySiraNo + 1
mID = mySiraNo

Dim myConnection As SqlConnection
Dim cmdSQL As New SqlCommand

myConnection = New SqlConnection(clsConst.strConAccess)
cmdSQL.Connection = myConnection
cmdSQL.Connection.Open()

cmdSQL.CommandText = "INSERT INTO tbl_AMIR
(ID,AMIR_ADI,AMIR_KIMLIKSICIL_NO,AMIR_TURKODU)VALUES (@mID, @mAmir,
@mAmirKSNO, @mAmirTurKodu)"

cmdSQL.Parameters.Add(New SqlParameter("@mID", SqlDbType.BigInt, 8,
mID))
cmdSQL.Parameters(0).Value = mID

cmdSQL.Parameters.Add(New SqlParameter("@mAmir", SqlDbType.NChar, 50,
mAmir))
cmdSQL.Parameters(1).Value = mAmir

cmdSQL.Parameters.Add(New SqlParameter("@mAmirKSNO", SqlDbType.NChar,
7, mAmirKSNO))
cmdSQL.Parameters(2).Value = mAmirKSNO

cmdSQL.Parameters.Add(New SqlParameter("@mAmirTurKodu",
SqlDbType.NChar, 2, mAmirTurKodu))
cmdSQL.Parameters(3).Value = mAmirTurKodu

Try
cmdSQL.ExecuteNonQuery()
myConnection.Close()
Catch ex As Exception
Dim msg As String = "(AMIRLAER TABLOSU) Bilgiler DatabankasÄ±na
aktarÄ±lamamÄ±ÅŸtÄ±r: " & vbCrLf
Dim tmpSTR As String
tmpSTR = "TB-Ã‡EK PRINT Version: 1.0.0a" & vbCrLf & vbCrLf
tmpSTR = tmpSTR & msg & ex.Message.ToString & vbCrLf
MsgBox(tmpSTR, MsgBoxStyle.Critical, "TB-Ã‡EK PRINT")
Finally
myConnection.Close()
End Try
End Sub

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

I have no idea that first one is fail and next sub updfates its database.

I have checked the code and didn't find any problem. Can someone check it
for me.

I thank you in advance reading my post.

Rgds.
GC

CT · Oct 28, 2005

Have you tried stepping through the code, or just running in Debug mode to
see which line of code that causes the problem?

Guest · Oct 28, 2005

Hi CT,

It fails when reaches the ExecuteNonQuery line.
Try
cmdSQL.ExecuteNonQuery()
myConnection.Close()
Catch ex As Exception

And I have no idea why this is happining. I only find in sting the "
CHQ_INFO "
was written as CHQ_INFO but in SQL Database column name was CHQINFO.

After I corrected and I checked at this 3 times and still fails when I am
inthe debug mode
and line was cmdSQL.ExecuteNonQuery().

Thank you for your kind understanding.

Rgds,
GC

CT · Oct 28, 2005

Have you checked the CommandText property of the SelectCommand just before
calling the ExecuteNonQuery method?

Guest · Oct 28, 2005

Hi CT,

After my previous post I used the debuging and use to Step Into method. The
last column has int varibale SQL DATA TYPE and Lenght was 4. But I was trying
to insert String SQL DATA TYPE with Lenght 8. Once I fixed it everything
workd fine.

I thank you for your kind help. You showed me the right direction.

Rgds,
GC

Input string was not in a correct format.

Guest

CT

Guest

CT

Guest