Insert data into a SQL Server database using vb.net

Question

I create a SQL Server database and I want to add some data in a particular table of that database. I use some textbox to input the data and an add button to complete. But when I tap the button the whole process was stopped and indicate an error in the DBSQL module which is shown below.

Here's my code:

Imports System.Data
Imports System.Data.SqlClient

Module DBSQLServer
    Public con As New SqlConnection("Data Source=JOYALXDESKTOP\SQLEXPRESS;Initial Catalog=SaleInventory;Integrated Security=True")
    Public cmd As New SqlCommand
    Public da As New SqlDataAdapter
    Public ds As New DataSet
    Public dt As DataTable
    Public qr As String
    Public i As Integer

    Public Function searchdata(ByVal qr As String) As DataSet
        da = New SqlDataAdapter(qr, con)
        ds = New DataSet
        da.Fill(ds)
        Return ds

    End Function

    Public Function insertdata(ByVal qr As String) As Integer

        cmd = New SqlCommand(qr, con)
        con.Open()
        i = cmd.ExecuteNonQuery()
        con.Close()
        Return i

    End Function
End Module

The error occurs on this line:

i = cmd.ExecuteNonQuery()

The error is:

System.Data.SqlClient.SqlException: 'Incorrect syntax near ')'

Here's my add button code:

Private Sub Add_Click(sender As Object, e As EventArgs) Handles add.Click
        If (isformvalid()) Then
            qr = "Insert into tblProductInfo (ProName, ProDesc, ProPrice, ProStock) Values('" & nametext.Text & "','" & descriptiontext.Text & "','" & pricetext.Text & "','" & stocktext.Text & "',)"
            Dim logincorrect As Boolean = Convert.ToBoolean(insertdata(qr))
            If (logincorrect) Then
                MsgBox("Stock Added Successfully ...", MsgBoxStyle.Information)
            Else
                MsgBox("Something Wrong. Record Not Saved. Please Check and Try Again...", MsgBoxStyle.Critical)
            End If
        End If
    End Sub

When I copy the details of that error it shows:

System.Data.SqlClient.SqlException
HResult=0x80131904
Message=Incorrect syntax near ')'.
Source=.Net SqlClient Data Provider

StackTrace:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at InventoryManagement.DBSQLServer.insertdata(String qr) in C:\Users\Joy Alx\source\repos\InventoryManagement\InventoryManagement\DBClass\DBSQLServer.vb:line 25 at InventoryManagement.stock.Add_Click(Object sender, EventArgs e) in C:\Users\Joy Alx\source\repos\InventoryManagement\InventoryManagement\Screens\Tools\stock.vb:line 29 at System.Windows.Forms.Control.OnClick(EventArgs e) at Bunifu.Framework.UI.BunifuImageButton.OnClick(EventArgs e) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun() at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel() at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine) at InventoryManagement.My.MyApplication.Main(String[] Args) in :line 81


If I have done anything wrong to ask this type question, I am sorry. I am new in this community.Thanks in advance.

<..>& stocktext.Text & "',)" - Notice extra ',' before closing brackets ? — Vytautas Plečkaitis
– Vytautas Plečkaitis, Commented Jul 12, 2019 at 15:51
What @VytautasPlečkaitis said is your immediate problem. However, you should look in to using Parameters as it will help prevent this sort of error and many others by making the code easier to read and also prevent malicious attacks on your database/application — JayV
– JayV, Commented Jul 12, 2019 at 15:53
Errors like this come by people reading the VB code that builds the SQL code but never reading the SQL code. If you're told that there's a syntax error in your SQL, look at your SQL. — jmcilhinney
– jmcilhinney, Commented Jul 12, 2019 at 15:55
Yes, parametarized queries or stored procedures is the way to go. Also make sure that data types are valid - e.g. you're not pushing string to int or bit field — Vytautas Plečkaitis
– Vytautas Plečkaitis, Commented Jul 12, 2019 at 15:57
Pls pay attention what product tags you use! Your code is for MS SQL Server, while you tagged your question as MySQL. — Shadow
– Shadow, Commented Jul 12, 2019 at 16:24

Vytautas Plečkaitis · Accepted Answer · 2019-07-12 18:05:06Z

2

There's issue with your query :

qr = "Insert into tblProductInfo (ProName, ProDesc, ProPrice, ProStock) Values('" & nametext.Text & "','" & descriptiontext.Text & "','" & pricetext.Text & "','" & stocktext.Text & "',)"

Should be

qr = "Insert into tblProductInfo (ProName, ProDesc, ProPrice, ProStock) Values('" & nametext.Text & "','" & descriptiontext.Text & "','" & pricetext.Text & "','" & stocktext.Text & "')"

Imagine SQL query being like this :

Insert into tblProductInfo (ProName, ProDesc, ProPrice, ProStock) Values('[name]','[description]','[price]','[stock]',)

and

Insert into tblProductInfo (ProName, ProDesc, ProPrice, ProStock) Values('[name]','[description]','[price]','[stock]')

Edit : Also I have to agree with colleagues - use parametarised queries or stored procedures - that would prevent SQL Injection. Also make sure that you are validating inputs before pushing them to db - pushing text to int field will fail.

edited Jul 12, 2019 at 18:05

answered Jul 12, 2019 at 15:53

Vytautas Plečkaitis

8561 gold badge14 silver badges23 bronze badges

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

4 Comments

Hasan Patel Over a year ago

should be something like this SqlCommand obj_sqlCommand = new SqlCommand(); and obj_sqlCommand.CommandType = CommandType.StoredProcedure;

granadaCoder Over a year ago

Please stop writing answers that continue to promote sql-injection attacks. thecrazyprogrammer.com/2014/10/…

Vytautas Plečkaitis Over a year ago

@granadaCoder I only pointed out what was wrong with the code (as in what was causing issue). I would never recommend using this approach anywhere close to production environment (even in private test environment it is frawned upon) - actually I would recommend stopping using sql queries altogether (use entity framework/stored procedures/etc), but that would be totally out of scope of this question. I did mention that there are better/safer ways, but that is for OP to choose, learn and use.

granadaCoder Over a year ago

I know..the "this fixes your issue".... But when that isn't pointed out to the poser of the question...they usually assume "its all good". Your "edit" is appropriate, thank you.

Collectives™ on Stack Overflow

Insert data into a SQL Server database using vb.net

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related