oleDb Command Parameters

The oldDb Command does not support Named Parameters. If I am reading them
correctly, the docs say that the order of the parameter should match the
order they are declared in the Stored Procedure. When I do this the data
ends up in the wrong fields. I am using C# standard with the MSDE.

The Stored Procedure is:
INSERT INTO dbo.DemoShipments
(PTYPE, RequestDate, Company, Dept, Name, JobTitle, Phone,
Ext, Addr1, City, State, Zip, email, Source)
VALUES (@PTYPE, @RequestDate, @Company, @Dept, @Name, @JobTitle, @Phone,
@Ext, @Addr1, @City, @State, @Zip,
@Email, @Source)

private void Button1_Click(object sender, System.EventArgs e)
{
oleDbCmdAddDemoReq = new OleDbCommand("SpAddDemoReq", oleDbConDemoReq);
oleDbCmdAddDemoReq.CommandType = CommandType.StoredProcedure;

// NOTE: ORDER OF PARMS ADDS MUST MATCH ORDER OF PARMS
oleDbCmdAddDemoReq.Parameters.Add("@PTYPE", ThisType);
oleDbCmdAddDemoReq.Parameters.Add("@RequestDate", System.DateTime.Now);
oleDbCmdAddDemoReq.Parameters.Add("@Company", txtCompany.Text);
oleDbCmdAddDemoReq.Parameters.Add("@Dept", txtDept.Text);
oleDbCmdAddDemoReq.Parameters.Add("@Name", txtName.Text);
oleDbCmdAddDemoReq.Parameters.Add("@JobTitle", txtTitle.Text);
oleDbCmdAddDemoReq.Parameters.Add("@Phone", txtPhone.Text);
oleDbCmdAddDemoReq.Parameters.Add("@Ext", txtExt.Text);
oleDbCmdAddDemoReq.Parameters.Add("@Addr1", txtMailAddr.Text);
oleDbCmdAddDemoReq.Parameters.Add("@City", txtCity.Text);
oleDbCmdAddDemoReq.Parameters.Add("@State", drpState.SelectedValue);
oleDbCmdAddDemoReq.Parameters.Add("@Zip", txtZip.Text);
oleDbCmdAddDemoReq.Parameters.Add("@email", txtEmail.Text);
oleDbCmdAddDemoReq.Parameters.Add("@Source", "Web Form");

oleDbCmdAddDemoReq.Connection.Open();
oleDbCmdAddDemoReq.ExecuteNonQuery();
oleDbCmdAddDemoReq.Connection.Close();
}

When I do this the Zip info ends up in the Source field, the State ends up
in the Zip, the City ends up in the State and the Source ends up in the City
field. Yes, I can fix this by moving the fields around but if they were not
all text fields I would have been getting odd error messages that I would
not have understood.

What am I doing wrong?

Are there any plans for oldDb to support named parameters in the future? I
hope so because this pretty well sucks.

re: oleDb Command Parameters

you have @email in one and @Email in the other... Not sure if that explains
your problem or not...

--
Mike Mayer
http://www.mag37.com/csharp/
mike@mag37.com


"Bill Cart" <bcart@morpc.RemoveThis.org> wrote in message
news:uOsqV1feDHA.560@tk2msftngp13.phx.gbl...[color=blue]
> The oldDb Command does not support Named Parameters. If I am reading them
> correctly, the docs say that the order of the parameter should match the
> order they are declared in the Stored Procedure. When I do this the data
> ends up in the wrong fields. I am using C# standard with the MSDE.
>
> The Stored Procedure is:
> INSERT INTO dbo.DemoShipments
> (PTYPE, RequestDate, Company, Dept, Name, JobTitle, Phone,
> Ext, Addr1, City, State, Zip, email, Source)
> VALUES (@PTYPE, @RequestDate, @Company, @Dept, @Name, @JobTitle, @Phone,
> @Ext, @Addr1, @City, @State, @Zip,
> @Email, @Source)
>
> private void Button1_Click(object sender, System.EventArgs e)
> {
> oleDbCmdAddDemoReq = new OleDbCommand("SpAddDemoReq", oleDbConDemoReq);
> oleDbCmdAddDemoReq.CommandType = CommandType.StoredProcedure;
>
> // NOTE: ORDER OF PARMS ADDS MUST MATCH ORDER OF PARMS
> oleDbCmdAddDemoReq.Parameters.Add("@PTYPE", ThisType);
> oleDbCmdAddDemoReq.Parameters.Add("@RequestDate", System.DateTime.Now);
> oleDbCmdAddDemoReq.Parameters.Add("@Company", txtCompany.Text);
> oleDbCmdAddDemoReq.Parameters.Add("@Dept", txtDept.Text);
> oleDbCmdAddDemoReq.Parameters.Add("@Name", txtName.Text);
> oleDbCmdAddDemoReq.Parameters.Add("@JobTitle", txtTitle.Text);
> oleDbCmdAddDemoReq.Parameters.Add("@Phone", txtPhone.Text);
> oleDbCmdAddDemoReq.Parameters.Add("@Ext", txtExt.Text);
> oleDbCmdAddDemoReq.Parameters.Add("@Addr1", txtMailAddr.Text);
> oleDbCmdAddDemoReq.Parameters.Add("@City", txtCity.Text);
> oleDbCmdAddDemoReq.Parameters.Add("@State", drpState.SelectedValue);
> oleDbCmdAddDemoReq.Parameters.Add("@Zip", txtZip.Text);
> oleDbCmdAddDemoReq.Parameters.Add("@email", txtEmail.Text);
> oleDbCmdAddDemoReq.Parameters.Add("@Source", "Web Form");
>
> oleDbCmdAddDemoReq.Connection.Open();
> oleDbCmdAddDemoReq.ExecuteNonQuery();
> oleDbCmdAddDemoReq.Connection.Close();
> }
>
> When I do this the Zip info ends up in the Source field, the State ends up
> in the Zip, the City ends up in the State and the Source ends up in the[/color]
City[color=blue]
> field. Yes, I can fix this by moving the fields around but if they were[/color]
not[color=blue]
> all text fields I would have been getting odd error messages that I would
> not have understood.
>
> What am I doing wrong?
>
> Are there any plans for oldDb to support named parameters in the future? I
> hope so because this pretty well sucks.
>
>[/color]

re: oleDb Command Parameters

Try replacing the @parameter_name with ? in your SQL statement,

(PTYPE, RequestDate, Company, Dept, Name, JobTitle, Phone,
Ext, Addr1, City, State, Zip, email, Source)
VALUES (@PTYPE, @RequestDate, @Company, @Dept, @Name, @JobTitle, @Phone,
@Ext, @Addr1, @City, @State, @Zip,
@Email, @Source)


should be

(PTYPE, RequestDate, Company, Dept, Name, JobTitle, Phone,
Ext, Addr1, City, State, Zip, email, Source)
VALUES (@PTYPE, @RequestDate, @Company, @Dept, @Name, @JobTitle, @Phone,
@Ext, @Addr1, @City, @State, @Zip,
@Email, @Source)

"Bill Cart" <bcart@morpc.RemoveThis.org> wrote in message
news:uOsqV1feDHA.560@tk2msftngp13.phx.gbl...[color=blue]
> The oldDb Command does not support Named Parameters. If I am reading them
> correctly, the docs say that the order of the parameter should match the
> order they are declared in the Stored Procedure. When I do this the data
> ends up in the wrong fields. I am using C# standard with the MSDE.
>
> The Stored Procedure is:
> INSERT INTO dbo.DemoShipments
> (PTYPE, RequestDate, Company, Dept, Name, JobTitle, Phone,
> Ext, Addr1, City, State, Zip, email, Source)
> VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
>
> private void Button1_Click(object sender, System.EventArgs e)
> {
> oleDbCmdAddDemoReq = new OleDbCommand("SpAddDemoReq", oleDbConDemoReq);
> oleDbCmdAddDemoReq.CommandType = CommandType.StoredProcedure;
>
> // NOTE: ORDER OF PARMS ADDS MUST MATCH ORDER OF PARMS
> oleDbCmdAddDemoReq.Parameters.Add("@PTYPE", ThisType);
> oleDbCmdAddDemoReq.Parameters.Add("@RequestDate", System.DateTime.Now);
> oleDbCmdAddDemoReq.Parameters.Add("@Company", txtCompany.Text);
> oleDbCmdAddDemoReq.Parameters.Add("@Dept", txtDept.Text);
> oleDbCmdAddDemoReq.Parameters.Add("@Name", txtName.Text);
> oleDbCmdAddDemoReq.Parameters.Add("@JobTitle", txtTitle.Text);
> oleDbCmdAddDemoReq.Parameters.Add("@Phone", txtPhone.Text);
> oleDbCmdAddDemoReq.Parameters.Add("@Ext", txtExt.Text);
> oleDbCmdAddDemoReq.Parameters.Add("@Addr1", txtMailAddr.Text);
> oleDbCmdAddDemoReq.Parameters.Add("@City", txtCity.Text);
> oleDbCmdAddDemoReq.Parameters.Add("@State", drpState.SelectedValue);
> oleDbCmdAddDemoReq.Parameters.Add("@Zip", txtZip.Text);
> oleDbCmdAddDemoReq.Parameters.Add("@email", txtEmail.Text);
> oleDbCmdAddDemoReq.Parameters.Add("@Source", "Web Form");
>
> oleDbCmdAddDemoReq.Connection.Open();
> oleDbCmdAddDemoReq.ExecuteNonQuery();
> oleDbCmdAddDemoReq.Connection.Close();
> }
>
> When I do this the Zip info ends up in the Source field, the State ends up
> in the Zip, the City ends up in the State and the Source ends up in the[/color]
City[color=blue]
> field. Yes, I can fix this by moving the fields around but if they were[/color]
not[color=blue]
> all text fields I would have been getting odd error messages that I would
> not have understood.
>
> What am I doing wrong?
>
> Are there any plans for oldDb to support named parameters in the future? I
> hope so because this pretty well sucks.
>
>[/color]

re: oleDb Command Parameters

I can't use the ? because the SQL is a stored procedure in an MSDE so it has
to have the SQL syntax and not the bizarre ado way.

If I make it a local SQL statemnet I can use the ? and it works ok but I am
trying to learn how to use stored procedures.


"Hasani" <HJB417@hotmail.c0m> wrote in message
news:Nc09b.119091$ev.23125192@twister.nyc.rr.com.. .[color=blue]
> Try replacing the @parameter_name with ? in your SQL statement,
>
> (PTYPE, RequestDate, Company, Dept, Name, JobTitle, Phone,
> Ext, Addr1, City, State, Zip, email, Source)
> VALUES (@PTYPE, @RequestDate, @Company, @Dept, @Name, @JobTitle, @Phone,
> @Ext, @Addr1, @City, @State, @Zip,
> @Email, @Source)
>
>
> should be
>
> (PTYPE, RequestDate, Company, Dept, Name, JobTitle, Phone,
> Ext, Addr1, City, State, Zip, email, Source)
> VALUES (@PTYPE, @RequestDate, @Company, @Dept, @Name, @JobTitle, @Phone,
> @Ext, @Addr1, @City, @State, @Zip,
> @Email, @Source)
>
> "Bill Cart" <bcart@morpc.RemoveThis.org> wrote in message
> news:uOsqV1feDHA.560@tk2msftngp13.phx.gbl...[color=green]
> > The oldDb Command does not support Named Parameters. If I am reading[/color][/color]
them[color=blue][color=green]
> > correctly, the docs say that the order of the parameter should match the
> > order they are declared in the Stored Procedure. When I do this the data
> > ends up in the wrong fields. I am using C# standard with the MSDE.
> >
> > The Stored Procedure is:
> > INSERT INTO dbo.DemoShipments
> > (PTYPE, RequestDate, Company, Dept, Name, JobTitle,[/color][/color]
Phone,[color=blue][color=green]
> > Ext, Addr1, City, State, Zip, email, Source)
> > VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
> >
> > private void Button1_Click(object sender, System.EventArgs e)
> > {
> > oleDbCmdAddDemoReq = new OleDbCommand("SpAddDemoReq", oleDbConDemoReq);
> > oleDbCmdAddDemoReq.CommandType = CommandType.StoredProcedure;
> >
> > // NOTE: ORDER OF PARMS ADDS MUST MATCH ORDER OF PARMS
> > oleDbCmdAddDemoReq.Parameters.Add("@PTYPE", ThisType);
> > oleDbCmdAddDemoReq.Parameters.Add("@RequestDate", System.DateTime.Now);
> > oleDbCmdAddDemoReq.Parameters.Add("@Company", txtCompany.Text);
> > oleDbCmdAddDemoReq.Parameters.Add("@Dept", txtDept.Text);
> > oleDbCmdAddDemoReq.Parameters.Add("@Name", txtName.Text);
> > oleDbCmdAddDemoReq.Parameters.Add("@JobTitle", txtTitle.Text);
> > oleDbCmdAddDemoReq.Parameters.Add("@Phone", txtPhone.Text);
> > oleDbCmdAddDemoReq.Parameters.Add("@Ext", txtExt.Text);
> > oleDbCmdAddDemoReq.Parameters.Add("@Addr1", txtMailAddr.Text);
> > oleDbCmdAddDemoReq.Parameters.Add("@City", txtCity.Text);
> > oleDbCmdAddDemoReq.Parameters.Add("@State", drpState.SelectedValue);
> > oleDbCmdAddDemoReq.Parameters.Add("@Zip", txtZip.Text);
> > oleDbCmdAddDemoReq.Parameters.Add("@email", txtEmail.Text);
> > oleDbCmdAddDemoReq.Parameters.Add("@Source", "Web Form");
> >
> > oleDbCmdAddDemoReq.Connection.Open();
> > oleDbCmdAddDemoReq.ExecuteNonQuery();
> > oleDbCmdAddDemoReq.Connection.Close();
> > }
> >
> > When I do this the Zip info ends up in the Source field, the State ends[/color][/color]
up[color=blue][color=green]
> > in the Zip, the City ends up in the State and the Source ends up in the[/color]
> City[color=green]
> > field. Yes, I can fix this by moving the fields around but if they were[/color]
> not[color=green]
> > all text fields I would have been getting odd error messages that I[/color][/color]
would[color=blue][color=green]
> > not have understood.
> >
> > What am I doing wrong?
> >
> > Are there any plans for oldDb to support named parameters in the future?[/color][/color]
I[color=blue][color=green]
> > hope so because this pretty well sucks.
> >
> >[/color]
>
>[/color]