Inserting data into database (sqlexception) not working

Question

I'm Trying to take data entered by the user from the form view and insert it to the table in a database The problem is that whenever the compiler reach the Rtype variable to store its value it gives me this error: I know what the error means but i simply can't get it to work . The following is my code in the class Form1

Imports System.Data.SqlClient

Public Class Form1`

    Private Sub newBtn_Click(sender As Object, e As EventArgs) Handles BtnNwRoom.Click
        Dim obj As New Hotl()
        Dim selectedItem As Object
        selectedItem = hotelCombobox.SelectedItem()
        If (obj.addnew(CInt(Me.roomNum.Text), CInt(selectedItem), Me.roomType.Text, Me.price.Text) = False) Then
            MsgBox(" no record is added, Try again later")
        End If
    End Sub
End class

This is the add new function :

        Public Function addnew(ByVal roomNo As Integer, ByVal hotelNo As String, ByVal RoomType As String, ByVal price As Integer) As Boolean

        Dim sqlstmnt = "insert into Room (roomNo,hotelNo,RoomType,price) values( " & roomNo & " , " & hotelNo & " , " & RoomType & " , " & price & ")"
        MsgBox(sqlstmnt)
        conn = ConNew()
        '''''''''''''''''''''''''''''' Execute Reader
        ''''''''''''''''''''''''''''''''''''''''''''''

        Dim command As New SqlCommand(sqlstmnt, conn)
        If command.ExecuteNonQuery() = 1 Then
            MessageBox.Show("insertion Succeded")
            Return True
        Else
            Return False
        End If
    End Function

Use sql parameteres, that will probably solve this issue and -more important- your inherent sql injection vulnerability. — Tim Schmelter
– Tim Schmelter, Commented Apr 18, 2016 at 12:47
One of your values is the text "big" but you aren't wrapping text values in single quotes, so itis being interpreted as a column name, hence your being told that that column name is invalid. You could just put single quotes in the proper places in your SQL but that is a bandaid measure. Do it properly with parameters, as @TimSchmelter suggests. See here: jmcilhinney.blogspot.com.au/2009/08/… — jmcilhinney
– jmcilhinney, Commented Apr 18, 2016 at 12:51

JaggenSWE · Accepted Answer · 2016-04-18 13:23:01Z

1

As Tim said, use parameterized queries instead.

But, the main cause of your issue is here:

 RoomType As String

         Dim sqlstmnt = "insert into Room (roomNo,hotelNo,RoomType,price) values( " & roomNo & " , " & hotelNo &
 " , " & RoomType & " , " & price & ")"

RoomType is defined as a string, but you have no enclosing apostrophes in the Query (hence it will be interpreted as a numeric or a name, not a string.

So, in this particular case, use this:

Dim sqlstmnt = "insert into Room (roomNo,hotelNo,RoomType,price) values( " & roomNo & " , " & hotelNo &
     " , '" & RoomType & "' , " & price & ")"

But to stress the importance of (among other things) security, use parameterized questions instead and not raw user input directly in the SQL Query.

And just to clarify, here's an example WITH a parameterized Query:

Public Function addnew(ByVal roomNo As Integer, ByVal hotelNo As String, ByVal RoomType As String, ByVal price As Integer) As Boolean
    Dim sqlstmnt As String = "INSERT INTO ROOM (roomNo,hotelNo,RoomType,price) VALUES(@roomNo, @hotelNo, @RoomType, @price)"
    MsgBox(sqlstmnt)
    conn = ConNew()
    '''''''''''''''''''''''''''''' Execute Reader
    ''''''''''''''''''''''''''''''''''''''''''''''

    Dim command As New SqlCommand(sqlstmnt, conn)
    command.Parameters.Add("@roomNo",SqlDbType.Int).Value = roomNo
    command.Parameters.Add("@hotelNo",SqlDbType.Int).Value = hotelNo
    command.Parameters.Add("@RoomType",SqlDbType.NVarChar,50).Value = RoomType
    command.Parameters.Add("@price",SqlDbType.Int).Value = price

    If command.ExecuteNonQuery() = 1 Then
        MessageBox.Show("insertion Succeded")
        Return True
    Else
        Return False
    End If
End Function

Using this code you're protected against SQL Injections and won't have to run into unforseen problems for using reserved Words etc.

edited Apr 18, 2016 at 13:23

answered Apr 18, 2016 at 12:52

JaggenSWE

2,1762 gold badges25 silver badges45 bronze badges

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

5 Comments

Tim Schmelter Over a year ago

The main cause is that he doesn't use paramterized queries. He should not care about enclosing apostrophes, don't show him how he can hide his main issue which is a sql injection vulnerability ;)

JaggenSWE Over a year ago

It tried to emphasize the importance of using parameterized queries, but still, it's good to help people get an understanding for what they're doing, and what they did wrong (syntactically) in their original question. :)

xTMx Over a year ago

I only just recently started learning vb.net and i'm just using the basics of vb ( i don't know what paramterized queries mean because we didn't reach them yet in our course but i'll read about them ) .Thanks for the answer!

Tim Schmelter Over a year ago

Sure. Unfortunately many will skip the parameter part of your question if they'll get a "working" solution. That's why still estimated 90% of all programmers use string concatenation. All the more because you haven't shown a working solution which uses parameters. I'm sure OP will add apostrophes instead.

JaggenSWE Over a year ago

True, however it's a piece of the same logic as with car tuning. You can tune the car to get more Power out of the Engine and to reduce fuel consumtion, but should you then forbid it because 90% will use it to drive too fast? It's a dilemma.

Collectives™ on Stack Overflow

Inserting data into database (sqlexception) not working

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related