Hello,
I have been working with this problem now for several days. I can delete and Update my data but I can not get it to insert a new record. I receive the syntax error insert into. I am not using any reserved words and I have not been able to find my answer googling like crazy. Any help would be greatly appreciated. below is the code that seems to give me the problem. -
-
ElseIf NEWMODE = True Then
-
-
Dim oAdapter As OleDb.OleDbDataAdapter
-
Dim cb As OleDb.OleDbCommandBuilder
-
Dim dr As DataRow
-
Dim ds As DataSet
-
Dim strSQL As String = "SELECT * FROM [Private Criminal Complaints 2]"
-
-
ds = New DataSet()
-
oAdapter = New OleDb.OleDbDataAdapter(strSQL, Con)
-
oAdapter.Fill(ds) 'Execute the Query and grab results
-
-
Try
-
dr = ds.Tables(0).NewRow()
-
-
dr.BeginEdit()
-
-
dr.Item("CompLastName") = txtCLname.Text
-
dr.Item("CompFirstName") = txtCFname.Text
-
dr.Item("DefLastName") = txtDLname.Text
-
dr.Item("DefFirstName") = txtDFname.Text
-
dr.Item("NameADA") = txtADA.Text
-
dr.Item("Detective") = txtDet.Text
-
dr.Item("Disposition") = txtDisposition.Text
-
dr.Item("ReasonFiled") = txtreason.Text
-
dr.Item("DateFiled") = txtDate.Text
-
-
-
dr.EndEdit()
-
-
ds.Tables(0).Rows.Add(dr)
-
cb = New OleDb.OleDbCommandBuilder(oAdapter)
-
oAdapter.InsertCommand = cb.GetInsertCommand()
-
oAdapter.Update(ds)
-
ds.AcceptChanges()
-
-
MessageBox.Show("Insert Successful")
-
NEWMODE = False 'Add success, end NEWMODE
-
ClearTextBoxes() 'Add Success, clean up textboxes
-
Catch ex As Exception
-
MessageBox.Show(ex.Message)
-
-
Finally
-
Con.Close()
-
End Try
-
-
Else 'Its not Editmode or Newmode
-
'Do nothing
-
End If
-
-
RefreshLV() 'Regardless of what happens, might as well refresh the data in the Listview
-
End Sub
-
Thank you everyone for your help I have figured out the problem.
I needed to add the following code -
ds.Tables(0).Rows.Add(dr)
-
cb = New OleDb.OleDbCommandBuilder(oAdapter)
-
-
cb.QuotePrefix = "["
-
cb.QuoteSuffix = "]"
-
-
oAdapter.InsertCommand = cb.GetInsertCommand()
-
-
oAdapter.Update(ds)
-
ds.AcceptChanges()
-
Hopefully this will help people in the future. thank you again for all of your responses.
9 2748 NeoPa 32,556
Expert Mod 16PB
Your title implies this is related to the SQL INSERT INTO command, but I see no sign of that. My guess is you're not hooking the right experts.
Thanks for the reply. I am using the Oledbcommandbuilder. I think I am just incredibly lost at this point. I am very new to all of this and I have been following several different guides to accomplish my task. I am so close to finishing as it does everything but insert a new record. Do you have any suggestions on a better way to insert a new record that would get rid of this syntax error?
This looks more like vb or vb.net not vba. Is this an Access problem or have you posted in the wrong forum by mistake.
cheers,
NeoPa 32,556
Expert Mod 16PB Ed Pisa:
Do you have any suggestions on a better way to insert a new record that would get rid of this syntax error?
That depends on what you're really after. generally, in Access, you would simply use a form to manage updating of your data for you. There are various wizards, but the simplest handles record management including all of the above. If you're new to Access it's perfectly possible you're trying to code your way to what Access already does for you. You wouldn't be the first to follow that path.
If I am not mistaken, this is ADO.NET, which should easily be converted to standard ADO. If you can obtain a Reference to the ActiveX Data Objects X.X Object Library, and you feel as though this functionality can be incorporated into your existing environment, then we can try the conversion and see what happens. The other option would be to Post this in the .NET Forum and see what, if any, response you obtain there. P.S. - I just took a 15 minute Crash Course in ADO.NET. Try this revised Syntax, it comes with no guarantee whatsoever, since I have no way of testing it. -
'Code intentionally omitted...
-
oAdapter.Fill (ds)
-
-
cb = New OleDb.OleDbCommandBuilder(oAdapter)
-
-
dr = ds.Tables(0).NewRow()
-
-
dr("CompLastName") = txtCLname.Text
-
dr("CompFirstName") = txtCFname.Text
-
dr("DefLastName") = txtDLname.Text
-
dr("DefFirstName") = txtDFname.Text
-
dr("NameADA") = txtADA.Text
-
dr("Detective") = txtDet.Text
-
dr("Disposition") = txtDisposition.Text
-
dr("ReasonFiled") = txtreason.Text
-
dr("DateFiled") = txtDate.Text
-
-
ds.Tables(0).Rows.Add (dr)
-
-
oAdapter.Update (ds.GetChanges())
-
-
ds.AcceptChanges()
-
-
MessageBox.Show ("Insert Successful")
-
'Code intentionally omitted...
Thanks for the advice I will have to try it first thing tomorrow since it is on my desktop at work.
I have a form in access but the problem is the staff that uses it is not the most technically inclined. Through their clicking it was managed to delete an entire column and lost alot of information. I know that you can sent permissions and what not but I thought by creating an easy to use interface through visual studio 2010 it would help the end user. Thanks for your reply I am just going to have to try try again.
NeoPa 32,556
Expert Mod 16PB
The question here Ed, is how comfortable are you (relatively) working in .NET and Access.
Unless you are comfortable in .NET and completely lost in Access, the most promising approach would be to develop the form further in Access and VBA. There is a great deal that can be done to control access to the data this way, and much of it can be done simply by configuring the properties of the form and its controls, though on top of that is a whole area of control using VBA code in the various event procedures.
Thank you everyone for your help I have figured out the problem.
I needed to add the following code -
ds.Tables(0).Rows.Add(dr)
-
cb = New OleDb.OleDbCommandBuilder(oAdapter)
-
-
cb.QuotePrefix = "["
-
cb.QuoteSuffix = "]"
-
-
oAdapter.InsertCommand = cb.GetInsertCommand()
-
-
oAdapter.Update(ds)
-
ds.AcceptChanges()
-
Hopefully this will help people in the future. thank you again for all of your responses.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Jonathan Presnell via .NET 247 |
last post by:
I am trying to insert some data into a sql server 2000 databaseusing c#.net. I am getting the following error message:
SQL Error 8178: Prepared Statement ..... expects @Param1, whichwas not...
|
by: dcarson |
last post by:
I've read about this error in several other discussions, but still
can't seem to pinpoint the problem with my code. Everything seemed to
be working fine for some time, but it now tends to bomb out...
|
by: jinhy82 |
last post by:
Hi! I am currently creating a Registration form which contained: UserID
Password, FirstName and LastName.
These details would be inserted into Ms Access when I click submi
button. But I...
|
by: Kevin Murphy |
last post by:
Using PG 7.4.3 on Mac OS X 10.2.8, the following "insert into ... select ..."
statement completed and then announced a syntax error, which seems bizarre.
(Don't be confused by the fact that the...
|
by: mabond |
last post by:
Hi
VB.NET 2005 Express edition
Microsoft Access 2000 (SP-3)
Having trouble writing an "insert into" command for a Microsoft table I'm
accessing through oledb.
I've tried to follow the same...
|
by: Iwan Petrow |
last post by:
Hi,
I do this - take some data with sqldataadaptor (at this moment
2000rows) in fill datatable.
For each two rows do some calculations and save data to the database
with insert command.
(the...
|
by: dbdb |
last post by:
hi guys,
i just new in asp.net world.
i have an error for my first web test.
here my code :
sub OnBtnSendClicked (s As Object, e As EventArgs)
Dim strConn As String =...
|
by: gilesy |
last post by:
Hi, I have a ploblem with an insert statement using an access database, I have the same code with a sql database which works but it doesn't seem to work on access. Could someone please help.
For...
|
by: gilesy |
last post by:
Hi, I have a ploblem with an insert statement using an access database, I have the same code with a sql database which works but it doesn't seem to work on access. Could someone please help.
For i =...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |