Click here to Skip to main content
13,197,796 members (43,489 online)
Rate this:
 
Please Sign up or sign in to vote.
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 12-Aug-16 1:33am
Updated 12-Aug-16 1:44am

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

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:
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...
  Permalink  
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web03 | 2.8.171020.1 | Last Updated 12 Aug 2016
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100