none
DataGridView Update

    Question

  • Hello,

    I am using VB2008 and MSAccess.

    following this thread

    http://social.msdn.microsoft.com/forums/en-US/vbgeneral/thread/0d5e3307-40e8-422c-a83a-697ca12b5209

    i've modified it for my use, having the following codes,

       Dim ds As DataSet = New DataSet

        Dim adapter As OleDb.OleDbDataAdapter

        Dim dt As OleDb.OleDbDataAdapter

        Dim builder As OleDb.OleDbCommandBuilder

    Private Sub ViewAll()

    conn.Open()

     

            sql_string = "select * from mlist order by slno asc"

     

            dt = New OleDb.OleDbDataAdapter(sql_string, conn)

            builder = New OleDbCommandBuilder(dt)

            ds = New DataSet()

            dt.Fill(ds, "mlist")

            DGVMaster.DataSource = ds.Tables("mlist").DefaultView

            With DGVMaster

                .Columns(0).Width = 80

                .Columns(0).HeaderText = "No."

                .Columns(1).Width = 200

                .Columns(1).HeaderText = "Candidates Name"

                .Columns(2).Width = 100

                .Columns(2).HeaderText = "Position"

                .Columns(25).DefaultCellStyle.Format = "HH:mm"

            End With

            Me.DGVMaster.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure

            Me.DGVMaster.SelectionMode = DataGridViewSelectionMode.FullRowSelect

            Me.BnMaster.BindingSource = bs

            conn.Close()

        End Sub

        Private Sub EmployeeBNSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EmployeeBNSaveItem.Click

     

            Me.Validate()

            Me.dt.Update(Me.ds.Tables("Mlist"))

            Me.ds.AcceptChanges()

            ViewAll()

        End Sub

     

     the if-then-else is working fine and able to load my data on the DGV. now my problem is with Update.

    i am receiving this error

    Syntax error (missing operator) in query expression '((SlNo = ?) AND ((? = 1 AND C_Name IS NULL) OR (C_Name = ?)) AND ((? = 1 AND Post IS NULL) OR (Post = ?)) AND ((? = 1 AND Salary IS NULL) OR (Salary = ?)) AND ((? = 1 AND CoAcc IS NULL) OR (CoAcc = ?)) AND ((? = 1 AND Housing IS NULL) OR (Housing = ?)) AN'.

    on my

            Me.dt.Update(Me.ds.Tables("Mlist"))

    Can somebody help me point the right direction as i can't seem to find the right one.

     thanks again.

     

    • Moved by Vicky SongMicrosoft employee Wednesday, September 29, 2010 2:09 AM (From:Visual Studio Database Development Tools (Formerly "Database Edition Forum"))
    Tuesday, September 28, 2010 10:29 AM

Answers

  • There are two chances, you have either not used a primary key in your database or you have used an access keyword.

    I'm not sure if for instance "post" is a keyword in an ms Access (Jet) database, but I thought we don't see the whole SQL transactcode string.

     


    Success
    Cor
    • Marked as answer by medel Sunday, October 10, 2010 10:43 AM
    Wednesday, September 29, 2010 5:45 AM

All replies

  • Hello moedel,

    I am moving this thread from “Visual Studio Database Development Tools” to “Visual Basic General” forum, since the issue is related to Visual Basic. There are more Visual Basic experts in the “Visual Basic General” forum and you will get better response.

    Thanks for your understanding.

    Regards,

    Vicky Song

    Wednesday, September 29, 2010 2:08 AM
  • with my code posted above, i found out recently that my BindingNavigator is not navigating on my DGV but it was able to display the whole number of data. I have changed my DataSource from: DGVMaster.DataSource = bs bs.DataSource = ds bs.DataMember = ds.Tables("mlist").TableName Me.BnMaster.BindingSource = bs to: DGVMaster.DataSource = ds.Tables("mlist").DefaultView To accommodate the CommandBuilder code. now my additional question is what will i do to fix my code for my DGV.datasource so that it will work for both the commandbuilder and binding navigator. many thanks
    • Edited by medel Wednesday, September 29, 2010 5:47 AM
    Wednesday, September 29, 2010 5:38 AM
  • There are two chances, you have either not used a primary key in your database or you have used an access keyword.

    I'm not sure if for instance "post" is a keyword in an ms Access (Jet) database, but I thought we don't see the whole SQL transactcode string.

     


    Success
    Cor
    • Marked as answer by medel Sunday, October 10, 2010 10:43 AM
    Wednesday, September 29, 2010 5:45 AM
  • thank you for your reply, i have primary key on my Mlist Table and i have replaced the Post fieldname into WOrk, and now i got the error No Value Given for one or more required parametes. but my Sql_String selects all, and i didn't specify any field name. why did i get that error message?
    Wednesday, September 29, 2010 6:15 AM
  • How many fields do you have the max is 99

     


    Success
    Cor
    Wednesday, September 29, 2010 6:23 AM
  • i only have 29 fields, i'm curious because if i fill my DGV by adding DataSource from the designview, it's working fine, but i don't want to use that because if i change something on my table, i have to redo the dataset, and i think i will learn more if i hard code my project.
    Wednesday, September 29, 2010 6:31 AM
  • i have fixed it, i manually typed my field inside my Sql_String statement, 1. now my only problem is the binding source of my binding navigator, can you help me fix it, i still have my code which i posted previously. 2. also i found out that my insert command is not working as i got "systax error in INSERT INTO statement"
    • Edited by medel Wednesday, September 29, 2010 7:49 AM revised the question
    Wednesday, September 29, 2010 6:54 AM
  • i have fixed it, i manually typed my field inside my Sql_String statement, 1. now my only problem is the binding source of my binding navigator, can you help me fix it, i still have my code which i posted previously. 2. also i found out that my insert command is not working as i got "systax error in INSERT INTO statement"
    my question number 1 about bindingnavigator bindingsource has been fixed, only the 2nd question abount the Insert Statement is still not working. any help please?
    Wednesday, September 29, 2010 8:34 AM
  • Hello medel,

    Thanks for your post.

    What is your insert statement? There could be a number of reasons that you are getting this error.
    Generally, this problem may occur if your database table contains column names that use Microsoft Jet 4.0 reserved words. Change the column names in your database table so that you do not use Jet 4.0 reserved words.

    More information about this error, you could check below links. Hope they could make you get some ideas.

    http://oscarvalles.wordpress.com/2010/07/10/ms-access-syntax-error-in-insert-into-statement/
    (MS Access – Syntax error in INSERT INTO statement)
    http://support.microsoft.com/kb/892608
    (You may receive a "Syntax error in INSERT INTO statement" error message when you use ADO.NET code to access Office Access 2003)

    If you have any concerns, please feel free to follow up.

    Best regards,
    Liliane
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please mark the replies as answers if they help and unmark them if they provide no help. Welcome to the All-In-One Code Framework! If you have any feedback, please tell us. Thanks
    Tuesday, October 05, 2010 5:15 AM
  • Medel,

    Will you be so kind to create for every problem a new question. 

    Your question was.

    Can somebody help me point the right direction as i can't seem to find the right one.

    When I see now these later question I get the idea we succeeded in that.

    So create now more specific new questions in a way that more persons are able to help you.

    The question you have now stated you have created somewhere between the lines as a reply on an answer on your original question.

    Thanks

     



    Cor
    Tuesday, October 05, 2010 5:34 AM
  • Hello Liliane,

    thank you for your reply, i have changed my column name to avoid Jet 4.0 reserved words. i have an If-Then-Else statement that generates the sql_string and its able to display me my data.

    my problem is regarding the usage of CommandBuilder, i have tried the suggestions from MSDN and search thru google and my Update is working (Cor's suggestion regarding reserved words works), but after the Update were fixed, now i receive an error on the Insert as i cannot add new data.

     

    Sunday, October 10, 2010 8:50 AM
  • Was my advice not to create a new question about your update not to change the question where my replies currently don't fit anymore.

    In these way helping you in these forums becomes without sense.

     


    Success
    Cor
    Sunday, October 10, 2010 9:09 AM
  • Was my advice not to create a new question about your update not to change the question where my replies currently don't fit anymore.

    In these way helping you in these forums becomes without sense.

     


    Success
    Cor


    Cor,

    i'm sorry if my question is out of the line. anyhow, thanks again as it is your suggestion that helps me answer my problem.

     

    Sunday, October 10, 2010 10:44 AM