xTMx xTMx - 1 year ago 70
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

Answer Source

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.