ASP.NET, VB.NET can't insert data into a SQL Server database

Question

I have been troubleshooting this for a week. I try to insert a value into a SQL Server database. It doesn't show any error but when I check the database, there's no data inserted. I might doing something that is wrong here but I can't find it. Thanks for helping.

Dim connect As New SqlConnection(ConfigurationManager.ConnectionStrings("SqlServer").ToString())

Using coa As New SqlCommand()
    With coa
        .Connection = connect
        .CommandType = CommandType.Text
    End With

    Try
       connect.Open()

       Dim insertcmd As String

       insertcmd = "insert into tblUserSection (@newValue, @SectionName, @newSectionID) values ," _
       & "@newValue, @SectionName, @newSectionID);"

       coa.Parameters.Add(New SqlParameter("@newValue", SqlDbType.BigInt))
       coa.Parameters("@newValue").Value = newValue

       coa.Parameters.Add(New SqlParameter("@SectionName", SqlDbType.NVarChar))
       coa.Parameters("@SectionName").Value = SectionName.ToString

       coa.Parameters.Add(New SqlParameter("@newSectionID", SqlDbType.BigInt))
       coa.Parameters("@newSectionID").Value = newSectionID

       coa.ExecuteNonQuery()
       connect.Close()

       MsgBox("success insert")

    Catch ex As Exception
       MsgBox("Fail to Save to database")
    End Try
End Using

Don't know why you wouldn't see an exception; that query string doesn't look right at all. — lc.
– lc., Commented Aug 21, 2015 at 3:53
i edit the example from Microsoft page to fit with my codes. — Ismael Bin Azman
– Ismael Bin Azman, Commented Aug 21, 2015 at 3:56
You are probably inserting the data in one database but then looking for it in another database. — Steve Wellens
– Steve Wellens, Commented Aug 21, 2015 at 4:06
i already fetch data from other table. i put the alert to print out the value and it really shows the value. the thing is, there is no update in database. — Ismael Bin Azman
– Ismael Bin Azman, Commented Aug 21, 2015 at 7:33

Jeff S · Accepted Answer · 2015-08-21 04:29:31Z

1

The insert command is incorrect. It has parameters for both the column names and the value; the parameter names should only be used for the values.

Assuming the column names match the parameter names, here's an updated version of the command.

insertcmd = "insert into tblUserSection (newValue, SectionName, newSectionID) values ," _
        & "@newValue, @SectionName, @newSectionID);"

The more curious question is why isn't an error showing up. That's because the insert statement is never getting executed. The ExecuteNonQuery command is run against the connection but insertcmd is never associated with the execution in any way.

I'd recommend creating a SQLCommand and using that to execute the query. Here's a sample (and my code might have mistakes, my vb.net is pretty rusty):

Dim sqlcommand as New SqlCommand(coa)
sqlcommand.text = insertcmd
sqlcommand.type = Text
sqlcommand.Parameters.Add(New SqlParameter("@newValue", SqlDbType.BigInt))
sqlcommand.Parameters("@newValue").Value = newValue
sqlcommand.Parameters.Add(New SqlParameter("@SectionName", SqlDbType.NVarChar))
sqlcommand.Parameters("@SectionName").Value = SectionName.ToString
sqlcommand.Parameters.Add(New SqlParameter("@newSectionID", SqlDbType.BigInt))
sqlcommand.Parameters("@newSectionID").Value = newSectionID
sqlcommand.ExecuteNonQuery()

answered Aug 21, 2015 at 4:29

Jeff S

7,49432 silver badges32 bronze badges

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

2 Comments

marc_s Over a year ago

Plus: after the values keyword, there is an extra erroneous comma, and the opening bracket after the values keyword is missing ....

Ismael Bin Azman Over a year ago

its similar from what i do. still gettting no luck. everything compiled and runs well. but theres no update in a database.

Sonu Singh · Accepted Answer · 2015-08-21 10:02:46Z

0

You need to set CommandText property of SqlCommand after creating the insert command string. like:

Dim connect As New SqlConnection(ConfigurationManager.ConnectionStrings("SqlServer").ToString())
    Using coa As New SqlCommand()
        With coa
            .Connection = connect
            .CommandType = CommandType.Text
        End With
        Try
            connect.Open()
            Dim insertcmd As String
            insertcmd = "insert into [TableName] (newValue, SectionName, newSectionID) values " _
            & "(@newValue, @SectionName, @newSectionID);"
            coa.CommandText = insertcmd
            coa.Parameters.Add(New SqlParameter("@newValue", SqlDbType.BigInt))
            coa.Parameters("@newValue").Value = newValue
            coa.Parameters.Add(New SqlParameter("@SectionName", SqlDbType.NVarChar))
            coa.Parameters("@SectionName").Value = SectionName.ToString()
            coa.Parameters.Add(New SqlParameter("@newSectionID", SqlDbType.BigInt))
            coa.Parameters("@newSectionID").Value = newSectionID
            coa.ExecuteNonQuery()
            connect.Close()
            MsgBox("success insert")
        Catch ex As Exception
            MsgBox("Fail to Save to database")
        End Try
    End Using

edited Aug 21, 2015 at 10:02

answered Aug 21, 2015 at 4:34

Sonu Singh

1878 bronze badges

1 Comment

Sonu Singh Over a year ago

@IsmaelBinAzman Use this entire code this works fine at my end. I found some problem in your query string.

Collectives™ on Stack Overflow

ASP.NET, VB.NET can't insert data into a SQL Server database

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related