error in insert into statement

[babbu]

m using access 2007 and vb 2005
this is my code

Dim drNewRow As DataRow = m_DataTable.NewRow()
            drNewRow("Code") = txtColourCode.Text.Trim
            drNewRow("Colour Name") = txtBaseColour.Text.Trim
            drNewRow("Price") = txtPrice.Text.Trim

            m_DataTable.Rows.Add(drNewRow)

            m_DataAdapter.Update(m_DataTable)

[TomW]

That much looks correct, I'm assuming the error is in your actual insert statement connected to your adapter. Can you provide that query? Also add the exact error message that you are receiving.

[babbu]

select * from accesstable

[TomW]

Thats not an insert query...

[GeekByChoiCe]

if i have to guess i would say the problem is in this line
drNewRow("Price") = txtPrice.Text.Trim
if the Price is declared as money then you have to convert the txtPrice.text
drNewRow("Price") = Convert.ToDouble(txtPrice.Text.Trim)
but as i said...without any more details its hard to guess.

[babbu]

price is declared as text in the database.

[GeekByChoiCe]

then please provide su your Update query. the query you posted is a select query.

[babbu]

the data adapter handles my update insert and delete query...

[raymyster]

why not use ado.net instead?

use commandtext its easier!

[TomW]

the data adapter handles my update insert and delete query...

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.

[babbu]

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

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

[TomW]

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

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

[samehsenosi]

hi
my be you can use the following method :

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,

[babbu]

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

[TomW]

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.

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.

[babbu]

still the same error

[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?

[babbu]

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()

[TomW]

No I want to see exactly what the Insert Into statement that the command builder made looks like.