|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Syntax error in INSERT INTO statementme Syntax error in INSERT INTO statement. Thought this was automaticaly created according to MS. Im lost, my code is below to copy and paste. Just change to your database, tablename, fields to edit and sql. AGGHHHHHH thanks Public Sub insertit(ByRef Msql As String) Dim Connection As OleDb.OleDbConnection = New OleDb.OleDbConnection Dim cns As String = "" Dim mDBN As String = "C:\BNM.MDB" Dim cCON As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mDBN & ";Persist Security Info = False" Connection.ConnectionString = cCON Connection.Open() Dim DAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(Msql, Connection) Dim Cmand As New OleDb.OleDbCommand Dim CBuild As New OleDb.OleDbCommandBuilder Dim DataSets As New DataSet Dim Dataroww As DataRow DAdapter.Fill(DataSets, "Numbers") CBuild = New OleDb.OleDbCommandBuilder(DAdapter) 'create pkey With DataSets.Tables("Numbers") ..PrimaryKey = New DataColumn() {.Columns("Number")} End With 'create a select command with all parameters DAdapter.InsertCommand = CBuild.GetInsertCommand() Dataroww = DataSets.Tables("Numbers").NewRow Dataroww.BeginEdit() Dataroww("Number") = "9999999" Dataroww("Name") = "KJHGFDSA" Dataroww("Block") = True Dataroww.EndEdit() DataSets.Tables("Numbers").Rows.Add(Dataroww) DAdapter.Update(DataSets, "Numbers") ' errors out here insert error DataSets.AcceptChanges() Hi,
Usually what causes this is one of the column names is a keyword. To prevent the error you need to place the column name in [ ]. Instead of using a query like Select * from MyTable try using Select [col1], [col2], [col3] from MyTable The commandbuilder will use the col name is [ ] so you will not get the error. Ken --------------------------- Show quoteHide quote "Warex" <Igiveitalla***@juno.com> wrote in message news:%232TiiErCHHA.4772@TK2MSFTNGP02.phx.gbl... > Maybe someone can help, my code works untill it gets to update when it > tells me Syntax error in INSERT INTO statement. > Thought this was automaticaly created according to MS. > Im lost, my code is below to copy and paste. Just change to your database, > tablename, fields to edit and sql. > > AGGHHHHHH > > thanks > Public Sub insertit(ByRef Msql As String) > > Dim Connection As OleDb.OleDbConnection = New OleDb.OleDbConnection > > Dim cns As String = "" > > Dim mDBN As String = "C:\BNM.MDB" > > Dim cCON As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & > mDBN & ";Persist Security Info = False" > > Connection.ConnectionString = cCON > > Connection.Open() > > Dim DAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(Msql, > Connection) > > Dim Cmand As New OleDb.OleDbCommand > > Dim CBuild As New OleDb.OleDbCommandBuilder > > Dim DataSets As New DataSet > > Dim Dataroww As DataRow > > DAdapter.Fill(DataSets, "Numbers") > > CBuild = New OleDb.OleDbCommandBuilder(DAdapter) > > 'create pkey > > With DataSets.Tables("Numbers") > > .PrimaryKey = New DataColumn() {.Columns("Number")} > > End With > > 'create a select command with all parameters > > DAdapter.InsertCommand = CBuild.GetInsertCommand() > > Dataroww = DataSets.Tables("Numbers").NewRow > > Dataroww.BeginEdit() > > Dataroww("Number") = "9999999" > > Dataroww("Name") = "KJHGFDSA" > > Dataroww("Block") = True > > Dataroww.EndEdit() > > DataSets.Tables("Numbers").Rows.Add(Dataroww) > > DAdapter.Update(DataSets, "Numbers") ' errors out here insert error > > DataSets.AcceptChanges() > > AGGHHHHHHHHHH
WHY did the MicroSoft Example that I printed at 40 pages DID NOT say that? AGGGGGGGGGGGHHHHHHHHHHH Thanks....... Hitting myself, hitting myself, hitting myself............... Show quoteHide quote "Ken Tucker [MVP]" <vb***@bellsouth.net> wrote in message news:eAukh8wCHHA.4844@TK2MSFTNGP02.phx.gbl... > Hi, > > Usually what causes this is one of the column names is a > keyword. To prevent the error you need to place the column name in [ ]. > > Instead of using a query like > Select * from MyTable > > try using > > Select [col1], [col2], [col3] from MyTable > > The commandbuilder will use the col name is [ ] so you will not get the > error. > > Ken > --------------------------- > "Warex" <Igiveitalla***@juno.com> wrote in message > news:%232TiiErCHHA.4772@TK2MSFTNGP02.phx.gbl... >> Maybe someone can help, my code works untill it gets to update when it >> tells me Syntax error in INSERT INTO statement. >> Thought this was automaticaly created according to MS. >> Im lost, my code is below to copy and paste. Just change to your >> database, tablename, fields to edit and sql. >> >> AGGHHHHHH >> >> thanks >> Public Sub insertit(ByRef Msql As String) >> >> Dim Connection As OleDb.OleDbConnection = New OleDb.OleDbConnection >> >> Dim cns As String = "" >> >> Dim mDBN As String = "C:\BNM.MDB" >> >> Dim cCON As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & >> mDBN & ";Persist Security Info = False" >> >> Connection.ConnectionString = cCON >> >> Connection.Open() >> >> Dim DAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(Msql, >> Connection) >> >> Dim Cmand As New OleDb.OleDbCommand >> >> Dim CBuild As New OleDb.OleDbCommandBuilder >> >> Dim DataSets As New DataSet >> >> Dim Dataroww As DataRow >> >> DAdapter.Fill(DataSets, "Numbers") >> >> CBuild = New OleDb.OleDbCommandBuilder(DAdapter) >> >> 'create pkey >> >> With DataSets.Tables("Numbers") >> >> .PrimaryKey = New DataColumn() {.Columns("Number")} >> >> End With >> >> 'create a select command with all parameters >> >> DAdapter.InsertCommand = CBuild.GetInsertCommand() >> >> Dataroww = DataSets.Tables("Numbers").NewRow >> >> Dataroww.BeginEdit() >> >> Dataroww("Number") = "9999999" >> >> Dataroww("Name") = "KJHGFDSA" >> >> Dataroww("Block") = True >> >> Dataroww.EndEdit() >> >> DataSets.Tables("Numbers").Rows.Add(Dataroww) >> >> DAdapter.Update(DataSets, "Numbers") ' errors out here insert error >> >> DataSets.AcceptChanges() >> >> > > I'm sure Microsoft took it for granted, as most of us do.
I've gotten a little paranoid about it, myself. Stop hitting yourself, you're going to cause a brain tumor, and then you'll never get your work done. ;-) Robin S. ------------------------------ Show quoteHide quote "Warex" <Igiveitalla***@juno.com> wrote in message news:%23FIQXS0CHHA.4428@TK2MSFTNGP04.phx.gbl... > AGGHHHHHHHHHH > WHY did the MicroSoft Example that I printed at 40 pages DID NOT say that? > AGGGGGGGGGGGHHHHHHHHHHH > > Thanks....... > > Hitting myself, hitting myself, hitting myself............... > > "Ken Tucker [MVP]" <vb***@bellsouth.net> wrote in message > news:eAukh8wCHHA.4844@TK2MSFTNGP02.phx.gbl... >> Hi, >> >> Usually what causes this is one of the column names is a >> keyword. To prevent the error you need to place the column name in [ ]. >> >> Instead of using a query like >> Select * from MyTable >> >> try using >> >> Select [col1], [col2], [col3] from MyTable >> >> The commandbuilder will use the col name is [ ] so you will not get the >> error. >> >> Ken >> --------------------------- >> "Warex" <Igiveitalla***@juno.com> wrote in message >> news:%232TiiErCHHA.4772@TK2MSFTNGP02.phx.gbl... >>> Maybe someone can help, my code works untill it gets to update when it >>> tells me Syntax error in INSERT INTO statement. >>> Thought this was automaticaly created according to MS. >>> Im lost, my code is below to copy and paste. Just change to your >>> database, tablename, fields to edit and sql. >>> >>> AGGHHHHHH >>> >>> thanks >>> Public Sub insertit(ByRef Msql As String) >>> >>> Dim Connection As OleDb.OleDbConnection = New OleDb.OleDbConnection >>> >>> Dim cns As String = "" >>> >>> Dim mDBN As String = "C:\BNM.MDB" >>> >>> Dim cCON As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & >>> mDBN & ";Persist Security Info = False" >>> >>> Connection.ConnectionString = cCON >>> >>> Connection.Open() >>> >>> Dim DAdapter As OleDb.OleDbDataAdapter = New >>> OleDb.OleDbDataAdapter(Msql, Connection) >>> >>> Dim Cmand As New OleDb.OleDbCommand >>> >>> Dim CBuild As New OleDb.OleDbCommandBuilder >>> >>> Dim DataSets As New DataSet >>> >>> Dim Dataroww As DataRow >>> >>> DAdapter.Fill(DataSets, "Numbers") >>> >>> CBuild = New OleDb.OleDbCommandBuilder(DAdapter) >>> >>> 'create pkey >>> >>> With DataSets.Tables("Numbers") >>> >>> .PrimaryKey = New DataColumn() {.Columns("Number")} >>> >>> End With >>> >>> 'create a select command with all parameters >>> >>> DAdapter.InsertCommand = CBuild.GetInsertCommand() >>> >>> Dataroww = DataSets.Tables("Numbers").NewRow >>> >>> Dataroww.BeginEdit() >>> >>> Dataroww("Number") = "9999999" >>> >>> Dataroww("Name") = "KJHGFDSA" >>> >>> Dataroww("Block") = True >>> >>> Dataroww.EndEdit() >>> >>> DataSets.Tables("Numbers").Rows.Add(Dataroww) >>> >>> DAdapter.Update(DataSets, "Numbers") ' errors out here insert error >>> >>> DataSets.AcceptChanges() >>> >>> >> >> > > Warex,
Are you aware that you use some not needed code. (inline everytime under your code) > the = "" is not needed in VB.Net> Dim cns As String = "" However you don't use that string > 'create pkey I don't see the sence for this one> With DataSets.Tables("Numbers") > .PrimaryKey = New DataColumn() {.Columns("Number")} > End With > DataSets.AcceptChanges() This one is included in the DataAdapter Update, only if you use partial updates you need it. Cor 
Other interesting topics
Sending mail from page
How to attach an exe to the debugger? question about opening SQL results in Excel from ASP.NET via XML User Control saving properties Is my interpretation of COM interface correct? byte array concatenation Getting the generated name attribute for use in JavaScript problem with DataSet.GetXml() method solution required Throwing an exception on a thread.... |
|||||||||||||||||||||||
