how to display or get all names of access database by ID

Question

i have like 10 contacts on my database but the ID not Arrangement from 1 to 10 it like 1 2 3 5 6 8.... and im setting a button when click the lll1 is increase like lll1++ and Take lll1 value to ID so it display contacts by click button but when it get to number 4 or 7 for example (1 2 3 5 6 8....) the form stop and show error that Value Cannot be null is there way to skip number if not have value also when the ID of database finish like after 10 contacts when i press button and lll1 increase to 11 and database dont have contacts 11 it show same error Value cannot be null , help me plz this important to me thanks

private void button6_Click(object sender, EventArgs e)
{
     OleDbConnection cn = new OleDbConnection();
     cn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database9.accdb";
     OleDbCommand cmd = new OleDbCommand();
     cn.Open();
     cmd.Connection = cn;
     cmd.CommandText = "Select * from Contacts where ID=" + lll1.Text;
     OleDbDataReader reader = cmd.ExecuteReader();

     string sql = "select Attachments.FileData from Contacts where ID =" + lll1.Text;
     OleDbCommand vcom = new OleDbCommand(sql, cn);
     byte[] ImageByte = (byte[])vcom.ExecuteScalar(); //contains 20 extra bytes
     MemoryStream MemStream = new MemoryStream(ImageByte.Skip(20).ToArray()); //Read bytes starting at position 20
     Image image = Image.FromStream(MemStream); 
     pictureBox1.Image = image;
     while (reader.Read())
     {
         fl1.Text = reader.GetString(reader.GetOrdinal("First Name"));
     }
     cn.Close
}
public int o { get; set; }
private void button5_Click(object sender, EventArgs e)
{          
    o++;
    lll1.Text = o.ToString();
    button6_Click(sender, e);
}

  `   string sql = "select Attachments.FileData from Contacts where ID =" + 
       lll1.Text;
         if (DBNull.Value.Equals("Attachments.FileData"))
                {
            MessageBox.Show("no value");
                }
         else
                {
            OleDbCommand vcom = new OleDbCommand(sql, cn);
            byte[] ImageByte = (byte[])vcom.ExecuteScalar(); //contains 20 
            extra bytes
            MemoryStream MemStream = new 
            MemoryStream(ImageByte.Skip(20).ToArray()); //Read byte starting at position 20
            Image image = Image.FromStream(MemStream); 
            pictureBox1.Image = image;
            }`

If there is no record matching the Where condition the return value for ExecuteScalar is null. Try to check if the return is null before attempting to transform that return in a byte array — Steve, Nov 04 '19 at 13:59
I Don't know how to get to null value so i do a condition if it null then skip or increase or stop — Best Movies, Nov 04 '19 at 14:05
Check against `DBNull.Value` https://learn.microsoft.com/en-us/dotnet/api/system.dbnull.value?view=netframework-4.8 — Rakesh, Nov 04 '19 at 14:28
Dear @Rakesh is what i,m doing right i just edit the post see the second one it doesn't work — Best Movies, Nov 04 '19 at 15:36
try ExecuteReader and check HasRows instead ExecuteScalar. [ExecuteReader.HasRows vs ExecuteScalar() is DBNull](https://stackoverflow.com/questions/13527300/executereader-hasrows-vs-executescalar-is-dbnull) — Mihal By, Nov 04 '19 at 17:28
try ExecuteReader.HasRows vs ExecuteScalar() is DBNull [https://stackoverflow.com/questions/13527300/executereader-hasrows-vs-executescalar-is-dbnull](https://stackoverflow.com/questions/13527300/executereader-hasrows-vs-executescalar-is-dbnull) — Mihal By, Nov 04 '19 at 17:30

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

0

You need to check if ExecuteScalar returns NULL.

            var result = vcom.ExecuteScalar(); //contains 20 extra bytes
            if (result != null && result != DBNull.Value)
            {
                byte[] imageByte = result as byte[];

                if (imageByte.Length > 20)
                {
                    using (MemoryStream memStream = new
                        MemoryStream(imageByte.Skip(20).ToArray())) //Read byte starting at position 20
                    {
                        Image image = Image.FromStream(memStream);
                        pictureBox1.Image = image;
                    }
                }
            }
            else
            {
                MessageBox.Show("No image");
            }

Looking at the rest of the code, please consider using object/control names that make sense in the context, like avoiding lll1, button5, buton6 etc.

Read about ExecuteScalar here

Also consider using parameterised SQL queries, read here

Finally, wrap your connection object, cn, in a using statement.

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 04 '19 at 16:38

Rakesh

654
4
10
23

didn' t work either it showing same error `value cannot be null` on line `` ` MemoryStream MemStream1 = new MemoryStream(ImageByte1.Skip(20).ToArray());` – Best Movies Nov 04 '19 at 16:54
Is `imageByte` null? If not, how many elements are there in `imageByte`, i.e `imageByte.Length` – Rakesh Nov 04 '19 at 16:58
i have 10 contacts imagebyte show the first id 68442 and the second one 666557 but i cant see the third one because the third one number 4 in id and in number 3 it get error and crash show the error value cannot be null – Best Movies Nov 04 '19 at 17:09
Not sure what the issue is, but try the updated code and tell me what you see. – Rakesh Nov 04 '19 at 17:12
it show on console.write this `A first chance exception of type 'System.NullReferenceException'` – Best Movies Nov 04 '19 at 17:15
Is this with the updated code for the length check? – Rakesh Nov 04 '19 at 17:16
there is error expected on this line `MessageBox.Show($"Cannot skip 20 elements from imageByte, imagebyte is {imageByte.Length} long."); ` – Best Movies Nov 04 '19 at 17:21
Change `if (!DBNull.Value.Equals(result))` to `if (result!=null)`. – Rakesh Nov 04 '19 at 17:24
yes it worked thanks alot it show the message now without crashing – Best Movies Nov 04 '19 at 17:30
Dear @Rakesh what does this error mean it shown when contacts id finish `Too few parameters. Expected 1.` on this line `var result = vcom.ExecuteScalar();` – Best Movies Nov 04 '19 at 17:48
how to get max ID for database to do a code that count stop if it on max ID number – Best Movies Nov 04 '19 at 17:52
Too few params - `lll1.Text` is empty. – Rakesh Nov 04 '19 at 17:52
To get maxID, use `MAX` in sql. – Rakesh Nov 04 '19 at 17:53
sorry i mean last id not max – Best Movies Nov 04 '19 at 17:56

how to display or get all names of access database by ID

1 Answers1