?
Solved

ASP.NET SQL Exception

Posted on 2009-04-17
13
Medium Priority
?
370 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:RalphHxyz
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 14

Expert Comment

by:amar31282
ID: 24169269
i think you are missing myConn.Open()
0
 
LVL 20

Expert Comment

by:informaniac
ID: 24169491
R u getting any error msg?

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

0
 

Author Comment

by:RalphHxyz
ID: 24169866
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Accepted Solution

by:
mvgeertruyen earned 2000 total points
ID: 24169920
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
 
LVL 14

Expert Comment

by:amar31282
ID: 24170020
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
 
LVL 14

Expert Comment

by:amar31282
ID: 24170040
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
 

Author Comment

by:RalphHxyz
ID: 24170092
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
 
LVL 6

Expert Comment

by:mvgeertruyen
ID: 24170161
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
 
LVL 14

Expert Comment

by:amar31282
ID: 24170217

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

Open in new window

0
 
LVL 14

Expert Comment

by:amar31282
ID: 24170233
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
 

Author Closing Comment

by:RalphHxyz
ID: 31571540
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
 

Author Comment

by:RalphHxyz
ID: 24170703
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
 
LVL 14

Expert Comment

by:amar31282
ID: 24170731
Doesn't matter. Your issue resolved that matters.

Cheers

Regards,
Amarjit
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question