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

• lc.: Don't know why you wouldn't see an exception; that query string doesn't look right at all.
• Ismael Bin Azman: any idea how to write a right query?
• Ismael Bin Azman: i edit the example from Microsoft page to fit with my codes.
• Steve Wellens: You are probably inserting the data in one database but then looking for it in another database.
• Ismael Bin Azman: 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.

Answer 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()

• marc_s: Plus: after the values keyword, there is an extra erroneous comma, and the opening bracket after the values keyword is missing ....
• Ismael Bin Azman: its similar from what i do. still gettting no luck. everything compiled and runs well. but theres no update in a database.

Answer 2

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

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