0

I wrote some code to access the id form table dbo.details by using user email id stored in session, but I get this error

The multi-part identifier "abc@gmail.com" could not be bound

I have used the built-in server of Visual Studio 2017.

This is my code:

protected void Page_Load(object sender, EventArgs e)
{
        if (Session["user"] == null)
        {
            Response.Write("<script>alert('you have to login to Checkout!')</script>");
            Response.Redirect("login.aspx");
        }
        else
        {
            string S1 = Convert.ToString(Session["user"].ToString());

            SqlConnection scon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            String myquery = "select ID from dbo.details where email=" + S1;

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = myquery;
            cmd.Connection = scon;

            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;

            DataSet ds = new DataSet();
            da.Fill(ds);

            int details_id = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
            Response.Write(details_id);
        }
}

I have checked all the names and they are ok.

I don't know what to do now!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jay Bobade
  • 3
  • 2
  • 5
    Don't use SQL Injection-prone code and the problem will 'solve itself'. Search for "C# SQL parameters" and update the code accordingly. – user2864740 Oct 04 '18 at 16:56
  • When you build your string, you need to enclose the email in single quotes. But even that's still not a good idea, you should be using parameterized queries. – Duston Oct 04 '18 at 16:57
  • 2
    Details about the *why* this occurs (and how to avoid it) are given in https://stackoverflow.com/q/7505808/2864740 – user2864740 Oct 04 '18 at 16:59
  • Some tutorials/explanation sites (using the previous search suggestion): https://csharp-station.com/Tutorial/AdoDotNet/Lesson06 , https://www.dreamincode.net/forums/topic/268104-the-right-way-to-query-a-database-parameterizing-your-sql-queries/ , etc. – user2864740 Oct 04 '18 at 17:01
  • 1
    SOL Parameters worked Great. – Jay Bobade Oct 04 '18 at 17:21
  • try this String myquery = "select ID from dbo.details where email='" + S1;+"'" – Murat Can OĞUZHAN Oct 04 '18 at 17:40
  • @MuratCanOĞUZHAN that is terrible advice. They be using parameters, not building up a sql injection vulnerable string. – Sean Lange Oct 04 '18 at 18:44
  • @SeanLange he asked his error. In this question "parameterised queries" is additional info or advice. – Murat Can OĞUZHAN Oct 05 '18 at 19:27
  • 1
    @MuratCanOĞUZHAN sure you can take that approach but not helping somebody fix such a MAJOR problem is just perpetuating that coding style which truly horrible. – Sean Lange Oct 08 '18 at 13:25
  • @SeanLange Yes you are right on that point. Thanks to defend the truth. – Murat Can OĞUZHAN Oct 10 '18 at 19:48

2 Answers2

0

Use string.Format() method with single quotation mark

string _user= Convert.ToString(Session["user"].ToString());

String SQL = string.Format("select ID from dbo.details where email='{0}'", _user);
Md. Nazmul Nadim
  • 207
  • 2
  • 5
-4

The root cause of the error is this line -

String myquery = "select ID from dbo.details where email=" + S1;

it will build the query as 

select ID from dbo.details where email=abc@gmail.com;

Now if you run this query, you will get exception as email is suppose to be string enclosed in single quotes. So change your myQuery to add single quotes around email like below .

String myquery = "select ID from dbo.details where email='" + S1 + "'";

But, its always recommended to use parameterised queries. to avoid SQL injections.

Vinit
  • 2,540
  • 1
  • 15
  • 22
  • 2
    Would be a much better answer if you demonstrated how to use parameters instead of demonstrating the worst possible way to write sql. – Sean Lange Oct 04 '18 at 18:43
  • https://stackoverflow.com/questions/7505808 and https://stackoverflow.com/questions/5468425 are both linked to the question.. – user2864740 Oct 04 '18 at 22:36
  • wondering why the answer got a downvote, I explained why the OP is getting the exception and provided options to fix the issue including a recommended approach by sharing link which has a good explanation of using paramaterised queries. – Vinit Oct 04 '18 at 23:31
  • It wasn't my downvote but if you added the 2-3 lines of code to demonstrate parameters it would be worthy of an upvote. – Sean Lange Oct 08 '18 at 13:27