By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,687 Members | 2,046 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,687 IT Pros & Developers. It's quick & easy.

"Insert Into tbl1 Values(" & dynamicParams & ")" ?

P: n/a
Hello,

I have to create a table in an Access mdb (remotely) on
the fly.

Create Table tbl1(fld1 Integer, fld2 varchar(10), fld3...)

Then I have to insert data:

Insert Into tbl1 Values(" & val1 & ", '" & val2 & "',...")"

I would like to make this dynamic rather than hardcoding
the parameters. Is there some kind of object that I could
populate with the parameters that I need to use to create
tbl1 or even just to insert values into tbl1? The idea is
to make this dynamic because the table I have to create on
the fly will vary in number of columns. So rather than
hardcoding these parameters I wanted to loop through a
resultset to see how many columns I need (and what
datatypes) and then create and populate the table using an
ADO command object, unless there is a better way.

Thanks,
Rich
Nov 20 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
"Rich" <an*******@discussions.microsoft.com> wrote in message
news:25*****************************@phx.gbl...
Hello,

I have to create a table in an Access mdb (remotely) on
the fly.

Create Table tbl1(fld1 Integer, fld2 varchar(10), fld3...)

Then I have to insert data:

Insert Into tbl1 Values(" & val1 & ", '" & val2 & "',...")"

I would like to make this dynamic rather than hardcoding
the parameters. Is there some kind of object that I could
populate with the parameters that I need to use to create
tbl1 or even just to insert values into tbl1? The idea is
to make this dynamic because the table I have to create on
the fly will vary in number of columns. So rather than
hardcoding these parameters I wanted to loop through a
resultset to see how many columns I need (and what
datatypes) and then create and populate the table using an
ADO command object, unless there is a better way.


Look at the OleDbDataAdapter and OleDbCommandBuilder. They do that already.

David
Nov 20 '05 #2

P: n/a
Thanks for your reply. Yes, I am familiar with Command
Builder. I confess, that I am using com ADO in a Non
DotNet application. I was hoping there was some VB object
or technique that I wasn't familiar with for not having to
hardcode parameters. Thinking outloud here I am thinking
maybe I could make up a kind of library file with
parameter lists I could use. Basically, I am just making
sure there isn't some kind of functionality for the
Command object that I wasn't familiar with before I start
re-inventing the wheel :).

Rich

-----Original Message-----
"Rich" <an*******@discussions.microsoft.com> wrote in messagenews:25*****************************@phx.gbl...
Hello,

I have to create a table in an Access mdb (remotely) on
the fly.

Create Table tbl1(fld1 Integer, fld2 varchar(10), fld3...)
Then I have to insert data:

Insert Into tbl1 Values(" & val1 & ", '" & val2 & "',...")"
I would like to make this dynamic rather than hardcoding
the parameters. Is there some kind of object that I could populate with the parameters that I need to use to create tbl1 or even just to insert values into tbl1? The idea is to make this dynamic because the table I have to create on the fly will vary in number of columns. So rather than
hardcoding these parameters I wanted to loop through a
resultset to see how many columns I need (and what
datatypes) and then create and populate the table using an ADO command object, unless there is a better way.

Look at the OleDbDataAdapter and OleDbCommandBuilder.

They do that already.
David
.

Nov 20 '05 #3

P: n/a

"Rich" <an*******@discussions.microsoft.com> wrote in message
news:25*****************************@phx.gbl...
Thanks for your reply. Yes, I am familiar with Command
Builder. I confess, that I am using com ADO in a Non
DotNet application. I was hoping there was some VB object
or technique that I wasn't familiar with for not having to
hardcode parameters. Thinking outloud here I am thinking
maybe I could make up a kind of library file with
parameter lists I could use. Basically, I am just making
sure there isn't some kind of functionality for the
Command object that I wasn't familiar with before I start
re-inventing the wheel :).

Rich


Ado has the recordset. Just open a recordset against the new table and the
recordset will suck up all the table metadata. Add rows to the recordset
and then recordset.update will save them to the database.

David

Nov 20 '05 #4

P: n/a

"Rich" <an*******@discussions.microsoft.com> wrote in message
news:25*****************************@phx.gbl...
Thanks for your reply. Yes, I am familiar with Command
Builder. I confess, that I am using com ADO in a Non
DotNet application.
Then why are you asking in a .NET group? Try
microsoft.public.vb.database.ado or microsoft.public.vb.general.discussion.
I was hoping there was some VB object
or technique that I wasn't familiar with for not having to
hardcode parameters. Thinking outloud here I am thinking
maybe I could make up a kind of library file with
parameter lists I could use. Basically, I am just making
sure there isn't some kind of functionality for the
Command object that I wasn't familiar with before I start
re-inventing the wheel :).


You can

-- open a Recordset and check its Fields
-- use the OpenSchema method of the Connection(?) object
-- use the ADOX library.
Nov 20 '05 #5

P: n/a
Hi Rich,

I could never find a sample about the use of parameters in OleDb so I have
made one.

Let me know if this makes it more clear to you? (The used datagrid is a
webgrid because this does not work with a windowsform grid, however that is
only to show that the sample is correct, without the grid all is the same).

Cor
\\\
cmd.CommandText = "INSERT INTO tblUsers (UserId, Firstname) " & _
"VALUES (@UserId, '@Name')"
conn.Open()
Dim myparam1 As New OleDb.OleDbParameter("@UserId", _
OleDb.OleDbType.Integer)
Dim myparam2 As New OleDb.OleDbParameter("@Name", _
OleDb.OleDbType.VarWChar)
cmd.Parameters.Add(myparam1)
cmd.Parameters.Add(myparam2)
myparam1.Value = 1
myparam2.Value = "Manuel"
cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
cmd.Parameters.Add(myparam2)
cmd.Parameters.Add(myparam1)
myparam2.Value = "Terry"
cmd.CommandText = ("UPDATE tblUsers " & _
"SET FirstName=@Name WHERE UserID=@userId")
cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
cmd.Parameters.Add(myparam1)
cmd.CommandText = "Select * from tblUsers " & _
"where (UserId = @UserId)"
Dim dr As OleDb.OleDbDataReader
dr = cmd.ExecuteReader()
DataGrid1.DataSource = dr
DataGrid1.DataBind()
conn.Close()
///

Nov 20 '05 #6

P: n/a
Yes, well, I have had problems with the ADO recordset
object in vbscripting, so I had put it out of my mind.
But now I think I should invoke it (give it a try).

As for posting in the com vb newsgroup, well, I admit that
I did post something similar but no solutions. Just
thought maybe someone here might have another
perspective. And yes. The ADO recordset object. I just
can't think why I totally blocked it out of my mind.

Well, thanks all.
Rich

-----Original Message-----
Thanks for your reply. Yes, I am familiar with Command
Builder. I confess, that I am using com ADO in a Non
DotNet application. I was hoping there was some VB objector technique that I wasn't familiar with for not having tohardcode parameters. Thinking outloud here I am thinking
maybe I could make up a kind of library file with
parameter lists I could use. Basically, I am just making
sure there isn't some kind of functionality for the
Command object that I wasn't familiar with before I start
re-inventing the wheel :).

Rich

-----Original Message-----
"Rich" <an*******@discussions.microsoft.com> wrote inmessage
news:25*****************************@phx.gbl.. .
Hello,

I have to create a table in an Access mdb (remotely) on
the fly.

Create Table tbl1(fld1 Integer, fld2 varchar(10),fld3...)
Then I have to insert data:

Insert Into tbl1 Values(" & val1 & ", '" & val2& "',...")"
I would like to make this dynamic rather than hardcoding the parameters. Is there some kind of object that Icould populate with the parameters that I need to use tocreate tbl1 or even just to insert values into tbl1? The
ideais to make this dynamic because the table I have to
create
on the fly will vary in number of columns. So rather than
hardcoding these parameters I wanted to loop through a
resultset to see how many columns I need (and what
datatypes) and then create and populate the table
using
an ADO command object, unless there is a better way.


Look at the OleDbDataAdapter and OleDbCommandBuilder.

They do that already.

David
.

.

Nov 20 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.