i cant add in the data?
code of add info:

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

    If String.IsNullOrWhiteSpace(txtadmin.Text) Or String.IsNullOrWhiteSpace(txtname.Text) Or String.IsNullOrWhiteSpace(txtcourse.Text) Or String.IsNullOrWhiteSpace(txtic.Text) Or String.IsNullOrWhiteSpace(txtgender.Text) Or String.IsNullOrWhiteSpace(txtaddress.Text) Or String.IsNullOrWhiteSpace(txttel.Text) Or String.IsNullOrWhiteSpace(txtemail.Text) Or String.IsNullOrWhiteSpace(txttpye.Text) Then
        MessageBox.Show("Please complete the on the box.", "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Exit Sub
    End If

    Dim Conn As System.Data.OleDb.OleDbConnection
    Dim ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\temp\Database1.accdb"
    Conn = New System.Data.OleDb.OleDbConnection(ConnectionString)


    Try
        If Conn.State = ConnectionState.Open Then Conn.Close()
        Conn.Open()

        Dim sql As String = "insert into tbl_datain ([UserName] values('" & txtname.Text & "' AdminNo = '" & txtadmin.Text & "'Course Title = '" & txtcourse.Text & "'""'ICNo = '" & txtic.Text & "'""'Gender = '" & txtgender.Text & "'""'Address= '" & txtaddress.Text & "'""'TelNo = '" & txttel.Text & "'""'Email = '" & txtemail.Text & "' Type ='" & txttpye.Text & "')"
        Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql, Conn)
        sqlCom.Connection = Conn

        Dim result As Integer = sqlCom.ExecuteNonQuery()

        sqlCom.Dispose()
        Conn.Close()

        If result > 0 Then
            MessageBox.Show("Successfully created.")
        Else
            MessageBox.Show("Failure to create.")

        End If
        txtname.Text = ""
        txtadmin.Text = ""
        txtcourse.Text = ""
        txtic.Text = ""
        txtgender.Text = ""
        txtaddress.Text = ""
        txttel.Text = ""
        txtemail.Text = ""

        txtname.Focus()


    Catch ex As Exception
        MessageBox.Show("Failed to connect to Database..", "Database Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try



End Sub

Your query is wrong: ([UserName] is breaking it. Looks like you copied something, but didn't get all of it. An insert query should look like this:

Dim sql As String = "insert into tbl_datain 
    ([UserName], AdminNo, CourseTile, ICNo, Gender, Address, TelNo, Email, Type) values ('" & txtname.Text & "', '" & txtadmin.Text & "', '" & txtcourse.Text & "', '" & txtic.Text & "', '" & txtgender.Text & "', '" & txtaddress.Text & "', '" & txttel.Text & "', '" & txtemail.Text & "', '" & txttpye.Text & "')"

Edited 2 Years Ago by pritaeas

Do works from brain. Copy Paste is most bad habit. Don’t copy everything without clearing conception. See, for what reason the commands are using and how.
Following solutions from your earlier post, is acceptable and permissible. Blindly copy paste is most foolish work. If you have any question to clear your conceptions without hesitation question it. No one can say he knows everything.

Well
The SQL Statement to insert data in a database is “INSERT INTO TABLENAME (FIELD1, FIELD2, FIELD3,..................) VALUES(VALUE1, VALUE2, VALUE3,.....................)”
You can omit the field names, but you will have to supply values against every field name respectively. The syntax is “INSERT INTO TABLENAME VALUES(VALUE1, VALUE2, VALUE3,.....................)”
Your SQL Statement is wrong, you did not perfectly use it. It should be

"insert into tbl_datain (UserName, AdminNo, CourseTile, ICNo, Gender, Address, TelNo, Email, Type) values ('" & txtname.Text & "', '" & txtadmin.Text & "', '" & txtcourse.Text & "', '" & txtic.Text & "', '" & txtgender.Text & "', '" & txtaddress.Text & "', '" & txttel.Text & "', '" & txtemail.Text & "', '" & txttpye.Text & "')"
Which pritaeas says.

or

"insert into tbl_datain values ('" & txtname.Text & "', '" & txtadmin.Text & "', '" & txtcourse.Text & "', '" & txtic.Text & "', '" & txtgender.Text & "', '" & txtaddress.Text & "', '" & txttel.Text & "', '" & txtemail.Text & "', '" & txttpye.Text & "')"

Hope, it will help you.

If Conn.State = ConnectionState.Open Then Conn.Close()
        Conn.Open()

        Dim sql As String = "INSERT INTO tbl_info ([AdminNo],UserName, Course Title, ICNo, Gender, Address, Data of Birth, TelNo, Email, Type) values ('" & txtadmin.Text & "', '" & txtname.Text & "', '" & txtcourse.Text & "', '" & txtic.Text & "', '" & txtgender.Text & "', '" & txtaddress.Text & "','" & txtbirth.Text & "', '" & txttel.Text & "', '" & txtemail.Text & "', '" & txttpye.Text & "')"
        Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql)
        sqlCom.Connection = Conn

        **Dim result As Integer = sqlCom.ExecuteNonQuery** i get a error (syntax error in INSERT INTO statement )

        sqlCom.Dispose()
        Conn.Close()

Edited 2 Years Ago by Xiao_1

You did a mistake in field name.

"INSERT INTO tbl_info ([AdminNo],UserName, Course Title, ICNo, Gender, Address, Data of Birth, TelNo, Email, Type) values ('"

SQL Statement never accept any field name which contains space. You have two field names "Course Title" and "Data of Birth". It treats each word as a field name and there is no comma between two field names. So it occurs an error like "INSERT INTO statement". This is the main cause for error.

To treate as a single word field name covered them by box "[]" brackets.Like "[Course Title]" and "[Data of Birth]"

Your SQL Statement should be

 Dim sql As String = "INSERT INTO tbl_info ([AdminNo],UserName, [Course Title], ICNo, Gender, Address, [Data of Birth], TelNo, Email, Type) values ('" & txtadmin.Text & "', '" & txtname.Text & "', '" & txtcourse.Text & "', '" & txtic.Text & "', '" & txtgender.Text & "', '" & txtaddress.Text & "','" & txtbirth.Text & "', '" & txttel.Text & "', '" & txtemail.Text & "', '" & txttpye.Text & "')"

Along with the above post; use parameterized SQL query statement to save your database from malicious user.

This article has been dead for over six months. Start a new discussion instead.