C#•13mo ago

✅ SQLConnection and queries to the database

Hey there! I've been having this problem lately with something i'm writing, which essentially boils down to the following code:

String conn = $@"Server={server};Database={database};Trusted_Connection=True;";
this.Connection = new SqlConnection(connectionString);
this.Connection.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM Alunos", this.Connection);
SqlDataReader reader = cmd.ExecuteReader();

String conn = $@"Server={server};Database={database};Trusted_Connection=True;";
this.Connection = new SqlConnection(connectionString);
this.Connection.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM Alunos", this.Connection);
SqlDataReader reader = cmd.ExecuteReader();

When I run this, it seems to just not find my Alunos table, and I end up having to write [{database}].[dbo].[Alunos] even though I had specified that I wanted to start out with a database in my connection string. What am I doing wrong here? I don't want to have to write my queries with dbo.(...); I'd rather just switch to the database I want at runtime and then query a table directly 😅

19 Replies

bin01ary•13mo ago

When you do a select statement, normally it would be [{database}].[dbo].[Alunos] not dbo.[{database}.Alunos]. Your database name should come before the schema name (dbo). I'm guessing something is wrong with the way your database is setup.

Unknown User•13mo ago

Message Not Public

bin01ary•13mo ago

I was referring to when they mentioned dbo.[{database}.Alunos]

Unknown User•13mo ago

Message Not Public

AlexOP•13mo ago

Oh- right I was just stupid and wrote it wrong I'll edit it

AlexOP•13mo ago

That is how its written, I just copied it wrong lol

AlexOP•13mo ago

Oh wait no it isn't because there's Escola.Alunos at the end still

bin01ary•13mo ago

The other thing I can suggest is change the first line

String conn = $@"Server={server};Database={database};Trusted_Connection=True;";

String conn = $@"Server={server};Database={database};Trusted_Connection=True;";

so you just use $ instead of $@

String conn = $"Server={server};Database={database};Trusted_Connection=True;";

String conn = $"Server={server};Database={database};Trusted_Connection=True;";

assuming you have variables for server and database

AlexOP•13mo ago

Yep, I do, and that didn't seem to work, but after getting your insight... I think I know what's happening But just to confirm it...

CREATE TABLE [Escola.Alunos] (
    NumeroInterno INT IDENTITY (1,1) NOT NULL,
    Nome VARCHAR(50) NOT NULL,
    Idade INT NOT NULL,
    Localidade VARCHAR(50) NOT NULL,

    PRIMARY KEY(NumeroInterno)
)

CREATE TABLE [Escola.Alunos] (
    NumeroInterno INT IDENTITY (1,1) NOT NULL,
    Nome VARCHAR(50) NOT NULL,
    Idade INT NOT NULL,
    Localidade VARCHAR(50) NOT NULL,

    PRIMARY KEY(NumeroInterno)
)

When ... I create a table like this... I'm creating a table named "Escola.Alunos" right?

bin01ary•13mo ago

yep. Escola.Alunos is now your table name.

AlexOP•13mo ago

God damnit. Thank you so much That's embarassing

bin01ary•13mo ago

it happens to all of us

AlexOP•13mo ago

It should be [Escola].[Alunos] right?

bin01ary•13mo ago

is Escola your database name?

AlexOP•13mo ago

Yeah

bin01ary•13mo ago

you can just do Alunos if you are running the create statement in the Escola database then you can SELECT * FROM Alunos if you are connected to the Escola database

AlexOP•13mo ago

Yep, thank you!

bin01ary•13mo ago

$close

MODiX•13mo ago

Use the /close command to mark a forum thread as answered

Gaming

Programming

✅ SQLConnection and queries to the database