pasing listview results through SQL to get "result"

Question

im trying to parse the result of arr[0] through a sql command to get the result and then put it in replacment of arr[0] result.

the first result arr[0] gives me is: 34

so i want to get the result 34 parse it through my SQL connection to return the result "Trtanium".

Here is the code im working with;

 private void JitaOrePrices() { ListViewItem itm; //listview settings. listView1_Jita.View = View.Details; listView1_Jita.GridLines = true; listView1_Jita.FullRowSelect = true; //convert to string. string string1 = "http://api.eve-central.com/api/marketstat?typeid=34&minQ=1&typeid=35&minQ=1&typeid=36&minQ=1&typeid=37&minQ=1&typeid=38&minQ=1&typeid=39&minQ=1&typeid=40&minQ=1&typeid=11399&minQ=1&usesystem=30000142"; // add colums to put into. listView1_Jita.Columns.Add("Type", 45); listView1_Jita.Columns.Add("(B)", 70); listView1_Jita.Columns.Add("(S)", 70); //set arrays to put into. string[] arr = new string[3]; XmlDocument doco = new XmlDocument(); doco.Load(string1); XmlNodeList Blist = doco.SelectNodes("/evec_api/marketstat/type"); foreach (XmlNode xnod in Blist) { SqlConnection SQLC = new SqlConnection("user id=No_IdeaV2;" + "password = ********;server =******;" + "Trusted_Connection=yes;" + "database = 12345;" + "connection timeout = 30"); SQLC.Open(); SqlCommand SQLLookup = new SqlCommand("SELECT typeName FROM invTypes WHERE typeID = 'THIS IS WHERE I AM STUCK'"); XmlNode znod = xnod.SelectSingleNode("buy"); XmlNode dnod = xnod.SelectSingleNode("sell"); if (xnod.Attributes["id"] != null) { arr[0] = xnod.Attributes["id"].InnerText; arr[1] = znod.SelectSingleNode("max").InnerText; arr[2] = dnod.SelectSingleNode("max").InnerText; } itm = new ListViewItem(arr); itm.Font = new Font("Tahima", 9); listView1_Jita.Items.Add(itm); } }

René Vogt · Accepted Answer · 2015-11-26 12:27:51Z

Supposing your typeID column is a numeric field, create your sql command like this:

using (SqlConnection SQLC = new SqlConnection( /* as shown by OP */)) { SQLC.Open(); foreach (XmlNode xnod in Blist) { // I guess you would leave those nodes out entirely if (xnod.Attributes["id"] == null) continue; XmlNode znod = xnod.SelectSingleNode("buy"); XmlNode dnod = xnod.SelectSingleNode("sell"); arr[0] = xnod.Attributes["id"].InnerText; arr[1] = znod.SelectSingleNode("max").InnerText; arr[2] = dnod.SelectSingleNode("max").InnerText; SqlCommand SQLLookup = new SqlCommand(string.Format("SELECT typeName FROM invTypes WHERE typeID = {0}", arr[0]), SQLC); // don't forget to set the connection arr[0] = SQLLookup.ExecuteScalar() as string; itm = new ListViewItem(arr); itm.Font = new Font("Tahima", 9); listView1_Jita.Items.Add(itm); } }

I put the connection to the outer scope so that you don't have to open a new connection for every node. And it's inside a using statement so it's properly closed and cleaned up afterward.

Be sure to add some error handling, since the sql connection may throw exception on network errors or you typeID may not be found etc.

If your typeID column is of a string type, change the format string to "... WHERE typeID = '{0}'"

Collectives™ on Stack Overflow

pasing listview results through SQL to get "result"

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related