VB.NET

VB.NET RSS

error in insert into statement

Please support our VB.NET advertiser: Intel Parallel Studio Home

•

Join Date: Jun 2009

Posts: 132

Reputation: babbu has a little shameless behaviour in the past

Solved Threads: 13

babbu

Offline

Junior Poster

Re: error in insert into statement

-1

#11

Sep 27th, 2009

•

Originally Posted by TomW

The dataadapter does not automatically create any of these commands.

If you are referring to the use of a commandbuilder attached to your dataadapter to generate the insert, update & delete statements; I would like to see the block of coding that creates this and fills your dataadapter so that I may see where it is causing you problems.

Also does the table have a primary key/unique field, the command builder needs this in order to be able to perform these statements.

this is the code

 Help with Code Tags 
 VB.NET Syntax (Toggle Plain Text) 
m_DataAdapter = New OleDb.OleDbDataAdapter("Select * from [Base Colours]", oledbcon)
        m_DataAdapter.Fill(m_DataTable)
        m_CommandBuilder = New OleDb.OleDbCommandBuilder(m_DataAdapter)
        DataGridView1.DataSource = m_DataTable
m_DataAdapter = New OleDb.OleDbDataAdapter("Select * from [Base Colours]", oledbcon)
        m_DataAdapter.Fill(m_DataTable)
        m_CommandBuilder = New OleDb.OleDbCommandBuilder(m_DataAdapter)
        DataGridView1.DataSource = m_DataTable

•

Join Date: Sep 2009

Posts: 303

Reputation: TomW is on a distinguished road

Solved Threads: 44

TomW

Offline

Posting Whiz

Re: error in insert into statement

#12

Sep 27th, 2009

Changes below. Also I dont know what fields your table has but the CommandBuilder object requires a primary key.

 Help with Code Tags 
 VB.NET Syntax (Toggle Plain Text) 
m_DataAdapter = New OleDb.OleDbDataAdapter("Select * from [Base Colours]", oledbcon)
        m_DataAdapter.Fill(m_DataTable)
        m_CommandBuilder = New OleDb.OleDbCommandBuilder(m_DataAdapter)
 
        m_DataAdapter.InsertCommand = m_CommandBuilder.GetInsertCommand
        m_DataAdapter.UpdateCommand = m_CommandBuilder.GetUpdateCommand
        m_DataAdapter.DeleteCommand = m_CommandBuilder.GetDeleteCommand
        DataGridView1.DataSource = m_DataTable
m_DataAdapter = New OleDb.OleDbDataAdapter("Select * from [Base Colours]", oledbcon)
        m_DataAdapter.Fill(m_DataTable)
        m_CommandBuilder = New OleDb.OleDbCommandBuilder(m_DataAdapter)

        m_DataAdapter.InsertCommand = m_CommandBuilder.GetInsertCommand
        m_DataAdapter.UpdateCommand = m_CommandBuilder.GetUpdateCommand
        m_DataAdapter.DeleteCommand = m_CommandBuilder.GetDeleteCommand
        DataGridView1.DataSource = m_DataTable

Last edited by TomW; Sep 27th, 2009 at 8:13 am.

•

Join Date: Sep 2009

Posts: 7

Reputation: samehsenosi is an unknown quantity at this point

Solved Threads: 0

samehsenosi samehsenosi is offline

Offline

Newbie Poster

Re: error in insert into statement

#13

Sep 27th, 2009

hi
my be you can use the following method :

 Help with Code Tags 
 VB.NET Syntax (Toggle Plain Text) 
dim b1 , b2 , b3 as string
b1  = "'" textbox1.text & "',"
b2 = "'" textbox2.text & "',"
b3 = "'" textbox3.text & "'"
dim  s as string
s = "Insert into table name "
s = s & "(field1,field2,field3) values"
s = s & "(b1,b2,b3)"
cmd.commandtext=s
cmd.excuteNonQuery()
 
msgbox "data saved succesfully"
dim b1 , b2 , b3 as string
b1  = "'" textbox1.text & "',"
b2 = "'" textbox2.text & "',"
b3 = "'" textbox3.text & "'"
dim  s as string
s = "Insert into table name "
s = s & "(field1,field2,field3) values"
s = s & "(b1,b2,b3)"
cmd.commandtext=s
cmd.excuteNonQuery()

msgbox "data saved succesfully"

use this method to execute the insert into

and attention to make the same values and target fields is the same..

_________________________
i hope it'll be useful

sameh senosi,

•

Join Date: Jun 2009

Posts: 132

Reputation: babbu has a little shameless behaviour in the past

Solved Threads: 13

babbu

Offline

Junior Poster

Re: error in insert into statement

#14

Sep 30th, 2009

i am aware of the insert query. but i wanted to add data using command builder.

•

Join Date: Sep 2009

Posts: 303

Reputation: TomW is on a distinguished road

Solved Threads: 44

TomW

Offline

Posting Whiz

Re: error in insert into statement

#15

Sep 30th, 2009

•

Originally Posted by babbu

i am aware of the insert query. but i wanted to add data using command builder.

See my above example, creating the command builder an attaching it to your dataadapter, only allows the commandbuilder to get the structure of the tables from your selectcommand. You still have to individually assign each of the new commands to the appropiate commands in the dataadapter.

 Help with Code Tags 
 VB Syntax (Toggle Plain Text) 
m_DataAdapter.InsertCommand = m_CommandBuilder.GetInsertCommand        
m_DataAdapter.UpdateCommand = m_CommandBuilder.GetUpdateCommand        
m_DataAdapter.DeleteCommand = m_CommandBuilder.GetDeleteCommand
m_DataAdapter.InsertCommand = m_CommandBuilder.GetInsertCommand        
m_DataAdapter.UpdateCommand = m_CommandBuilder.GetUpdateCommand        
m_DataAdapter.DeleteCommand = m_CommandBuilder.GetDeleteCommand

On an additional note; I would suggest not using "Select *" in conjunction with a command builder; instead explicitily write out each of the column names you want to work with. Previously I have run into problems doing the former mentioned.

Using the "Select *" method in conjuction with typed datasets causes an error with the command builder if you ever make changes to the database table in the future such as adding additional columns, regardless if you need to use them or not in your program, the typed dataset then no longer matches the command builders table structure. You would then need to go and update the typed datasets and redistribute the program with every change.

Last edited by TomW; Sep 30th, 2009 at 8:41 am.

•

Join Date: Jun 2009

Posts: 132

Reputation: babbu has a little shameless behaviour in the past

Solved Threads: 13

babbu

Offline

Junior Poster

Re: error in insert into statement

#16

Sep 30th, 2009

still the same error

•

Join Date: Sep 2009

Posts: 303

Reputation: TomW is on a distinguished road

Solved Threads: 44

TomW

Offline

Posting Whiz

Re: error in insert into statement

#17

Sep 30th, 2009

Hmmmm check a couple of things.
01) Have a messagebox display the actual comandbuilder.GetInsertCommand.ToString and let me know exactly what the insert statement looks like.

02) Does your table have a primary key?

•

Join Date: Jun 2009

Posts: 132

Reputation: babbu has a little shameless behaviour in the past

Solved Threads: 13

babbu

Offline

Junior Poster

Re: error in insert into statement

#18

Sep 30th, 2009

•

Originally Posted by TomW

Hmmmm check a couple of things.
01) Have a messagebox display the actual comandbuilder.GetInsertCommand.ToString and let me know exactly what the insert statement looks like.

02) Does your table have a primary key?

yes my table has a primary key.

if u want the exact exception that is occuring this is it

System.Data.OleDb.OleDbException was unhandled
ErrorCode=-2147217900
Message="Syntax error in INSERT INTO statement."
Source="Microsoft Office Access Database Engine"
StackTrace:
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
at libra_plasticvb.frmBaseColours.btnAdd_Click(Object sender, EventArgs e) in D:\waheebdocs\project1\libra plastic\libra plasticvb\Backup\libra plasticvb\Base Colours.vb:line 45
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.ButtonBase.OnKeyUp(KeyEventArgs kevent)
at System.Windows.Forms.Control.ProcessKeyEventArgs(Message& m)
at System.Windows.Forms.Control.ProcessKeyMessage(Message& m)
at System.Windows.Forms.Control.WmKeyChar(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at libra_plasticvb.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()