New row not shown in Access after INSERT

31 Jan 2007 2:20 AM

CZuhars

Hi,

My .NET application writes to an Access 2003 db through OleDb. I'm
using a stored query in Access to insert a new row. The app's C# code
sample includes:

myCommand.CommandType = CommandType.StoreQuery;
myCommand.CommandText = "InsertName";

myCommand.Parameters.AddWithValue("@FIRST", "First name");
myCommand.Parameters.AddWithValue("@LAST", "Last name");

myCommand.Command.Open();
int rowNum = myCommand.ExecuteNonQuery(); // returns 1 row added
myCommand.Command.Close();

The Access Query - (an Append Query?) is:
INSERT INTO Members ( first_name, last_name )
SELECT [@FIRST] AS Expr1, [@LAST ]AS Expr2;

When the app runs, debug says the query returns a row. But when I
look at the table's data, nothing is there.

Any advice?

Thanks!

31 Jan 2007 10:02 AM

Patrice

Are other changes taken into account ?

The usual problem is a db file with the "always copy" property defined in
the VS.NET project. It causes the runtime db file to be always overwritten
when your launch the application...

(also I thought OleDb was not supporting named parameters ?)

---
Patrice

"CZuhars" <czuh***@gmail.com> a écrit dans le message de news:

1170210006.637573.10***@s48g2000cws.googlegroups.com...
Show quote

> Hi,
>
> My .NET application writes to an Access 2003 db through OleDb. I'm
> using a stored query in Access to insert a new row. The app's C# code
> sample includes:
>
> myCommand.CommandType = CommandType.StoreQuery;
> myCommand.CommandText = "InsertName";
>
> myCommand.Parameters.AddWithValue("@FIRST", "First name");
> myCommand.Parameters.AddWithValue("@LAST", "Last name");
>
> myCommand.Command.Open();
> int rowNum = myCommand.ExecuteNonQuery(); // returns 1 row added
> myCommand.Command.Close();
>
> The Access Query - (an Append Query?) is:
> INSERT INTO Members ( first_name, last_name )
> SELECT [@FIRST] AS Expr1, [@LAST ]AS Expr2;
>
> When the app runs, debug says the query returns a row. But when I
> look at the table's data, nothing is there.
>
> Any advice?
>
> Thanks!
>

31 Jan 2007 1:00 PM

CZuhars

No other changes are happening. I'm just staring with this project,
and my INSERTS are the first thing I'm starting with.

In researching how to do this, I've been told that you can use the
queries in an Access file like a stored procedure on an SQL Server.
However, the named parameters is another issue in question - Not sure
if you can use them, or if you *can't* use the '@' in front of
parameter names. I'd rather not use just straight SQL commands.

Show quote

On Jan 31, 5:02 am, "Patrice" <http://www.chez.com/scribe/> wrote:
> Are other changes taken into account ?
>
> The usual problem is a db file with the "always copy" property defined in
> the VS.NET project. It causes the runtime db file to be always overwritten
> when your launch the application...
>
> (also I thought OleDb was not supporting named parameters ?)
>
> ---
> Patrice
>
> "CZuhars" <czuh***@gmail.com> a écrit dans le message de news:
> 1170210006.637573.10***@s48g2000cws.googlegroups.com...
>
> > Hi,
>
> > My .NET application writes to an Access 2003 db through OleDb. I'm
> > using a stored query in Access to insert a new row. The app's C# code
> > sample includes:
>
> > myCommand.CommandType = CommandType.StoreQuery;
> > myCommand.CommandText = "InsertName";
>
> > myCommand.Parameters.AddWithValue("@FIRST", "First name");
> > myCommand.Parameters.AddWithValue("@LAST", "Last name");
>
> > myCommand.Command.Open();
> > int rowNum = myCommand.ExecuteNonQuery(); // returns 1 row added
> > myCommand.Command.Close();
>
> > The Access Query - (an Append Query?) is:
> > INSERT INTO Members ( first_name, last_name )
> > SELECT [@FIRST] AS Expr1, [@LAST ]AS Expr2;
>
> > When the app runs, debug says the query returns a row. But when I
> > look at the table's data, nothing is there.
>
> > Any advice?
>
> > Thanks!