oleDb Command Parameters

[Bill Cart]

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.

[Michael Mayer]

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/
mi**@mag37.com
"Bill Cart" <bc***@morpc.RemoveThis.org> wrote in message
news:uO*************@tk2msftngp13.phx.gbl...

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.

[Hasani]

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" <bc***@morpc.RemoveThis.org> wrote in message
news:uO*************@tk2msftngp13.phx.gbl...

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 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.

[Bill Cart]

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" <HJ****@hotmail.c0m> wrote in message
news:Nc**********************@twister.nyc.rr.com.. .

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" <bc***@morpc.RemoveThis.org> wrote in message
news:uO*************@tk2msftngp13.phx.gbl...

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

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.