From combo box to textbox

Question

I can't figure out what's wrong or what the solution might me with my code it just gives me that it can't convert from string to int when i'm not using any ints only strings I also can't figure out how to write and get the datetime to be placed back into the datetimepicker.

private void cbxProducts_SelectedIndexChanged(object sender, EventArgs e)
        {
            string constring = "Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename = \"C:\\Users\\hannes.corbett\\Desktop\\Barcode Scanning\\Barcode Scanning\\BarcodeDB.mdf\"; Integrated Security = True";
            string Query = "SELECT * FROM Products where Name='" + cbxProducts.Text + "' ; ";
            SqlConnection conDataBase = new SqlConnection(constring);
            SqlCommand cmdDataBase = new SqlCommand(Query, conDataBase);
            SqlDataReader myReader;
            try
            {
                conDataBase.Open();
                myReader = cmdDataBase.ExecuteReader();

                string sBarcode = myReader.GetString("Barcodes");
                string sName = myReader.GetString("Name");
                var sDate = myReader.GetDateTime("EDate");
                string sQuantity = myReader.GetInt32("Quantity")).ToString();
                string sPrice = myReader.GetInt32("Price")).ToString();
                tbxBar.Text = sBarcode;
                tbxName.Text = sName;
                sDate = dateDate.Value;
                tbxPrice.Text = sPrice;
                tbxQua.Text = sQuantity;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

EDIT: Error Message

EDIT 2: I get the error message

"An invalid attempt was made to read when no data was available"

I have data in all of the strings in my database but i still get this error

Can you please provide the exact error message? I think that either `string sQuantity = myReader.GetInt32("Quantity")).ToString();` or `string sPrice = myReader.GetInt32("Price")).ToString();` is wrong. Check what data types you have in your db. — Raphael Müllner, Sep 21 '17 at 11:06
With this info we can only guess. My guess is that Price or Quantity or both is not int. — Crowcoder, Sep 21 '17 at 11:06
Cannot convert from string to int is what i get when i hover but i can't run the code since "Barcodes" and all of them is underlined with red — Hannes Corbett, Sep 21 '17 at 11:09
You should _always_ use [parameterized queries](https://en.wikipedia.org/wiki/Prepared_statement). This kind of string concatenations are open for [SQL Injection](https://en.wikipedia.org/wiki/SQL_injection) attacks. Alsu use `using` statement to dispose your connection and command automatically — Soner Gönül, Sep 21 '17 at 11:40
your question was about why you got the error "can't convert from string to int" and the answer is because the methods `GetString`, `GetDateTime` and `GetInt32` works with field indexes instead of field names. Accept an answer and try to work out by yourself the solution to your program or make a new question about getting field values by name (also already answered here) — MtwStark, Sep 22 '17 at 08:16

MtwStark · Answer 1 · 2017-09-22T08:19:47.977

2

you have to use the index of the column not the name

string sBarcode = myReader.GetString(IndexOfBarcodesColumn);

something like this

string sBarcode = myReader.GetString(0);
string sName = myReader.GetString(1);
var sDate = myReader.GetDateTime(2);
string sQuantity = myReader.GetInt32(3).ToString();
string sPrice = myReader.GetInt32(4).ToString();

or you can use field names directly from reader

string sBarcode = myReader.Item["Barcodes"];
string sName = myReader.Item["Name"];
var sDate = myReader.Item["EDate"];
string sQuantity = myReader.Item["Quantity"];
string sPrice = myReader.Item["Price"];

edited Sep 22 '17 at 08:19

answered Sep 21 '17 at 11:20

MtwStark

3,866
1
18
32

Ok, i'm new to doing things with databases so how do i find the index of the different columns? – Hannes Corbett Sep 21 '17 at 11:24
1

if you use `SELECT * FROM Products` the order of columns is the one of creation in database, but you can get the columns in the order you want using something like `SELECT Barcodes, Name, EDate, Quantity, Price FROM Products` so barcodes will have index 0, Name index 1 and so on.. – MtwStark Sep 21 '17 at 11:28
if this answer your question, please accept the answer – MtwStark Sep 21 '17 at 11:34
you have to use indexes instead of names – MtwStark Sep 21 '17 at 11:44
When i put .Item it can't find anything for it so .Item just gets underlined – Hannes Corbett Sep 22 '17 at 07:50
@HannesCorbett SqlDataReader seems not to have an `Item`-method. Therefore this won't work. – MatSnow Sep 22 '17 at 07:54
What could i use instead any suggestions? – Hannes Corbett Sep 22 '17 at 07:56
You wrote that my answer works. You seem to have a complete different error now. But, like already mentioned: Instead of writing comments here, create a new question or at least edit your original question and add the error you get now. – MatSnow Sep 22 '17 at 08:01
SqlDataReader DO have th Item property [link](https://msdn.microsoft.com/en-us/library/f01t4cfy(v=vs.110).aspx) and it works as expected – MtwStark Sep 22 '17 at 08:04
1

@MtwStark I think we both are wrong and right. Have a look at [this](https://forums.asp.net/t/rss/1601495). Your syntax seems to be a mix of VB.NET and C#. – MatSnow Sep 22 '17 at 08:19
1

@MatSnow you are right I was using vb notation for parenthesis instead c# square brackets. I have now corrected it! :) I use vb and it was a ctrl-c/ctrl-v mistake ;) – MtwStark Sep 22 '17 at 08:22

MatSnow · Accepted Answer · 2017-09-22T09:24:05.630

0

Like this it should work with columnnames or if you want to use columnIndex, do it the way @MtwStark described in his answer.

string sBarcode = myReader["Barcodes"].ToString();
string sName = myReader["Name"].ToString();
var sDate = myReader["EDate"];
string sQuantity = myReader["Quantity"].ToString();
string sPrice = myReader["Price"].ToString();

Concerning following error message

"An invalid attempt was made to read when no data was available"

You have to call myReader.Read() first. (like someone asked in this question)

while (myReader.Read()) {
    //do here whatever you want with the records returned from myReader
    string sBarcode = myReader["Barcodes"].ToString();
    string sName = myReader["Name"].ToString();
    var sDate = myReader["EDate"];
    string sQuantity = myReader["Quantity"].ToString();
    string sPrice = myReader["Price"].ToString();
    ...
}

edited Sep 22 '17 at 09:24

answered Sep 21 '17 at 11:41

MatSnow

7,357
3
19
31

When i do this myReader get's underlined with read and it says "method name expected" – Hannes Corbett Sep 21 '17 at 11:47
Fixed it so i can run the code but i get the error "C # An invalid attempt was made to read when no data was available" when i try to choose from my combobox" – Hannes Corbett Sep 22 '17 at 07:46
1

If your original question is answered, you should accept an answer here and create a new question. – MatSnow Sep 22 '17 at 07:49
is `Item` the default property? can you use `myReader["FieldName"]` instead of `myReader.item["FieldName"]`? – MtwStark Sep 22 '17 at 08:24
1

@MtwStark Yes, should work. The same applies to VB.NET. I never write `myReader.Item("FieldName")` i always use `myReader("FieldName")`. – MatSnow Sep 22 '17 at 08:27
1

@HannesCorbett the question is not "please make my program work" but "why I get this error?" so it is answered – MtwStark Sep 22 '17 at 08:29
@HannesCorbett I agree with MtwStark. Read [this](https://meta.stackoverflow.com/questions/318267/ask-the-user-to-create-a-new-question-after-being-asked-in-comments/318313) – MatSnow Sep 22 '17 at 08:32

From combo box to textbox

2 Answers2