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

  • Thread starter Thread starter Rich
  • Start date Start date
R

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
 
Rich said:
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.
Click to expand...

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

David
 
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
 
Rich said:
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
Click to expand...

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
 
Thanks for your reply. Yes, I am familiar with Command
Builder. I confess, that I am using com ADO in a Non
DotNet application.
Click to expand...

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 :).
Click to expand...

You can

-- open a Recordset and check its Fields
-- use the OpenSchema method of the Connection(?) object
-- use the ADOX library.
 
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()
///
 
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
 
Back
Top