C# Discussion Boards - CodeProject

Re: Programatically Connect to SQL Server instance After the VPN Connection is Established

14-Apr-10 22:48

Vuyiswa Maseko wrote:
Please look at this Document here , it has screen shots of the settings

Cool, that saves some questions. You probably also tried replacing the computername with an IP-address/port number combination?

Last thing to verify would be the Sql Browser Service[^].

What VPN-software are you using?

I are Troll Suspicious | :suss:

Vimalsoft(Pty) Ltd15-Apr-10 21:51

Vimalsoft(Pty) Ltd

15-Apr-10 21:51

Good Day Eddy

i have enabled the SQl Browser and changed my Connection string to be something like this

Data Source=tcp:196.211.164.122\ABC\SUTSVR,1433;Initial Catalog=pubs;User ID=sa;Password=OOPS

and i was able to connect to the SQL Server, and i tried to run an insert on a test table, and i found errors that says the stored procedure does not exist. Because i have SQL 2000 that was installed a long time ago and i have a SQL 2008 instance, i assume it trying to connect to SQL 2000 as it might be a default instance. What i did to confirm this i wrote a query that will return the version of SQL and bind it to a Label and it came back as SQL 2000 and now my problem is that i used the correct instance name, not the SQL 2000 instance name, but it connect to SQL 2000 not SQL 2008.The SQl 2000 and SQL 2008 uses the same port 1433, how can i make SQL 2008 a default instance, because the Instance name of the SQL2000 is equal to the name of the Server (ABC)and the SQl 2008 instance is ABC\SUTSVR how do i change the default to SQL

Re: Programatically Connect to SQL Server instance After the VPN Connection is Established

Eddy Vluggen16-Apr-10 2:34

Re: Programatically Connect to SQL Server instance After the VPN Connection is Established

16-Apr-10 2:34

Both Sql Servers are installed on the same machine? There can be only one default instance[^], so if the other is on the same machine, then this will be a named instance.

The "default" instance is just the server-name without anything special. If you installed a named instance, then you have to specify that name when connection, like Server\SQLEXPRESS.

AFAIK, you cannot change the instancenames on Sql2000 or 2005 without reinstalling the server. In Sql2008, it should be possible to change the name.

I are Troll Suspicious | :suss:

Vimalsoft(Pty) Ltd18-Apr-10 20:52

Vimalsoft(Pty) Ltd

18-Apr-10 20:52

Good Day Eddy

I have changed my Connection string to this

"Data Source=tcp:196.211.124.132\XCDSVR,1433;Initial Catalog=pubs;User ID=sa;Password=oops;";

and it works.

Thank you for your Help

Kind Regards

Connect to my database csharp smart device

Tunisien8614-Apr-10 2:41

14-Apr-10 2:41

Hi,
I develop a charp smart device application.
I tried to connect my Formconnexion but doesn't work for me:

using System;
using System.Linq;
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 ModeDifféré
{
    public partial class Connexion : Form
    {
        public Connexion()
        {
            InitializeComponent();
        }

        private void Connexion_load(object sender, EventArgs e)
        {
            createconnexion();
        }

        private void createconnexion()
        {
            /*string chbd= Environment. + "\\BaseGmaoLocale.sdf";
            //string chcon= "Data source=\'[0]'\ BaseGmaoLocale.sdf;";
            SqlCeConnection sqlceconn= new SqlCeConnection(chcon);
            sqlceconn.Open();*/
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string chcon = null;
            SqlCeCommand command;
            SqlCeDataAdapter adapter = new SqlCeDataAdapter();
            adapter.TableMappings.Add("Table", "Connexion");
            DataSet ds = new DataSet();
            string sql = null;
            string login,MotPass;
            sql = "Select login from Connexion where login=@login and MotPass=@MotPass ";
            string chcc= "BaseGmaoLocale.sdf;";
            SqlCeConnection sqlceconn= new SqlCeConnection(chcc);
            sqlceconn.Open();
            try
            {
                  sqlceconn.Open();
                command = new SqlCeCommand(sql, sqlceconn);
                adapter.SelectCommand = command;
                adapter.Fill(ds, "SQL Temp Table");
                adapter.Dispose();
                command.Dispose();
                sqlceconn.Close();

                MessageBox.Show("Number of row(s) - " + ds.Tables[0].Rows.Count);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Can not open connection ! ");
            }
            if (ds.Tables[0].Rows.Count != 0)
            {
                MessageBox.Show("Authentification réussie");
                MenuP m = new MenuP();
                m.Show();
            }
            else
            {
                MessageBox.Show("Login ou mot de passe incorrect veuillez réessayer");
            }
        }
    }
}

4 notifications appears:

Warning	1	The variable 'chcon' is assigned but its value is never used	C:\Users\Admin\Documents\Visual Studio 2008\Projects\GMAOMobile\ModeDifféré\Connexion.cs	
Warning	2	The variable 'login' is declared but never used	C:\Users\Admin\Documents\Visual Studio 2008\Projects\GMAOMobile\ModeDifféré\Connexion.cs	
Warning	3	The variable 'MotPass' is declared but never used	C:\Users\Admin\Documents\Visual Studio 2008\Projects\GMAOMobile\ModeDifféré\Connexion.cs	
Warning	4	The variable 'ex' is declared but never used	C:\Users\Admin\Documents\Visual Studio 2008\Projects\GMAOMobile\ModeDifféré\Connexion.cs

When running it tells me there are an error in the connexion string chcc
Can u help me
Thanks

Eddy Vluggen14-Apr-10 2:58

14-Apr-10 2:58

Tunisien86 wrote:
When running it tells me there are an error in the connexion string chcc

A connectionstring should contain more than just a filename. Start your research here[^] Smile | :)

I are Troll Suspicious | :suss:

Tunisien8614-Apr-10 3:30

14-Apr-10 3:30

Hi,
As I read in the link u give me,I replace my string connexion by:

string chcc = "Data Source=BaseGmaoLocale.sdf;Persist Security Info=False;";

My database is a local database,when running the 4 notifications still and an sqlexception apperas:

The database file cannot be found. Check the path to the database. [ Data Source = BaseGmaoLocale.sdf ]

Any help will be useful.
Thanks

Eddy Vluggen14-Apr-10 3:34

14-Apr-10 3:34

Tunisien86 wrote:
when running the 4 notifications still

Yup. As the notifications say, you have declared variables without using them. If you don't want the notification, then remove those declarations.

Tunisien86 wrote:
and an sqlexception apperas

It's saying that it cannot find the database. You'll need to give a path + filename, or put the database-file in the current directory Smile | :)

I are Troll Suspicious | :suss:

Tunisien8614-Apr-10 4:28

14-Apr-10 4:28

Hi,
I did as u told me like that:

string chcc = "Data Source=C:\\Users\\Admin\\Documents\\Visual Studio 2008\\Projects\\GMAOMobile\\ModeDifféré\BaseGmaoLocale.sdf;Persist Security Info=False;";

But still the same error.
Also,I don't understand u note:

put the database-file in the current directory

Thank u for u contribution

Eddy Vluggen14-Apr-10 4:48

14-Apr-10 4:48

Tunisien86 wrote:
But still the same error.

That's a location on your development-PC, whereas your application is looking in a location on your mobile device. To what path are you deploying your database? Try someting like this;

string connectionString = @"Data Source='Mobile Device\Program Files\MyApplication\Orders.sdf';"

Tunisien86 wrote:
Also,I don't understand u note:

If you don't add a path then Windows will assume the "current working directory". That's initially the location from where you started the application.

I are Troll Suspicious | :suss:

Tunisien8614-Apr-10 4:50

14-Apr-10 4:50

Hi,
I depass the problem of the database file by adding this:

string chcc = "Data Source=C:\\Users\\Admin\\Documents\\Visual Studio 2008\\Projects\\GMAOMobile\\ModeDifféré\\BaseGmaoLocale.sdf;Persist Security Info=False;";

and also the step:

adapter.Fill(ds);

But a new sqlexception appears:

cannot found table 0

and althought the login and pasword are my database .sdf,always"can not open connection"
Help me
Thanks

Eddy Vluggen14-Apr-10 5:02

14-Apr-10 5:02

Tunisien86 wrote:
and also the step

That's a different step from your original code, which holds the line

adapter.Fill(ds, "SQL Temp Table");

Check your select-statement, or post it here.

I are Troll Suspicious | :suss:

Tunisien8614-Apr-10 5:13

14-Apr-10 5:13

Hi,
my select statement is:

sql = "Select login from Connexion where login=@login and MotPass=@MotPass ";

Also i wanna now how to deal with the result of the

ds.Tables("Connexion").Rows.Count) - 1

Thank u

Eddy Vluggen14-Apr-10 5:29

14-Apr-10 5:29

Tunisien86 wrote:
Select login from Connexion where login=@login and MotPass=@MotPass

Doesn't this statement return NULL if the login doesn't exist? Btw, filling a dataset is a bit superfluous if you're only interested in the result of the query. There's a ExecuteScalar method that returns the first value of the first row.

Tunisien86 wrote:
Also i wanna now how to deal with the result of the

I do not understand what you are asking; that line in the original post evaluates an expression to a boolean, and takes an action based on that bool.

I are Troll Suspicious | :suss:

Tunisien8619-Apr-10 23:02

19-Apr-10 23:02

Hi,
This is the code that solved my problem:

private void button1_Click(object sender, EventArgs e)
        {

            /*string req = "select Login, MotPasse from Connexion";
            DataTable tab = new DataTable();
            SqlCeCommand com = new SqlCeCommand(req, sqlceconn);
            SqlCeDataAdapter ada = new SqlCeDataAdapter(com);
            ada.Fill(tab);*/
            //Create the connection
            string wCS = @"Data Source =\Storage Card\ModeDifféré\BaseGmaoLocale.sdf;";
            SqlCeConnection sqlceconn = new SqlCeConnection(wCS);

            //Create the command
            SqlCeCommand command = sqlceconn.CreateCommand();
            command.CommandText = "Select login from Connexion where Login=@Login and MotPasse=@MotPasse ";

            //Add the parameters
            string s1=textBox1.Text; //store the login name here YOU MUST FILL THIS IN
            SqlCeParameter Login = new SqlCeParameter("@Login", SqlDbType.NVarChar);
            Login.Value = s1;

            string s2=textBox2.Text; //store the password here YOU MUST FILL THIS IN
            SqlCeParameter MotPasse = new SqlCeParameter("@MotPasse", SqlDbType.NVarChar);
            MotPasse.Value = s2;

            command.Parameters.Add(Login);
            command.Parameters.Add(MotPasse);

            //Create the adapter
            SqlCeDataAdapter adapter = new SqlCeDataAdapter(command);

            //Create and fill the dataset
            DataSet ds = new DataSet();

            try
            {
                adapter.Fill(ds, "SQL Temp Table");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            if ((ds.Tables.Count > 0) && (ds.Tables["SQL Temp Table"] != null))
            {MessageBox.Show("Number of row(s) - " + ds.Tables["SQL Temp Table"].Rows.Count);
                if(ds.Tables["SQL Temp Table"].Rows.Count>0)
                { MessageBox.Show("Authentification réussie");
                MenuP m = new MenuP();
                m.Show();}
                else MessageBox.Show("Login ou mot de passe incorrect veuillez réessayer");}
            adapter.Dispose();
            sqlceconn.Dispose();
            command.Dispose();



        }

Thank u a lot for u contribution Smile | :)

Eddy Vluggen20-Apr-10 1:38