'Failed to convert parameter value from a String to a Int32/

Question

I have already post this question but now in details. I have trying to insert Date from Vb.net to SQL Database But when i click insert Button then show me above error message.I need your help Thanks.

I have been trying for a long time to insert the data in many ways, but have been consistently failing.

'Sql Database table data type is below.
';;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
Userid  int Checked
EmpSalary   int Checked
EmpName varchar(50) Checked
FatherName  varchar(50) Checked
EmailAddress    varchar(50) Checked
BankAccount int Checked
HomeAddress varchar(50) Checked
PersonalMobile  int Checked
DateofBirth date    Checked
EmpImage    image   Checked
';;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
'My Vb.net insert Query Code is below.

    Public Sub ExecuteMyQuery(cmd As SqlCommand, MyMessage As String)
        con.Open()
        If cmd.ExecuteNonQuery = 1 Then
            MessageBox.Show(MyMessage)
        Else
            MessageBox.Show("Query Not Execute")

        End If
        con.Close()
    End Sub
 Dim insertquery As String = "Insert into AddNew(Userid,EmpSalary,EmpName,FatherName,EmailAddress,BankAccount,HomeAddress,PersonalMobile,DateofBirth,EmpImage) values(@EmpID,@EmpSalary,@EmpName,@FatherName,@EmailAddress,@BankAccount,@HomeAddress,@PersonalMobile,@DtpDOB,@img)"

   Dim cmd As New SqlCommand(insertquery, con)

        cmd.Parameters.Add("@EmpID", SqlDbType.Int).Value = EmpID.Text
        cmd.Parameters.Add("@EmpSalary", SqlDbType.Int).Value = EmpSalary.Text
        cmd.Parameters.Add("@EmpName", SqlDbType.VarChar).Value = EmpName.Text
        cmd.Parameters.Add("@FatherName", SqlDbType.VarChar).Value = FatherName.Text
        cmd.Parameters.Add("@EmailAddress", SqlDbType.VarChar).Value = EmailAddress.Text
        cmd.Parameters.Add("@BankAccount", SqlDbType.Int).Value = BankAccount.Text
        cmd.Parameters.Add("@HomeAddress", SqlDbType.VarChar).Value = HomeAddress.Text
        cmd.Parameters.Add("@PersonalMobile", SqlDbType.Int).Value = PersonalMobile.Text
        cmd.Parameters.Add("@DtpDOB", SqlDbType.Date).Value = DtpDOB.Value
        cmd.Parameters.Add("@img", SqlDbType.Image).Value = img

        ExecuteMyQuery(cmd, "Saved Your Data Successful")

    End Sub

You're providing strings `EmpID.Text`, `EmpSalary.Text` when a `int` is expected. BTW, a Salary should be a Currency/Decimal value. If `PersonalMobile` is a phone *number* (code), that's a `string`, not an `int`. Use Blob (byte array) Field for the Image. `varchar` may not be the right container type for your strings: you probably want a `nvarchar` field. Be sure you're not providing a value of an IDENTITY field. — Jimi, Apr 08 '20 at 19:43
If `Userid` is actually a value **>you<** need to generate, the parameter will be `cmd.Parameters.Add("@EmpID", SqlDbType.Int).Value = Convert.ToInt32(EmpID.Text)`. BUT, you should have already validated the input, thus providing already converted values: you cannot store values coming directly from what can be User input, here. — Jimi, Apr 08 '20 at 19:51
Why do you think the problem is with the Date field? Or did you mean data? — Mary, Apr 09 '20 at 15:43

Caius Jard · Answer 1 · 2020-04-09T12:49:36.650

0

Give this a go:

Dim r as New Regex("[^0-9]")

cmd.Parameters.Add("@EmpID", SqlDbType.Int).Value = r.Replace(EmpID.Text, "")
cmd.Parameters.Add("@EmpSalary", SqlDbType.Int).Value = r.Replace(EmpSalary.Text, "")
cmd.Parameters.Add("@EmpName", SqlDbType.VarChar).Value = EmpName.Text
cmd.Parameters.Add("@FatherName", SqlDbType.VarChar).Value = FatherName.Text
cmd.Parameters.Add("@EmailAddress", SqlDbType.VarChar).Value = EmailAddress.Text
cmd.Parameters.Add("@BankAccount", SqlDbType.Int).Value = r.Replace(BankAccount.Text, "")
cmd.Parameters.Add("@HomeAddress", SqlDbType.VarChar).Value = HomeAddress.Text
cmd.Parameters.Add("@PersonalMobile", SqlDbType.Int).Value = r.Replace(PersonalMobile.Text, "")
cmd.Parameters.Add("@DtpDOB", SqlDbType.Date).Value = DtpDOB.Value
cmd.Parameters.Add("@img", SqlDbType.Image).Value = img

It strips out non numbers present in text fields, meaning the conversion should succeed..

It would, however be better to a) Use a MaskedTextBox, a NumericUpDown or a textbox that rejects non-numbers and b) convert the text to int yourself so you can handle if it fails, using a nice error message:

Try
    cmd.Parameters.Add("@EmpID", SqlDbType.Int).Value = Convert.ToInt32(EmpID.Text)
    cmd.Parameters.Add("@EmpSalary", SqlDbType.Int).Value = Convert.ToInt32(EmpSalary.Text)
    ...
Catch ex as Exception
    'the last added parameter is the one that hit the problem
    MessageBox.Show($"Error adding a value for {cmd.Parameters.Last().ParameterName}")
End Try

edited Apr 09 '20 at 12:49

answered Apr 08 '20 at 20:24

Caius Jard

72,509
5
49
80

Yes you are right Jimi actually problem is (PersonalMobile) Now i understand where is problem But please can you help me about date format i mean my user form show date format 09-04-2020 But when i save my data then date format is 2020-04-09 But i want format dd-MM-yyyy please tell me how it possible. – Official BaRyaR Apr 09 '20 at 09:05
and please tell me which type use data type for Phone Number? – Official BaRyaR Apr 09 '20 at 09:25
Dates don't have a format, they're just a number (internally) so you saying "my date is 09-04-2020 not 2020-04-09" is like saying "my age is 27 not 27.0" or "may age is 27 not 0.27 *10^2" - same thing, internally. When you do `SELECT * FROM table` and the date shows like 2020-04-09 that is just the formatting your SQL query tool has chosen to use. Forget about it. Set your date time format in the app – Caius Jard Apr 09 '20 at 12:52
I've encountered all kinds of data types used for phone numbers, but most often theyre a number because it stops people putting crap in the field (like the extension number in "424-555-2626 ext 1212") – Caius Jard Apr 09 '20 at 12:53
Caius Jard Now what i do use data type for saving phone number in SQL table – Official BaRyaR Apr 09 '20 at 16:12
Caiuse Jard please look this code and set for date formating please i thank full to you cmd.Parameters.Add("@DtpDOB", SqlDbType.DateTime("dd-MM-yyyy")).Value = DtpDOB.Value – Official BaRyaR Apr 09 '20 at 16:16
please write a query for me just one time date and phone number. I'm confused by the date and phone number.what is data type both? – Official BaRyaR Apr 09 '20 at 16:21
It's already in the answer. STORED DATES DON'T HAVE A FORMAT. Formatting is something that happens when you display a date, NOT when you store it. – Caius Jard Apr 09 '20 at 16:35
OK Thank you Caius jard But can you tell me which type i use data type for store phone number – Official BaRyaR Apr 09 '20 at 19:28
Use a long (in .net) and a bigint in the database – Caius Jard Apr 10 '20 at 05:52
Don't forget to accept and upvote answers to your questions – Caius Jard Apr 10 '20 at 05:52

'Failed to convert parameter value from a String to a Int32/

1 Answers1

Linked