msaccess sql insert command in vb.net

[rudy]

I'm trying to insert text values into a table in a MSAccess DB
the values are stored in string variables
Imports System.Data.Ole Db ....
Dim name As String
Dim position As String
Dim site As String
Dim MDBConn As New OleDbConnection ("Provider=Micr osoft.Jet.OLEDB .4.0;
Data Source = C:\Test.mdb")
Dim cmd As New OleDbCommand("C REATE TABLE tblTest(site string,namex
string, positionx string)", MDBConn)
MDBConn.Open()
cmd.ExecuteNonQ uery()
cmd.Connection = MDBConn
site = 'testsite'
name = 'testname'
position = 'testposition'
cmd.CommandText = "insert into tblTest (site, namex, positionx) VALUES
(site, name, position)"
cmd.ExecuteNonQ uery()
which generates an error but if I use
cmd.CommandText = "insert into tblTest (site, namex, positionx) VALUES
('site', 'name', 'position')"
of course it puts the variable names into the table
any help would be appreciated

[Ken Tucker [MVP]]

Hi,

I think you are trying to insert the values in the variables into
the table. Try this instead

cmd.CommandText = String.format(" insert into tblTest (site, namex,
positionx) VALUES ({0}, {1}, {2})", site, name, position)
Ken
------------------
"rudy" <rm****@umich.e du> wrote in message
news:11******** *************@g 44g2000cwa.goog legroups.com...

I'm trying to insert text values into a table in a MSAccess DB
the values are stored in string variables
Imports System.Data.Ole Db ....
Dim name As String
Dim position As String
Dim site As String
Dim MDBConn As New OleDbConnection ("Provider=Micr osoft.Jet.OLEDB .4.0;
Data Source = C:\Test.mdb")
Dim cmd As New OleDbCommand("C REATE TABLE tblTest(site string,namex
string, positionx string)", MDBConn)
MDBConn.Open()
cmd.ExecuteNonQ uery()
cmd.Connection = MDBConn
site = 'testsite'
name = 'testname'
position = 'testposition'
cmd.CommandText = "insert into tblTest (site, namex, positionx) VALUES
(site, name, position)"
cmd.ExecuteNonQ uery()
which generates an error but if I use
cmd.CommandText = "insert into tblTest (site, namex, positionx) VALUES
('site', 'name', 'position')"
of course it puts the variable names into the table
any help would be appreciated

[Lucky]

hi rudy,
first i would say at least you should paste error so that one can
get an idea.

and now the problem i've seen in your code :

site = 'testsite'
name = 'testname'
position = 'testposition'

how come such assignment worked that i dotnt get it. first a single
quote represents comment in vb so u must get an error with such
statement. another thing is you should assign values like this.

site = "'testsite' "
name = "'testname' "
position = "'testposition' "

now let's c your query string.

cmd.CommandText = "insert into tblTest (site, namex, positionx) VALUES
(site, name, position)"

well here you have directly use the name of the variables . using
direct names of the variables will be considered as value input. u must
have to build query using those variables. like the way Ken has showed.
just check out his posting.

[rudy]

thanks ken this is what finally worked

cmd.CommandText = String.format(" insert into tblTest (site, namex,
positionx) VALUES ('{0}', '{1}', '{2}')", site, name, position)

I really appreciate you and others who monitor these groups
an invaluable resource for newbies like me

[rudy]

thanks
the quotes were a typo