SQL in VS.Net

[quinnipiac0807]

I'm trying to create a statement for a dropdown list but it says there incorrect syntax near +. I think this should work. What I want to actually show up in the ddl is Asset Tag - AssetMfr AssetDesc.

string sql = "SELECT a.AssetID, a.AssetMfrID, m.AssetMfrID, a.AssetDescID, d.AssetDescID, AssetTag + ' - ' + assetmfr as AssetMfr + assetdesc as AssetDesc " +

"FROM psd_Asset a" +
"LEFT JOIN psd_AssetMfr m on a.AssetMfrID = m.AssetMfrID " +
"LEFT JOIN psd_AssetDesc d on a.AssetDescID = d.AssetDescID " +
"WHERE IsActive = 0 AND IsBroken = 0 " +
"ORDER BY AssetTag ";

Any Suggestions. Any help is always apreciated.

 

[Veep]

Why did you stop aliasing here: AssetTag + ' - ' + assetmfr as AssetMfr + assetdesc as AssetDesc " +

?

 

[meckeard]

I think I see the problem:

string sql = "SELECT a.AssetID, a.AssetMfrID, m.AssetMfrID, a.AssetDescID, d.AssetDescID, AssetTag + ' - ' + assetmfr as AssetMfr + assetdesc as AssetDesc " +

When you build the value 'AssetMfr', you should stop here and then add your comma, then move on to the field assetdesc. Like this:

string sql = "SELECT a.AssetID, a.AssetMfrID, m.AssetMfrID, a.AssetDescID, d.AssetDescID, AssetTag + ' - ' + assetmfr as AssetMfr, assetdesc as AssetDesc " +

Mark

 

[quinnipiac0807]

This is what I have. It still isn't working. It say's there's a problem near '.'. No idea what that means. Anyone else have an idea?


private bool LoadAsset()
{
SqlConnection Conn = new SqlConnection(Global.ConnectionString);
Conn.Open();
//Populate Asset DDL
string sql = "SELECT a.AssetID, a.AssetMfrID, m.AssetMfrID, a.AssetDescID, d.AssetDescID, a.AssetTag + ' - ' + assetmfr as m.AssetMfr, assetdesc as d.AssetDesc " +
"FROM psd_Asset a" +
"LEFT JOIN psd_AssetMfr m ON a.AssetMfrID = m.AssetMfrID " +
"LEFT JOIN psd_AssetDesc d ON a.AssetDescID = d.AssetDescID " +
"WHERE IsActive = 0 AND IsBroken = 0 " +
"ORDER BY AssetTag ";

SqlCommand Cmd = new SqlCommand(sql, Conn);
SqlDataReader DR = Cmd.ExecuteReader();

ddlAsset.DataValueField = "AssetID";
ddlAsset.DataTextField = "AssetTag";
ddlAsset.DataSource = DR;
ddlAsset.DataBind();

ddlAsset.Items.Insert(0, "Please Select Asset.");
ddlAsset.Items[0].Value = "";

DR.Close();
Cmd.Dispose();
Conn.Close();
return true;
}

 

[tgreer]

I haven't given this a thorough looking-over, but have two suggestions:

1) Don't build dynamic SQL queries, use stored procedures!

2) If you must build your query, use the "@" modifier for your string.



Thomas D. Greer

http://www.tgreer.com

Providing PostScript & PDF
Training, Development & Consulting

 

[chiph]

string sql = "SELECT a.AssetID, a.AssetMfrID, m.AssetMfrID, a.AssetDescID, d.AssetDescID, a.AssetTag + ' - ' + assetmfr as m.AssetMfr, assetdesc as d.AssetDesc " +

You've aliased your alias. Should be:

string sql = "SELECT a.AssetID, a.AssetMfrID, m.AssetMfrID, a.AssetDescID, d.AssetDescID, a.AssetTag + ' - ' + m.assetmfr as AssetMfr, d.assetdesc as AssetDesc " +

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first