There is no row at position 0

Question

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

C#

There is no row at position 0

here is the code

C#

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:

C#

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 12-Aug-16 1:33am

Member 9983063

Updated 3-Oct-18 21:57pm

Herman<T>.Instance

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2016-08-12T01:44:00

Solution 1

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:

C#

if (ds != null)

Because you always do this before it:

C#

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:

C#

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...

Posted 12-Aug-16 1:44am

OriginalGriff

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();

Richard Deeming 5-Sep-16 10:11am

That code is STILL vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

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...