ASP.NET SQL Exception

ASP.NET page calling a ACCESS query causes a exception from .aspx C#.

qry_Update-All fails (exception):
                OleDbCommand command = new OleDbCommand();
                command.Connection = myConn;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "qGroupCustomer";
                command.ExecuteNonQuery();
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "qry_Update-All"; // Updates all _ to 0
                command.ExecuteNonQuery();
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "qry_P_LOG_Update";

The query runs fine in ACCESS and all of the other queries run correctly
(Except of course for qry_P_LOG_Update which never gets called)!
qry_Update-All never runs when called from the .aspx page but runs fine when called directly in ACCES..

Thanks for the help (once again:-)
Ralph
RalphHxyzAsked:
Who is Participating?
 
mvgeertruyenCommented:
I think you have to include more quotes as well (around UNDERSCORE as this is text):

"UPDATE Detail SET Detail.Contents = " + ZERO +" WHERE (((Detail.Contents)=""" + UNDERSCORE +"""))"
0
 
amar31282Commented:
i think you are missing myConn.Open()
0
 
Obadiah ChristopherCommented:
R u getting any error msg?

@amar31282 He's havin problm with the third update. Am I Right Ralph?

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
RalphHxyzAuthor Commented:
OPPs sorry for not posting the myConnOpen() but it is there otherwise none of the
queries would run.
No I am not getting any error messages, the .aspx page loads fine
but of course the content has not been updated.
I modified the C# code to use CommandType.Text but still get the Exception:

myConn.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = myConn;

string ZERO = "0";
string UNDERSCORE = "_";
command.CommandType = CommandType.Text;
command.CommandText = "UPDATE Detail SET Detail.Contents = " + ZERO +" WHERE (((Detail.Contents)=" + UNDERSCORE +"))";
command.ExecuteNonQuery();
}
catch (Exception ex)
{
// exception handling?
}
finally
{
This was just a cut for illustration the {} are correct (or it would not run for the other queries).
Thanks for the replies, they help to check everything.
RAlph
0
 
amar31282Commented:
It seems correct in all forms

Try putting a break at
catch (Exception ex)
{
//Exception
}

and see what is the error coming in ex.
Or get the ex.Message into some textbox/ label and see the error
try
{
myConn.Open(); 
OleDbCommand command = new OleDbCommand(); 
command.Connection = myConn; 
 
string ZERO = "0"; 
string UNDERSCORE = "_"; 
command.CommandType = CommandType.Text; 
command.CommandText = "UPDATE Detail SET Detail.Contents = " + ZERO +" WHERE (((Detail.Contents)=" + UNDERSCORE +"))"; 
command.ExecuteNonQuery(); 
} 
catch (Exception ex) 
{ 
//Exception
} 
finally 
{
myConn.close();
myConn.dispose();
command.dispose();
}

Open in new window

0
 
amar31282Commented:
If the error is SQL statement syntax is not correct then check that you have blank space between all the quotes and the text like

"Where  
is wrong and
" Where is right
0
 
RalphHxyzAuthor Commented:
UNDERSCORE is not text it is a variable.
Or do mean that there should be quotes sent with the variable?
This would alos apply to ZERO.
"" + ZERO + "" and ""  + UNDERSCORE  + ""
Does not work, what is the correct syntax to embed quotes?
Ralph
0
 
mvgeertruyenCommented:
In SQL (probably in access as well) your string would look like UPDATE Detail SET Detail.Contents = 0 WHERE (((Detail.Contents)= _ )) which won't work. I think the statement should be something like

UPDATE Detail SET Contents = 0 WHERE Contents =  '_'
(not sure why you are using the vars based on the snippet)

amar31282's suggestion to wrap it into a try/catch block is probably the fastest way to see what is going wrong here.  
0
 
amar31282Commented:

"UPDATE Detail SET Detail.Contents = " + ZERO + " WHERE Detail.Contents='UNDERSCORE' "; 

Open in new window

0
 
amar31282Commented:
Really don't know what are you doing in query..
Try it
If you want to use ZERO and UNDERSCORE text

Other is also give
"UPDATE Detail SET Detail.Contents = 'ZERO'  WHERE Detail.Contents='UNDERSCORE' "; 
 
 
"UPDATE Detail SET Detail.Contents =0  WHERE Detail.Contents='_' "; 

Open in new window

0
 
RalphHxyzAuthor Commented:
It was the quotes!!
I still do not know why calling the ACCESS query did not work but my method of using CommandType.Text with variables works as long as I
include quotes in the variable contents!!

Here is the correct syntax:
                string ZERO = "\"0\"";
                string UNDERSCORE = "\"_\"";
                command.CommandType = CommandType.Text;
                command.CommandText = "UPDATE Detail SET Detail.Contents = " + ZERO + " WHERE (((Detail.Contents)=" + UNDERSCORE + "))";
                command.ExecuteNonQuery();
I sure would like to know why this does not work:
                //command.CommandType = CommandType.StoredProcedure;
                //command.CommandText = "qry_Update-All";
                //command.ExecuteNonQuery();
0
 
RalphHxyzAuthor Commented:
Darn I should have waited a little before awarding the points.
This also works!!
command.CommandText = "UPDATE Detail SET Detail.Contents = 0 WHERE (((Detail.Contents)='_'))";
Sorry,
Ralph
0
 
amar31282Commented:
Doesn't matter. Your issue resolved that matters.

Cheers

Regards,
Amarjit
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.