sql insert not working vb.net 2012

Question

I was trying my best to insert records to a db. I am not getting the records added nor it shows some error. Please help me. I am using localdb with Visual studio 2012.

Imports System.Data.SqlClient
Imports System.Data

Public Class tax_configure

    Private Sub tax_configure_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        With tax_on_combo
            .SelectedIndex = 0
        End With
    End Sub

    Private Sub add_tax_cata_Click(sender As Object, e As EventArgs) Handles add_tax_cata.Click
        Dim con As New SqlConnection
        Dim com As New SqlCommand
        Try
            con.ConnectionString = "Data Source=(LocalDB)\v11.0;AttachDbFilename=E:\My VB projects\Test App\Test App\bin\Debug\test_db.mdf;Integrated Security=True;Connect Timeout=30"
            con.Open()
            com.Connection = con
            com.CommandText = "INSERT INTO [dbo].[tax_details] ([tax_catagory], [tax_rate], [ed_cess_rate], [s_ed_cess_rate], [tax_on]) VALUES (@tax_catagory, @tax_rate, @ed_cess_rate, @s_ed_cess_rate, @tax_on)"
            com.Parameters.Add(New SqlParameter("@tax_catagory", tax_cata_name.Text))
            com.Parameters.Add(New SqlParameter("@tax_rate", Val(tax_rate_txt.Text)))
            com.Parameters.Add(New SqlParameter("@ed_cess_rate", Val(Ecess_rate_txt.Text)))
            com.Parameters.Add(New SqlParameter("@s_ed_cess_rate", Val(SEcess_rate_txt.Text)))
            com.Parameters.Add(New SqlParameter("@tax_on", tax_on_combo.SelectedText))
            com.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            con.Close()
        End Try


    End Sub
End Class

If you cant be bothered to tell us what "some error" is, shall we guess at solutions? You should not use `Val` it only ever returns Double which may not match the type in the DB, it will also crash horribly if the user types "I Like Pie" for the tax rate — Ňɏssa Pøngjǣrdenlarp, Mar 09 '16 at 15:20
Are you asking us how to read an error message? It's like reading anything else. You look at it, group characters into words, group words into statements, etc. — David, Mar 09 '16 at 15:23
Also, you're trying to add parameters to a query that doesn't have any. — David, Mar 09 '16 at 15:23
that was a typo. I am not getting the rows inserted. it isnt showing any errors too. — Ayoob Khan, Mar 09 '16 at 15:24
i tried to insert parameters. but it didnt work. then I tried with plain text. — Ayoob Khan, Mar 09 '16 at 15:25
Could you fix your question code to show exactly what is your real code? — Steve, Mar 09 '16 at 15:25
The post says `it shows some error` - is there an error message or not? — Ňɏssa Pøngjǣrdenlarp, Mar 09 '16 at 15:27
@Plutonix I see. Then how can I send a decimal value to my db? — Ayoob Khan, Mar 09 '16 at 15:27
@FernandoSanchiz I am not getting any exception message as of now. — Ayoob Khan, Mar 09 '16 at 15:35
For another thing, `tax_on_combo.SelectedText` doesnt do what you think it does, so an empty string could be getting passed there (in addition to the problem with `Val`) — Ňɏssa Pøngjǣrdenlarp, Mar 09 '16 at 15:37
@AyoobKhan: When you step through this in a debugger, where *specifically* does it fail? Is this code invoked at all? What are the runtime values of the parameters you add to the query? Are they all what you expect after you evaluate them with `Val()`? Is the query executed? What is the result of the query? You need to debug this. — David, Mar 09 '16 at 15:38
If this code doesn't raise any exception (and there are many possibilities of exception here) then start getting the result of the ExecuteNonQuery value. If this result is 1 then your record has been inserted in the database pointed by the connection string. Check if you have a master (empty) copy of it in your project files and that this copy has not the property Copy To Output Directory set to Copy Always — Steve, Mar 09 '16 at 15:42
@Steve thank you so much for this. I had 2 copies of the databases in my data folder. Now its working — Ayoob Khan, Mar 09 '16 at 16:02

score 0 · Accepted Answer · answered Mar 09 '16 at 15:26

0

The CommandText doesn't have parameters that match the parameter names you're adding. Instead, you've hard-coded

VALUES ('Ana', '2', '2', '2', 'Both' )

Instead, try:

VALUES (@tax_catagory, @tax_rate, @ed_cess_rate, @s_ed_cess_rate, @tax_on)

answered Mar 09 '16 at 15:26

Steve Barron

944
1
6
15

thank you Steve. I tried this before. Then also it didnt work. Do you think there is some syntax errors in my code? – Ayoob Khan Mar 09 '16 at 15:33

sql insert not working vb.net 2012

1 Answers1