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.OleDb ....


Dim name As String
Dim position As String
Dim site As String
Dim MDBConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source = C:\Test.mdb")
Dim cmd As New OleDbCommand("CREATE TABLE tblTest(site string,namex
string, positionx string)", MDBConn)
MDBConn.Open()
cmd.ExecuteNonQuery()
cmd.Connection = MDBConn
site = 'testsite'
name = 'testname'
position = 'testposition'
cmd.CommandText = "insert into tblTest (site, namex, positionx) VALUES
(site, name, position)"
cmd.ExecuteNonQuery()


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

 

[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