Click here to Skip to main content
14,734,630 members
Please Sign up or sign in to vote.
1.80/5 (2 votes)
See more:
Hello Guys! i am facing a similar problem and i dont know how to solve them i tried many things but this issue i can't solve yet please help
actually problem is i want to select value from database and if ds == null so textbox value i want to insert in database but when i insert it's shown me error
There is no row at position 0

here is the code
string query = "Select * From Contacts where [Phone No] = " + textBox00.Text + "";
                    using (OleDbConnection conn = new OleDbConnection(connStr))
                    {
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn))
                        {
                            conn.Open();
                            DataSet ds = new DataSet();
                            if (ds != null)
                            {
                                adapter.Fill(ds);
                                label128.Text = ds.Tables[0].Rows[0]["Name"].ToString();
                                setaluefortext00001 = ds.Tables[0].Rows[0]["Phone No"].ToString();
                                setaluefortext00002 = ds.Tables[0].Rows[0]["Address"].ToString();
                                conn.Close();   
                            }
                            else
                            {
                                adapter.Fill(ds);
                                connection.Open();
                                OleDbCommand command1 = new OleDbCommand();
                                command1.Connection = connection;
                                command1.CommandText = "insert into Contacts ([Name],[Phone No],[Address])values('" + textBox.Text + "'," + textBox00.Text + ",'" + textBox000.Text + "');";
                                command1.ExecuteNonQuery();
                                connection.Close();
                                label128.Text = textBox.Text;
                                setaluefortext00002 = textBox00.Text;
                                setaluefortext00003 = textBox000.Text;
                            }


What I have tried:

string query = "Select * From Contacts where [Phone No] = " + textBox00.Text + "";
                    using (OleDbConnection conn = new OleDbConnection(connStr))
                    {
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn))
                        {
                            conn.Open();
                            DataSet ds = new DataSet();
                            if (ds != null)
                            {
                                adapter.Fill(ds);
                                label128.Text = ds.Tables[0].Rows[0]["Name"].ToString();
                                setaluefortext00001 = ds.Tables[0].Rows[0]["Phone No"].ToString();
                                setaluefortext00002 = ds.Tables[0].Rows[0]["Address"].ToString();
                                conn.Close();   
                            }
                            if (ds == null)
                            {
                                adapter.Fill(ds);
                                connection.Open();
                                OleDbCommand command1 = new OleDbCommand();
                                command1.Connection = connection;
                                command1.CommandText = "insert into Contacts ([Name],[Phone No],[Address])values('" + textBox.Text + "'," + textBox00.Text + ",'" + textBox000.Text + "');";
                                command1.ExecuteNonQuery();
                                connection.Close();
                                label128.Text = textBox.Text;
                                setaluefortext00002 = textBox00.Text;
                                setaluefortext00003 = textBox000.Text;
                            }
Posted
Updated 3-Oct-18 22:57pm
v2

1 solution

Where do I start?
Firstly, don't do it like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

Secondly, this test is redundant:
if (ds != null)
Because you always do this before it:
DataSet ds = new DataSet();
ds can never be null.

Thirdly, don't use magic numbers! If you are going to use absolute values to access a single row, then you have to test first to ensure that there is a row at all:
label128.Text = ds.Tables[0].Rows[0]["Name"].ToString();
You don't check, so if there are no rows where the phone number is an exact match, you will get the exception you show. Check the row count, and don't proceed if it is zero.

And BTW: Do yourself a favour, and stop using Visual Studio default names for everything - you may remember that "TextBox8" is the mobile number today, but when you have to modify it in three weeks time, will you then? Use descriptive names - "tbMobileNo" for example - and your code becomes easier to read, more self documenting, easier to maintain - and surprisingly quicker to code because Intellisense can get to to "tbMobile" in three keystrokes, where "TextBox8" takes thinking about and 8 keystrokes... "label128"? That's going to be a very, very busy UI you got there...
   
Comments
Member 9983063 12-Aug-16 7:53am
   
but bro how to solve this issue
OriginalGriff 12-Aug-16 7:58am
   
As I said: check the rows count!
Member 9983063 12-Aug-16 8:30am
   
well i found the solution here is this!
string query = "Select * From Contacts where [Phone No] = " + textBox00.Text + "";
using (OleDbConnection conn = new OleDbConnection(connStr))
{
using (OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn))
{
conn.Open();
DataSet ds = new DataSet();
adapter.Fill(ds);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
label128.Text = ds.Tables[0].Rows[0]["Name"].ToString();
setaluefortext00001 = ds.Tables[0].Rows[0]["Phone No"].ToString();
setaluefortext00002 = ds.Tables[0].Rows[0]["Address"].ToString();
}
else
{
OleDbCommand command1 = new OleDbCommand();
command1.Connection = conn;
command1.CommandText = "insert into Contacts ([Name],[Phone No],[Address])values('" + textBox.Text + "'," + textBox00.Text + ",'" + textBox000.Text + "');";
command1.ExecuteNonQuery();
label128.Text = textBox.Text;
setaluefortext00002 = textBox00.Text;
setaluefortext00003 = textBox000.Text;
}
conn.Close();
Member 9983063 12-Aug-16 8:30am
   
well by the way thanks for you help
OriginalGriff 12-Aug-16 8:40am
   
You're welcome! But ds will still never be null, so that test is redundant...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900