Trouble with query results

[BrasilianGuy]

wrote this script below to retrieve data from a specific table.
private void savenewid()
{
Connection.Open();
String Newproductid = productid.Text;
String SQLStatementInsert = "insert into releasedids (newid) Values ("+ Newproductid+")";
SqlCommand Commandinsert = new SqlCommand(SQLStatementInsert, Connection);
SqlDataReader insertReader = Commandinsert.ExecuteReader();
lblresult.Text = "The New Id you entered has been Saved";
btnSave.Visible = false;
insertReader.Close();
Connection.Close();

Connection.Open();

String SQLStatementselectnewid = "SELECT * FROM productrefresh WHERE productnumber = " + Newproductid;
SqlCommand dataCommand = new SqlCommand(SQLStatementselectnewid, Connection);
SqlDataReader dataReader = dataCommand.ExecuteReader();

if (dataReader.HasRows)
{
String Strproduct = dataReader.GetString(0);
String SQLStatementDelete = "UPDATE prodreminder set product =" + Newproductid + " 2 WHERE product =" + Strproduct;
SqlCommand CommandDelete = new SqlCommand(SQLStatementDelete, Connection);
CommandDelete.ExecuteNonQuery();


lblresult2.Text = "This product has reminders request";
}
else
{
lblresult2.Text = "This product does not have any reminder request";

}

dataReader.Close();
Connection.Close();
}

It gives me this error “Invalid attempt to read when no data is present.” In the highlighted line right after the if statement.

I know the query is returning a row which explains why it is going into the if statement but I can’t figure out why it is not grabing the result from that specific cell (GetString(0)).



Thnaks

 

[AlexCuse]

I think you need to tell your dataReader to Read before it is asked to return a string? Something like this maybe?

if(dataReader.Read())
{
String Strproduct = dataReader.GetString(0);
}

you may not need to use the if, I don't have what I need to test this at the moment. But I'm sure you do ;-)

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[BrasilianGuy]

It's working but the update statement is not updating the table

any ideas

private void savenewid()
{
Connection.Open();
String Newproductid = productid.Text;
String SQLStatementInsert = "insert into releasedids (newid) Values ("+ Newproductid+")";
SqlCommand Commandinsert = new SqlCommand(SQLStatementInsert, Connection);
SqlDataReader insertReader = Commandinsert.ExecuteReader();
lblresult.Text = "The New Id you entered has been Saved";
btnSave.Visible = false;
insertReader.Close();
Connection.Close();

Connection.Open();

String SQLStatementselectnewid = "SELECT * FROM productrefresh WHERE productnumber = "+Newproductid;
SqlCommand dataCommand = new SqlCommand(SQLStatementselectnewid, Connection);
SqlDataReader dataReader = dataCommand.ExecuteReader();

if (dataReader.Read())
{
String Strproduct = dataReader.GetString(0);
String SQLStatementDelete = "UPDATE prodreminder set product ="+Newproductid+" WHERE product ="+Strproduct;
SqlCommand CommandDelete = new SqlCommand(SQLStatementDelete, Connection);
//SqlDataReader ReaderDelete = CommandDelete.ExecuteReader();


lblresult2.Text = "This product has reminders request";
}
else
{
lblresult2.Text = "This product does not have any reminder request";

}

dataReader.Close();
Connection.Close();
}

thanks

in advance

Marcus

 

[AlexCuse]

First off, you might want to rename this statement

String SQLStatementDelete = "UPDATE prodreminder set product ="+Newproductid+" WHERE product ="+Strproduct;

so it is called SQLStatementUPdate ;-)

Second, I don't know what your column's data types are, but if both are character data (char, varchar, nchar, nvarchar, text) you need to put single quotes around them. If product ID is numeric data type, then no quotes needed there. But it'd be something like this:

String SQLStatementDelete = "UPDATE prodreminder set product =[COLOR=red]'[/color]"+Newproductid+ "[COLOR=red]'[/color] WHERE product =[COLOR=red]'[/color]"+Strproduct+[COLOR=red]"'"[/color];

I imagine you will need to do this on Strproduct but maybe not Newproductid. A little background, if your string is NOT in single quotes, then SQL will look for a column with that name, which probably does not exists. So nothing will be updated. Does this make sense?

Hope this helps,

Alex



Ignorance of certain subjects is a great part of wisdom

 

[dEVooXiAm]

Maybe because you have UPDATE execution line commented out..?

//SqlDataReader ReaderDelete = CommandDelete.ExecuteReader();

Also, why do you use readers for doing no-result-data SQL statements (it does not look like you are reading any results from them) ?

------------------
When you do it, do it right.

 

[AlexCuse]

>>>Maybe because you have UPDATE execution line commented out..?

Yes, that would help as well. But you still will need to make the changes, as your query is currently written it would not be updating anything anyway (unless both of the parameters you are feeding it are numeric).

Ignorance of certain subjects is a great part of wisdom

 

[BrasilianGuy]

which one is the no-result-SQL???? the insert or the select???

I though the reader is to execute the query and the reason I commented this line

//SqlDataReader ReaderDelete = CommandDelete.ExecuteReader();

is because it was running inside of an open reader

SqlDataReader dataReader = dataCommand.ExecuteReader()

Sorry for the noob questions this is my first app

 

[AlexCuse]

AFAIK, you would still need to execute your command. I have never used the DataReader in this fashion though.

I think you might want to have a look at this link on the SqlCommand.ExecuteNonQuery method, this looks more like what you are trying to do. Then you could key off of the returned value (rows affected) to determine whether or not to reset your label's text.

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx

I hope this helps, and try not to get too frustrated with your first program. I promise it will get easier

Alex

Ignorance of certain subjects is a great part of wisdom

 

[BrasilianGuy]

Thanks Alex....

The other thing I'm trying to do is to combine both wuerie into one

I'm comming from a Coldfusion background where we are used to use one sql estament to sort the table and one update statement to go trough the results of the SQL statement.

That is pretty much what I tried to do here.

But I guess I can go for something like this instead

Connection.Open();


String SQLStatementUpdate = "UPDATE prodreminder set product ='" + Newproductid + "' WHERE EXISTS (SELECT * FROM productrefresh WHERE productnumber = '" + Newproductid + "')";
SqlCommand CommandUpdate = new SqlCommand(SQLStatementUpdate, Connection);
SqlDataReader ReaderUpdate = commandUpdate.ExecuteReader();

if (ReaderUpdate.HasRows == true)
{

lblresult2.Text = "This product has reminders request";
}
else
{
lblresult2.Text = "This product does not have any reminder request";

}

ReaderUpdate.Close();

What do you think???

Thanks

Marcus

 

[AlexCuse]

Marcus-

I don't think you want to use where exists?

I am confused now as to what exactly you want your SQL statement to do, because this is very much different from what you initially had. Can you please explain what the goal is for this query?

If the goal is to update all rows in the table, then this is the query you want. But I don't think this is what you want to do.

And like I said, I would replace your reader with an ExecuteNonQuery method run against your existing connection. This will return rows effected, and you can key off of that to set your label's text property accordingly. I think that Using a reader would probably work, but it is not the best way to execute a DML statement. Better off to catch bad habits now, you know? They will be harder to break later on.

HOpe this helps,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[BrasilianGuy]

My goals are (in a nutshell):

1. (insert statement) Is to insert the ID entered in the text box into table A.

2. Use the same ID to Search for the OldID in a table B. Now That I've found the oldID I want to UPDATE the oldID with the new ID in the table C.

The first piece is working....


I tried this for the update and it worked half way....

            Connection.Open();
            
            String SQLStatementUpdate = "UPDATE prodreminder set product ='" + Newproductid + "' from productrefresh WHERE  (productrefresh.oldproductnumber = prodreminder.product)";
            SqlCommand CommandUpdate = new SqlCommand(SQLStatementUpdate, Connection);
            SqlDataReader ReaderUpdate = CommandUpdate.ExecuteReader();

            if (ReaderUpdate.Read())
            {         
                
                lblresult2.Text = "This product has reminders request";
            }
            else
            {
                lblresult2.Text = "This product does not have any reminder request";
                
            }

It just updates one record....

Thanks

MArcus

 

[AlexCuse]

Wow, I think you are making this way more complicated than it needs to be. I don't see why you need the two extra tables at all. Anyway, since it involves two tables you need a join somewhere in your SQL Statement. Your generated SQL string should look something like this (with real value of newproductid plugged in of course):

update prodreminder
set prodreminder.product = '<value of newproductid>'
from prodreminder inner join productrefresh 
on productrefresh.oldproductnumber = prodreminder.product

If you can make your string look like that, you should be good.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom