Solved

Updation query in Access through VB.net

Posted on 2006-07-13
4
208 Views
Last Modified: 2010-04-23
Hello experts I am working on an application where I update the MsAccess database by coding. Now I am stuck with minor error but i did't able to rectify.
please tell me what's error in this code as it show me the syntax error
Private Sub updateAppNote()
        If AppConn.State.ToString = "Closed" Then
            AppConn.Open()
        End If
       
        Dim SQL As String = "UPDATE Applicant_Note " & _
        "set Time_Stamp='" & Now.ToShortDateString & " " & Now.ToShortTimeString & "', Note='" & txtnote.txt  & _
        "' Where NID=" & CLng(dgvNoteList.CurrentRow.Cells(0).Value)

        MsgBox(SQL)
        Dim Dc As New OleDbCommand(SQL, AppConn)
        Dc.ExecuteNonQuery()
        AppConn.Close()
0
Comment
Question by:EmmanuelNwokedi
  • 3
4 Comments
 
LVL 35

Expert Comment

by:YZlat
ID: 17100760
If AppConn.State = ConnectionState.Closed Then
            AppConn.Open()
        End If
0
 
LVL 35

Accepted Solution

by:
YZlat earned 50 total points
ID: 17103706
also when using Access database you should wrap Date values in #. Is Time_Stamp a string field or a date field?

If date, then do:

 Dim SQL As String = "UPDATE Applicant_Note " & _
        "set Time_Stamp=#" & Now & "#, Note='" & txtnote.txt  & _
        "' Where NID=" & CLng(dgvNoteList.CurrentRow.Cells(0).Value)

if it's a string, use

Dim SQL As String = "UPDATE Applicant_Note " & _
        "set Time_Stamp='" & Now.ToShortDateString.ToString & " " & Now.ToShortTimeString.ToString & "', Note='" & txtnote.txt  & _
        "' Where NID=" & CLng(dgvNoteList.CurrentRow.Cells(0).Value)

or

Dim SQL As String = "UPDATE Applicant_Note " & _
        "set Time_Stamp='" & Now.ToString & "', Note='" & txtnote.txt  & _
        "' Where NID=" & CLng(dgvNoteList.CurrentRow.Cells(0).Value)

0
 

Author Comment

by:EmmanuelNwokedi
ID: 17106640
Hello experts I am working on an application where I update the MsAccess database by coding. Now I am stuck with minor error but i did't able to rectify.
please tell me what's error in this code as it show me the "syntax error in update statement"
I try with every possible way still it show me the same error. Please Help me out
Private Sub updateAppNote()
        If AppConn.State.ToString = "Closed" Then
            AppConn.Open()
        End If
       
        Dim SQL As String = "UPDATE Applicant_Note " & _
        "set Time_Stamp='" & now & "', Note='" & txtnote.text  & _
        "' Where NID=" & CLng(dgvNoteList.CurrentRow.Cells(0).Value)

        MsgBox(SQL)
        Dim Dc As New OleDbCommand(SQL, AppConn)
        Dc.ExecuteNonQuery()
        AppConn.Close()
0
 
LVL 35

Expert Comment

by:YZlat
ID: 17109142
Dim SQL As String = "UPDATE Applicant_Note " & _
        "set Time_Stamp=#" & now & "#, Note='" & txtnote.text  & _
        "' Where NID=" & CLng(dgvNoteList.CurrentRow.Cells(0).Value)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now