CODEWITHSUNDEEP
vb.netvb.net-2010
xTMx
xTMx

Reputation: 77

Inserting data into database (sqlexception) not working

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:Found in the image link 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

Upvotes: 1

Views: 62

Answers (1)

JaggenSWE
JaggenSWE

Reputation: 2106

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.

Upvotes: 1

Related Questions