xTMx xTMx - 6 months ago 7
Vb.net Question

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 & ")"
conn = ConNew()
'''''''''''''''''''''''''''''' Execute Reader

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


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.