locked
Error converting data type varchar to bigint RRS feed

  • Question

  • 

    hi,please i need help.

    when i try to insert data on the sql database have this error message appear. "Error converting data type varchar to bigint."

    this is the current Code:

    oDataadapter1 = New SqlDataAdapter("Insert into dbo.batch values ('" + Trim(txtBatch_ID.Text) + "','" + Trim("Doccas Food - Spain") + "','" + Trim("ReportName") + "','" + Trim("2020-10-26 13:00:00") + "','" + Trim("Ricardo") + "','" + Trim("2020-10-26 13:00:00") + "','" + Trim("2020-10-26 13:00:00") + "','" + Trim("2020-10-26 14:00:00") + "','" + "10" + "','" + "Test" + "','" + "1500.0" + "','" + "1510.0" + "','" + "1" + "','" + "4" + "','" + "200" + "','" + "210" + "','" + "2" + "','" + "3" + "','" + "0" + "," + "')", CC)
    Dim oCmd1 As SqlCommandBuilder = New SqlCommandBuilder(oDataadapter1)
    odataset1 = New DataSet
    oDataadapter1.Fill(odataset1, "batch")

    The data that generates the error is the number 19, (marked with bold, in the code there is a "0" to test)

    Tuesday, November 3, 2020 3:06 PM

Answers

  • Try something like this: …"2" + "','" + "3" + "'," + "0" + ")", CC), i.e. remove the incorrect apostrophes.

    Clarify if more values are required after this “0”. And consider Parameterised Queries, which are more reliable.

    Since this is Visual Basic, use ‘&’ instead of ‘+’.

    Show the modified statement if still does not work.


    • Edited by Viorel_MVP Tuesday, November 3, 2020 4:35 PM
    • Proposed as answer by Maxi456 Tuesday, November 3, 2020 4:36 PM
    • Marked as answer by ri_montoya Wednesday, November 4, 2020 7:19 AM
    Tuesday, November 3, 2020 4:35 PM

All replies

  • Try something like this: …"2" + "','" + "3" + "'," + "0" + ")", CC), i.e. remove the incorrect apostrophes.

    Clarify if more values are required after this “0”. And consider Parameterised Queries, which are more reliable.

    Since this is Visual Basic, use ‘&’ instead of ‘+’.

    Show the modified statement if still does not work.


    • Edited by Viorel_MVP Tuesday, November 3, 2020 4:35 PM
    • Proposed as answer by Maxi456 Tuesday, November 3, 2020 4:36 PM
    • Marked as answer by ri_montoya Wednesday, November 4, 2020 7:19 AM
    Tuesday, November 3, 2020 4:35 PM
  • I second using parameters

    From Microsoft page.

    Public Function CreateSqlDataAdapter(ByVal selectCommand As SqlCommand, _
        ByVal connection As SqlConnection) As SqlDataAdapter
    
        Dim adapter As New SqlDataAdapter(selectCommand)
        adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    
        ' Create the commands.
        adapter.InsertCommand = New SqlCommand( _
            "INSERT INTO Customers (CustomerID, CompanyName) " & _
             "VALUES (@CustomerID, @CompanyName)", connection)
    
        adapter.UpdateCommand = New SqlCommand( _
            "UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " & _
            "WHERE CustomerID = @oldCustomerID", connection)
    
        adapter.DeleteCommand = New SqlCommand( _
            "DELETE FROM Customers WHERE CustomerID = @CustomerID", connection)
    
        ' Create the parameters.
        adapter.InsertCommand.Parameters.Add("@CustomerID", _
            SqlDbType.Char, 5, "CustomerID")
        adapter.InsertCommand.Parameters.Add("@CompanyName", _
            SqlDbType.VarChar, 40, "CompanyName")
    
        adapter.UpdateCommand.Parameters.Add("@CustomerID", _
            SqlDbType.Char, 5, "CustomerID")
        adapter.UpdateCommand.Parameters.Add("@CompanyName", _
            SqlDbType.VarChar, 40, "CompanyName")
        adapter.UpdateCommand.Parameters.Add("@oldCustomerID", _
            SqlDbType.Char, 5, "CustomerID").SourceVersion = DataRowVersion.Original
    
        adapter.DeleteCommand.Parameters.Add("@CustomerID", _
            SqlDbType.Char, 5, "CustomerID").SourceVersion = DataRowVersion.Original
    
        Return adapter
    End Function


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    My GitHub code samples
    GitHub page

    Tuesday, November 3, 2020 6:04 PM
  • Hi again Viorel! :P

    Thanks for your answer, was very helpful to me,  it's solved.

    Thanks!

    Wednesday, November 4, 2020 7:20 AM
  • Do not ignore the suggestions related to Parameterised Queries, which do not only make the code readable, but also avoid errors and introduce a shield against attacks.

    Wednesday, November 4, 2020 10:01 AM