oleDb Command Parameters

  • Thread starter Thread starter Bill Cart
  • Start date Start date
B

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.
 
you have @email in one and @Email in the other... Not sure if that explains
your problem or not...
 
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)
 
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.
 
Back
Top