CODEWITHSUNDEEP
c#sqlasp.netvisual-studio
A. McN
A. McN

Reputation: 31

My SQL statement reads the first row from a database

I've been trying to create a basic internal messaging system for my website. It will let me insert a new row into the messages table, but the sellerID in the messsages (which is the userID in the itemsupdate table) returns the value of the first row in the itemsupdate table.

I want it to insert the userID value into the sellerID field. 

string message = messagebox.Text;
string itemid = Request.QueryString["itemID"];
string finduser = Session["ID"].ToString();

//comunicate to the web server
string connectionString = WebConfigurationManager.ConnectionStrings["itemsconnection"].ConnectionString;
SqlConnection myConnection = new SqlConnection(connectionString);

// myConnection.ConnectionString is now set to connectionString.

myConnection.Open();
string messages = "SELECT userID FROM itemsupdate INNER JOIN users ON itemsupdate.userID = users.ID";
SqlCommand command = new SqlCommand(messages, myConnection);
int sellerid = Convert.ToInt32(command.ExecuteScalar().ToString());

//add the above variables to the table 'comments6'
string query = "INSERT INTO messages(message, buyerID, sellerID, itemID) VALUES(@message, @finduser, @sellerid, @itemid)";
SqlCommand myCommand = new SqlCommand(query, myConnection);
//create a parameterised object
myCommand.Parameters.AddWithValue("@finduser", finduser);
myCommand.Parameters.AddWithValue("@sellerid", sellerid);
myCommand.Parameters.AddWithValue("@itemid", itemid);
myCommand.Parameters.AddWithValue("@message", message);
myCommand.ExecuteNonQuery();
myConnection.Close();

I am not getting any error messages. Any help would be really appreciated. I am also relatively new to SQL and StackOverflow, so I will come across novice.

Upvotes: 0

Views: 128

Answers (2)

Kritner
Kritner

Reputation: 13765

It looks like your problem is in your first query:

SELECT userID 
FROM itemsupdate 
INNER JOIN users ON itemsupdate.userID = users.ID

Ok @t0mm13b, how would I fix this?

You have nothing to filter on userId, and since you're doing ExecuteScalar you're only grabbing the first row returned. You probably need to add in a where clause like

where itemId = @itemid // from your queryString param?

Think of it this way:

itemId    sellerId
1         1
2         1
3         2

If you were buying item 3, and need the seller, grabbing the first row returned in a query like:

select sellerId
from table

could return you 1, or 2. You're not limiting to the specified item. You want this instead:

select sellerId
from table
where itemId = 3

Upvotes: 1

user5683823
user5683823

Reputation:

Look at your SELECT statement: SELECT userID FROM itemsupdate INNER JOIN...

You have no WHERE clause in that statement, to select a specific row. So, no matter what else happens in the world, that SELECT will always return all the rows in the join. And since you are requesting ExecuteScalar, you are picking up the value in the first column in the first row that comes up.

Think about your logic - this is a "bug" (a logic mistake), not a coding/syntax/language error. Logic mistakes do not raise exceptions.

Upvotes: 0

Related Questions