INSERT query not working vb.net sql

Question

I am working on a project! M using vb 2012 with .mdf as database!

Problem is M unable to save record to database using the sql insert statement! PFB "app.config" | "insert code (save button click event)", and help me out! Its small mistake but m unable to crack it!

app.config-->
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
</configSections>
<connectionStrings>
    <add name="SQLConnStr" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\balancesheet.mdf;Integrated Security=True"
        providerName="System.Data.SqlClient" />

</connectionStrings>
<startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
</startup>
</configuration>

  insert query-->>

 Private Sub Button5_Click(sender As Object, e As EventArgs) Handles saveexp.Click
    If descexp.Text <> "" And amountexp.Text <> "" Then
        Dim SQLConnStr As New SqlConnection
        Dim Company As String = ""
        SQLConnStr.ConnectionString = ConfigurationManager.ConnectionStrings("SQLConnStr").ConnectionString()
        Dim cmd As New SqlCommand
        If SQLConnStr.State = ConnectionState.Broken Or SQLConnStr.State = ConnectionState.Closed Then
            SQLConnStr.Open()
        End If
        cmd.Connection = SQLConnStr
        cmd.CommandText = "insert into Expense (date,Description,Amount) values('" & expdate.Value & "', '" & descexp.Text & "','" & amountexp.Text & "')"
        cmd.ExecuteNonQuery()
    End If

End Sub

you should also use prepared statements and parameters to avoid SQL injection or errors with certain names. — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Jun 19, 2014 at 13:50
i changed date to Edate in database and in insert query too! still no help...! — AmIt PagarIa
– AmIt PagarIa, Commented Jun 19, 2014 at 13:55
ooh... I will work on it...right now i m requesting for solution for this core coding! as m new to vb and sql!Please help — AmIt PagarIa
– AmIt PagarIa, Commented Jun 19, 2014 at 13:57
are you getting an error? if [date] is a date type, your SQL is treating it as text. implement parameters and the problem will likely resolve itself — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Jun 19, 2014 at 13:59

Mitchell · Accepted Answer · 2014-06-19 13:53:58Z

1

Try this

Dim con As New SqlConnection
Dim cmd As New SqlCommand
Try
con.ConnectionString = ConfigurationManager.ConnectionStrings("SQLConnStr").ConnectionString()
con.Open()
cmd.Connection = con
cmd.CommandText = "INSERT INTO [Expense] (date,Description,Amount) VALUES('" & expdate.Value & "', '" & descexp.Text & "','" & amountexp.Text & "')"    
cmd.ExecuteNonQuery()

Catch ex As Exception
MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
Finally
con.Close()
End Try

I wasnt able to test it.. but i think it should work..

answered Jun 19, 2014 at 13:53

Mitchell

7,2122 gold badges18 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

AmIt PagarIa Over a year ago

thnq buddy...but i am getting no result as well as no error as described in catch statement!

AmIt PagarIa Over a year ago

brother! i got ther error! but m confused about the solution...! when i tried to hard code the sql connection string the insert query worked...!! how can i avoid hardcoding here so that i can publish m desktop ap on other pc with no error!!...?? Please check my app.config A request :)

Mitchell Over a year ago

Thats very strange; this way is perfectly fine.. can you test the output of the variable? like: messagebox.show(ConfigurationManager.ConnectionStrings("SQLConnStr").ConnectionString())

Mitchell Over a year ago

The only difference between your solutions and my working project is that I first define the ConfigurationManager.ConnectionStrings to a variable (string), and then I use the string for the SqlConnection class. Like this: Dim ConnectionString as string = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString() Dim SqlConnection as new SqlConnection(ConnectionString);

AmIt PagarIa Over a year ago

Dim ConnectionString As String = ConfigurationManager.ConnectionStrings("SQLConnStr").ConnectionString() Dim SqlConnection As New SqlConnection(ConnectionString) If SqlConnection.State = ConnectionState.Closed Or SqlConnection.State = ConnectionState.Broken Then SqlConnection.Open() End If

|

Collectives™ on Stack Overflow

INSERT query not working vb.net sql

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related