none
I need help in DELETING a row with DATE/TIME format in DATAGRIDVIEW using VB.net

    Question

  • hello all

    I have MS ACCESS database and a table name as ALARM.

    table fields are

    Date- property date/time

    Time- property date/time

    Message - property text

    now I am updating the datagridview in alarm conditions,

    and when I press delete button I want that the selected row must be deleted., my code is

        Dim conn As OleDb.OleDbConnection
        Dim strSQL As String
        Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\gcodeuser.accdb;Persist Security Info=False;"
        Dim dbReader As OleDb.OleDbDataReader

            conn = New OleDb.OleDbConnection(strConn)
            conn.Open()
            Dim query = "DELETE FROM Alarm WHERE Time = " + dgView.CurrentRow.Cells(1).Value + ""
            Dim cmdel As OleDbCommand = New OleDbCommand(query, conn)
            Try
                cmdel.ExecuteNonQuery()
                alrmtAdapter.Fill(Me.alrdset.Alarm)

                conn.Close()
            Catch ex As Exception
                MsgBox(ex.Message.ToString, , "Addition Error")

            End Try
            myDA = New OleDbDataAdapter(cmdel)
            Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(myDA)
            myDataSet = New DataSet()
            myDA.Fill(myDataSet, "Alarm")------>'the cursor points here
            dgView.DataSource = myDataSet.Tables("Alarm").DefaultView
            conn.Close()

    I am getting an error as

    "Syntex error(missing operator) in query expression 'Time=12:35:05'.

    I dont know what I should do please help

    thanking you

    Wednesday, May 30, 2012 11:07 AM

Answers

All replies

  • Hello,

    I am not that good with Access, but is seems to me that there is a syntax error in :

    "DELETE FROM ALARM WHERE Time = 12:35:05"

    Indeed, "12:35:05" is not a time datatype, it is not a number, it is not a string datatype (because the single quotes are missing). You need to convert it.

    In regular SQL, you should write something like :

    "DELETE FROM ALARM WHERE Time = convert(time,'12:30:35')"

    I would also like to offer some suggestions on your code :

    • Do not name your table columns "Time" or "Date". This is awful and confusing, because Time and Date are reserved words in many languages.... Rather, use "AlarmTime" or "AlarmDate".
    • Add an "AlarmID" integer field to your table and define it a a primary key. Set it to be an Identity. Then, use this key to delete your records. Your SQL then becomes "DELETE FROM ALARM WHERE AlarmID = 25". Which is easier to manage. And avoids errors when you have two alarms at the same time.

    • Edited by Sygrien Wednesday, May 30, 2012 11:26 AM
    Wednesday, May 30, 2012 11:20 AM
  • try this 

           conn.Open()
            Dim query = "DELETE FROM Alarm WHERE Time = ?"
            Dim cmdel As OleDbCommand = New OleDbCommand(query, conn)
            Dim mydate = Convert.ToDateTime(dgView.CurrentRow.Cells(1).Value);
            cmdel.Parameters.AddWithValue("?", mydate)
            Try
                cmdel.ExecuteNonQuery()
                alrmtAdapter.Fill(Me.alrdset.Alarm)
    
                conn.Close()
            Catch ex As Exception
                MsgBox(ex.Message.ToString, , "Addition Error")
    
            End Try

    Wednesday, May 30, 2012 11:26 AM
  • In MS ACCESS database I have changed Date and Time column to AlarmDate and AlarmTime

    AlarmDate---property date/time , format-LongDate, Input Mask->00:00:00;;_

    AlarmTime---property date/time. format->Long Time, Input Mask ->short date(00-00-0000;0;_)

    Message

    but if i write

     Try
                        conn = New OleDb.OleDbConnection(strConn)
                        conn.Open()
                        Label6.Text = "Following error"
                        strSQL = "Insert Into Alarm Values ('" & Now.ToShortDateString() & "','" & Now.ToLongTimeString() & "','" & Label6.Text & "' )"

                        cmdInsert = New OleDbCommand(strSQL, conn)

                        cmdInsert.ExecuteNonQuery()
                        prevaltag(0) = alrm(0)
                        MsgBox("User added successfully")
                        History.alrmtAdapter.Fill(History.alrdset.Alarm)

                        conn.Close()

                    Catch ex As Exception
                        MsgBox(ex.Message.ToString, , "Insert  Error")
                    End Try
                   
                    If conn.State = True Then
                        conn.Close()

                    End If

    and retrive the values in datagridview, I still see date and time in AlarmTime column.

    I dont know why I am seeing this

    please help me..

    thanking you

    Wednesday, May 30, 2012 12:33 PM
  • @Pooyan

    Your command gives the same error as I was previously getting.

    Wednesday, May 30, 2012 1:00 PM
  • You need to also change the DataGridView column names.

    In design mode, click on the small right arrow, then "Edit Columns". Click on each column and change its "HeaderText" property.

    How did your Insert command go ? Did it work ?

    And what about your Delete command ? Did you change it, so you delete on the ID, rather than on the AlarmTime ?

    Wednesday, May 30, 2012 1:04 PM
  • Note there is a good deal going on here so take your time reading this and if you have questions let me know. All good database table designs have at least one primary key which in your case would make life easier.

    Your should add a auto-incrementing primary key to this table (current versions of MS-Access will prompt you to add a primary key if you don't and will offer to add a auto-inc primary key). So the key does not show set ColumnMapping to Hidden as shown below but now you cannot access the primary key via the DataGridView so after doing the above place a BindingSource component on your form, set the DataSource to your DataTable which was populated via your SELECT statement to the alarm table. The BindingSource becomes the DataSource of the DataGridView. To get the current row (in the DataGridView) primary key you would use the following (assuming BindingSource1 is the name of the BindingSource)

    If BindingSource1.Current IsNot Nothing Then
        Dim Identifier As String = CType(BindingSource1.Current, DataRowView).Item("Identifier").ToString
    End If

    I casted Identifier as a string because of the delete example below.

    ''' <summary>
    ''' Used to remove a row from the alarm table based on
    ''' primary key Identifier
    ''' </summary>
    ''' <param name="Identifier"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function DeleteAlarm(ByVal Identifier As String) As Boolean
        Using cn As New OleDb.OleDbConnection With
                {
                    .ConnectionString = dbConnectionString
                }
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                cmd.CommandText = "DELETE FROM ALARM WHERE Identifier = " & Identifier
                cn.Open()
                Dim Affected = cmd.ExecuteNonQuery
                Return Affected > 0
            End Using
        End Using
    End Function

    Here is your connection string set up as a variable since it is used more than one place

    Private dbConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\gcodeuser.accdb;Persist Security Info=False"

    Loading data and hiding the primary key

    Public Function Load_Alarms() As DataTable
        Using cn As New OleDb.OleDbConnection With
                {
                    .ConnectionString = dbConnectionString
                }
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                cmd.CommandText = "SELECT * FROM Alarm"
                Dim dt As New DataTable
                cn.Open()
                dt.Load(cmd.ExecuteReader)
                dt.Columns("Identifier").ColumnMapping = MappingType.Hidden
                dt.AcceptChanges()
                Return dt
            End Using
        End Using
    End Function
    Another advantage is no need to reload your DataGridView, instead use the remove method of the BindingSource if the row was successfully remove from MS-Access


    KSG

    Wednesday, May 30, 2012 1:31 PM
  • Why do you it so difficult in a kind of vintage VB style, use binding, the DataGridView and the datatable are made for that.

    Look at this sample on our website.

    http://www.vb-tips.com/StronglyTypedDataTableInsertDelete.ASPX

    Showing the datatable in the DataGridView means only to do

    DataGridView1.DataSource = MyNorthwindDataSet.Employees


    Success
    Cor

    Wednesday, May 30, 2012 4:03 PM
  • Why do you it so difficult in a kind of vintage VB style, use binding, the DataGridView and the datatable are made for that.

    Look at this sample on our website.

    http://www.vb-tips.com/StronglyTypedDataTableInsertDelete.ASPX

    Showing the datatable in the DataGridView means only to do

    DataGridView1.DataSource = MyNorthwindDataSet.Employees


    Success
    Cor

    Hi Cor,

    I see nothing wrong with using bindings especially in my shop which uses IBM-DB2 where I’ve never had any success with strongly typed data as the native data provider has never proven to be reliable. Bringing this style to MS-Access in our development team makes sense so that both DB2 and MS-Access are consistent in how things are done.

    Personally I favor implementing a BindingSource as it offers many benefits over a DataTable.

    I see your suggestion as yet another avenue to accomplish a delete but no matter what method is used they should add a primary key.


    KSG

    Wednesday, May 30, 2012 6:15 PM
  • @kevinistructor

    Hi I have taken the Data-table in ACCESS as Alarm

    and fields are. AlarmID(primary key, property--Auto Number), AlarmDate, AlarmTime,Message.

    and set the datagridview to show only AlarmDate, Alarmtime, and Message fields. And AlarmID is hidden.

    Now,How to insert an AutoNumber in Database?

    Thursday, May 31, 2012 4:57 AM
  • Hi, all thank you for your response.

    I am still in trouble, while retrieving the data in datagrid I see Date and time in AlarmTime column too,, I just want to insert current time .

    Moreover I have used AlarmID as primary key and changed the column name to AlarmDate, AlarmTime and Message.

    I want to show just three field in datagridview namely "AlarmDate","AlarmTime"and Message.

    And I want to delete the row selected on the datagird using AlarmID. but I am getting error,

    please help

    command for insert

    """"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""

     conn = New OleDb.OleDbConnection(strConn)
                    conn.Open()
    
                    Try
                        conn = New OleDb.OleDbConnection(strConn)
                        conn.Open()
                        Label6.Text = "Following error"
                        strSQL = "Insert Into Alarm (AlarmDate,AlarmTime,Message) VALUES (' " + Now.ToShortDateString() + "','" + Now.ToShortTimeString() + "','" + Label6.Text + "' )"
    
                        cmdInsert = New OleDbCommand(strSQL, conn)
    
                        cmdInsert.ExecuteNonQuery()
                        prevaltag(0) = alrm(0)
                        MsgBox("User added successfully")
                        ' History.alrmtAdapter.Fill(History.alrdset.Alarm)
    
                        conn.Close()
    
                    Catch ex As Exception
                        MsgBox(ex.Message.ToString, , "Insert  Error")
                    End Try
    

    '"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""'

    command for delete

    """""""""""""""""""""""""""""""""""""""""""""""""""""""""'''

     conn = New OleDb.OleDbConnection(strConn)
            conn.Open()
            Dim query = "DELETE FROM Alarm WHERE AlarmID = " + dgView.CurrentRow.Cells(0).Value + ""
            Dim cmdel As OleDbCommand = New OleDbCommand(query, conn)
            Try
                cmdel.ExecuteNonQuery()
                conn.Close()
            Catch ex As Exception
                MsgBox(ex.Message.ToString, , "Addition Error")
    
            End Try
            If conn.State = True Then
                conn.Close()
    
            End If
    getting the table as

           and while deleting I am getting error as

    Please help me.

    thanking you

    Thursday, May 31, 2012 5:50 AM
  • Kevin,

    Of course you would add a bindingsource, it came in version 2.0 to overcome some flaws in the binding which was in 1.x. As it should currently always be backwards compatible, they added the bindingsource as a kind of intermediate (they also added direct functionality like for the new collection classes).

    However, I tried to keep my reply as simple as possible and direct binding the datatable to the datagridview would go in this case.


    Success
    Cor

    • Marked as answer by Bhatt Chirag Friday, June 01, 2012 4:24 AM
    • Unmarked as answer by Bhatt Chirag Friday, June 01, 2012 4:33 AM
    Thursday, May 31, 2012 6:32 AM
  • Hello,

    The concatenation operator in Visual Basic is "&". When building your query string, you should write :

    Dim query as String = "DELETE FROM Alarm WHERE AlarmID = " & dgView.CurrentRow.Cells(0).Value 
    

    Now, please read carefully the advices from Cor.

    What you are showing us is very old fashioned code. You can certainly make it work, but you would get a much more flexible code by using databinding and datasources. Of course, this would involve some time on your part, to learn new concepts and new techniques.

    If you are interested, you can view these videos which will teach you the basic of displaying data on forms.

    http://msdn.microsoft.com/en-us/vstudio/bb643826

    http://msdn.microsoft.com/en-us/vstudio/bb643826

    http://msdn.microsoft.com/en-us/vstudio/bb643827

    • Marked as answer by Bhatt Chirag Friday, June 01, 2012 4:24 AM
    • Unmarked as answer by Bhatt Chirag Friday, June 01, 2012 4:33 AM
    Thursday, May 31, 2012 10:49 AM
  • Hello,

    I would highly advise using parameters when building the where clause of your SQL and don't obtain data from the DataGridView but instead from the underlying DataSource which would be a DataTable.

    The following example loads several rows from a database (note the database has the same name as yours and is located in the application folder i.e. Bin\Debug) table into a DataTable which becomes the DataSource of a BindingSource and the BindingSource becomes the DataSource of a DataGridView. The DataGridView has nothing set at design time.

    Operation 1: Read data as mention above

    Operation 2: Remove current row from database table and BindingSource/DataTable/DataGridView

    To edit a record use the same model as removing a row but using UPDATE in your SQL. Get use to parameters.

    For adding a record download the example at the following link, reply #25 I show how to get the new identifier.

    http://www.vbforums.com/showthread.php?t=655337

    Note: There is a module that nicely wraps dialogs at the end which is a partial version of this module.

    File name AlarmCode.vb

    Module AlarmCode
        Private AlarmDatabase As String = IO.Path.Combine(Application.StartupPath, "gcodeuser.accdb")
        Private Builder As New OleDb.OleDbConnectionStringBuilder With
            {
                .Provider = "Microsoft.ACE.OLEDB.12.0",
                .DataSource = AlarmDatabase
            }
        Public Function LoadAlarms() As DataTable
            Using cn As New OleDb.OleDbConnection With
                    {
                        .ConnectionString = Builder.ConnectionString
                    }
                Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                    cmd.CommandText = "SELECT AlarmID As Identifier, AlarmDate,AlarmTime, Message FROM Alarm"
                    Dim dt As New DataTable
                    Try
                        cn.Open()
                        dt.Load(cmd.ExecuteReader)
                        dt.Columns("Identifier").ColumnMapping = MappingType.Hidden
                    Catch ex As Exception
                        MessageBox.Show("Failed to load Alarm data. See error message below" & Environment.NewLine & ex.Message)
                    End Try
                    dt.AcceptChanges()
                    Return dt
                End Using
            End Using
        End Function
        Public Function RemoveCurrentAlarm(ByVal Identifier As Integer) As Boolean
            Dim AffectedRows As Integer = 0
            Using cn As New OleDb.OleDbConnection With
                    {
                        .ConnectionString = Builder.ConnectionString
                    }
                Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                    cmd.CommandText = "DELETE FROM Alarm WHERE AlarmId=P1"
                    Dim IdentifierParameter As New OleDb.OleDbParameter With
                        {
                            .DbType = DbType.Int32,
                            .ParameterName = "P1",
                            .Value = Identifier
                        }
                    cmd.Parameters.Add(IdentifierParameter)
                    Try
                        cn.Open()
                        AffectedRows = cmd.ExecuteNonQuery
                    Catch ex As Exception
                        My.Dialogs.ExceptionDialog("Failed to remove current alarm", "Error", ex)
                    End Try
                End Using
            End Using
            Return AffectedRows > 0
        End Function
    End Module

    File name Form1.vb (one DataGridView, one button)

    Public Class AlarmMain
        WithEvents bsData As New BindingSource
        Private Sub AlarmMain_Load(
            ByVal sender As System.Object,
            ByVal e As System.EventArgs) Handles MyBase.Load
            bsData.DataSource = LoadAlarms()
            DataGridView1.DataSource = bsData
            Dim Position = bsData.Find("Identifier", 3)
            If Position <> -1 Then
                bsData.Position = Position
            End If
        End Sub
        Private Sub cmdDelete_Click(
            ByVal sender As System.Object,
            ByVal e As System.EventArgs) Handles cmdDelete.Click
            If My.Dialogs.Question("Remove current record?") Then
                '
                ' Ask user if they really want to remove the alarm
                '
                If RemoveCurrentAlarm(
                    CInt(CType(bsData.Current, DataRowView).Item("Identifier"))) Then
                    '
                    ' Alarm has been removed from database now remove from DataTable
                    ' which removes it from view of course.
                    '
                    bsData.RemoveCurrent()
                End If
            End If
        End Sub
    End Class

    File name MyDialogs.vb

    Namespace My
        <Global.System.ComponentModel.EditorBrowsable(
            Global.System.ComponentModel.EditorBrowsableState.Never)> _
        Partial Friend Class _Dialogs
            Public Function Question(ByVal Text As String) As Boolean
                Return (MessageBox.Show(Text,
                        My.Application.Info.Title,
                        MessageBoxButtons.YesNo,
                        MessageBoxIcon.Question,
                        MessageBoxDefaultButton.Button2) = MsgBoxResult.Yes)
            End Function
            Public Sub InformationDialog(ByVal Text As String, ByVal Title As String)
                MessageBox.Show(Text, Title, MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Sub
            Public Sub ExceptionDialog(ByVal Text As String, ByVal Title As String, ByVal ex As Exception)
                Dim Message As String = String.Concat(Text, Environment.NewLine, ex.Message)
                MessageBox.Show(Message, Title, MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Sub
            Public Sub ExceptionDialog(ByVal ex As Exception, ByVal Text As String, ByVal Title As String)
                MessageBox.Show(String.Format("{0}{1}{2}",
                                Text,
                                Environment.NewLine,
                                ex.Message),
                                Title,
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Error)
            End Sub
        End Class
        <Global.Microsoft.VisualBasic.HideModuleName()> _
        Friend Module KSG_Dialogs
            Private instance As New ThreadSafeObjectProvider(Of _Dialogs)
            ReadOnly Property Dialogs() As _Dialogs
                Get
                    Return instance.GetInstance()
                End Get
            End Property
        End Module
    End Namespace


    KSG

    Thursday, May 31, 2012 1:26 PM
  • hi, I have inserted the Images for Datagirdview and the queries also.

    I still getting the default date in AlarmTime column which I dont want to see. Please help

    Friday, June 01, 2012 4:33 AM
  • hi, I have inserted the Images for Datagirdview and the queries also.

    I still getting the default date in AlarmTime column which I dont want to see. Please help


    What images? What does your code look like for AlarmTime that is getting a date?

    KSG

    Friday, June 01, 2012 6:30 AM
  • @ kevin

    My code for inserting the values in Alarm Table is

      Dim time As DateTime = DateTime.Now
                    Dim format As String = "d MMM        yyyy"
                    Dim format1 As String = "HH:mm:ss"
     conn = New OleDb.OleDbConnection(strConn)
                    conn.Open()
    
                    Try
                        conn = New OleDb.OleDbConnection(strConn)
                        conn.Open()
                        Label6.Text = "Following error" 
                        strSQL = "Insert Into Alarm (AlarmDate,AlarmTime,Message) VALUES ('" & time.ToString(format) & "','" & time.ToString(format1) & "','" + Label6.Text + "' )"
                        cmdInsert = New OleDbCommand(strSQL, conn)
    
                        cmdInsert.ExecuteNonQuery()
                        prevaltag(0) = alrm(0)
                        MessageBox.Show("User added successfully", time.ToString(format1))
                        History.alrmtAdapter.Fill(History.alrdset.Alarm)
    
                        conn.Close()
    
                    Catch ex As Exception
                        MsgBox(ex.Message.ToString, , "Insert  Error")
                    End Try
    
                    If conn.State = True Then
                        conn.Close()
    
                    End If

    I see the default date as shown in the image in Datagridview but in the Actual databse it just shows the current time.

    Friday, June 01, 2012 7:21 AM
  • @kevininstructor

    in actual database I can see

    please help me...

    thank you

    • Marked as answer by Bhatt Chirag Friday, June 01, 2012 10:10 AM
    Friday, June 01, 2012 7:24 AM
  • This is just the way the datagridview displays the information.

    You simply need to modify the format of that field.

    1. In design mode, click on the small right arrow, then on "Edit Columns"
    2. Select the column of the AlarmTime.
    3. Click on "DefaultCellStyle" and click on the ellipsis (...) button
    4. Click on "Format" and click on the ellipsis (...) button
    5. Click on "Datetime" and select your format

    • Edited by Sygrien Friday, June 01, 2012 9:28 AM
    Friday, June 01, 2012 9:28 AM
  • Thank You very much all of you and specially,, @ Sygrien...

    The problem is solved

    regards

    Friday, June 01, 2012 10:11 AM