-
Aug 8th, 2012, 08:58 AM
#1
Thread Starter
Member
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()
-
Aug 8th, 2012, 09:26 AM
#2
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 -
-
Aug 8th, 2012, 09:31 AM
#3
Thread Starter
Member
Re: Issues updating access database from datagrid
thank you, i will give it a try now
-
Aug 8th, 2012, 09:40 AM
#4
Thread Starter
Member
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
-
Aug 8th, 2012, 10:06 AM
#5
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 -
-
Aug 8th, 2012, 10:25 AM
#6
Thread Starter
Member
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!!!
-
Aug 8th, 2012, 10:37 AM
#7
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 -
-
Aug 8th, 2012, 10:39 AM
#8
Thread Starter
Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|