locked
Error saving a DataSet to Access database RRS feed

  • Question

  • I got a rather strange error in a sub-routine, saving a DataSet row to an Access database.
    The code reads:

    Public Class Form1
        Dim strSql As String = ""
        Public m_lngUserID As Long = 54
        Public m_strSchedulesConnect As String
        Public m_Path As String
    
    Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load
            Dim m_path = My.Application.Info.DirectoryPath.ToString
            m_strSchedulesConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                "Data Source=" & m_path & "\Schedules.accdb;" & _
                                "Jet OLEDB:Database Password=xxxx;"
    
            SavePrivateSettings()
    End Sub
    
    Private Sub SavePrivateSettings()
            Try
                Dim con As New OleDbConnection(m_strSchedulesConnect)
                con.Open()
                strSql = "SELECT * FROM PrivateSettings WHERE CLng(EmployeeID) ="
                strSql = strSql & Chr(34) & m_lngUserID & Chr(34)
                Dim da As New OleDbDataAdapter(strSql, con)
                Dim ds = New DataSet
                da.Fill(ds, "PrivateSettings")
    
                ' edit the DataSet object
                Dim dr As DataRow = ds.Tables("PrivateSettings").Rows(0)
                dr("LastFormUsed") = Me.Name
                Dim objCommandBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(da)
                da.Update(ds, "PrivateSettings")     '<--- line 70, error here
    
                ds = Nothing
                da = Nothing
                con.Close()
                con = Nothing
    
            Catch ex As Exception
                Debug.Print(Err.Number & vbCrLf & ex.Message & vbCrLf & ex.StackTrace & " - SavePrivateSettings")
            End Try
    End Sub
    End Class

    The error shows in line 70:

    da.Update(ds, "PrivateSettings")

    with the StackTrace reading:

    A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
    5
    No value is given for one or more necessary parameters.
       ved System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
       at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
       at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
       at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
       at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
       at WindowsApplication1.Form1.SavePrivateSettings() i C:\VB_Net Programmer\Test_ADO_Net\WindowsApplication1\WindowsApplication1\Form1.vb:line 70 - SavePrivateSettings

    I can not for the 'life of me' figure out this missing parameter. Could somebody out there please point me right?

    Kind regards
    Jorgen


    levesen

    Wednesday, October 24, 2012 3:55 PM

Answers

  • Hei again,
    just found out the problem, there were actually two of the parameters which made all the headache. The "E-MailAdress" had to be enclosed in brackets and the "CDate(Now)" had to be a string format. The working code looks like this:

    Dim myCommand As OleDbCommand = New OleDb.OleDbCommand("Select * from MyRec")
                    myCommand.Connection = m_ConContacts
                    myCommand.CommandText = "UPDATE MyRec SET FirstName = ?" & _
                    ", LastName = ?" & _
                    ", [E-MailAdress] = ?" & _
                    ", SMTPserver = ?" & _
                    ", Country = ?" & _
                    ", LanguageScreen = ?" & _
                    ", LanguagePrint = ?" & _
                    ", DateInstalled = ?" & _
                    " WHERE RecID = ?"
                    myCommand.Parameters.AddWithValue("@FirstName", txtFirstName.Text)
                    myCommand.Parameters.AddWithValue("@LastName", txtLastName.Text)
                    myCommand.Parameters.AddWithValue("@[E-MailAdress]", txtMail.Text)
                    myCommand.Parameters.AddWithValue("@SMTPserver", txtServer.Text)
                    myCommand.Parameters.AddWithValue("@Country", cboCountry.Text)
                    myCommand.Parameters.AddWithValue("@LanguageScreen", cboLanguage.Text)
                    myCommand.Parameters.AddWithValue("@LanguagePrint", cboLanguage.Text)
                    myCommand.Parameters.AddWithValue("@DateInstalled", CStr(CDate(Now)))
                    myCommand.Parameters.AddWithValue("@RecID", 1)
                    myCommand.ExecuteNonQuery()

    I think we can safely close this thread now - and I sincerely thank you very much for your work, and patience, guiding me back on the right path.
    With kind regards
    Jorgen

     

    levesen

    • Marked as answer by levesen Monday, November 12, 2012 10:46 PM
    Monday, November 12, 2012 7:48 PM

All replies

  • Hi Jorgen,

    I read the code and I think it is fine since  you used question mark instead of named parameters.

    Could you please get the update command and post it here?

    Best Regards,


    Bob Wu [MSFT]
    MSDN Community Support | Feedback to us

    Friday, October 26, 2012 9:59 AM
  • Hi Bob,

    the update command is posted above:

    Dim objCommandBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(da)
    da.Update(ds, "PrivateSettings")     '<--- line 70, error here
    
    

    as well as the StackTrace error. The DataSet (ds) is showning correctly the one database row it should.

    Kind regards
    Jorgen


    levesen

    Sunday, October 28, 2012 7:34 PM
  • Hi Jorgen,

    What Bob meant was to post the Update command that the CommandBuilder generated.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Monday, October 29, 2012 4:52 AM
  • Hi Jorgen,

    You can get the command by calling OleDbDataAdapter.UpdateCommand Property.


    Bob Wu [MSFT]
    MSDN Community Support | Feedback to us

    Monday, October 29, 2012 9:57 AM
  • Hi again both,

    I am not quite sure of what to look for, but the update command text is as follows:

    Debug.Print(objCommandBuilder.GetUpdateCommand.CommandText.ToString):
    
    UPDATE PrivateSettings SET EmployeeID = ?, SurName = ?, FirstName = ?, DepartmentID = ?, DepartmentName = ?, LocationID = ?, LocationName = ?, InternalPhoneNo = ?, Security = ?, SoundOn = ?, LanguageScreen = ?, LanguagePrint = ?, DayStartTime = ?, DayCloseTime = ?, Address1 = ?, Address2 = ?, Address3 = ?, Zip = ?, Town = ?, State = ?, Country = ?, Mail = ?, PrivatePhone = ?, PrivateFax = ?, PrivateMail = ?, InternetUrl = ?, PrivateIncommingMailServer = ?, PrivateOutgoingMailServer = ?, PrivateMailUserName = ?, PrivateMailPassword = ?, E-MailSysRespons = ?, EmailFootNote = ?, EmailFont = ?, EmailFontSize = ?, LetterFootNote = ?, LetterFont = ?, LetterFontSize = ?, ShowBirthdayAlarm = ?, ShowAlarmBefore = ?, UseWord = ?, AutoLogIn = ?, AllowErrorSend = ?, PicInDatabase = ?, StoreEmails = ?, ShowBirthMeetingPlace = ?, ShowHolidayMeetingPlace = ?, PrivateLetterFolder = ?, WarehouseID = ?, WarehouseName = ?, UseOutlook = ?, LastFormUsed = ? WHERE ((RecordID = ?) AND ((? = 1 AND EmployeeID IS NULL) OR (EmployeeID =
     ?)) AND ((? = 1 AND SurName IS NULL) OR (SurName = ?)) AND ((? = 1 AND FirstName IS NULL) OR (FirstName = ?)) AND ((? = 1 AND DepartmentID IS NULL) OR (DepartmentID = ?)) AND ((? = 1 AND DepartmentName IS NULL) OR (DepartmentName = ?)) AND ((? = 1 AND LocationID IS NULL) OR (LocationID = ?)) AND ((? = 1 AND LocationName IS NULL) OR (LocationName = ?)) AND ((? = 1 AND InternalPhoneNo IS NULL) OR (InternalPhoneNo = ?)) AND ((? = 1 AND Security IS NULL) OR (Security = ?)) AND ((? = 1 AND SoundOn IS NULL) OR (SoundOn = ?)) AND ((? = 1 AND LanguageScreen IS NULL) OR (LanguageScreen = ?)) AND ((? = 1 AND LanguagePrint IS NULL) OR (LanguagePrint = ?)) AND ((? = 1 AND DayStartTime IS NULL) OR (DayStartTime = ?)) AND ((? = 1 AND DayCloseTime IS NULL) OR (DayCloseTime = ?)) AND ((? = 1 AND Address1 IS NULL) OR (Address1 = ?)) AND ((? = 1 AND Address2 IS NULL) OR (Address2 = ?)) AND ((? = 1 AND Address3 IS NULL) OR (Address3 = ?)) AND ((? = 1 AND Zip IS NULL) OR (Zip = ?)) AND ((? = 1 AND Town IS NULL) OR (Town = ?)) AN
    D ((? = 1 AND State IS NULL) OR (State = ?)) AND ((? = 1 AND Country IS NULL) OR (Country = ?)) AND ((? = 1 AND Mail IS NULL) OR (Mail = ?)) AND ((? = 1 AND PrivatePhone IS NULL) OR (PrivatePhone = ?)) AND ((? = 1 AND PrivateFax IS NULL) OR (PrivateFax = ?)) AND ((? = 1 AND PrivateMail IS NULL) OR (PrivateMail = ?)) AND ((? = 1 AND InternetUrl IS NULL) OR (InternetUrl = ?)) AND ((? = 1 AND PrivateIncommingMailServer IS NULL) OR (PrivateIncommingMailServer = ?)) AND ((? = 1 AND PrivateOutgoingMailServer IS NULL) OR (PrivateOutgoingMailServer = ?)) AND ((? = 1 AND PrivateMailUserName IS NULL) OR (PrivateMailUserName = ?)) AND ((? = 1 AND PrivateMailPassword IS NULL) OR (PrivateMailPassword = ?)) AND ((? = 1 AND E-MailSysRespons IS NULL) OR (E-MailSysRespons = ?)) AND ((? = 1 AND EmailFont IS NULL) OR (EmailFont = ?)) AND ((? = 1 AND EmailFontSize IS NULL) OR (EmailFontSize = ?)) AND ((? = 1 AND LetterFont IS NULL) OR (LetterFont = ?)) AND ((? = 1 AND LetterFontSize IS NULL) OR (LetterFontSize = ?)) AND ((? = 1 A
    ND ShowBirthdayAlarm IS NULL) OR (ShowBirthdayAlarm = ?)) AND ((? = 1 AND ShowAlarmBefore IS NULL) OR (ShowAlarmBefore = ?)) AND ((? = 1 AND UseWord IS NULL) OR (UseWord = ?)) AND ((? = 1 AND AutoLogIn IS NULL) OR (AutoLogIn = ?)) AND ((? = 1 AND AllowErrorSend IS NULL) OR (AllowErrorSend = ?)) AND ((? = 1 AND PicInDatabase IS NULL) OR (PicInDatabase = ?)) AND ((? = 1 AND StoreEmails IS NULL) OR (StoreEmails = ?)) AND ((? = 1 AND ShowBirthMeetingPlace IS NULL) OR (ShowBirthMeetingPlace = ?)) AND ((? = 1 AND ShowHolidayMeetingPlace IS NULL) OR (ShowHolidayMeetingPlace = ?)) AND ((? = 1 AND PrivateLetterFolder IS NULL) OR (PrivateLetterFolder = ?)) AND ((? = 1 AND WarehouseID IS NULL) OR (WarehouseID = ?)) AND ((? = 1 AND WarehouseName IS NULL) OR (WarehouseName = ?)) AND ((? = 1 AND UseOutlook IS NULL) OR (UseOutlook = ?)) AND ((? = 1 AND LastFormUsed IS NULL) OR (LastFormUsed = ?)))

    does this explain what are missing? Looks to me everything is missing! 

    Kind regards
    Jorgen


    levesen

    Monday, October 29, 2012 1:25 PM
  • Hi Jorgen,

    The command text seems fine.

    Could you please upload a demo to SkyDrive and share it with us? What's more, instead of using select *, trying select the fields you want, such as select EmployeeID,SurName,..

    Best Regards,


    Bob Wu [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, October 30, 2012 9:29 AM
  • Hi again,
    the program is now uploaded to:

    https://skydrive.live.com/redir?resid=26469113D42E0C01!104

    Kind regards
    Jorgen


    levesen

    Tuesday, October 30, 2012 4:57 PM
  • Hi Jorgen,

    Thank you for sharing the demo with us. I can reproduce this issue now. However, I'm not sure why this issue occur yet, I will consult this with some other engineers.

    Best Regards,


    Bob Wu [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, October 31, 2012 10:19 AM
  • I am sorry, forgot to include the MyRec recordset in the database. I have re-uploaded the project once more.

    I would be very grateful if somebody could spare the time to take a look at the code, and perhaps point out the error I have made!
    Kind regards
    Jorgen


    levesen

    Tuesday, November 6, 2012 7:54 PM
  • All of a sudden it became very quiet, no one wants to help me out with this one?

    Well, in the meantime I have been trying a great number of variations to get this update working, but it looks like there are no straight answer to that one. Fx. I have two different forms with nearly the same code - one is updating and the other is not:

    The one which is updating:

    strSql = "SELECT DateRegister,FirstName,LastName,RegistrationID,RegistrationKey,HardDiskSerial From MyRec WHERE CLng(RecID) ="
                    strSql = strSql & Chr(34) & "1" & Chr(34)
                    Dim dsMyRec As New DataSet()
                    Dim da As New OleDbDataAdapter(strSql, m_ConContacts)
                    da.Fill(dsMyRec, "MyRecord")
    
                    Dim hdString As String = identifier("Win32_DiskDrive", "Signature")
    
                    Dim dr As DataRow
                    dr = dsMyRec.Tables("MyRecord").Rows(0)
                    dr("DateRegister") = CDate(Now)
                    dr("RegistrationID") = txtSoftwareCode.Text
                    dr("RegistrationKey") = Trim(txtLiberationKey.Text)
                    dr("HardDiskSerial") = hdString
    
                    Dim objCommandBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(da)
                    da.Update(dsMyRec, "MyRecord")

    This other one is NOT updating:

    strSql = "SELECT FirstName,LastName,E-MailAdress,SMTPserver,Country,LanguageScreen,LanguagePrint,DateInstalled From MyRec WHERE CLng(RecID) ="
                    strSql = strSql & Chr(34) & "1" & Chr(34)
                    Dim dsMyRec As New DataSet()
                    Dim da As New OleDbDataAdapter(strSql, m_ConContacts)
                    da.Fill(dsMyRec, "MyRecord")
    
                    Dim dr As DataRow
                    dr = dsMyRec.Tables("MyRecord").Rows(0)
                    dr("FirstName") = Trim(txtFirstName.Text)
                    dr("LastName") = Trim(txtLastName.Text)
                    dr("E-MailAdress") = Trim(txtMail.Text)
                    dr("SMTPserver") = Trim(txtServer.Text)
                    dr("Country") = cboCountry.Text
                    dr("LanguageScreen") = cboLanguage.Text
                    dr("LanguagePrint") = cboLanguage.Text
                    dr("DateInstalled") = CType(Now, Date)
    
                    Dim objCommandBuilder As New OleDbCommandBuilder(da)
                    da.Update(dsMyRec, "MyRecord")

    Quite interesting - and quite frustrating.

    The error stated in the last code block:

    No value is given for one or more necessary parameters.  (red.: translated from Norwegian)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
       at WindowsApplication1.frmFirstScreen.cmdOK_Click(Object sender, EventArgs e) i C:\VB_Net Programmer\MyContacts\MyContacts_ADO.NET\MyContacts\frmFirstScreen.vb:linje 134 - cmdOK_Click

    Kind regards
    Jorgen


    levesen




    • Edited by levesen Friday, November 9, 2012 9:18 PM
    Friday, November 9, 2012 9:12 PM
  • Sorry Jorgen, I've been super-busy. I'm in the middle of doing stuff right now, but I'll try to take a closer look at this later today.

    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Friday, November 9, 2012 9:17 PM
  • Hi Jorgen,

    I can't actually try your code (that you uploaded to SkyDrive) because I don't have VB installed with Visual Studio. In looking at your code and the error you show, this looks different than the error in your original post. Although it's the same error message (something about data missing for necessary parameters), I think it's throwing the exception from a different command. In looking at the StackTrace, this appears to be associated with the da.Fill() method, not the da.Update() method. 

    Still don't know why, but are you certain it's blowing up on the .Update and not the .Fill?


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Saturday, November 10, 2012 4:55 PM
  • Hi again Bonnie,

    Please do not let me interfere with your work, but I am happy for all the help I am getting.
    You are quite right about the above mentioned code not beeing part of the original post, and the error is in the fill method. I could not wait more, and have moved forward starting converting the actual programmes in question (the original post was a test program for converting from ADODB to ADO.Net). After two months of on/off work with this conversion, I wonder why on earth Microsoft did not incorporated the ADODB in the new net framework - that was a simple an easy operation, Edit-Update and the database was updated.
    Now I am not quite sure of what is going on where, in one case the code updates and in another similar case it is not. Not to mention the various informations on the web and in books (Evangelos Petroutsos: Mastering Visual Basic 2010) giving different solutions to something which should be quite simple.
    Status is the same as some months before: Sometimes the insert new rows to the database works, somtimes not, sometimes the update database works, somtimes not.
    I do miss a working example of how to go about inserting new rows and plain updating - using VB2010, ado.net  and Access database.

    With kind regards
    Jorgen


    levesen

    Sunday, November 11, 2012 1:01 PM
  • Hi Jorgen,

    Sorry you're having so many problems with your DataAccess stuff. May I suggest reading some of my blog posts about DataAccess, DataSets and Databinding ...

    First, check out my 3-part series on Data Access for some basic ideas. I'm using a SQL database, but the same would apply to other databases (except you'd use OleDb classes instead of Sql classes):

    http://geek-goddess-bonnie.blogspot.com/2009/09/dataaccess-part-i.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-ii.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-iii.html

    Each post adds extra complexity to the Data Access classes, but more flexiblity. The first post is enough to get you going in the right direction and give you a general idea of the concept, but the second post is more useful. The third post gets into using anonymous delegates and may be too much for a beginner.

    Secondly, there's a post I have about creating Typed DataSets without TableAdapters being generated.I know that DataSets have gotten a "bum rap" lately since lots of people prefer using Entity Framework, LINQ to SQL and similar types of ORMs. But I find Typed DataSets to be quite simple to use (as long as you avoid the TableAdapters):

    http://geek-goddess-bonnie.blogspot.com/2010/04/create-xsd.html

    And lastly, a very brief post about Databinding:

    http://geek-goddess-bonnie.blogspot.com/2009/09/keeping-datagrids-and-other-ui-controls.html

    I hope these help and give you some ideas. Some of what I just wrote above might not really apply to you (it is a pre-written blurb I use to copy/paste into forum replies to help others with similar issues). But, it doesn't hurt to do some more reading, right? ;0)


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Sunday, November 11, 2012 4:35 PM
  • Thanks Bonnie, more to read - but it looks very interesting. I will be back when I have absorbed the gist of blogspot's :-).

    Kind regards
    Jorgen


    levesen

    Sunday, November 11, 2012 7:44 PM
  • It is getting more and more interesting this update business. I followed your advise (well, some of it) in your blogspot and wrote the following codes:

    Dim myCommand As OleDbCommand = New OleDb.OleDbCommand("Select * from MyRec")
                    myCommand.Connection = m_ConContacts
                    myCommand.CommandText = "UPDATE MyRec SET FirstName = '" & txtFirstName.Text & _
                    "', LastName = '" & txtLastName.Text & _
                    "', E-MailAdress = '" & txtMail.Text & _
                    "', SMTPserver = '" & txtServer.Text & _
                    "', Country = '" & cboCountry.Text & _
                    "', LanguageScreen = '" & cboLanguage.Text & _
                    "', LanguagePrint = '" & cboLanguage.Text & _
                    "', DateInstalled = '" & CDate(Now) & _
                    "' WHERE RecID = '" & 1 & "'"
                    Debug.WriteLine(myCommand.CommandText)
                    myCommand.ExecuteNonQuery()

    The Debug.WriteLine shows as follows:

    UPDATE MyRec SET FirstName = 'Jorgen', LastName = 'Lev', E-MailAdress = 'mail@lev.com', SMTPserver = 'mail.xxx.com', Country = 'Norway', LanguageScreen = 'English', LanguagePrint = 'English', DateInstalled = '12.11.2012 14:25:20' WHERE RecID = '1'

    The code updates nothing, but tells me I got a syntax error in the update statement:

    A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
    Syntax error in the UPDATE-statement	(red.: translated from Norwegian)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
       at WindowsApplication1.frmFirstScreen.cmdOK_Click(Object sender, EventArgs e) i C:\VB_Net Programmer\MyContacts\MyContacts_ADO.NET\MyContacts\frmFirstScreen.vb:linje 142 - cmdOK_Click

    I would sincerely like to know what I have done wrong (now again).
    Kind regards
    Jorgen


    levesen



    • Edited by levesen Monday, November 12, 2012 2:02 PM
    Monday, November 12, 2012 2:00 PM
  • It's probably got something do with the date field. You should really use parameters and not create the string like that. That's bad practice and, as you can see, error-prone. Here's what you should have (note that I use ? not the @parm syntax in the actual command string, since that's necessary with Access, although I'm not sure what would happen if you used the actual parameter names, since I've never used Access).

    Dim myCommand As OleDbCommand = New OleDb.OleDbCommand("Select * from MyRec") myCommand.Connection = m_ConContacts myCommand.CommandText = "UPDATE MyRec SET FirstName = ?" & _ ", LastName = ?" & _ ", E-MailAdress = ?" & _ ", SMTPserver = ?" & _ ", Country = ?" & _ ", LanguageScreen = ?" & _ ", LanguagePrint = ?" & _ ", DateInstalled = ?" & CDate(Now) & _ " WHERE RecID = ?" ' With parameters in Access, the order you add them must correspond to the order they're used in the command ' The parameter names below are not used in the above command, just makes it easier to read myCommand.Parameters.AddWithValue("@FirstName", txtFirstName.Text); myCommand.Parameters.AddWithValue("@LastName", txtLastName.Text); myCommand.Parameters.AddWithValue("@E-MailAdress", txtMail.Text); myCommand.Parameters.AddWithValue("@SMTPserver", txtServer.Text); myCommand.Parameters.AddWithValue("@Country", cboCountry.Text); myCommand.Parameters.AddWithValue("@LanguageScreen", cboLanguage.Text); myCommand.Parameters.AddWithValue("@LanguagePrint", cboLanguage.Text); myCommand.Parameters.AddWithValue("@DateInstalled", CDate(Now)); myCommand.Parameters.AddWithValue("@RecID", 1); 'Debug.WriteLine(myCommand.CommandText) myCommand.ExecuteNonQuery()



    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Monday, November 12, 2012 3:16 PM
  • Thanks Bonnie,
    I adjusted the code according to your above suggestion. I did edit the line  (", DateInstalled = ?" & CDate(Now) & _) and removed the "& CDate(Now)" as I thought it was a 'misspelling' - but still it gives the samme error as before: "Syntax error in the UPDATE-statement."

    The command text:

    UPDATE MyRec SET FirstName = ?, LastName = ?, E-MailAdress = ?, SMTPserver = ?, Country = ?, LanguageScreen = ?, LanguagePrint = ?, DateInstalled = ? WHERE RecID = ?
    The revised code:
    Dim myCommand As OleDbCommand = New OleDb.OleDbCommand("Select * from MyRec")
                    myCommand.Connection = m_ConContacts
                    myCommand.CommandText = "UPDATE MyRec SET FirstName = ?" & _
                    ", LastName = ?" & _
                    ", E-MailAdress = ?" & _
                    ", SMTPserver = ?" & _
                    ", Country = ?" & _
                    ", LanguageScreen = ?" & _
                    ", LanguagePrint = ?" & _
                    ", DateInstalled = ?" & _
                    " WHERE RecID = ?"
                    
                    myCommand.Parameters.AddWithValue("@FirstName", txtFirstName.Text)
                    myCommand.Parameters.AddWithValue("@LastName", txtLastName.Text)
                    myCommand.Parameters.AddWithValue("@E-MailAdress", txtMail.Text)
                    myCommand.Parameters.AddWithValue("@SMTPserver", txtServer.Text)
                    myCommand.Parameters.AddWithValue("@Country", cboCountry.Text)
                    myCommand.Parameters.AddWithValue("@LanguageScreen", cboLanguage.Text)
                    myCommand.Parameters.AddWithValue("@LanguagePrint", cboLanguage.Text)
                    myCommand.Parameters.AddWithValue("@DateInstalled", CDate(Now))
                    myCommand.Parameters.AddWithValue("@RecID", 1)
                    Debug.WriteLine(myCommand.CommandText)
                    myCommand.ExecuteNonQuery()
    Kind regards
    Jorgen


    levesen

    Monday, November 12, 2012 4:48 PM
  • Wow Jorgen, I'm stumped too. Your UPDATE command looks fine, syntax-wise (unless Access SQL syntax is different than SQL Server syntax, but I doubt it for this simple command).

    You've already tried it without the Date parameter and still got the error, but perhaps there's something screwy with another one of your parameters. The only thing I can suggest is to try it with only one parameter (like maybe the FirstName) and see if that works. If so, adjust the UPDATE string and add parameters one-by-one and see what happens.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Monday, November 12, 2012 5:19 PM
  • Hei again,
    just found out the problem, there were actually two of the parameters which made all the headache. The "E-MailAdress" had to be enclosed in brackets and the "CDate(Now)" had to be a string format. The working code looks like this:

    Dim myCommand As OleDbCommand = New OleDb.OleDbCommand("Select * from MyRec")
                    myCommand.Connection = m_ConContacts
                    myCommand.CommandText = "UPDATE MyRec SET FirstName = ?" & _
                    ", LastName = ?" & _
                    ", [E-MailAdress] = ?" & _
                    ", SMTPserver = ?" & _
                    ", Country = ?" & _
                    ", LanguageScreen = ?" & _
                    ", LanguagePrint = ?" & _
                    ", DateInstalled = ?" & _
                    " WHERE RecID = ?"
                    myCommand.Parameters.AddWithValue("@FirstName", txtFirstName.Text)
                    myCommand.Parameters.AddWithValue("@LastName", txtLastName.Text)
                    myCommand.Parameters.AddWithValue("@[E-MailAdress]", txtMail.Text)
                    myCommand.Parameters.AddWithValue("@SMTPserver", txtServer.Text)
                    myCommand.Parameters.AddWithValue("@Country", cboCountry.Text)
                    myCommand.Parameters.AddWithValue("@LanguageScreen", cboLanguage.Text)
                    myCommand.Parameters.AddWithValue("@LanguagePrint", cboLanguage.Text)
                    myCommand.Parameters.AddWithValue("@DateInstalled", CStr(CDate(Now)))
                    myCommand.Parameters.AddWithValue("@RecID", 1)
                    myCommand.ExecuteNonQuery()

    I think we can safely close this thread now - and I sincerely thank you very much for your work, and patience, guiding me back on the right path.
    With kind regards
    Jorgen

     

    levesen

    • Marked as answer by levesen Monday, November 12, 2012 10:46 PM
    Monday, November 12, 2012 7:48 PM
  • I wondered about your DateInstalled column, if it was of type char (or varchar) or of datetime in your database. But since you had other problems besides that column, you had to have some way of tracking them all down.

    Anyway, glad you got it all fixed!


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Tuesday, November 13, 2012 1:55 AM