ExecuteNonQuery() error in vb

[ZIPB]

Need some help with the code below. Getting and error at ExecuteNonQuery()

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles btnRoomSave.Click

    Dim con As New OleDbConnection
    Dim cmd As New OleDb.OleDbCommand
    cmd = New OleDbCommand(Command, con)
    con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\PATIENT_O\PATIENT_O\Patient-O.accdb'"

    cmd.CommandType = System.Data.CommandType.Text
    cmd.Connection = con
    con.Open()

    cmd.CommandText = "INSERT INTO Rooms (RoomID, First Name, Middle Name, Last Name, Room Type, Amt/Day, Comments) VALUES ('@RoomID, @FirstName, @Middle Name, @Last Name, @Room Type, @Amt/Day, @Comments')"
    'cmd.CommandText = "INSERT INTO [Rooms] (]RoomID], [First Name], [Middle Name], [Last Name], [Room Type], [Amt/Day], [Comments]) VALUES (" & txtRoomID.Text & ", '" & txtroomFName.Text & "', '" & txtroomMName.Text & "', '" & txtroomLName.Text & "','" & txtRoomtype.Text & "', '" & txtroomAmount.Text & "', '" & txtroomComment.Text & "')"
    cmd.Parameters.AddWithValue("@RoomID", Val(txtRoomID.Text))
    cmd.Parameters.AddWithValue("@FirstName", txtroomFName.Text)
    cmd.Parameters.AddWithValue("@Middle Name", txtroomMName.Text)
    cmd.Parameters.AddWithValue("@Last Name", txtroomLName.Text)
    cmd.Parameters.AddWithValue("@Room Type", txtRoomtype.Text)
    cmd.Parameters.AddWithValue("@Amt/Day", txtroomAmount.Text)
    cmd.Parameters.AddWithValue("@Comments", txtroomComment.Text)


    cmd.ExecuteNonQuery()
    Try

        MsgBox("Record Added")
    Catch exceptionObject As Exception
        MessageBox.Show(exceptionObject.Message)
    Finally
        con.Close()
    End Try
End Sub

[|-|x]

What is the error?

Also, why is the execute not inside the try block?

I'm going to take a stab in the dark here and guess that your issue has something to do with having a slash in your parameter name "@Amt/Day" - try removing that and see what happens.

[|-|x]

Also, you will need to block the column names in your insert statement as you had done previously in the commented out line, ie: INSERT INTO Rooms (RoomID, [First Name], [Middle Name], [Last Name], [Room Type], [Amt/Day], Comments) VALUES ...

That's what I can see from a quick glance over your code. If you try these changes and then provide any error messages still received I/someone will help further.

[ZIPB]

Im getting the error OleDbException was Unhandled when i run it as is.

WHEN I PUT THE ExecuteNonQuery inside the try block i get SYNTAX ERROR IN INSERT INTO STATEMENT

"@Amt/Day" is set as a text feild

When i remove the the blocks from the column i get SYNTAX ERROR IN INSERT INTO STATEMENT

[Begginnerdev]

Why not use a data adapter?

For example:

       Try
            Dim da As New OleDbDataAdapter(New OleDbCommand("SELECT * FROM Rooms WHERE 1=2", _
                                           New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\PATIENT_O\PATIENT_O\Patient-O.accdb'"))) 

                                           'The select gets the table structure.
            Dim ds As New DataSet

            da.Fill(ds, "Rooms")
            Dim dr As DataRow = ds.Tables("Rooms").Rows.Add()

            'Replace these names with the exact names you have.

            dr("[RoomID]") = IIf(String.IsNullOrEmpty(txtRoomID.Text), DBNull.Value, txtRoomID.Text)
            dr("[First Name]") = IIf(String.IsNullOrEmpty(txtroomFName.Text), DBNull.Value, txtroomFName.Text)
            dr("[Middle Name]") = IIf(String.IsNullOrEmpty(txtroomMName.Text), DBNull.Value, txtroomMName.Text)
            dr("[Last Name]") = IIf(String.IsNullOrEmpty(txtroomLName.Text), DBNull.Value, txtroomLName.Text)
            dr("[Room Type]") = IIf(String.IsNullOrEmpty(txtRoomType.Text), DBNull.Value, txtRoomType.Text)
            dr("[Amt/Day]") = IIf(String.IsNullOrEmpty(txtroomAmount.Text), DBNull.Value, txtroomAmount.Text)
            dr("[Comments]") = IIf(String.IsNullOrEmpty(txtroomComment.Text), DBNull.Value, txtroomComment.Text)

            da.InsertCommand = New OleDb.OleDbCommandBuilder(da).GetInsertCommand
            da.Update(ds, "Rooms")

            da = Nothing
            ds = Nothing
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

My eye catches txtRoomComment.text.

If there is a ' character present in that field, you would have been terminating the sql statement.

DataAdapters protect against this.

[ZIPB]

Im getting an error saying

system.argumentexception column ['RoomID'] does not belong to table Rooms when i comment it out i get the same error for the following column

[Begginnerdev]

You will have to change the column names in the dr("column") with the correct column names.

[ZIPB]

The colums names are correct

[|-|x]

Can you post your table structure. There is obviously something not quite right.

RE: your original method

WHEN I PUT THE ExecuteNonQuery inside the try block i get SYNTAX ERROR IN INSERT INTO STATEMENT

That is what I would expect to see since your SQL is a bit off.

"@Amt/Day" is set as a text feild

The datatype is not relevant, @Amt/Day is not a valid parameter name, you need to remove the slash: ie @AmtDay

When i remove the the blocks from the column i get SYNTAX ERROR IN INSERT INTO STATEMENT

Thats because you need the square brackets.

The following are sytax errors in your INSERT SQL;

1. you can't have spaces in column names without them, or the SQL will think they are separate fields or aliases and wont match the actual column names.
2. you can't have spaces or special characters in parameter names.
3. you don't need to quote ' the parameters as they are already of the appropriate datatype. You have quoted the whole lot which will try to insert a single literal string instead of the parameter values.

You're corrected SQL is:

cmd.CommandText = "INSERT INTO Rooms ([RoomID], [First Name], [Middle Name], [Last Name], [Room Type], [Amt/Day], [Comments]) VALUES (@RoomID, @FirstName, @MiddleName, @LastName, @RoomType, @AmtDay, @Comments)"

[|-|x]

Beginnerdev is correct in saying this:

txtRoomComment.text.

If there is a ' character present in that field, you would have been terminating the sql statement

In addition to the syntax changes above, you should apply some parsing/checking for characters that may break your sql. This can be done with a simple Replace but there are several characters that may have adverse affects. It is common to have a function that prepares string / user input data for database insertion, which handles this type of thing.

[ZIPB]

Thanks guys. It woked after the changes. This is the final code

    Dim con As New OleDbConnection
    Dim cmd As New OleDb.OleDbCommand
    cmd = New OleDbCommand(Command, con)
    con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\PATIENT_O\PATIENT_O\Patient-O.accdb"

    cmd.CommandType = System.Data.CommandType.Text
    cmd.Connection = con
    con.Open()

    cmd.CommandText = "INSERT INTO Rooms ([RoomID], [FirstName], [MiddleName], [LastName], [RoomType], [AmtDay], [Comments]) VALUES (@RoomID, @FirstName, @MiddleName, @LastName, @RoomType, @AmtDay, @Comments)"
    cmd.Parameters.AddWithValue("@RoomID", txtRoomID.Text)
    cmd.Parameters.AddWithValue("@FirstName", txtroomFName.Text)
    cmd.Parameters.AddWithValue("@MiddleName", txtroomMName.Text)
    cmd.Parameters.AddWithValue("@LastName", txtroomLName.Text)
    cmd.Parameters.AddWithValue("@RoomType", txtRoomtype.Text)
    cmd.Parameters.AddWithValue("@AmtDay", txtroomAmount.Text)
    cmd.Parameters.AddWithValue("@Comments", txtroomComment.Text)


    'cmd.ExecuteNonQuery()
    Try
        cmd.ExecuteNonQuery()
        MsgBox("Record Added")
    Catch exceptionObject As Exception

        MessageBox.Show(exceptionObject.Message)
    Finally
        con.Close()
    End Try
    txtRoomID.Text = ""
    txtroomFName.Text = ""
    txtroomMName.Text = ""
    txtroomLName.Text = ""
    txtRoomtype.Text = ""
    txtroomAmount.Text = ""
    txtroomComment.Text = ""

[babujibabuji]

if you are trying to pull the data from excel sheet in to database. Try DTS pakages.uploading speed will be better than this method. your application will hang when you upload huge data.