help in updating a database access..

[pao09]

can someone please check my code because it doesnt update my database.. i set the room no. as the primary key.. can you please help, its urgent.. i need it tomorrow.. thanks.

Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=..\data\YayaDB.mdb"
        con.Open()
        Dim query = "UPDATE Rooms SET Rooms.Customer_Name = @Name, Rooms.CheckIn_Date = @Date, Rooms.Time_In = @TimeIn, Rooms.Time_Out = @TimeOut, Rooms.Price = @Price WHERE Rooms.Room = @RoomNo"
        Dim cmd = New OleDbCommand(query, con)
        cmd.Connection = con
        cmd.Parameters.AddWithValue("@RoomNo", txtRoom.Text)
        cmd.Parameters.AddWithValue("@Name", txtName.Text)
        cmd.Parameters.AddWithValue("@Date", txtDate.Text)
        cmd.Parameters.AddWithValue("@TimeIn", txtTimeIn.Text)
        cmd.Parameters.AddWithValue("@TimeOut", txtTimeOut.Text)
        cmd.Parameters.AddWithValue("@Price", txtPrice.Text)

        cmd.ExecuteNonQuery()
        con.Close()

[cgeier]

Add some error handling to your code. Also double check the data types in the database. I think that if you use ".AddWithValue" it is assumed that you are passing the correct data types.

Ex: if "CheckIn_Date" in the database is of type Date/Time then you probably need to pass it a variable of that type.

Dim CheckIn_Date As DateTime
cmd.Parameters.AddWithValue("@Date", CheckIn_Date)

Instead of:
cmd.Parameters.AddWithValue("@Date", txtDate.Text)
This is probably passing a String (char/varchar).

Likewise with some of your other columns.
Also, it's probably not a good idea to be using reserved words as variables names (such as "Date").

See the following:
http://support.microsoft.com/kb/321266
http://support.microsoft.com/kb/286335
http://msdn.microsoft.com/en-us/library/ms714540(VS.85).aspx

Try
Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=..\data\YayaDB.mdb"
        con.Open()
        Dim query = "UPDATE Rooms SET Rooms.Customer_Name = @Name, Rooms.CheckIn_Date = @Date, Rooms.Time_In = @TimeIn, Rooms.Time_Out = @TimeOut, Rooms.Price = @Price WHERE Rooms.Room = @RoomNo"
        Dim cmd = New OleDbCommand(query, con)
        cmd.Connection = con
        cmd.Parameters.AddWithValue("@RoomNo", txtRoom.Text)
        cmd.Parameters.AddWithValue("@Name", txtName.Text)
        cmd.Parameters.AddWithValue("@Date", txtDate.Text)
        cmd.Parameters.AddWithValue("@TimeIn", txtTimeIn.Text)
        cmd.Parameters.AddWithValue("@TimeOut", txtTimeOut.Text)
        cmd.Parameters.AddWithValue("@Price", txtPrice.Text)

       ' cmd.ExecuteNonQuery()

        Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
        Console.WriteLine("RowsAffected: {0}", rowsAffected)

        con.Close()
Catch ex As Exception 
   Console.WriteLine(ex.Message) 
  'Dim retVal = MsgBox("Error: " & ex.ToString(), MsgBoxStyle.Critical + MsgBoxStyle.OkOnly)
End Try

[pao09]

the try and catch doesnt affect the application.. it accepts when i click the button but still doesnt update the database..

[cgeier]

There are actually quite a different ways to connect to a database. I haven't figured out why your's doesn't work yet, but below is one that should work.

I'm assuming that you've already added a Reference to "System.data" (or added "Imports System.Data.OleDb" above your class definition).

Private Sub updateDB()


        Try
            Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source==..\data\YayaDB.mdb;"
            Dim con As OleDb.OleDbConnection = New OleDb.OleDbConnection(ConnectionString)

            Dim cmd As OleDbCommand = con.CreateCommand()

            cmd.CommandText = "UPDATE Rooms SET Rooms.Customer_Name = @Name, Rooms.CheckIn_Date = @Date, Rooms.Time_In = @TimeIn, Rooms.Time_Out = @TimeOut, Rooms.Price = @Price WHERE Rooms.Room = @RoomNo"

            cmd.Parameters.AddWithValue("@Name", txtName.Text)
            cmd.Parameters.AddWithValue("@Date", txtDate.Text)
            cmd.Parameters.AddWithValue("@TimeIn", txtTimeIn.Text)
            cmd.Parameters.AddWithValue("@TimeOut", txtTimeOut.Text)
            cmd.Parameters.AddWithValue("@Price", txtPrice.Text)
            cmd.Parameters.AddWithValue("@RoomNo", txtRoom.Text)


            con.Open()
            Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
            ' cmd.ExecuteNonQuery()

            'Console.WriteLine("RowsAffected: {0}", rowsAffected)
            Dim retVal = MsgBox("RowsAffected: " & rowsAffected, MsgBoxStyle.Information + MsgBoxStyle.OkOnly)

            con.Close()
        Catch ex As Exception
            'Console.WriteLine(ex.Message)
            Dim retVal = MsgBox("Error: " & ex.ToString(), MsgBoxStyle.Critical + MsgBoxStyle.OkOnly)
        End Try

    End Sub

[cgeier]

Ok, I think I figured out why your code doesn't work. For some reason, it seems that the order your parameters are listed in matters. At least for the one in the where clause (@RoomNo needs to be listed last). It's probably a bug and you should open a case with MS.

Change your code FROM:

cmd.Parameters.AddWithValue("@RoomNo", txtRoom.Text)
            cmd.Parameters.AddWithValue("@Name", txtName.Text)
            cmd.Parameters.AddWithValue("@Date", txtDate.Text)
            cmd.Parameters.AddWithValue("@TimeIn", txtTimeIn.Text)
            cmd.Parameters.AddWithValue("@TimeOut", txtTimeOut.Text)
            cmd.Parameters.AddWithValue("@Price", txtPrice.Text)

TO:

cmd.Parameters.AddWithValue("@Name", txtName.Text)
            cmd.Parameters.AddWithValue("@Date", txtDate.Text)
            cmd.Parameters.AddWithValue("@TimeIn", txtTimeIn.Text)
            cmd.Parameters.AddWithValue("@TimeOut", txtTimeOut.Text)
            cmd.Parameters.AddWithValue("@Price", txtPrice.Text)
            cmd.Parameters.AddWithValue("@RoomNo", txtRoom.Text)

Notice that the following line is now the last parameter added:
cmd.Parameters.AddWithValue("@RoomNo", txtRoom.Text)

[cgeier]

The other version I posted will work too. Here's the implementation using the code from your original post.

Dim con As New OleDb.OleDbConnection
 
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=..\data\YayaDB.mdb"

con.Open()

Dim query = "UPDATE Rooms SET Rooms.Customer_Name = @Name, Rooms.CheckIn_Date = @Date, Rooms.Time_In = @TimeIn, Rooms.Time_Out = @TimeOut, Rooms.Price = @Price WHERE Rooms.Room = @RoomNo"
            
Dim cmd = New OleDbCommand(query, con)
cmd.Connection = con

cmd.Parameters.AddWithValue("@Name", txtName.Text)
cmd.Parameters.AddWithValue("@Date", txtDate.Text)
cmd.Parameters.AddWithValue("@TimeIn", txtTimeIn.Text)
cmd.Parameters.AddWithValue("@Price", txtPrice.Text)
cmd.Parameters.AddWithValue("@TimeOut", txtTimeOut.Text)
            
'It appears that variables in the where clause need to be
'listed last. Follow the order that you list the parameters in 
'your update statement and it should be ok
            
cmd.Parameters.AddWithValue("@RoomNo", txtRoom.Text)

' cmd.ExecuteNonQuery()

Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
Dim retVal = MsgBox("RowsAffected: " & rowsAffected, MsgBoxStyle.Information + MsgBoxStyle.OkOnly)

'Console.WriteLine("RowsAffected: {0}", rowsAffected)

[pao09]

my code is working.. you definitely identify the error.. thanks cgeier..