Showing error "There is no row at position 0"

Posted Date: 15 Jul 2012 Posted By:: sajitha Member Level: Bronze Member Rank: 1747 Points: 5 Responses: 6

Below function is for performing clockin operation for staff and student. if clockin field is empty, insert new clockin time otherwise message "you have already clocked in". Find my code and database

protected void clockInButton_Click(object sender, EventArgs e)
{
clockIn();
}
public void clockIn()
{
string dt=System.DateTime.Now.ToString("dd-MM-yyyy");
DataSet ds = new DataSet();
string connectionString = ConfigurationManager.ConnectionStrings["TMSConnectionstring"].ToString();
SqlConnection conn = new SqlConnection(connectionString);

conn.Open();
string cmdstr = "select Clock_in from Login_log where User_id =" + userIDTextBox.Text + " AND CONVERT(varchar(20),Login_time, 103) ='" + dt + "'";
SqlCommand checkUsr = new SqlCommand(cmdstr, conn);
SqlDataAdapter da = new SqlDataAdapter(checkUsr);
da.Fill(ds);
try
{
if (ds.Tables[0].Rows.Count >= 0)
{

if (ds.Tables[0].Rows[0]["Clock_in"].ToString() == null)
{
insertClockin();// problem here....not performing this function.

}
else
{
Response.Write("you have already clocked in!");
}

}
}

catch
{
Label1.Text = "error";
}
finally
{
conn.Close();
}

}
public void insertClockin()
{

SqlConnection conn;
SqlCommand comm;
string connectionString = ConfigurationManager.ConnectionStrings["TMSConnectionString"].ConnectionString;
conn = new SqlConnection(connectionString);
comm = new SqlCommand("insert into Login_log(User_id, Login_time, Clock_in, application) values(@usrid, @logtime, @clocktime, @appln)", conn);
comm.Parameters.Add("@usrid", System.Data.SqlDbType.Int);
comm.Parameters["@usrid"].Value = userIDTextBox.Text;
comm.Parameters.Add("@logtime", System.Data.SqlDbType.DateTime);
comm.Parameters["@logtime"].Value = loginTextBox.Text;
comm.Parameters.Add("@clocktime", System.Data.SqlDbType.Time);
comm.Parameters["@clocktime"].Value = clockInTextBox.Text;
comm.Parameters.Add("@appln", System.Data.SqlDbType.NVarChar,50);
comm.Parameters["@appln"].Value = "AMS";
try
{
conn.Open();
comm.ExecuteNonQuery();

}
catch(Exception ex)
{
Response.Write(ex.Message);

}

finally
{
conn.Close();
}

}

DATABASE
User_id Login_time Logout_time Last_modified
4 2012-05-14 16:39:53.000 2012-05-14 16:39:58.000 2012-05-14 16:39:58.000

Clock_in Clock_out Jobs_done application
16:39:53 16:39:58 worked on the project NULL

Responses

#680408 Author:

Pawan Awasthi Member Level: Diamond Member Rank: 4 Date: 15/Jul/2012 Rating: 2 out of 5

Points: 4

Hai Sajitha,
There is couple of things which I can see in your code:


if (ds.Tables[0].Rows[0]["Clock_in"].ToString() == null)
{
insertClockin();// problem here....not performing this function.
}

This is the first place where you are checking the comparison between ds.Tables[0].Rows[0]["Clock_in"].ToString() and null value, which is wrong.
You can check like


ds.Tables[0].Rows[0]["Clock_in"]== null


ds.Tables[0].Rows[0]["Clock_in"].ToString()== string.Empty

The next thing is your insert method:


comm.Parameters.Add("@usrid", System.Data.SqlDbType.Int);
comm.Parameters["@usrid"].Value = userIDTextBox.Text;
comm.Parameters.Add("@logtime", System.Data.SqlDbType.DateTime);
comm.Parameters["@logtime"].Value = loginTextBox.Text;
comm.Parameters.Add("@clocktime", System.Data.SqlDbType.Time);
comm.Parameters["@clocktime"].Value = clockInTextBox.Text;
comm.Parameters.Add("@appln", System.Data.SqlDbType.NVarChar,50);

These values will be null or empty always as you are not passing anything here.
So to resolve your issue, you need to pass the values to the insert function where you are calling this method. If the value is not there, you can pass either null or empty as.


insertClockin(userIDTextBox.Text, loginTextBox.Text, clockInTextBox.Text,"AMS");

Now your method will be changed as:


public void insertClockin(string userid, string logtime, string clocktime, string ams) 
{
 SqlConnection conn;
 SqlCommand comm;
 string connectionString = ConfigurationManager.ConnectionStrings["TMSConnectionString"].ConnectionString;
 conn = new SqlConnection(connectionString);
 comm = new SqlCommand("insert into Login_log(User_id, Login_time, Clock_in, application) values(userid, logtime, clocktime, ams)", conn);
try
{
 conn.Open();
 comm.ExecuteNonQuery();
}
catch(Exception ex)
{
 Response.Write(ex.Message);
}
finally
{
 conn.Close();
}
}

Hope it will be helpful to you to resolve your issue.

Regards,
Pawan Awasthi(DNS MVM)
+91 8143683708 (pawansoftit@gmail.com)
Outstanding Contribution Award..NTT Data Inc

#680436 Author: Prasad kulkarni Member Level: Diamond Member Rank: 8 Date: 15/Jul/2012 Rating: Points: 2
The error is very descriptive, "There is no row at position 0" The fetched data is either DBNULL or there is no row at desired location check following example `ds.Tables[0].Rows[0]["Clock_in"].ToString().trim()== ""` Thanks Koolprasd2003 [DotNetSpider MVM]

#680438 Author: Alwyn Duraisingh Member Level: Gold Member Rank: 11 Date: 15/Jul/2012 Rating: Points: 2
We can skip this error by checking if the dataset is null if dataset has tables if dataset has a table with rowcount > 0 if the three checklist is OK, then this error will not come Regards, Alwyn Duraisingh.M << Database Administrator >> Jesus saves! The rest of us better make backups...

#680451 Author: sajitha Member Level: Bronze Member Rank: 1747 Date: 15/Jul/2012 Rating: Points: 1
Thank you for quick response.I changed the code like below, but still showing the same error. public void clockIn() { string dt=System.DateTime.Now.ToString("dd-MM-yyyy"); DataSet ds = new DataSet(); string connectionString = ConfigurationManager.ConnectionStrings["TMSConnectionstring"].ToString(); SqlConnection conn = new SqlConnection(connectionString); conn.Open(); string cmdstr = "select Clock_in from Login_log where User_id =" + userIDTextBox.Text + " AND CONVERT(varchar(20),Login_time, 103) ='" + dt + "'"; SqlCommand checkUsr = new SqlCommand(cmdstr, conn); SqlDataAdapter da = new SqlDataAdapter(checkUsr); da.Fill(ds); try { if (ds.Tables[0].Rows[0]["Clock_in"].ToString() == "") { insertClockin(userIDTextBox.Text, loginTextBox.Text, clockInTextBox.Text, "AMS"); } else { Response.Write("you have already clocked in!"); } } catch { Label1.Text = "error"; } finally { conn.Close(); } } public void insertClockin(string userid, string logtime, string clocktime, string ams) { SqlConnection conn; SqlCommand comm; string connectionString = ConfigurationManager.ConnectionStrings["TMSConnectionString"].ConnectionString; conn = new SqlConnection(connectionString); comm = new SqlCommand("insert into Login_log(User_id, Login_time, Clock_in, application) values(userid, logtime, clocktime, ams)", conn); try { conn.Open(); comm.ExecuteNonQuery(); } catch (Exception ex) { Response.Write(ex.Message); } finally { conn.Close(); } } }

#680549 Author: Ravindran Member Level: Diamond Member Rank: 3 Date: 16/Jul/2012 Rating: Points: 3
Sajitha, You dont have row in the postition ) so error is coming rewrite like this `if (ds.Tables[0].Rows.Count > 0) { if (ds.Tables[0].Rows[0]["Clock_in"].ToString() == null) { insertClockin();// problem here....not performing this function. } else { Response.Write("you have already clocked in!"); } }` Regards N.Ravindran Your Hard work never fails

#680714 Author: sajitha Member Level: Bronze Member Rank: 1747 Date: 17/Jul/2012 Rating: Points: 1
Thank You Guys....its working now.it had problem with the datetime field.

Post Reply

This thread is locked for new responses. Please post your comments and questions as a separate thread.
If required, refer to the URL of this page in your new post.

	Next : Need Rich Text Editor...
	Previous : I need logic for the below question with proper explanation
	Return to Discussion Forum
	Post New Message
	Category:

Related Messages