Select Query within Insert 1

[ksbrace]

Hello,
I'm trying to peform an Insert, but I also need to perform a Select within the Insert. Unfortunately, I have not been too efficient with my sql. I'm using C# on a windows application and using Access 2003 as the Database. Any help would be greatly appreciated! thanks in advance.

strSQL = "Insert into Jobs(CustomerID, StartDate, EndDate, Labor, Materials, Days) Values(Select MAX(CustomerID) from Customers,'" + txtStartDate.Text + "','" + txtEndDate.Text + "','" + intLabor + "','" + intMaterials + "' ,'" + txtDay.Text + "')";

 

[megabyte214]

Try something like this:

strSQL = "Select MAX(CustomerID) from Customers";

objcommand = new System.Data.OleDb.OleDbCommand(strSQL);

objcommand.Connection = this.oleDbConnection1;
objcommand.Connection.Open();
int result = 0;
try
{
	objAdapter = new System.Data.OleDb.OleDbDataAdapter();
	objAdapter.SelectCommand = objcommand;
	result = (int)objcommand.ExecuteScalar();
	objcommand.Connection.Close();
}
catch(Exception exc)
{
	exc.ToString();
}

strSQL = "Insert into Jobs(CustomerID, StartDate, EndDate, Labor, Materials, Days) Values(" + result + ",'" + txtStartDate.Text + "','" + txtEndDate.Text + "','" + intLabor + "','" + intMaterials + "' ,'" + txtDay.Text + "')";