Insert Query defined centrally

Question

User122187137 posted

Can I define Insert query only once as in stored procedures for access database?

Answer 1

User-1917713218 posted

Yes you can

but in that you need to pass field and table name dynamically.

Answer 2

User1867929564 posted

refer this,

http://support.microsoft.com/kb/202116 
Secondly if you make a query in mdb like in emp table(4 columns)

INSERT INTO Employee ( empid, fname, lname, deptid ) values(?,?,?,?)

It will ask for paramter.
I think you can invoke this from asp.net and pass parameter)

http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

http://www.xefteri.com/articles/show.cfm?id=6 

Answer 3

User122187137 posted

How can I pass field and table name dynamically?

Answer 4

User3866881 posted

Hi：）

First I beg your pardon but I cannot understand how you can use query in Access as StoredProcdure? I know that Access db doesn't support SP.

And If you want to dynamically craete an insert with different tables as well as its names. I think you use something like this:

public boolean DynamicInsert(string insertsql,OleDbParameter[]parameters)

{

          using (OleDbConnection con = new OleDbConnection(Your conn str))

           {

                   OleDbCommand cmd = new OleDbCommand(insertsql,con);

                  cmd.Parameters.AddRange(parameters);

                  con.Open();

                  return cmd.ExecuteNonQuery()>0;

           }

}

You can call this method as:

DynamicInsert("insert into tableA values(@a,@b)",new OleDbParameter[]

{

new OleDbParameterp{ParameterName="@a",Type="xxx",Value="xxx",

new OleDbParameterp{ParameterName="@b",Type="yyy",Value="yyy",

}

);

With different kinds of insert sql statement as parameters, you should finish the task.

Answer 5

User-1199946673 posted

First I beg your pardon but I cannot understand how you can use query in Access as StoredProcdure? I know that Access db doesn't support SP

Access doesn't support Stored Procedures. However, it does support stored queries (single sql statements) that you can execute just like SQL stored procedures...

Answer 6

User3866881 posted

Oh, you mean just like the 2man's or what I say, hans_v?

Just use OleDbCommand+Your sql statement?

Thx again. Correct me if I'm wrong：）

Answer 7

User-1199946673 posted

I mean this: In Access, you can store a query named query1, for example:

 

PARAMETERS @Field1 Text ( 255 ), @ID Long;
UPDATE table1 SET Field1 = @Field1 WHERE ID = @ID

 

Then you can do something like:

 

        Using cn As New Data.OleDb.OleDbConnection("youconnectionstring")
            Using cmd As New Data.OleDb.OleDbCommand("query1", cn)
                cmd.CommandType = Data.CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("Field1", "test")
                cmd.Parameters.AddWithValue("ID", 1)
                cn.Open()
                cmd.ExecuteNonQuery()
            End Using
        End Using

Answer 8

User3866881 posted

Oh man, you are really a hero——hans_v!

Specialized in Access...

Yours is right.

I've also learnt a lot from you

Thx again

Hope we can futher discuss more on some issues