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

[Rich]

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

 

[David Browne]

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

 

[Rich]

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

 

[David Browne]

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

 

[Jeff Johnson [MVP: VB]]

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.

 

[Cor Ligthert]

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

 

[Rich]

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