Solved

Updation query in Access through VB.net

Posted on 2006-07-13
4
209 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

778 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