How to get data in a SQL Server foreign key using asp.net?

Question

This question is somewhat similar and related to my previous question How to get data in an sql foreign key using asp.net entity framework? so I will be using the same SQL Server tables.

This time, instead of accessing the foreign key value through Entity Framework, I want to know how to do it in a regular SQL script like this (take note, the script that I'm talking about is inside the string.Format):

public static void getRowNumber(string brand)
{
    string query = string.Format("SELECT COUNT(*) FROM stringInstrumentItem WHERE brandId LIKE @brand");

    conn1.Open();

    command1.CommandText = query;
    command1.Parameters.Add(new SqlParameter("brand", brand));

    Int32 count = (Int32)command1.ExecuteScalar();

    conn1.Close();

    command1.Parameters.Clear();

    AddASPXAndCSFile.X = count;
}

Just to recap, I have two SQL Server tables called stringInstrumentItem and brand. I created the foreign key in the table stringInstrumentItem referring to the primary key of the table brand. So I'm attempting to access the column name in table brand through stringInstrumentItem.

As you can see, I'm attempting to access the value in table brand through brandId, which does not work.

Hope you guys can help me on this one. Cheers!

What's the datatype of brandId? Usually an ID column is int. — SS_DBA
– SS_DBA, Commented Jun 29, 2017 at 14:57
I believe you need the @ sign in this new SqlParameter("@brand", brand) to access the parameter. — SS_DBA
– SS_DBA, Commented Jun 29, 2017 at 14:58
@WEI_DBA - yes its an integer but i want to access the field name in brand..how do i achieve that? — RockStar
– RockStar, Commented Jun 29, 2017 at 15:00

Win · Accepted Answer · 2017-06-29 15:06:46Z

2

If I understand your question correctly, it seems like a simple query -

SELECT COUNT(*) FROM stringInstrumentItem 
WHERE brandId IN (SELECT brandId FROM brand WHERE name LIKE @brand)

Please make use you have @ and % (based on your requirement).

command1.Parameters.Add(new SqlParameter("@brand", "%" + brand + "%"));

edited Jun 29, 2017 at 15:06

answered Jun 29, 2017 at 15:00

Win

62.4k13 gold badges106 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

RockStar Over a year ago

oh I see. I was not aware that you can nest the sql script like that. cool info.

RockStar Over a year ago

Hi! If your interested in answering another question, here's the link stackoverflow.com/questions/44847998/…

RockStar Over a year ago

Hi Win! unfortunately i have deleted the question regarding the foreign key because i decided to try other solutions but if your interested for more questions, i have another one up, check it out if your available stackoverflow.com/questions/44872117/…

Collectives™ on Stack Overflow

How to get data in a SQL Server foreign key using asp.net?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related