Home » VB.NetRSS

inserting into datatable ( access datanase ) using tableadapter

Hi everyone,

I am a veteran programmer . However , I am new to visual basic 2010 . I am trying to create a database that ould store data within an access file *.mdb .I have created the database which includes multiple tables . moreover i have created app.config file using wizard and my database along with dataset appear in the visual basic 2010 project . moreover when i test the function insert in the dataset the function performs well and the output is correct and i can see the new row added in my access database . however when i try to use the ATLtableadapter.insertquery ( ....) (ATL is a datatable in my database) function in my form  (in view code) the row doesn't get added.

When i first used this function an exception occured was something like first system class exception data.dll something along that lines. 

So any help would be appreciated . however , when i created a new oledb.oledbconnection and put  its connection string as the one in windowsapplication1.my.settings.marketconnectionstring. and used the open comand to open a connection . the message stoppped appearing . however , the insertquery is not adding the row in my database still when i use it in the form.

therefore , any help would be appretioated

Moreover , i added the atltableadapter to  my form . moreover , i am not beginning a transaction or doing anything else as i am using the function without setting up a transaction or doing anything else . except so my code could be sumarised as:

dim instance as oledb.oledbconnection

instance = new oledb.oledbconnection

with instance

.connectionstring = windowsapplication.my.setting,marketsconnectionstring

.open()

end with

ATLtableadapter1.insertquery(..)

do i need to add anything else or do something thanks again

 

    

 

24 Answers Found

 

Answer 1

anyone
 

Answer 2

What is your connection string in the project Setting? can you post and see if we cann analyze it?

you need an OleDbCommand

        Dim oledbConn As OleDb.OleDbConnection = New OleDb.OleDbConnection() 'Connection string in the parenthesis
        Dim oledbCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("Insert Statement", oledbConn)
        oledbCmd.ExecuteNonQuery()

 

 

Answer 3

ya the connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=|DataDirectory|\markets.mdb;"

 

Answer 4

did you add the OleDbCommand?
 

Answer 5

no not yet i will try it later.

thanks i will post if anything happens

 

Answer 6

nope it did nt work

 

this message keeps appearing:

a first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

 

Answer 7

actually know my code is :

 

 

Dim oledbConn As OleDb.OleDbConnection = New OleDb.OleDbConnection(WindowsApplication1.My.Settings.marketsConnectionString)

 

 

Dim oledbCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("Insert Statement", oledbConn)

oledbConn.Open()

oledbCmd.ExecuteNonQuery()

AtlTableAdapter1.InsertQueryatl(....)

these two errors keep popping

A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll

A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

and the error syntax error in insert into occurs

 

Answer 8

so there was a syntax error in my atl insert i fixed it but it promptsme that one or more parameter is unfilled

in this

 

oledbCmd.ExecuteNonQuery()

 and moreover is the insert statement taken from the comandtext when i press on the query

my code now is:

Dim

 

 

oledbConn As OleDb.OleDbConnection = New OleDb.OleDbConnection(WindowsApplication1.My.Settings.marketsConnectionString)

Dim

 

 

oledbCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("INSERT INTO `BOS` (`.......`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", oledbConn)

oledbConn.Open()

oledbCmd.ExecuteNonQuery()

 

 

 

 

thanks for you help i really need aid on this one

 

Answer 9

Hello Jet,

Try some more modern VB code it looks now if it is done by a C# developer or somebody using the oldest seldom used VB Net version 2002

TryUsing oledbConn AsNew OleDb.OleDbConnection(WindowsApplication1.My.Settings.marketsConnectionString)
        Using oledbCmd AsNew OleDb.OleDbCommand("Insert Statement", oledbConn)
          oledbConn.Open()
          oledbCmd.ExecuteNonQuery()
          ' AtlTableAdapter1.InsertQueryatl(....) 'This row should be completely uselessEndUsingEndUsingCatch ex As Exception
      MessageBox.Show(ex.ToString)
    EndTryEndSub

An ExecuteNonQuery is performing SQL transact statements like done in the SQL Query tool, so the adapter row confuses me.

 

 

Answer 10

but how do you set the paramters for the columns in the table moreover . the insert statment between parantheses is it the same as teh comand text .if so do i need to set values from there . if so how.

 

thank you very much

 

Answer 11

It will look something like the following:

Dim oledbConn As OleDb.OleDbConnection = New OleDb.OleDbConnection(WindowsApplication1.My.Settings.marketsConnectionString)

Dim oledbCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("INSERT INTO BOS (Col1, Col2, Col3, ...) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", oledbConn) 

oledbCmd.Parameters.AddWithValue("Param1", ParamValue1)
oledbCmd.Parameters.AddWithValue("Param2", ParamValue2)
oledbCmd.Parameters.AddWithValue("Param3", ParamValue3)
oledbCmd.Parameters.AddWithValue("Param4", ParamValue4)
oledbCmd.Parameters.AddWithValue("Param5", ParamValue5)

oledbConn.Open()

oledbCmd.ExecuteNonQuery()

 

Answer 12

so param1 is reserved for col1

 

thankl you verymuch seriuosly.

 

Answer 13

Your insert statement might not contain a record.

Adam

 

Answer 14

how do i check for records. sorry for being lost but it s  my first time working with visual basic 2010 .

 

Answer 15

It will look something like the following:

Dim oledbConn As OleDb.OleDbConnection = New OleDb.OleDbConnection(WindowsApplication1.My.Settings.marketsConnectionString)



Dim oledbCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("INSERT INTO BOS (Col1, Col2, Col3, ...) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", oledbConn) 



oledbCmd.Parameters.AddWithValue("Param1", ParamValue1)

oledbCmd.Parameters.AddWithValue("Param2", ParamValue2)

oledbCmd.Parameters.AddWithValue("Param3", ParamValue3)

oledbCmd.Parameters.AddWithValue("Param4", ParamValue4)

oledbCmd.Parameters.AddWithValue("Param5", ParamValue5)



oledbConn.Open()



oledbCmd.ExecuteNonQuery()




Paul ~~~~ Microsoft MVP (Visual Basic)

i want to ask you if i need to set a parameter in

oledbCmd.Parameters.AddWithValue("Param1", ParamValue1)

lets say i am using the string defined variable coll1 in my code to set a value for Col1 in the table

wold i write

oledbCmd.Parameters.AddWithValue("Param1", coll1)

to set that parammeter .

thank you again

 

Answer 16

Set a breakpoint on the line following the query. Hover over the text to copy the full query that will be executed. Is it a valid insert query with a record to insert or is it a 1/2 of an insert statement?

Adam

 

Answer 17

i want to ask you if i need to set a parameter in

oledbCmd.Parameters.AddWithValue("Param1", ParamValue1)

lets say i am using the string defined variable coll1 in my code to set a value for Col1 in the table

wold i write

oledbCmd.Parameters.AddWithValue("Param1", coll1)

to set that parammeter .

thank you again


Yes, that is correct.
 

Answer 18

 

 

Dim text3 AsString

text3 = "non"

 

 

Try

Using oledbConn AsNew OleDb.OleDbConnection(WindowsApplication1.My.Settings.testsConnectionString)

Using oledbCmd AsNew OleDb.OleDbCommand("INSERT INTO `testtable` (`test1`) VALUES (?)", oledbConn)

oledbCmd.Parameters.AddWithValue("Param1", text3)

oledbConn.Open()

oledbCmd.ExecuteNonQuery()

Me.TestsDataSet1.AcceptChanges()

EndUsing

EndUsing

Catch ex AsException

MessageBox.Show(ex.ToString)

EndTry

 

 

EndSub

ok so i tried this i made a new project and this is wat code i have and still it doesnt insert . this has only one parameter and i put a breakpoint at executenonquery and still it doesnt add . should i begin transaction should i define the insert function on my own or is it enough that it is autogenerated . i have no idea .

I mean i am trying to fix this it aint happening . any help  . like if someone wants to use an access database .

for your help

thank you all

 

Answer 19

however when i go to

serverversionnormalised it says specfic method not supported   ... when i do breakpoint at executenonquery()

and on local transaction it says .. write only properties not supported

and on transaction it says nothing

so i dnt know if it says anything

 

 

Answer 20

any help : )
 

Answer 21

Why are you using those single quotes in your sql transact code?

INSERT INTO `testtable` (`test1`) VALUES (?)

Could be

Insert into TestTable (Test1) Values (?) 


 

Answer 22

Please look at my example again. Note that I'm not using single quotes around column or table names so I'm not sure why you are doing this.

If you are getting an error, please post the error text and identify the line of code on which it occurs.

 

Answer 23

so i looked up this anyway i added this . i dnt know what is wrong to be honest this is puzzling as thre is no error being outputed so  dnt know to be honest it is just that its not adding anything . and the things is that

http://www.ehow.com/how_6962532_insert-data-access-using-vb_net.html

 

Imports

 

 

System.Data.OleDb

PublicClassForm1

PrivateSub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim text3 AsString

text3 = "non"

Try

Using oledbConn AsNew OleDb.OleDbConnection(WindowsApplication1.My.Settings.testsConnectionString)

oledbConn.Open()

Using oledbCmd AsNew OleDb.OleDbCommand("INSERT INTO testtable (test1) VALUES (?)", oledbConn)

oledbCmd.Parameters.AddWithValue("Param1", text3)

 

 

 

oledbCmd.ExecuteNonQuery()

EndUsing

EndUsing

Catch ex AsException

MessageBox.Show(ex.ToString)

EndTry

EndSub

PrivateSub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e AsSystem.EventArgs) Handles TextBox1.TextChanged

EndSub

EndClass

 

Answer 24

Hi jetnotif,

There is something incorrect of your above code. Please try with the below code to see if it works for you.

Using oledbCmd AsNew OleDb.OleDbCommand("INSERT INTO testtable (test1) VALUES (@Param1)", oledbConn)
oledbCmd.Parameters.AddWithValue("Param1", text3)

Please use "@" to mark the parameters in SQL query.

 

Best Regards,

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter