8 Replies - 4051 Views - Last Post: 03 December 2008 - 01:19 PM Rate Topic: -----

#1 pansylea  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 29-November 08

no row at position 1 ?

Posted 02 December 2008 - 01:47 PM

I'm TRYING to develop a windows mobile 5 application in microsoft visual studio with SQLCE. On the customer form (the one below) i have text boxes on the form which i want to get the data from to insert into the customer table but the exception catches it and says "There is no row at position 1". Does anyone know how to do this?? I've tried a couple of methods now and none seem to work :( :(

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;
using System.IO;
using System.Reflection;

namespace DeviceApplication1
{
	public partial class frmCars : Form
	{
		string connectionString = ("Data Source=" + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)) + "\\CarsGalore.sdf;Persist Security Info=False;");

		public frmCars()
		{
			InitializeComponent();
		}

		private void mnuContinue_Click(object sender, EventArgs e)
		{
			SqlCeConnection conn = new SqlCeConnection(connectionString);
			SqlCeCommand selectCommand = conn.CreateCommand();
			string select = "SELECT * FROM Customer";
			selectCommand.CommandText = select;
			SqlCeCommand insertCommand = conn.CreateCommand();
			string insert = "INSERT INTO Customer (No, Name, Address) VALUES (@no, @name,@address)";
			insertCommand.CommandText = insert;

			SqlCeDataAdapter customerDataAdapter = new SqlCeDataAdapter();
			customerDataAdapter.SelectCommand = new SqlCeCommand(select);
			customerDataAdapter.InsertCommand = new SqlCeCommand(insert);

			//DataSet carsGaloreDataSet = new DataSet();
			//DataTable dt = new DataTable();
			//customerDataAdapter.TableMappings.Add("Customer", carsGaloreDataSet);
			//dt = carsGaloreDataSet.Tables.Add("Customer");
			//customerDataAdapter.Fill(carsGaloreDataSet, "Customer");

			try
			{
				//int custIndex = Convert.ToInt32(noTextBox.Text);
				CarsGaloreDataSet.CustomerRow custRow = carsGaloreDataSet.Customer[1];
				int custNo = Convert.ToInt32(noTextBox.Text);
				string custName = (string)custRow["Name"];
				string custAddress = (string)custRow["Address"];

				CarsGaloreDataSet.CustomerRow lineItemRow = carsGaloreDataSet.Customer.NewCustomerRow();
				lineItemRow.No = custNo;
				lineItemRow.Name = custName;
				lineItemRow.Address = custAddress;
				carsGaloreDataSet.Customer.AddCustomerRow(lineItemRow);
			}
			catch (ConstraintException)
			{
				MessageBox.Show("A line item for this customer already exists. " + 
					"Please select another customer.", "Entry error");
			}
			catch (Exception ex)
			{
				MessageBox.Show(ex.Message, ex.GetType().ToString());
			}

			try
			{
				customerTableAdapter.Update(carsGaloreDataSet.Customer);

				//int custNo = (int)customerTableAdapter

				//foreach (CarsGaloreDataSet.CustomerRow row in carsGaloreDataSet.Customer)
				//{
					//row.No = custNo;
				//}

				customerTableAdapter.Update(carsGaloreDataSet.Customer);
			}
			catch(SqlCeException ex)
			{
				MessageBox.Show("Database error " + ex.Message, ex.GetType().ToString());
			}

			/*
			DataRow newCustomerRow = carsGaloreDataSet.Tables["Customer"].NewRow();
			//newCustomersRow = dt.NewRow();
			newCustomerRow["No"] = noTextBox.Text;
			newCustomerRow["Name"] = nameTextBox.Text;
			newCustomerRow["Address"] = addressTextBox.Text;
			//dt.Rows.Add(row);
			carsGaloreDataSet.Tables["Customer"].Rows.Add(newCustomerRow);
			

				conn.Open();

					try
					{
						insertCommand.Parameters.Add("@no",SqlDbType.Int, 4, noTextBox.Text);
						insertCommand.Parameters.Add("@name",SqlDbType.NVarChar, 30, nameTextBox.Text);
						insertCommand.Parameters.Add("@address", SqlDbType.NVarChar, 50,addressTextBox.Text);

						//customerDataAdapter.InsertCommand = com;
						customerDataAdapter.Update(carsGaloreDataSet, "Customer");

						MessageBox.Show("Updation is over");

					}

					catch (Exception ex)
					{

						MessageBox.Show("Error in connection ..." + ex.Message);

					}
					conn.Close();
				 */

			//Gets a collection that provides the master mapping
			// between a source table and a DataTable
			//customerDataAdapter.TableMappings.Add("Table", "Customer");

			//A data adapter object utilizes the Fill method 
			//to populate a DataSet or a DataTable object with data 
			//retrieved by a SELECT command. 
			//customerDataAdapter.Fill(carsGaloreDataSet);
			

			if ((rdbYes.Checked) == true)
			{
				Form hireForm = new frmHire();
				hireForm.Show();			   
			}
			else if ((rdbNo.Checked) == true)
			{
				this.Close();
			}
			else
			{
				MessageBox.Show("Please select yes or no!");
			}
		}

		private void mnuExit_Click(object sender, EventArgs e)
		{
			this.Close();
		}

		private void frmCars_Load(object sender, EventArgs e)
		{

			/*
			SqlCeConnection connection = new SqlCeConnection(connectionString);

			string selectStatement = "SELECT * FROM Customer";
			SqlCeCommand selectCommand = new SqlCeCommand(selectStatement, connection);



			try
			{
				//open the connection to the database
				connection.Open();
			}
			catch (SqlCeException ex)
			{
				MessageBox.Show("Error in connection ..." + ex.Message);
			}

			//Gets a collection that provides the master mapping
			// between a source table and a DataTable
			customerDataAdapter.TableMappings.Add("Table", "Customer");

			/*A data adapter object utilizes the Fill method 
			to populate a DataSet or a DataTable object with data 
			retrieved by a SELECT command. */ /*
			customerDataAdapter.Fill(carsGaloreDataSet);


			cboCust.Text = "";
			cboCust.Items.Clear();
			cmd = connection.CreateCommand();
			cmd.CommandText = "SELECT No FROM Customer;";
			SqlCeDataReader rdr = cmd.ExecuteReader();
			while (rdr.Read())
			{
				cboCust.Items.Add(rdr.GetString(0));
			}
			cboCust.Refresh();

			// Close the connection to the database.
			connection.Close();
			/*
			Globals g = Globals.GetInstance();
			noTextBox.Text = g.CustNo.ToString();
			nameTextBox.Text = g.CustName.ToString();
			addressTextBox.Text = g.CustAddress.ToString();
			*/
		}

	}
}



Is This A Good Question/Topic? 0
  • +

Replies To: no row at position 1 ?

#2 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: no row at position 1 ?

Posted 02 December 2008 - 02:28 PM

Are you certain that data is being returned from your query?

The index for row numbers starts at 0. So if you only have one row, then you would access it like the following.

CarsGaloreDataSet.CustomerRow custRow = carsGaloreDataSet.Customer[0];

Was This Post Helpful? 0
  • +
  • -

#3 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,957
  • Joined: 21-March 08

Re: no row at position 1 ?

Posted 02 December 2008 - 02:34 PM

care to tell us where the error is happening at?
Was This Post Helpful? 0
  • +
  • -

#4 pansylea  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 29-November 08

Re: no row at position 1 ?

Posted 02 December 2008 - 04:06 PM

ok, i realised that i didnt need this bit of code:

try
			{
				//int custIndex = Convert.ToInt32(noTextBox.Text);
				CarsGaloreDataSet.CustomerRow custRow = carsGaloreDataSet.Customer[1];
				int custNo = Convert.ToInt32(noTextBox.Text);
				string custName = (string)custRow["Name"];
				string custAddress = (string)custRow["Address"];

				CarsGaloreDataSet.CustomerRow lineItemRow = carsGaloreDataSet.Customer.NewCustomerRow();
				lineItemRow.No = custNo;
				lineItemRow.Name = custName;
				lineItemRow.Address = custAddress;
				carsGaloreDataSet.Customer.AddCustomerRow(lineItemRow);
			}
			catch (ConstraintException)
			{
				MessageBox.Show("A line item for this customer already exists. " +
					"Please select another customer.", "Entry error");
			}
			catch (Exception ex)
			{
				MessageBox.Show(ex.Message, ex.GetType().ToString());
			}



So the bit i'm stuck on now is how to get the last identity value from the customer table. I was trying to use the IDENT_CURRENT('Customer') but i get the error "This is not recognized by SQL Mobile". I'm using vs 2005 and SQLCE. Is there any other method that does this in SQLCE??

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;
using System.IO;
using System.Reflection;

namespace DeviceApplication1
{
	public partial class frmCars : Form
	{
		string connectionString = ("Data Source=" + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)) + "\\CarsGalore.sdf;Persist Security Info=False;");

		public frmCars()
		{
			InitializeComponent();
		}

		private void mnuContinue_Click(object sender, EventArgs e)
		{
			SqlCeConnection conn = new SqlCeConnection(connectionString);
			SqlCeCommand selectCommand = conn.CreateCommand();
			string select = "SELECT * FROM Customer";
			selectCommand.CommandText = select;
			SqlCeCommand insertCommand = conn.CreateCommand();
			string insert = "INSERT INTO Customer (No, Name, Address) VALUES (@no, @name,@address)";
			insertCommand.CommandText = insert;

			SqlCeDataAdapter customerDataAdapter = new SqlCeDataAdapter();
			customerDataAdapter.SelectCommand = new SqlCeCommand(select);
			customerDataAdapter.InsertCommand = new SqlCeCommand(insert);

			try
			{
				CarsGaloreDataSet.CustomerRow custRow = carsGaloreDataSet.Customer.NewCustomerRow();
				custRow.No = Convert.ToInt32(noTextBox.Text);
				custRow.Name = nameTextBox.Text;
				custRow.Address = addressTextBox.Text;
				carsGaloreDataSet.Customer.AddCustomerRow(custRow);
			}
			catch (Exception ex)
			{
				MessageBox.Show(ex.Message, ex.GetType().ToString());
			}

			try
			{
				customerTableAdapter.Update(carsGaloreDataSet.Customer);

				int custNo = (int)customerTableAdapter.GetLastIdentityValue();

				foreach (CarsGaloreDataSet.CustomerRow row in carsGaloreDataSet.Customer)
				{
					row.No = custNo;
				}

				customerTableAdapter.Update(carsGaloreDataSet.Customer);
			}
			catch(SqlCeException ex)
			{
				MessageBox.Show("Database error " + ex.Message, ex.GetType().ToString());
			}

			if ((rdbYes.Checked) == true)
			{
				Form hireForm = new frmHire();
				hireForm.Show();			   
			}
			else if ((rdbNo.Checked) == true)
			{
				this.Close();
			}
			else
			{
				MessageBox.Show("Please select yes or no!");
			}
		}

		private void mnuExit_Click(object sender, EventArgs e)
		{
			this.Close();
		}

		private void frmCars_Load(object sender, EventArgs e)
		{

		   
		}

	}
}


Was This Post Helpful? 0
  • +
  • -

#5 ThekillingGoku  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 20
  • Joined: 13-November 08

Re: no row at position 1 ?

Posted 03 December 2008 - 05:10 AM

Why not use stored procedures to call your db instead.
This way you can declare an output parameter which contains the last inserted value via '@@IDENTITY' or 'SCOPE_IDENTITY'.

I've not used the compact version of SQLServer before, but I'm sure this should be possible.
It's also good practice to keep SQL code in the DB anyway.
Was This Post Helpful? 0
  • +
  • -

#6 pansylea  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 29-November 08

Re: no row at position 1 ?

Posted 03 December 2008 - 11:06 AM

It's still not working, I'm trying to create a row to insert in to the customer table. The customer number, name and address. The customer number is the primary key, i dont think i set it to an identity field. I dont know what to do now, :( :(
Was This Post Helpful? 0
  • +
  • -

#7 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,957
  • Joined: 21-March 08

Re: no row at position 1 ?

Posted 03 December 2008 - 12:28 PM

View Postpansylea, on 3 Dec, 2008 - 01:06 PM, said:

It's still not working, I'm trying to create a row to insert in to the customer table. The customer number, name and address. The customer number is the primary key, i dont think i set it to an identity field. I dont know what to do now, :( :(


if you didn't set the field to an identity field, then SCOPE_IDENTITY or @@IDENTITY(which is what is supported by SQL CE) won't give you anything.
Was This Post Helpful? 0
  • +
  • -

#8 pansylea  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 29-November 08

Re: no row at position 1 ?

Posted 03 December 2008 - 12:47 PM

would anyone be able to give me some sample code on how to insert a row into a table with an insert statement??

i'm trying at the minute to use the query builder within vs 2005 to create one and then call it within the code.

Thanks
Was This Post Helpful? 0
  • +
  • -

#9 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,957
  • Joined: 21-March 08

Re: no row at position 1 ?

Posted 03 December 2008 - 01:19 PM

View Postpansylea, on 3 Dec, 2008 - 02:47 PM, said:

would anyone be able to give me some sample code on how to insert a row into a table with an insert statement??

i'm trying at the minute to use the query builder within vs 2005 to create one and then call it within the code.

Thanks


http://www.letmegoog...m/?q=sql+insert
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1