Incorrect syntax error

Greetings
I am receiving an incorrect syntax error on line 26 of this SQL. This is not the complete SQL. Any help is appreciated
thanks.
SELECT DISTINC
rtrim(t_Mfg_Order.Mfg_Order_Nbr) "Mfg_Order_Nbr"
t_Mfg_Order_Output.MMM_Id_Nbr "BOM_MMM_Id_Nbr"
t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr "BOM_Component_MMM_Id_Nbr"
t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr
t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr
MIN(upper (t_Item_Master_b.Inventory_UOM)) "Parent_Mfg_Invt_Unit"
MIN(t_Mfg_Order.BOM_Id) "BOM_Id"
MIN(t_Mfg_Order_Output.BOM_Qty) "BOM_Qty"
MIN( upper (t_Item_Master_a.Inventory_UOM)) "Component_Mfg_Invt_Unit"
planned_qty = MAX(qty
FROM (SELECT ISNULL((SELECT SUM(t_Mfg_Order_BOM_Component_b.Planned_Input_Qty
FROM t_Mfg_Order_BOM_Component t_Mfg_Order_BOM_Component_
WHER
t_Mfg_Order_BOM_Component_a.MMM_Facility_Code = t_Mfg_Order_BOM_Component_b.MMM_Facility_Code AN
t_Mfg_Order_BOM_Component_a.Mfg_Order_Nbr = t_Mfg_Order_BOM_Component_b.Mfg_Order_Nbr AN
t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr = t_Mfg_Order_BOM_Component_b.Mfg_Order_Operation_Seq_Nbr AN
t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr = t_Mfg_Order_BOM_Component_b.MMM_Id_Nbr AN
((t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr = t_Mfg_Order_BOM_Component_b.BOM_Component_Seq_Nbr) o
(t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr = t_Mfg_Order_BOM_Component_b.Alt_For_Component_Seq_Nbr) o
(t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr = t_Mfg_Order_BOM_Component_b.BOM_Component_Seq_Nbr) O
(t_Mfg_Order_BOM_Component_b.Alt_For_Component_Seq_Nbr = t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr))),1
FROM t_Mfg_Order_BOM_Component t_Mfg_Order_BOM_Component_b) AS X(qty)
Load_Usage_qty = MIN(qty
FROM (SELECT ISNULL((SELECT SUM(ISNULL(t_Load_Usage_c.Load_Usage_Qty,0)
FROM t_Load_Usage t_Load_Usage_c
t_Load t_Load_
WHERE t_Mfg_Order_BOM_Component_a.MMM_Facility_Code = t_Load_Usage_c.MMM_Facility_Code AN
t_Mfg_Order_BOM_Component_a.Mfg_Order_Nbr = t_Load_Usage_c.Mfg_Order_Nbr AN
t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr = t_Load_Usage_c.Mfg_Order_Operation_Seq_Nbr AN
t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr = t_Load_c.MMM_Id_Nbr AN
t_Load_c.Load_Id = t_Load_Usage_c.Load_Id),0
FROM t_Load_Usage t_Load_Usage_c, t_Load t_Load_c) AS Y(qty)
MIN(t_Mfg_Order_BOM_Component_a.Phase_In_Phase_Out_Code) "Phase_In_Phase_Out_Code"
MIN(t_Mfg_Order_BOM_Component_a.Component_Desc) "Component_Desc"
MIN(t_Mfg_Order_BOM_Component_a.Phase_In_Datetime) "Start_Eff_Date"
MIN(t_Mfg_Order_BOM_Component_a.Phase_Out_Datetime) "End_Eff_Date"
MIN(t_Mfg_Order_BOM_Component_a.Alternate_Priority_Nbr) "Alternate_Priority_Nbr"
MIN(t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr) "Alt_For_Component_Seq_Nbr",It seems like instead of ... planned_qty = MAX(qty) FROM ...
you should have ... planned_qty = (SELECT MAX(qty) FROM ... )
Igor
"CraigG" <anonymous@.discussions.microsoft.com> wrote in message
news:99CE603F-EC7B-4060-A986-9C1A1E8F9617@.microsoft.com...
> Greetings;
> I am receiving an incorrect syntax error on line 26 of this SQL. This is
not the complete SQL. Any help is appreciated.
> thanks..
> SELECT DISTINCT
> rtrim(t_Mfg_Order.Mfg_Order_Nbr) "Mfg_Order_Nbr",
> t_Mfg_Order_Output.MMM_Id_Nbr "BOM_MMM_Id_Nbr",
> t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr "BOM_Component_MMM_Id_Nbr",
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr,
> t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr,
> MIN(upper (t_Item_Master_b.Inventory_UOM)) "Parent_Mfg_Invt_Unit",
> MIN(t_Mfg_Order.BOM_Id) "BOM_Id",
> MIN(t_Mfg_Order_Output.BOM_Qty) "BOM_Qty",
> MIN( upper (t_Item_Master_a.Inventory_UOM))
"Component_Mfg_Invt_Unit",
> planned_qty = MAX(qty)
> FROM (SELECT ISNULL((SELECT
SUM(t_Mfg_Order_BOM_Component_b.Planned_Input_Qty)
> FROM t_Mfg_Order_BOM_Component t_Mfg_Order_BOM_Component_b
> WHERE
> t_Mfg_Order_BOM_Component_a.MMM_Facility_Code =t_Mfg_Order_BOM_Component_b.MMM_Facility_Code AND
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Nbr =t_Mfg_Order_BOM_Component_b.Mfg_Order_Nbr AND
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr
= t_Mfg_Order_BOM_Component_b.Mfg_Order_Operation_Seq_Nbr AND
> t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr =t_Mfg_Order_BOM_Component_b.MMM_Id_Nbr AND
> ((t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr =t_Mfg_Order_BOM_Component_b.BOM_Component_Seq_Nbr) or
> (t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr =t_Mfg_Order_BOM_Component_b.Alt_For_Component_Seq_Nbr) or
> (t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr =t_Mfg_Order_BOM_Component_b.BOM_Component_Seq_Nbr) OR
> (t_Mfg_Order_BOM_Component_b.Alt_For_Component_Seq_Nbr =t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr))),1)
> FROM t_Mfg_Order_BOM_Component t_Mfg_Order_BOM_Component_b) AS
X(qty),
> Load_Usage_qty = MIN(qty)
> FROM (SELECT ISNULL((SELECT
SUM(ISNULL(t_Load_Usage_c.Load_Usage_Qty,0))
> FROM t_Load_Usage t_Load_Usage_c,
> t_Load t_Load_c
> WHERE t_Mfg_Order_BOM_Component_a.MMM_Facility_Code =t_Load_Usage_c.MMM_Facility_Code AND
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Nbr =t_Load_Usage_c.Mfg_Order_Nbr AND
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr
= t_Load_Usage_c.Mfg_Order_Operation_Seq_Nbr AND
> t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr =t_Load_c.MMM_Id_Nbr AND
> t_Load_c.Load_Id = t_Load_Usage_c.Load_Id),0)
> FROM t_Load_Usage t_Load_Usage_c, t_Load t_Load_c) AS Y(qty),
> MIN(t_Mfg_Order_BOM_Component_a.Phase_In_Phase_Out_Code)
"Phase_In_Phase_Out_Code",
> MIN(t_Mfg_Order_BOM_Component_a.Component_Desc) "Component_Desc",
> MIN(t_Mfg_Order_BOM_Component_a.Phase_In_Datetime)
"Start_Eff_Date",
> MIN(t_Mfg_Order_BOM_Component_a.Phase_Out_Datetime) "End_Eff_Date",
> MIN(t_Mfg_Order_BOM_Component_a.Alternate_Priority_Nbr)
"Alternate_Priority_Nbr",
> MIN(t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr)
"Alt_For_Component_Seq_Nbr",sql

Incorrect syntax error

Greetings;
I am receiving an incorrect syntax error on line 26 of this SQL. This is no
t the complete SQL. Any help is appreciated.
thanks..
SELECT DISTINCT
rtrim(t_Mfg_Order.Mfg_Order_Nbr) "Mfg_Order_Nbr",
t_Mfg_Order_Output.MMM_Id_Nbr "BOM_MMM_Id_Nbr",
t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr "BOM_Component_MMM_Id_Nbr",
t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr,
t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr,
MIN(upper (t_Item_Master_b.Inventory_UOM)) "Parent_Mfg_Invt_Unit",
MIN(t_Mfg_Order.BOM_Id) "BOM_Id",
MIN(t_Mfg_Order_Output.BOM_Qty) "BOM_Qty",
MIN( upper (t_Item_Master_a.Inventory_UOM)) "Component_Mfg_Invt_Unit",
planned_qty = MAX(qty)
FROM (SELECT ISNULL((SELECT SUM(t_Mfg_Order_BOM_Component_b.Planned_Input_Qt
y)
FROM t_Mfg_Order_BOM_Component t_Mfg_Order_BOM_Component_b
WHERE
t_Mfg_Order_BOM_Component_a.MMM_Facility_Code = t_Mfg_Order_BOM_Component_b.
MMM_Facility_Code AND
t_Mfg_Order_BOM_Component_a.Mfg_Order_Nbr = t_Mfg_Order_BOM_Component_b.Mfg_
Order_Nbr AND
t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr = t_Mfg_Order_BOM_Co
mponent_b.Mfg_Order_Operation_Seq_Nbr AND
t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr = t_Mfg_Order_BOM_Component_b.MMM_Id_
Nbr AND
((t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr = t_Mfg_Order_BOM_Compon
ent_b.BOM_Component_Seq_Nbr) or
(t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr = t_Mfg_Order_BOM_Compone
nt_b.Alt_For_Component_Seq_Nbr) or
(t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr = t_Mfg_Order_BOM_Com
ponent_b.BOM_Component_Seq_Nbr) OR
(t_Mfg_Order_BOM_Component_b.Alt_For_Component_Seq_Nbr = t_Mfg_Order_BOM_Com
ponent_a.Alt_For_Component_Seq_Nbr))),1)
FROM t_Mfg_Order_BOM_Component t_Mfg_Order_BOM_Component_b) AS X(qty),
Load_Usage_qty = MIN(qty)
FROM (SELECT ISNULL((SELECT SUM(ISNULL(t_Load_Usage_c.Load_Usage_Qty,0))
FROM t_Load_Usage t_Load_Usage_c,
t_Load t_Load_c
WHERE t_Mfg_Order_BOM_Component_a.MMM_Facility_Code = t_Load_Usage_c.MMM_Fac
ility_Code AND
t_Mfg_Order_BOM_Component_a.Mfg_Order_Nbr = t_Load_Usage_c.Mfg_Order_Nbr AND
t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr = t_Load_Usage_c.Mfg
_Order_Operation_Seq_Nbr AND
t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr = t_Load_c.MMM_Id_Nbr AND
t_Load_c.Load_Id = t_Load_Usage_c.Load_Id),0)
FROM t_Load_Usage t_Load_Usage_c, t_Load t_Load_c) AS Y(qty),
MIN(t_Mfg_Order_BOM_Component_a.Phase_In_Phase_Out_Code) "Phase_In_Phase_Out
_Code",
MIN(t_Mfg_Order_BOM_Component_a.Component_Desc) "Component_Desc",
MIN(t_Mfg_Order_BOM_Component_a.Phase_In_Datetime) "Start_Eff_Date",
MIN(t_Mfg_Order_BOM_Component_a.Phase_Out_Datetime) "End_Eff_Date",
MIN(t_Mfg_Order_BOM_Component_a.Alternate_Priority_Nbr) "Alternate_Priority_
Nbr",
MIN(t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr) "Alt_For_Componen
t_Seq_Nbr",It seems like instead of ... planned_qty = MAX(qty) FROM ...
you should have ... planned_qty = (SELECT MAX(qty) FROM ... )
Igor
"CraigG" <anonymous@.discussions.microsoft.com> wrote in message
news:99CE603F-EC7B-4060-A986-9C1A1E8F9617@.microsoft.com...
> Greetings;
> I am receiving an incorrect syntax error on line 26 of this SQL. This is
not the complete SQL. Any help is appreciated.
> thanks..
> SELECT DISTINCT
> rtrim(t_Mfg_Order.Mfg_Order_Nbr) "Mfg_Order_Nbr",
> t_Mfg_Order_Output.MMM_Id_Nbr "BOM_MMM_Id_Nbr",
> t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr "BOM_Component_MMM_Id_Nbr",
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr,
> t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr,
> MIN(upper (t_Item_Master_b.Inventory_UOM)) "Parent_Mfg_Invt_Unit",
> MIN(t_Mfg_Order.BOM_Id) "BOM_Id",
> MIN(t_Mfg_Order_Output.BOM_Qty) "BOM_Qty",
> MIN( upper (t_Item_Master_a.Inventory_UOM))
"Component_Mfg_Invt_Unit",
> planned_qty = MAX(qty)
> FROM (SELECT ISNULL((SELECT
SUM(t_Mfg_Order_BOM_Component_b.Planned_Input_Qty)
> FROM t_Mfg_Order_BOM_Component t_Mfg_Order_BOM_Component_b
> WHERE
> t_Mfg_Order_BOM_Component_a.MMM_Facility_Code =
t_Mfg_Order_BOM_Component_b.MMM_Facility_Code AND
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Nbr =
t_Mfg_Order_BOM_Component_b.Mfg_Order_Nbr AND
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr
= t_Mfg_Order_BOM_Component_b.Mfg_Order_Operation_Seq_Nbr AND
> t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr =
t_Mfg_Order_BOM_Component_b.MMM_Id_Nbr AND
> ((t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr =
t_Mfg_Order_BOM_Component_b.BOM_Component_Seq_Nbr) or
> (t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr =
t_Mfg_Order_BOM_Component_b.Alt_For_Component_Seq_Nbr) or
> (t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr =
t_Mfg_Order_BOM_Component_b.BOM_Component_Seq_Nbr) OR
> (t_Mfg_Order_BOM_Component_b.Alt_For_Component_Seq_Nbr =
t_Mfg_Order_BOM_Component_a. Alt_For_Component_Seq_Nbr))),1)rkred">
> FROM t_Mfg_Order_BOM_Component t_Mfg_Order_BOM_Component_b) AS
X(qty),
> Load_Usage_qty = MIN(qty)
> FROM (SELECT ISNULL((SELECT
SUM(ISNULL(t_Load_Usage_c.Load_Usage_Qty,0))
> FROM t_Load_Usage t_Load_Usage_c,
> t_Load t_Load_c
> WHERE t_Mfg_Order_BOM_Component_a.MMM_Facility_Code =
t_Load_Usage_c.MMM_Facility_Code AND
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Nbr =
t_Load_Usage_c.Mfg_Order_Nbr AND
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr
= t_Load_Usage_c.Mfg_Order_Operation_Seq_Nbr AND
> t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr =
t_Load_c.MMM_Id_Nbr AND
> t_Load_c.Load_Id = t_Load_Usage_c.Load_Id),0)
> FROM t_Load_Usage t_Load_Usage_c, t_Load t_Load_c) AS Y(qty),
> MIN(t_Mfg_Order_BOM_Component_a.Phase_In_Phase_Out_Code)
"Phase_In_Phase_Out_Code",
> MIN(t_Mfg_Order_BOM_Component_a.Component_Desc) "Component_Desc",
> MIN(t_Mfg_Order_BOM_Component_a.Phase_In_Datetime)
"Start_Eff_Date",
> MIN(t_Mfg_Order_BOM_Component_a.Phase_Out_Datetime) "End_Eff_Date",
> MIN(t_Mfg_Order_BOM_Component_a.Alternate_Priority_Nbr)
"Alternate_Priority_Nbr",
> MIN(t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr)
"Alt_For_Component_Seq_Nbr",

Incorrect Syntax - Error Help

Hi! I need some help with this error which I'm unable to understand.

Any help will be highly appreciated.

<Error>
Line 1: Incorrect syntax near 'Number'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'Number'.

Source Error:

Line 69: Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
Line 70: Dim ds As DataSet = New DataSet
Line 71: da.Fill(ds, "Equip")
Line 72:
Line 73: dgdSearch.DataSource = ds.Tables("Equip").DefaultView

Source File: c:\inetpub\wwwroot\Equip log\Demo\WebForm3.aspx.vb Line: 71

Stack Trace:

[SqlException: Line 1: Incorrect syntax near 'Number'.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
Demo.WebForm3.butSearch_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\Equip log\Demo\WebForm3.aspx.vb:71
System.Web.UI.WebControls.Button.OnClick(EventArgs e)
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
System.Web.UI.Page.ProcessRequestMain() +1292

</Error

Private Sub butSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butSearch.Click
 Dim objConnection As SqlConnection
 Dim objCommand As SqlCommand
 Dim objAdapter As SqlDataAdapter
 Dim objDataReader As SqlDataReader
 Dim objDataSet As DataSet
 Dim strSearch As String
 Dim StrSQLQuery As String
 'Dim mySQL As String
 'Get Search
 strSearch = txtSearch.Text
 'If there's nothing to search for then don't search
 ' o/w build our SQL Query execute it.
 If Len(Trim(strSearch)) > 0 Then
 'Set up our connection.
 Dim strConn As String = ("server=(local);Integrated Security=SSPI;database=Equipment Log")
 '-- setup the select command
 Dim mySQL As String = "Select * From Equip Where " & ddlSearch.SelectedValue.ToString() & " Like '" & txtSearch.Text & " %' Order by DemoNum"
 Dim MyConn As New SqlConnection(strConn)
 '--create the SqlCommand object (a connection would need to be set up too)
 Dim cmd As SqlCommand = New SqlCommand(mySQL, MyConn)
 MyConn.Open()
 '--execute the query and fill a dataset with the results
 Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
 Dim ds As DataSet = New DataSet
 da.Fill(ds, "Equip")
 dgdSearch.DataSource = ds.Tables("Equip").DefaultView
 'Create new command object passing it our SQL Query
 'and telling it which connection to use.
 objCommand = New SqlCommand(strSearch, objConnection)
 'DataBind DG to DS
 ddlSearch.DataBind()
 dgdSearch.DataBind()
 MyConn.Close()
 Else
 txtSearch.Text = "Enter Serach Here"
 End If

I am assuming that ddlSearch.SelectedValue.ToString() returns the string "number". Number is a reserved word in SQL Server, so as such it needs to be enclosed in square brackets when it is used as a column name. Try this instead:

Dim mySQL As String = "Select * From Equip Where [" & ddlSearch.SelectedValue.ToString() & "] Like '" & txtSearch.Text & " %' Order by DemoNum"

Terrisql