Database Discussion Boards

Re: Connection string loaded from config file

Nigel Mackay31-Dec-09 19:32

31-Dec-09 19:32

I would have thought so. I have just written test code to return cnStr, and I find that dataSource is NIGELPC\\\\SQLEXPRESS!
Wrote a snippet to remove the extra \\ and now it works.

SQL Server 2005 Express edition Registration.

TinyDevices30-Dec-09 1:41

TinyDevices

30-Dec-09 1:41

Hi,
I'm trying to register SQL Server Express edition engine into Network. I've checked everything, but I'm not able to find it in the servers list. What are the things I should check more?
I check TCP/IP & Named pipes connections,
Enabled Remote connections,
*Restarted the service*
**Restarted the machine**!

But nothing works. May be that's what you call Express edition?

Re: SQL Server 2005 Express edition Registration.

Andy_L_J30-Dec-09 3:23

Andy_L_J

30-Dec-09 3:23

Was it a default instance?

Should be ServerName\SQLEXPRESS from memory.

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

Re: SQL Server 2005 Express edition Registration.

TinyDevices30-Dec-09 19:32

TinyDevices

30-Dec-09 19:32

Yes this is what is shown when I browse for SQL servers in the n/w through Management studio.
It gets displayed in the servers list in the local Mgmt studio. But when I open the studio from another machine, I'm not able to see. And it's vice-versa for the DB server that's hosted in the other machine. I'm not able to see it from my mgmt studio. But it gets displayed on his own mgmgt studio. You get it?

Re: SQL Server 2005 Express edition Registration.

Eddy Vluggen30-Dec-09 6:04

Eddy Vluggen

30-Dec-09 6:04

ConquistadorEx wrote:
I'm trying to register SQL Server Express edition engine into Network.

You'd have to explain that a bit. Once installed, all should be fine. Where are you registering it? What application?

ConquistadorEx wrote:
What are the things I should check more?

Whether the SQL Browser service is running. Check MSDN[^]. Also, check your firewalls, and try to connect to the database using SQL Management Studio or ISql.exe.

I are Troll Suspicious | :suss:

Re: SQL Server 2005 Express edition Registration.

TinyDevices30-Dec-09 19:34

TinyDevices

30-Dec-09 19:34

Please check my reply above. I'm just right clicking on the server instance displayed in the Mgmt Studio then selecting "Register".

Re: SQL Server 2005 Express edition Registration.

Eddy Vluggen31-Dec-09 5:02

Eddy Vluggen

31-Dec-09 5:02

Usually it's just a matter of installing, enabling remote connections if at all required, and done Smile | :)

Is your Windows-account allowed to see the other server? Are you on the same domain?

I are Troll Suspicious | :suss:

C# SQL express Login

kadaen29-Dec-09 22:56

kadaen

29-Dec-09 22:56

i am working on a project and one section of the program is password prohibitied, i am using visual studio 2010 coding in C# and using SQL express database, the project is working perfectly the only i can not seem to figure out is how to create the login form ... ex. you open the project then click on personal information, window pops up asking for username and password, if the information you neter matches the information that is in the database you will be able to continue if the information is wrong a messagebox will show up saying invalid entry

been looking all over the net for the problem im having but not having any luck with this issue

here is what i have so far

<code>
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace EasyStorePro
{
    public partial class LoginForm : Form
    {
        public LoginForm()
        {
            InitializeComponent();
        }

        private void btnLogin_Click(object sender, EventArgs e)
        {
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();

            conn.ConnectionString = "Data Source=KADAEN-PC\\SQLEXPRESS;Initial Catalog=EasyStorePro;Persist Security Info=True;User ID=************;Password=**********";
            string str = ("SELECT username,password FROM dbo.Login WHERE username='"+ tbUsername + "' and password='"+ mtbPassword +"'");

            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(str, conn);

            conn.Open();

            DataTable dt = new DataTable();
            System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);

            da.Fill(dt);

            if (dt.Rows.Count > 0)
            {
                MessageBox.Show("Welcome");
            }
            else
            {
                MessageBox.Show("Invalid Entry");
            }

            conn.Close();
        }
    }
}

</code>

Thank you for your time

Re: C# SQL express Login

nagendrathecoder29-Dec-09 23:18

nagendrathecoder

29-Dec-09 23:18

How this is a database related question? D'Oh! | :doh:

BTW, you can show the Login form as dialog using Form_Name.ShowDialog().
It'll open up as a popup dialog.

Re: C# SQL express Login

kadaen29-Dec-09 23:25

kadaen

29-Dec-09 23:25

I figured it was a database question, the problem lies in my program connecting and verifying data entered by the user to the information that is the database, I don’t have any problems bringing up the login form, the only complications I am having is with the username & password being verified with the information in the database and allowing the user to continue to their destination as long as all information entered is correct.

Re: C# SQL express Login

nagendrathecoder29-Dec-09 23:33

nagendrathecoder

29-Dec-09 23:33

ok, are you getting any error?
Exactly what problem are you facing?

Re: C# SQL express Login

kadaen29-Dec-09 23:43

kadaen

29-Dec-09 23:43

when I load up the program and open the login form, I input the username and password no error pops up just the messagebox, that says invalid entry, but the information i entered is the exact same information I put into the database itself. i am thinking my problem lies within the if statement but i could be wrong

if (dt.Rows.Count > 0)
           {
               MessageBox.Show("Welcome");
           }
           else
           {
               MessageBox.Show("Invalid Entry");
           }

i should be getting the messagebox that states Welcome, but im not.

Re: C# SQL express Login

nagendrathecoder29-Dec-09 23:44

nagendrathecoder

29-Dec-09 23:44

Try using this

string str = "select count(*)from Login where username='"+ tbUsername + "' and password='"+ mtbPassword +"'");

System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(str, conn);

conn.Open();

int rows = cmd.ExecuteScalar();

if (row > 0)
{
    MessageBox.Show"Welcome");
}
else
{
    MessageBox.Show("Invalid Entry");
}            
conn.Close();

Re: C# SQL express Login

kadaen29-Dec-09 23:54

kadaen

29-Dec-09 23:54

i get the following error when trying to run the program

Error 1 Cannot implicitly convert type 'object' to 'int'. An explicit conversion exists (are you missing a cast?)

Re: C# SQL express Login

nagendrathecoder29-Dec-09 23:58

nagendrathecoder

29-Dec-09 23:58

Ofcourse you have to cast it to int, i missed it and you too.

int row = Convert.ToInt32(cmd.ExecuteScalar());

Re: C# SQL express Login

kadaen30-Dec-09 0:06

kadaen

30-Dec-09 0:06

lol yeah i just converted it then seen your reply, ok the program runs now with no errors

but i still get the Invalid Entry MessageBox when inputting the username and password, i even added a new entry to the database and still get the same thing =/ .. this has had be stumped for the past few days

Re: C# SQL express Login

nagendrathecoder30-Dec-09 0:13

nagendrathecoder

30-Dec-09 0:13

Trace the program, see what is the value of "str" before executing the command.

Re: C# SQL express Login

kadaen30-Dec-09 2:58

kadaen

30-Dec-09 2:58

I looked at it, the str is the string for

SELECT username,password FROM Login WHERE username='" + tbUsername + "' and password='" + mtbPassword + "'"

i removed the string for this and put it directly into the cmd section...

System.Data.SqlClient.SqlCommand cmd;
            cmd = new System.Data.SqlClient.SqlCommand("SELECT username,password FROM Login WHERE username='" + tbUsername + "' and password='" + mtbPassword + "'", conn);

still not having any luck getting it to work.

thank you for time

Re: C# SQL express Login

nagendrathecoder30-Dec-09 17:25

nagendrathecoder

30-Dec-09 17:25

I said you to see the value of "str" after tbUsername & mtbPassword is binding with it.
Maybe something is wrong with those values.

Re: C# SQL express Login

loyal ginger30-Dec-09 15:41

loyal ginger

30-Dec-09 15:41

Just set a break point after the SELECT query is constructed. When it breaks at that point, copy the value of the string out and paste it somewhere else to execute the query to see the result. You may figure out the problem that way.

Re: C# SQL express Login

c0der200930-Dec-09 23:01

c0der2009

30-Dec-09 23:01

I too think this is the way to go.

Step 1:
First you have to make sure the connection string is correct.

Try this.

string str = ("SELECT username,password FROM dbo.Login ");<br />
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(str, conn);            <br />
conn.Open();            <br />
DataTable dt = new DataTable();            System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);<br />
da.Fill(dt);

then debug and see if the da has all details from the database table.

Step 2:
If step 1 was successful, use your program, debug and see what is the value of str just before the query is fired. Execute that query directly from Query Analyzer and verify the results.

Re: C# SQL express Login

kadaen31-Dec-09 4:14

kadaen

31-Dec-09 4:14

ok with that i get an error due to no connection string so i add in

conn.ConnectionString = "Data Source=KADAEN-PC\\SQLEXPRESS;Initial Catalog=EasyStorePro;Persist Security Info=True;User ID=********;Password=***********";

and it runs i think my main problem is in the if statment section, more or less getting the textbox to verify itself with the information in the database

so something like
password is equal to mtbPassword.Text

create & update account numbersfor existing records

Jassim Rahma29-Dec-09 22:52

Jassim Rahma

29-Dec-09 22:52

I have the following code to update the account number in the account summary & clients tables when creating a new client profile but I have a major problem now with existing records as the account concept was proposed lately.. I want to know how can I use the same code to loop through all existing (already created) records and define an account number for it?

note: I want a random weekday and dayofyear so not all records will have the same weekday creation..

INSERT INTO clients (company_id, client_name, client_cpr, client_passport, client_gender, client_nationality, client_date_of_birth, client_visa_number, client_visa_expiry, client_workpermit_number, client_workpermit_expiry, client_telephone, client_fax, client_mobile, send_sms, client_occupation, client_email, send_emails, client_salary, home_country_address, client_is_vip, client_is_blacklist) VALUES (@company_id, @client_name, @client_cpr, @client_passport, @client_gender, @client_nationality, @client_date_of_birth, @client_visa_number, @client_visa_expiry, @client_workpermit_number, @client_workpermit_expiry, @client_telephone, @client_fax, @client_mobile, @send_sms, @client_occupation, @client_email, @send_emails, @client_salary, @home_country_address, @client_is_vip, @client_is_blacklist)
SET @record_identity = SCOPE_IDENTITY()
INSERT INTO accounts_summary (client_category, client_id, cr_cpr_number, account_number, account_name) VALUES ('I', @record_identity, @client_cpr, SCOPE_IDENTITY(), @client_name)
SET @account_number_string = RIGHT('0' + convert(varchar(1), DATEPART(WEEKDAY, GETDATE())), 1)
SET @account_number_string = @account_number_string + RIGHT('000' + convert(varchar(3), DATEPART(DAYOFYEAR, GETDATE())), 3)
SET @account_number_string = @account_number_string + RIGHT('0000000' + convert(varchar(7), SCOPE_IDENTITY()), 7)
SET @account_number = convert(bigint, @account_number_string)
UPDATE accounts_summary SET account_number = @account_number WHERE account_id = SCOPE_IDENTITY()
UPDATE clients SET account_number = @account_number WHERE client_id = @record_identity

Re: create & update account numbersfor existing records

Andy_L_J30-Dec-09 3:21

Andy_L_J

30-Dec-09 3:21

A note,

No one will even look at this question until you edit the sql so that it fits within the page.

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

Re: create & update account numbersfor existing records

Jassim Rahma31-Dec-09 2:01

Jassim Rahma

31-Dec-09 2:01

I tried this code but it's looping forever and adding the first record from the clients into the accounts_summary thougsands of times.. why?

USE takhlees

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON

DELETE FROM accounts_summary

-- reset primary key

INSERT INTO accounts_summary (account_number, account_name, core_account, is_read_only) VALUES (10000000001, 'CASH', 'True', 'True')
INSERT INTO accounts_summary (account_number, account_name, core_account, is_read_only) VALUES (10000000002, 'ACCOUNTS RECEIVABLE', 'True', 'True')

DECLARE @client_id int, @client_cpr varchar(50), @client_name varchar(255), @account_number_string VARCHAR(50), @account_number numeric(38,0)

DECLARE		account_numbers CURSOR FOR
SELECT		clients.client_id, clients.client_cpr, clients.client_name
FROM		clients
ORDER BY	clients.client_name

OPEN account_numbers

FETCH NEXT FROM account_numbers INTO @client_id, @client_cpr, @client_name

WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO accounts_summary (client_category, client_id, cr_cpr_number, account_number, account_name) VALUES ('I', @client_id, @client_cpr, SCOPE_IDENTITY(), @client_name)
	-- get random from 1 to 7
	SET @account_number_string = RIGHT('0' + convert(varchar(1), DATEPART(WEEKDAY, GETDATE())), 1)
	-- get random from 1 to 365
	SET @account_number_string = @account_number_string + RIGHT('000' + convert(varchar(3), DATEPART(DAYOFYEAR, GETDATE())), 3)
	SET @account_number_string = @account_number_string + RIGHT('0000000' + convert(varchar(7), SCOPE_IDENTITY()), 7)
	SET @account_number = convert(bigint, @account_number_string)
	UPDATE accounts_summary SET account_number = @account_number WHERE account_id = SCOPE_IDENTITY()
	UPDATE clients SET account_number = @account_number WHERE client_id = @client_id
	
	FETCH NEXT FROM account_numbers INTO @client_id, @client_cpr, @client_name
END

CLOSE account_numbers
DEALLOCATE account_numbers

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.