Problems with C# and a SQL CE database

[xBlizzDevious]

Hey guys,

 

I'm a student and my project for this year, requires that I create a program with a database (and a bunch more stuff but I can't be bothered explaining the unnecessary stuff).

 

This probably isn't the best forum to ask in, but I know you guys are all helpful so I thought I'd ask anyways!

 

I can't figure out how to run a SQL statement with either a variable or the contents of a text box/listbox from the program.

 

ie:

string projSel = "SELECT CostCentre, Description FROM Project WHERE ProjectID = cbx_projID;";

I don't expect it to work that way - and it doesn't - but I can't figure out what way will work. I couldn't find the answer from quick googling, either.

 

Thanks in advance.

 

EDIT: Don't worry, guys. Figured it out. It was pretty much what I was expecting, but I didn't know the 'SelectedItem' bit as I'm new to C#. I'm new to programming, to be honest. Haha!

 

For anyone else who may be asking this quesion: it should read this:

string projSel = "SELECT CostCentre, Description FROM Project WHERE ProjectID = '" + cbx_projID.SelectedItem + "';";

[MikeD]

You should be using prepared statements (https://en.wikipedia.org/wiki/Prepared_statement).

 

When working with databases it is not a good idea to perform a query by building it yourself in a string. Instead you should use a prepared statement and then only fill in the parameters with the values specified by the user. This will allow the query to be optimized just once (but sometimes it depends on the actual values passed) and prevents SQL injection.

[xBlizzDevious]

I don't think I'll get marked down for the way I've done it, and it works, so I'm not changing it. Trust me, I've had too many bad occurrences from fixing things that aren't broken!

 

However, if you know C#, I wonder if you'd be able to help me with another problem I'm having?

 

I want to open up a form, and on load, it will fill the combobox. Then when that combobox is changed, I want it to fill the other textboxes in that form with the related information from that row.

 

I've used the above SQL statement to get the information, and that works. I have then put that information into a DataTable but I cannot figure out how to get the information out of the table.

 

Here's the code I have so far:
 

                con.Open();                string projSel = "SELECT ProjectID, CostCentre, Description FROM Project WHERE ProjectID = '" + cbx_projID.SelectedItem + "';";                SqlCeCommand c = new SqlCeCommand(projSel, con);                SqlCeDataReader reader;                reader = c.ExecuteReader();                DataTable dt = new DataTable();                dt.Columns.Add("ProjectID", typeof(String));                dt.Columns.Add("CostCentre", typeof(String));                dt.Columns.Add("Description", typeof(String));                txt_cc.Text = dt.CostCentre;                txt_desc.Text = dt.Description;                con.Close();

 

The txt_cc.Text = dt.CostCentre and the one below it are where I need to fill them. I just can't figure out how it works. I've tried it in some different array forms and with parenthesis and I just cannot figure out how the datatables work.

[MikeD]

Sorry for the late reply, I have been absent for a while.

 

In case it is still useful, what you actually need is to iterate through the lines of the reader and extract the information in each column. An example on doing this can be found here and the methods available can be found here.

If you still want to use the table here is an example of how it works.

 

Hope this helps.

[xBlizzDevious]

Unfortunately that isn't useful to me anymore as I've figured out how to fill in the text boxes. What I've been struggling with is getting information from the database if it's not a string. But I think I've fixed it.

 

As of now, I'm not needing help with any code. But it won't be long before I do, I'm sure. Haha!

 

Thanks anyways.