Using DB on multiple forms

Korsten

Hello,

I have a Visual Studio 2005 C# PPC program and I have some data I want to use on multiple forms.

I have to forms Form1 and Form2,

Form1 has a textbox and a button

Form2 has a textbox

Both these Forms are connected to the same database (db1)

When I fill the textbox (Form1) with a number and press the button I want to display form2 and the textbox on form 2 should display the Name that belongs with the number.

for instance:

database:

number name

1 a

2 b

3 c

4 d

5 e

etc.

when i then fill in on Form1 in a textbox number 3. On Form2 the textbox should display c.

The only thing displayed on Form1 is a textbox (to fill in the number) and a button (to check number and go to Form2 if it is a good number). Is this possible without a Datagrid on the Form

Hope my explanation is ok and hope someone can help

Korsten

Jeff Derstadt - MSFT

You can issue a SQL query to access the name column of the database and use the number column as parameter in the where clause:

In Form1, the logic when pushing the button could look like:

SqlConnection conn = ...;

conn.Open();

SqlCommand cmd = conn.CreateCommand();

cmd.CommandText = "SELECT * FROM table WHERE number = @num";

cmd.Parameters.Add(new SqlParameter("num", Int32.Parse(TexBox1.Text)));

string name = cmd.ExecuteScalar() as string;

form2.TextBox1.Text = name;

form2.ShowDialog();

Hope this helps.

Jeff Derstadt - MSFT

Korsten

Hello,

I don't know if this is the answer, because when i do this i get an error.

In the line:

SqlConnection conn = ....; I do SqlCeConnection conn = new SqlCeConnection("Data Source = path\\myDb.sdf")

when i do this i get an error during runtime: "SqlCeException was unhandled"

It says the path is not available but I know it is the good path

I have the SqlCe because i'm working on windows CE but that shouldn't be a problem

What am i doing wrong, what should i put in the new SqlCeConnection(.................)

Maybe worth mentioning in my database i have more collums then only 2, in my current I have 4 but that can be more.

Hope someone can help

Korsten

Jeff Derstadt - MSFT

Try adding a Try/Catch block around the exception to see if you can get any more details about the exception.

Also, if you are running this from within Visual Studio, keep in mind that by default the working/current path is the bin/Debug directory, and unless your path\myDb.sdf is copied there then the SqlCeConnection won't find it. You can change the working/current path by right-clicking on your project and choosing properties. In the Project Properties page, click on the "Debug" tab and change the "Working directory" to the path you expect. Have you tried using a full path to your database file

Jeff

Korsten

The problem with the path is gone, I'm working on a PPC so I had to go to the path of the DB on the PPC.

But now I have a new problem, when I continue the program gives a runtime error when I'm at the cmd.ExcecuteScalar() as string;

The error I get is:

SqlCeException was unhandled.

And the message with this error is: "There was an error parsing the query. [ Token line number = 1,Token line offset = 15,Token in error = table ]"

Can anyone help me

Btw, I don't know if this code will do what I want. As I read the ExcecuteScalar gets the value of the First collumn belonging to a row. If I'm wrong at this please tell me what it does and how this will help me

Korsten

Jeff Derstadt - MSFT

Sounds like you have an error in the syntax of your query string. What is the text of the query you are using

Jeff

Korsten

Hello Jef,

This is my entire code, rest is filled in by VS2005

Code Snippet

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;

namespace PpcDbTest

{

public partial class Form1 : Form

{

protected internal Form2 f2;

internal int vN;

internal string kN;

internal string rB;

internal string s;

public Form1()

{

InitializeComponent();

}

private void button1_Click(object sender, EventArgs e)

{

SqlCeDataReader rdr = null;

string connectionString = "Data Source = '\\Program Files\\PpcDbTest\\RouteDB.sdf'";

SqlCeConnection conn = new SqlCeConnection(connectionString);

SqlCeCommand cmd = conn.CreateCommand();

try

{

conn.Open();

cmd.CommandText = "SELECT * FROM table WHERE vN = @vNr";

cmd.Parameters.Add(new SqlCeParameter("vNr", Int32.Parse(textBox1.Text)));

rdr = cmd.ExecuteReader();

while (rdr.Read())

{

vN = (int)rdr["vN"];

kN = (string)rdr["k"];

rB = (string)rdr["rB"];

s = (string)rdr["s"];

}

if (f2 == null)

{

f2 = new Form2(this);

}

f2.Show();

this.Hide();

}

finally

{

if (rdr != null)

{

rdr.Close();

}

Hope this is enough information

Korsten

CommonGenius.com

"Table" is a reserved word in Sql. You should never create a table, or any other object, named "table". If you do not have control over the name of the table, refer to it in brackets, like this: "SELECT * FROM [table] ..."

Korsten

He Genius,

I've done what you told me, (I actually changed the tablename to dtable) but now it says i don't have the specified table.

Complete Runtime error is:

SqlCeException was unhandled

The specified table does not exist. [dtable]

Korsten

Code Snippet

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;

namespace PpcDbTest

{

public partial class Form1 : Form

{

protected internal Form2 f2;

internal int volgNr;

internal string klantNaam;

internal string redenBezoek;

internal string status;

public Form1()

{

InitializeComponent();

}

private void button1_Click(object sender, EventArgs e)

{

SqlCeDataReader rdr = null;

string connectionString = "Data Source = '\\Program Files\\PpcDbTest\\RouteDB.sdf'";

SqlCeConnection conn = new SqlCeConnection(connectionString);

SqlCeCommand cmd = conn.CreateCommand();

DataTable dtable = new DataTable("Route"); // HERE I CREATE THE dtable, "Route" is the name of the table in my database

try

{

conn.Open();

cmd.CommandText = "SELECT * FROM dtable WHERE volgNr = @vNr";

cmd.Parameters.Add(new SqlCeParameter("vNr", Int32.Parse(textBox1.Text)));

rdr = cmd.ExecuteReader();

while (rdr.Read())

{

volgNr = (int)rdr["volgNr"];

klantNaam = (string)rdr["klant"];

redenBezoek = (string)rdr["redenBezoek"];

status = (string)rdr["status"];

}

if (f2 == null)

{

f2 = new Form2(this);

}

f2.Show();

this.Hide();

}

finally

{

if (rdr != null)

{

rdr.Close();

}

Korsten

Hello,

Now I tried to immediatly acces the Route Table but now a other exception is given:

IndexOutOfRangeException was unhandled

An SqlCeParameter with ParameterName '@volgNr' is not contained by this SqlCeParameterCollection.

So now it checks the Table but it doesn't contain @volgNr (but this is in the Route Table)

Sorry to ask all these questions but i'm not experianced with databases.

But again, my code (as it is now)

Btw: all the exceptions are given on the rdr = cmd.ExecuteReader();

Hope someone can help

Korsten

Code Snippet

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;

namespace PpcDbTest

{

public partial class Form1 : Form

{

protected internal Form2 f2;

internal int volgNr;

internal string klantNaam;

internal string redenBezoek;

internal string status;

public Form1()

{

InitializeComponent();

}

private void button1_Click(object sender, EventArgs e)

{

SqlCeDataReader rdr = null;

string connectionString = "Data Source = '\\Program Files\\PpcDbTest\\RouteDB.sdf'";

SqlCeConnection conn = new SqlCeConnection(connectionString);

SqlCeCommand cmd = conn.CreateCommand();

try

{

conn.Open();

cmd.CommandText = "SELECT * FROM Route WHERE volgNr = @volgNr";

cmd.Parameters.Add(new SqlCeParameter("volgNr", Int32.Parse(textBox1.Text)));

rdr = cmd.ExecuteReader();

while (rdr.Read())

{

volgNr = (int)rdr["volgNr"];

klantNaam = (string)rdr["klant"];

redenBezoek = (string)rdr["redenBezoek"];

status = (string)rdr["status"];

}

if (f2 == null)

{

f2 = new Form2(this);

}

f2.Show();

this.Hide();

}

finally

{

if (rdr != null)

{

rdr.Close();

}

CommonGenius.com

The exception is telling you what is wrong. Your parameter should be named "@volgNr", not "volgNr".

Korsten

Thanx, that was what I needed, the coincidence is that I found it too already but forgot to post it on the forum.

But thanx all for the help

Korsten

CommonGenius.com

Glad you got it solved.

Using DB on multiple forms

Re: .NET Framework Data Access and Storage Using DB on multiple forms

Re: .NET Framework Data Access and Storage Using DB on multiple forms

Re: .NET Framework Data Access and Storage Using DB on multiple forms

Re: .NET Framework Data Access and Storage Using DB on multiple forms

Re: .NET Framework Data Access and Storage Using DB on multiple forms

Re: .NET Framework Data Access and Storage Using DB on multiple forms

Re: .NET Framework Data Access and Storage Using DB on multiple forms

Re: .NET Framework Data Access and Storage Using DB on multiple forms

Re: .NET Framework Data Access and Storage Using DB on multiple forms

Re: .NET Framework Data Access and Storage Using DB on multiple forms

Re: .NET Framework Data Access and Storage Using DB on multiple forms

Re: .NET Framework Data Access and Storage Using DB on multiple forms