Reputation: 77
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
Upvotes: 1
Views: 62
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