String or binary data would be truncated. The statement has been terminated

Question

Protected Sub cmdOK_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdOK.Click
        Dim medID, medName, comment As String
        Dim power, period, qtty As Integer
        'Dim nextDateOfDelivery As Date = getNextDateOfDelivery(Val(txtDays.Text))
        Dim today As System.DateTime
        Dim answer As System.DateTime
        today = System.DateTime.Now
        answer = today.AddDays(Val(txtDays.Text))
        Dim nextDateOfDelivery As Date = answer


        medName = Trim(txtMedName.Text)
        qtty = Val(txtQuantity.Text)
        comment = txtComment.Text
        power = txtPower.Text
        medID = lblMedID.Text
        period = Val(txtDays.Text)
        '............setting priorities
        '.......value of p(0) indicates priority for tablest and so on so forth.....
        Dim p() As Integer = {0, 0, 0, 0}
        For i = 0 To LBPriorities.Items.Count - 1
            Select Case LBPriorities.Items(i).Text
                Case "Tablet"
                    p(0) = i + 1
                Case "Capsule"
                    p(1) = i + 1
                Case "Liquid"
                    p(2) = i + 1
                Case "Injection"
                    p(3) = i + 1

            End Select

        Next

        '............if not available
        Dim strictlyThis As String = radNotAvailable.SelectedItem.Value
        Dim connStr As String = ConfigurationManager.ConnectionStrings("databaseConnectionString").ConnectionString

        Dim con As New SqlClient.SqlConnection(connStr)
        'Dim con As New SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;" & _
        '                                       "AttachDbFilename=|DataDirectory|\database.mdf;" & _
        '                                       "Integrated Security=True;User Instance=True")

        Dim sql As New SqlClient.SqlCommand("INSERT INTO medicine" & _
            "(medID, userID, medName, quantity, dateOfOrder, power, tabPriority, capPriority, liqPriority, " & _
            "injPriority, period, notAvailable, prescription, comment, nextDateOfDelivery)" & _
            "VALUES('" & medID & "','" & User.Identity.Name & "','" & medName & "'," & qtty & ",'" & today & "'," & power & _
            "," & p(0) & "," & p(1) & "," & p(2) & "," & p(3) & ",'" & period & "','" & strictlyThis & "', '' ,'" & comment & "','" & nextDateOfDelivery & "')", con)

        con.Open()
        sql.ExecuteNonQuery()
        con.Close()
        con.Dispose()

        lblMessage.Text = "The details of the medicine have been saved. You may upload a prescription"
        FUPrescription.Enabled = True
        cmdUpload.Enabled = True
    End Sub

this is what i am using now...but i get an error saying "String or binary data would be truncated. The statement has been terminated." what does this mean???is there something wrong with my sql statement???

Answer 1

I'd say it was the value you're storing in dateOfOrder.

According to your screenshot, this is a VarChar(10), but you're storing DateTime.Now.ToString() in there, the default value of which is of the format:

5/1/2008 6:32:06 PM

Which comes in at 19 characters.

I suggest you store Dates and Times in a more suitable field, such as a Date or DateTime field.

If you must store it as text, then you should at least store it in a format that is suitable for sorting, and unambiguous, such as:

DateTime.Now.ToString("o") // Round-trip format

Which will give you:

2008-06-15T21:15:07.0000000