VS 2010 Issues updating access database from datagrid-VBForums
Results 1 to 8 of 8

Thread: Issues updating access database from datagrid

  1. #1

    Thread Starter
    Member jbailey01's Avatar
    Join Date
    Jul 2012
    Posts
    45

    Question Issues updating access database from datagrid

    Hello, I am just trying to pull information from my datagrid and save it as a new record in access but having a hard time. Any recommendations would be appreciated.

    Code:
    Try
                For i = 0 To WelcomeForm.CallerDataGridView.RowCount - 1
                    If CDbl(WelcomeForm.CallerDataGridView.Rows(i).Cells("Id").Value.ToString) = _ID Then
                        Using dnn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\CallerLists\" + CallerTextBox.Text + ".mdb")
                            Using dmd As New OleDbCommand("INSERT INTO Completed (Call Type, Dealer Number, District, Supervision Group, Service or Tire, Store Number, Associate, City, State, Phone, Vehicle, Tire Size, Notes ) VALUES (@Call Type, @Dealer Number, @District, @Supervision Group, @Service or Tire, @Store Number, @Associate, @City, @State, @Phone, @Vehicle, @Tire Size, @Notes)", dnn)
                                dmd.Parameters.AddWithValue("@Call Type", WelcomeForm.CallerDataGridView.Rows(i).Cells("Call Type").Value.ToString)
                                dmd.Parameters.AddWithValue("@Dealer Number", WelcomeForm.CallerDataGridView.Rows(i).Cells("Dealer Number").Value.ToString)
                                dmd.Parameters.AddWithValue("@District", WelcomeForm.CallerDataGridView.Rows(i).Cells("District").Value.ToString)
                                dmd.Parameters.AddWithValue("@Supervision Group", WelcomeForm.CallerDataGridView.Rows(i).Cells("Supervision Group").Value.ToString)
                                dmd.Parameters.AddWithValue("@Service or Tire", WelcomeForm.CallerDataGridView.Rows(i).Cells("Service or Tire").Value.ToString)
                                dmd.Parameters.AddWithValue("@Store Number", WelcomeForm.CallerDataGridView.Rows(i).Cells("Store Number").Value.ToString)
                                dmd.Parameters.AddWithValue("@Call #", WelcomeForm.CallerDataGridView.Rows(i).Cells("Call #").Value.ToString)
                                dmd.Parameters.AddWithValue("@Retailer Type", WelcomeForm.CallerDataGridView.Rows(i).Cells("Retailer Type").Value.ToString)
                                dmd.Parameters.AddWithValue("@Store Name", WelcomeForm.CallerDataGridView.Rows(i).Cells("Store Name").Value.ToString)
                                dmd.Parameters.AddWithValue("@Associate", WelcomeForm.CallerDataGridView.Rows(i).Cells("Associate").Value.ToString)
                                dmd.Parameters.AddWithValue("@City", WelcomeForm.CallerDataGridView.Rows(i).Cells("State").Value.ToString)
                                dmd.Parameters.AddWithValue("@State", WelcomeForm.CallerDataGridView.Rows(i).Cells("State").Value.ToString)
                                dmd.Parameters.AddWithValue("@Phone", WelcomeForm.CallerDataGridView.Rows(i).Cells("Phone").Value.ToString)
                                dmd.Parameters.AddWithValue("@Vehicle", WelcomeForm.CallerDataGridView.Rows(i).Cells("Vehicle").Value.ToString)
                                dmd.Parameters.AddWithValue("@Tire Size", WelcomeForm.CallerDataGridView.Rows(i).Cells("Tire Size").Value.ToString)
                                dmd.Parameters.AddWithValue("@Notes", WelcomeForm.CallerDataGridView.Rows(i).Cells("Notes").Value.ToString)
                                dnn.Open()
    
                                dmd.ExecuteNonQuery()
                            End Using
                        End Using
                    End If
                Next
            Catch ex As Exception
                MsgBox(ex.ToString)
                Exit Sub
            End Try
    Error I recieve is
    System.Data.OleDb.OleDbException (0x80040E14): Syntax error in INSERT INTO statement.
    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 Mystery_Shopper_Pro.TireProsDealerForm.CompleteButton_Click(Object sender, EventArgs e) in C:\Users\Jbailey\documents\visual studio 2010\Projects\Mystery Shopper Pro\Mystery Shopper Pro\SurveyForms\TP\TirePros\TireProsDealerForm.vb:line 428

    With this line being 428...
    dmd.ExecuteNonQuery()

  2. #2
    PowerPoster stanav's Avatar
    Join Date
    Jul 2006
    Location
    Providence, RI - USA
    Posts
    9,292

    Re: Issues updating access database from datagrid

    Enclose all field names with blank spaces in square brackets and remove blank spaces from the parameters. For example:
    Code:
    Using dmd As New OleDbCommand("INSERT INTO Completed ([Call Type], .... ) VALUES (@CallType, ...)
        dmd.Parameters.AddWithValue("@CallType", WelcomeForm.CallerDataGridView.Rows(i).Cells("Call Type").Value.ToString)
    Let us have faith that right makes might, and in that faith, let us, to the end, dare to do our duty as we understand it.
    - Abraham Lincoln -

  3. #3

    Thread Starter
    Member jbailey01's Avatar
    Join Date
    Jul 2012
    Posts
    45

    Re: Issues updating access database from datagrid

    thank you, i will give it a try now

  4. #4

    Thread Starter
    Member jbailey01's Avatar
    Join Date
    Jul 2012
    Posts
    45

    Re: Issues updating access database from datagrid

    Still getting the same error...
    Code:
     Try
                For i = 0 To WelcomeForm.CallerDataGridView.RowCount - 1
                    If CDbl(WelcomeForm.CallerDataGridView.Rows(i).Cells("Id").Value.ToString) = _ID Then
                        Using dnn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\CallerLists\" + CallerTextBox.Text + ".mdb")
                            Using dmd As New OleDbCommand("INSERT INTO Completed ([Call Type], [Dealer Number], District, [Supervision Group], [Service or Tire], [Store Number], [Call #], [RetailerType], Associate, City, State, Phone, Vehicle, [Tire Size], Notes ) VALUES (@CallType, @DealerNumber, @District, @SupervisionGroup, @ServiceOrTire, @StoreNumber, @Call#, @RetailerType, @Associate, @City, @State, @Phone, @Vehicle, @TireSize, @Notes)", dnn)
                                dmd.Parameters.AddWithValue("@CallType", WelcomeForm.CallerDataGridView.Rows(i).Cells("Call Type").Value.ToString)
                                dmd.Parameters.AddWithValue("@DealerNumber", WelcomeForm.CallerDataGridView.Rows(i).Cells("Dealer Number").Value.ToString)
                                dmd.Parameters.AddWithValue("@District", WelcomeForm.CallerDataGridView.Rows(i).Cells("District").Value.ToString)
                                dmd.Parameters.AddWithValue("@SupervisionGroup", WelcomeForm.CallerDataGridView.Rows(i).Cells("Supervision Group").Value.ToString)
                                dmd.Parameters.AddWithValue("@ServiceOrTire", WelcomeForm.CallerDataGridView.Rows(i).Cells("Service or Tire").Value.ToString)
                                dmd.Parameters.AddWithValue("@StoreNumber", WelcomeForm.CallerDataGridView.Rows(i).Cells("Store Number").Value.ToString)
                                dmd.Parameters.AddWithValue("@Call#", WelcomeForm.CallerDataGridView.Rows(i).Cells("Call #").Value.ToString)
                                dmd.Parameters.AddWithValue("@RetailerType", WelcomeForm.CallerDataGridView.Rows(i).Cells("Retailer Type").Value.ToString)
                                dmd.Parameters.AddWithValue("@StoreName", WelcomeForm.CallerDataGridView.Rows(i).Cells("Store Name").Value.ToString)
                                dmd.Parameters.AddWithValue("@Associate", WelcomeForm.CallerDataGridView.Rows(i).Cells("Associate").Value.ToString)
                                dmd.Parameters.AddWithValue("@City", WelcomeForm.CallerDataGridView.Rows(i).Cells("State").Value.ToString)
                                dmd.Parameters.AddWithValue("@State", WelcomeForm.CallerDataGridView.Rows(i).Cells("State").Value.ToString)
                                dmd.Parameters.AddWithValue("@Phone", WelcomeForm.CallerDataGridView.Rows(i).Cells("Phone").Value.ToString)
                                dmd.Parameters.AddWithValue("@Vehicle", WelcomeForm.CallerDataGridView.Rows(i).Cells("Vehicle").Value.ToString)
                                dmd.Parameters.AddWithValue("@TireSize", WelcomeForm.CallerDataGridView.Rows(i).Cells("Tire Size").Value.ToString)
                                dmd.Parameters.AddWithValue("@Notes", WelcomeForm.CallerDataGridView.Rows(i).Cells("Notes").Value.ToString)
                                dnn.Open()
    
                                dmd.ExecuteNonQuery()
                            End Using
                        End Using
                    End If
                Next
            Catch ex As Exception
                MsgBox(ex.ToString)
                Exit Sub
            End Try

  5. #5
    PowerPoster stanav's Avatar
    Join Date
    Jul 2006
    Location
    Providence, RI - USA
    Posts
    9,292

    Re: Issues updating access database from datagrid

    Are you sure it's the same syntax error as before? As far as I see, the query looks fine - shouldn't have any syntax errors. You might get data type mismatched error though since you add values of string type for all the parameters.
    Let us have faith that right makes might, and in that faith, let us, to the end, dare to do our duty as we understand it.
    - Abraham Lincoln -

  6. #6

    Thread Starter
    Member jbailey01's Avatar
    Join Date
    Jul 2012
    Posts
    45

    Re: Issues updating access database from datagrid

    You were correct. Once I removed all the spaces from both database and code it ran without issue. Thank you stanav!!!

  7. #7
    PowerPoster stanav's Avatar
    Join Date
    Jul 2006
    Location
    Providence, RI - USA
    Posts
    9,292

    Re: Issues updating access database from datagrid

    Noticed that you use the "State" value for both city and state parameters... Something overlooked from copy&paste?
    Code:
    dmd.Parameters.AddWithValue("@City", WelcomeForm.CallerDataGridView.Rows(i).Cells("State").Value.ToString)
    dmd.Parameters.AddWithValue("@State", WelcomeForm.CallerDataGridView.Rows(i).Cells("State").Value.ToString)
    Let us have faith that right makes might, and in that faith, let us, to the end, dare to do our duty as we understand it.
    - Abraham Lincoln -

  8. #8

    Thread Starter
    Member jbailey01's Avatar
    Join Date
    Jul 2012
    Posts
    45

    Re: Issues updating access database from datagrid

    Wow. Your good. I didn't even notice that. Much appreciated!!!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.