Executing query in c# asp.net

Question

I've created a query to select the body of a message from the message database. I'm not sure how to execute it to get the body string back and store it. I've tried using ExecuteReader, ExecuteScalar, and ExecuteNonQuery. None of them work. ExecuteScalar was the closest to working but it only returned the body of the message of the first row no matter which row you were trying to view. Anyone know how to do this? It's gotta be a easy fix.

  SqlCommand com = new SqlCommand("SELECT Body FROM Messages WHERE MessageID=               MessageID", conn);
  com.Connection = conn;
  com.Connection.Open();
  String body;
  body = com.ExecuteScalar.ToString;

That's what I have now. Thanks in advance!

Anuraj · Accepted Answer · 2012-08-07 01:15:36Z

5

What is messageId in your query? You should be doing something like this

SqlCommand com = new SqlCommand("SELECT Body FROM Messages WHERE MessageID = @MessageId");
com.Parameters.AddWithValue("@MessageId", 1); //Replace 1 with messageid you want to get
string s = com.ExecuteScalar().ToString()

edited Aug 7, 2012 at 1:15

answered Aug 6, 2012 at 3:03

Anuraj

19.6k7 gold badges60 silver badges84 bronze badges

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

1 Comment

Kevin Over a year ago

what you said makes a lot of sense however the only problem is when I try to use AddParametersWithValue, I get: System.Data.SqlClient.SqlCommand does not contain a definition for AddParmatersWithValue and no extension method AddParametersWithValue accepting a first argument of type System.Data.SqlClient.SqlCommand could be found.

BizApps · Accepted Answer · 2012-08-06 03:12:31Z

2

You can use SQLDataAdapter and Datatable for this :

SqlCommand com = new SqlCommand("SELECT MessageID,Body FROM Messages WHERE MessageID=               MessageID", conn);
SqlDataAdapter dadapter=new SqlDataAdapter();
DataTable dt = new DataTable();
  com.Connection = conn;
  com.Connection.Open();
  String body;
  dadapter.SelectCommand=com;
  dadapter.Fill(dt);
  body = dr.Rows["Body"].toString();

answered Aug 6, 2012 at 3:12

BizApps

6,1409 gold badges42 silver badges65 bronze badges

Comments

Nudier Mena · Accepted Answer · 2012-08-06 02:45:35Z

1

you should try something like this.

 SqlCommand com = new SqlCommand("SELECT Body FROM Messages WHERE MessageID=              MessageID", conn);
com.Connection = conn;
com.Connection.Open();
String body;
SqlDataReader dr = com.ExecuteReader();
if(dr.HasRows){
while(dr.Read()){
 body+=dr["Body"].ToString();  
  }
}

I hope this works for you.

answered Aug 6, 2012 at 2:45

Nudier Mena

3,2842 gold badges24 silver badges22 bronze badges

1 Comment

Kevin Over a year ago

Thanks for the response. Unfortunately, that concatenates the body of every message, starting from the first row. What I want to do is be able to have a user select a row and based on the row he selected, the body of the message corresponding to that row will be displayed. Every row has a unique MessageID which is what I assumed the WHERE MessageID = MessageID would take care of. MessageID is a column in my database. The MessageID on the right hand side is a local c# variable holding the unique ID of the row the user clicked.

Dark Hippo · Accepted Answer · 2012-08-06 07:47:10Z

Based on your reply to Nudier below, you're trying to pass in the messageID of the selected message by using WHERE MessageID = MessageID

The reason this won't work, and the reason you're always getting the first row returned is that SQL doesn't know that MessageID is a variable you're trying to pass in. As far as SQL knows, MessageID is a column name, so all you're asking SQL to do is select the column "Body" of the row where the column MessageID = the column MessageID, so where MessageID equals itself, which always equates to true. And since ExecuteScalar always returns the first cell of the first row, your query will always return all rows from the Messages table, and the executeScalar will grab the first cell.

Hopefully that made sense, if not, just copy your query and run it against your SQL database, you should see what I mean about it returning all rows as the where clause always equals true.

To fix it, you need to take into account what Anuraj said about adding a parameter.

To pass in a variable to a SQL string in code, you need to parameterise it, then add the relevant parameter, so your SQL should become:

SELECT Body FROM Messages WHERE MessageID=@MessageID

(Notice the addition of the @ symbol before the parameter name?)

And directly below that line, you need to add the parameter in code using:

com.AddParameterWithValue("@MessageId", MessageId);

(I think that's right, I copied it from Anuraj, I normally do it slightly differently)

Again, to see this working, you can run it directly against the database with a parameter by using

DECLARE @messsageID AS INTEGER
SET @messageID = 1
SELECT Body FROM Messages WHERE MessageID=@messageID

Have a read of this for more details (or if I haven't managed to be entirely clear) http://www.csharp-station.com/Tutorial/AdoDotNet/lesson06

Collectives™ on Stack Overflow

Executing query in c# asp.net

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Related