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, Jun 19 '14 at 13:50
i changed date to Edate in database and in insert query too! still no help...! — AmIt PagarIa, Jun 19 '14 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, Jun 19 '14 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, Jun 19 '14 at 13:59
i am getting no error! i guess my cmd.executenonquery is not working ! — AmIt PagarIa, Jun 19 '14 at 14:09

score 1 · Accepted Answer · answered Jun 19 '14 at 13:53

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 '14 at 13:53

Mitchell

6,845
2
16
14

thnq buddy...but i am getting no result as well as no error as described in catch statement! – AmIt PagarIa Jun 19 '14 at 14:08
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 :) – AmIt PagarIa Jun 19 '14 at 15:02
Thats very strange; this way is perfectly fine.. can you test the output of the variable? like: messagebox.show(ConfigurationManager.ConnectionStrings("SQLConnStr").ConnectionString()) – Mitchell Jun 20 '14 at 16:05
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); – Mitchell Jun 20 '14 at 16:07
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 – AmIt PagarIa Jun 20 '14 at 18:22
NOt working yet..:( can u please share any small work done by u...! my project is to make a vb with .mdf as DB in a pc and publish it in 3 different pc's without any connection error...! can u please share a small example with App.config | and a form where user can insert record..! the code u provided is eas nd only works with select statement..!when i insert any data the DB is still the old even thoug i get no error Message i would be thankfully thankful if u just help me out.! m actually wasting a lot of time here! thanx in advance as i know u wont say NO :) – AmIt PagarIa Jun 20 '14 at 18:25
– Mitchell Jun 23 '14 at 08:56
protected static string ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; SqlConnection SqlConnection = new SqlConnection(ConnectionString); public void plaatsBericht(int userid, string message) { SqlConnection.Open(); string SqlQuery = "INSERT INTO [bericht] VALUES ( @geplaatstop)"; using (var SqlCmd = new SqlCommand(SqlQuery, SqlConnection)) { SqlCmd.Parameters.AddWithValue("geplaatstop", DateTime.Now); SqlCmd.ExecuteNonQuery(); } SqlConnection.Close(); } – Mitchell Jun 23 '14 at 08:58

INSERT query not working vb.net sql

1 Answers1