|
Hi,
when I execute this code i get the following error:Incorrect syntax near the keyword When.
StringBuilder sb = new StringBuilder();
foreach (GridViewRow row in GridView1.Rows)
{
string note1 = (row.FindControl("note1") as TextBox).Text.Replace(",", ".");
string note2 = (row.FindControl("note2") as TextBox).Text.Replace(",", ".");
string userID = (row.FindControl("lblUserID1") as Label).Text;
sb.AppendFormat(
@"UPDATE EXAMEN
SET NOTE = case when
CODE_MATIERE ='111' then {0}
when CODE_MATIERE ='112' then {1}
END
where NUM_INSCRIPTION = {2}
;
", note1, note2, userID);
}
</pre>
|
|
|
|
|
You need to enclose your CASE statement in brackets:
UPDATE EXAMEN
SET NOTE =
( CASE
WHEN CODE_MATIERE = '111' THEN '{0}'
WHEN CODE_MATIERE = '112' THEN '{1}'
END )
WHERE NUM_INSCRIPTION = {2};
I would also recommend using a parameterised update query - that way you only need to create the query once and you can pass the row-specific values (note1, note2, userId) as parameters.
|
|
|
|
|
You cannot have CR/LF in strings. Thus you have to concatenate your strings if you want to write them on several lines :
sb.AppendFormat(
@"UPDATE EXAMEN "
+ "SET NOTE = "
+ "(CASE "
+ "WHEN CODE_MATIERE = '111' then {0} "
+ "WHEN CODE_MATIERE = '112' then {1} "
+ "END) "
+ "WHERE NUM_INSCRIPTION = {2};"
, note1, note2, userID);
An even better option would be to use a parameterized query :
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "UPDATE EXAMEN "
+ "SET NOTE = "
+ "(CASE "
+ "WHEN CODE_MATIERE = '111' then @note1 "
+ "WHEN CODE_MATIERE = '112' then @note2 "
+ "END) "
+ "WHERE NUM_INSCRIPTION = @numinsc;"
SqlParameter param1 = new SqlParameter("note1", note1);
cmd.Parameters.Add(param1);
SqlParameter param2 = new SqlParameter("note2", note2);
cmd.Parameters.Add(param2);
SqlParameter param3 = new SqlParameter("numinsc", userID);
cmd.Parameters.Add(param3);
...
Edit : I replied to the wrong post. Sorry 
|
|
|
|
|
 
I tried using a parameterized query but it does not work:It update with zero all marks
foreach (GridViewRow row in GridView1.Rows)<br />
{<br />
TextBox notem1 = row.FindControl("note1") as TextBox;<br />
TextBox notem2 = row.FindControl("note2") as TextBox;<br />
Label num = row.FindControl("lblUserID1") as Label;<br />
SqlConnection myConnection = new SqlConnection(ConnectionString);<br />
<br />
SqlCommand myCommand = new SqlCommand(<br />
"UPDATE EXAMEN "<br />
+ "SET NOTE = "<br />
+ "(CASE "<br />
+ "WHEN CODE_MATIERE = '111' then @note1 "<br />
+ "WHEN CODE_MATIERE = '112' then @note2 "<br />
+ "END) "<br />
+ "WHERE NUM_INSCRIPTION = @num;"<br />
<br />
<br />
, myConnection);<br />
<br />
myCommand.Parameters.Add(new SqlParameter("@num", SqlDbType.VarChar, 4));<br />
<br />
myCommand.Parameters["@num"].Value = num.Text;<br />
myCommand.Parameters.Add(new SqlParameter("@note1", SqlDbType.Float, 2));<br />
<br />
myCommand.Parameters["@note1"].Value = notem1.Text.Replace(",", ".");<br />
<br />
<br />
myCommand.Parameters.Add(new SqlParameter("@note2", SqlDbType.Float, 2));<br />
<br />
myCommand.Parameters["@note2"].Value = notem2.Text.Replace(",", ".");<br />
myConnection.Open();<br />
<br />
myCommand.ExecuteNonQuery();<br />
<br />
myConnection.Close();<br />
<br />
}
modified on Thursday, October 14, 2010 6:21 AM
|
|
|
|
|
Try to use the correct types when you assign your parameter : here you define float parameters but you assign them string values.
Try instead :
string cmd = "UPDATE EXAMEN SET NOTE = (CASE WHEN CODE_MATIERE = '111' then @note1 WHEN CODE_MATIERE = '112' then @note2 END) WHERE NUM_INSCRIPTION = @num;";
float note1, note2;
string num;
SqlCommand myCommand;
SqlParameter param1, param2, param3;
using (SqlConnection myConnection = new SqlConnection(ConnectionString))
{
myConnection.Open();
foreach (GridViewRow row in GridView1.Rows)
{
note1 = float.Parse(((TextBox)row.FindControl("note1")).Text);
note2 = float.Parse(((TextBox)row.FindControl("note2")).Text);
num = ((Label)row.FindControl("lblUserID1")).Text;
myCommand = new SqlCommand(cmd, myConnection);
param1 = new SqlParameter("num", SqlDbType.VarChar, 4);
param1.Value = num;
myCommand.Parameters.Add(param1);
param2 = new SqlParameter("note1", SqlDbType.Float, 2);
param2.Value = note1;
myCommand.Parameters.Add(param2);
param3 = new SqlParameter("note2", SqlDbType.Float, 2);
param3.Value = note2;
myCommand.Parameters.Add(param3);
myCommand.ExecuteNonQuery();
}
myConnection.Close();
}
Several points here :
- Try to obtain your values in the type you will need them in your database (note1, note2, num).
- I removed the concatenations in the query string for higher simplicity.
- When you define your parameters, defines variables of type SqlParameter (as in my previous example) ; it will exhaust you from having to find them out using an array accessor.
- The '@' sign for parameters must only be present in the SQL query string, not in the declaration of the SqlParameter objects.
- It's better to open your connection before the loop, and close it after ; opening connection is time-consuming, and you'd better avoid it if you can. And, as SqlConnection implements IDisposable interface, it's even better to use it with an 'using' statement (which will take care of disposing the connection when not needed anymore).
If this still doesn't work, start a debug session and watch for the content of all your variables, you should see there what's the issue.
Hope it'll help 
|
|
|
|
|
Hi,
Thank you very much phil.
the program does not read TexBox of the gridview it returns me this message 'The format of the input string is incorrect.'
for this line : note1 = float.Parse(((TextBox)row.FindControl("note1")).Text);
when I debug I found that the value of note1 is 0.0 while I typed 13.
Thanks.
|
|
|
|
|
Hi,
the program does not read  TextBox)row.FindControl("note2")).Text;
it reads ((Label)row.FindControl("lblUserID1")).Text;
I think the problem come from there but I do not know how to correct that.
Thanks
|
|
|
|
|
It works thaaaaaaaaaaaaaaaaaaanks merciiiiiiiiiiii phil.o.
|
|
|
|
|
I m using asp .net 2.0.How to maximise the panel contol when the page is loaded in web application?Plese help..
|
|
|
|
|
Thanusree Duth wrote: How to maximise the panel contol when the page is loaded in web application
Not clear..
Whether you want to change the size. You can do from javascript as well.
|
|
|
|
|
Here is the solution to ur problem
function maximizePanel()
{
var screenHeight = window.screen.availHeight;
var screenWidth = window.screen.availWidth;
document.getElementById('<% =Panel1.ClientID %>').style.width = screenWidth - 10;
document.getElementById('<% =Panel1.ClientID %>').style.height = screenHeight - 10;
}
<body onload="maximizePanel()">
<form id="form1" runat="server">
<div>
<asp:Panel ID="Panel1" runat="server" Height="50px" Width="125px" BorderWidth="2">
</asp:Panel>
</div>
</form>
</body>
</html>
Tej Aj,
http://opexsolution.com/forum/
Fan of:
http://www.abtstudioz.com/
|
|
|
|
|
Hi experts,
Autocomplete can be done in 3 ways
1. 2.<asp:textbox runat="server" id="Textbox1" autocomplete="off">
3.Textbox1.Attributes.Add("autocomplete", "off");.
We can also set AutoCompleteType="disabled"
But i want to do this in whole application. Is it possible to put it in web.config or is there any other solutions for this.
|
|
|
|
|
Theoretically, you could write a Http Module that would add this. Basically, you would register for the PreSendRequestContent and PreRequestHandlerExecute events and modify the output content there.
|
|
|
|
|
You can also do this in a skin file.
1) Right click on your the root of your web project and select Add ASP.Net Folder -> Theme. Give it a name.
2) Right click on the new theme and select Add New Item -> Skin File
3) In your new .skin file add: <asp:textbox runat="server" autocomplete="off"></asp:textbox>
4) In web.config modify: <pages theme="Name of your theme in step 1">
That should get you rolling.
- S
50 cups of coffee and you know it's on!
Code, follow, or get out of the way.
|
|
|
|
|
Thanks a lot........
It works great....... one doubt..... will it have any impact(disadvantage)
|
|
|
|
|
I'm not aware of any draw backs to using skin files, other than having to manually override the skin for every textbox you want autocomplete enabled for (using txtWhatever.Attributes.Add("autocomplete", "on")).
- S
50 cups of coffee and you know it's on!
Code, follow, or get out of the way.
|
|
|
|
|
Let's say I have a ListView with DataPager like this:
<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="List.ascx.cs" Inherits="Lnow.Applications.CompanyManagement.Gui.Clients.List" %>
<asp:ListView runat="server" ID="itemsList" ItemPlaceholderID="item">
<LayoutTemplate>
<table class="simpleList">
<thead>
<tr>
<td>
<asp:Literal runat="server" Text="[Id]" meta:resourcekey="Id" />
</td>
<td>
<asp:Literal runat="server" Text="[Name]" meta:resourcekey="Name" />
</td>
</tr>
</thead>
<tfoot>
<tr>
<td colspan="2">
<asp:DataPager runat="server" ID="itemsPager" PagedControlID="itemsList" PageSize="5">
<Fields>
<asp:NumericPagerField />
</Fields>
</asp:DataPager>
</td>
</tr>
</tfoot>
<tbody>
<asp:PlaceHolder runat="server" ID="item" />
</tbody>
</table>
</LayoutTemplate>
<ItemTemplate>
<tr class="row">
<td>
<%# DataBinder.Eval(Container.DataItem, "Id") %>
</td>
<td>
<%# DataBinder.Eval(Container.DataItem, "Name") %>
</td>
</tr>
</ItemTemplate>
<AlternatingItemTemplate>
<tr class="row-alt">
<td>
<%# DataBinder.Eval(Container.DataItem, "Id") %>
</td>
<td>
<%# DataBinder.Eval(Container.DataItem, "Name") %>
</td>
</tr>
</AlternatingItemTemplate>
</asp:ListView>
Now I want to use DataPager to page search results. How do I do it?
I found two answers to this question, both have an issue I want to avoid:
1. Create method to support ListView's PagePropertiesChanging event.
2. Create method to support DataPager's PreRender event.
The issue, that I want to avoid is that both those methods require me to store collection of retrieved data somewhere. And I don't want to do it, cause I can't do it. Objects, that are bound don't have Serializable attribute, so I can't throw them into ViewState. Is there any other way to do this? Or do I have to create my own control for paging?
Don't forget to rate answer, that helped you. It will allow other people find their answers faster.
|
|
|
|
|
Hello, reviewing ASP.NET to handle printing of a worksheet.
Any suggestions as to what technology to use to handle it?
I had Crystal Reports in mind (have previous experience using it on Windows Forms) however in vs2010 and .net4 it seems to be in beta.
Any suggestions to handle this functionality? doesnt have to be a reporting tool.
Any help or tips are appreciated, thanks in advance!
|
|
|
|
|
Hi all,
I wonder if I can use some vb.net modules and c#.net modules in the same ASP.Net Application
If yes, how can i Merge between them? and if that possible, may you please give me a simple example.
Thanks
Abdullah Abdelhay
modified on Monday, October 11, 2010 8:05 AM
|
|
|
|
|
Yes sure, we can use some code written in VB and some in C# in same Asp.net application. But they must be in different files.
For that, you need to have vb and c# files in different folder in App_code folder.and you need to make the entry in web.config in CODESUBDIRECTORIES tag.
For details, Have a look
Click here
|
|
|
|
|
Hi,
Hope you have got the answer from Brij's Post.
I just wanted to share one of my article where I gave some good example of using this features with APP_CODE along with uses of others ASP.NET Folders
Beginner's Guide to ASP.NET Application Folder [^]
It may help you !
|
|
|
|
|
Hi,
Thank you for your answers that worked fine
but there are a small problem
When you create a C# project and create App_Code and add in it for example cscode & vbcode Classes
you can call vb classes in cs classes but not the opposite, if you try to call cs classes in vb classes that not work.
Why?
i hope that not a problem 
another question: Can we apply it on windows form applications?? 
Thanks
Abdullah Abdelhay
|
|
|
|
|
Hi,
Thank you for your answers that worked fine
but there are a small problem
When you create a C# project and create App_Code and add in it for example cscode & vbcode Classes
you can call vb classes in cs classes but not the opposite, if you try to call cs classes in vb classes that not work.
Why?
i hope that not a problem
another question: Can we apply it on windows form applications??
Thanks
Abdullah Abdelhay
modified on Tuesday, October 12, 2010 6:35 AM
|
|
|
|
|
Is this an answer. You should post your reply to the user's post you need to reply and not yours.
He is not notified for your answer and would never know you asked him something unless, of course, he checks your answer.
You may consider changing the type of the post to question and adding a reply to the user's post. 
..Go Green..
|
|
|
|
|
Hi all,
I have one query like in business logic layer of catch block i want to create log file for handling the exceptions and in UI layer i want to redirect to error.aspx page . how to do that ?
Regards
Rama
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
![Green Alien | [Alien]](https://codeproject.freetls.fastly.net/script/Forums/Images/alien.gif)
Indian CodeProject Members Meet 2010 - Nominate Now