|
To me this looks like it's not related to the code that you have posted but refers to some other code-path which you haven't yet refactored to use SQL-parameters.
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
I don't really understand what your doing on the database code.
I'm not an expert in c#, but learning, but I started back in 2006 using more of this style, which is similar to what Richard said to use earlier on the 15th.
I'm not implying that you should use this code sample, but to look at it to get a better idea of how it works.
So you make a connection string or read a stored one
Create a connection to the database
create a command object
Add your parameters to the Command Object
Open the database
Read the values returned
Close the Database
Return the values
public int GetAccountSortByAccountCode(int account)
{
int pValue = 0;
string connStr = ConfigurationManager.ConnectionString("DefaultConnection").ConnectionString;
SqlConnection conn = new SqlConnection(connStr);
string query =
"SELECT " +
" ac_sort_order " +
" FROM lkup_account_codes " +
" WHERE ac_code = @account";
SqlCommand cmd = new SqlCommand(query, conn);
SqlParameter param_account = new SqlParameter("@account", SqlDbType.Int);
param_account .Value = account;
cmd.Parameters.Add(param_account);
try
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
pValue = reader.GetInt32(0);
}
reader.Close();
}
catch (Exception ex)
{
}
finally
{
conn.Close();
}
return pValue;
}
|
|
|
|
|
Because you really wanted a code review:
The configuration property is called ConnectionStrings , and the indexer in C# uses square brackets.
Your SqlConnection , SqlCommand and SqlDataReader objects should be wrapped in using blocks.
command.Parameters.AddWithValue is less verbose than creating a new parameter, setting its value, and adding it to the collection. Shorter code means you're more likely to stick with the pattern!
Since you're only returning a single value, there's no need for ExecuteReader ; use ExecuteScalar instead. You can also add a TOP 1 to the query.
Your catch block is swallowing every exception, making it impossible to diagnose problems, or to even know when there is a problem.
public int GetAccountSortByAccountCode(int account)
{
string connStr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
const string query = "SELECT TOP 1 ac_sort_order FROM lkup_account_codes WHERE ac_code = @account";
using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand(query, conn))
{
cmd.Parameters.AddWithValue("@account", account);
conn.Open();
object commandResult = command.ExecuteScalar();
return (commandResult == null || Convert.IsDBNull(commandResult))
? 0
: Convert.ToInt32(commandResult);
}
}
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I translated that from VB to c# off the top of my head in VS2013. I was wondering why the ConfigurationManager wasn't right.
Now you have me thinking, I can implement similar techniques in VB perhaps
Original in VB
Public Shared Function check_4_Duplicate_AccountName( _
ByVal p_Test_AccountName As String) As Boolean
Dim pCount As Integer = 0
Dim dV As Boolean = True
Dim connStr As String = ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString
Dim query As String = _
" SELECT " & _
" COUNT(AccountName) " & _
" FROM CUSTOMER_ACCOUNTS " & _
" WHERE AccountName = @Test_AccountName"
Dim conn As New SqlConnection(connStr)
Dim cmd As New SqlCommand(query, conn)
Dim param_Test_AccountName As SqlParameter
param_Test_AccountName = New SqlParameter("@Test_AccountName", SqlDbType.VarChar, 80)
param_Test_AccountName.Value = p_Test_AccountName
cmd.Parameters.Add(param_Test_AccountName)
Try
conn.Open()
Dim myReader As SqlDataReader = cmd.ExecuteReader()
While myReader.Read()
If Not myReader.IsDBNull(0) Then
pCount = myReader.GetInt32(0)
End If
End While
myReader.Close()
myReader = Nothing
If (pCount = 0) Then
dV = False
End If
Catch ex As Exception
Finally
conn.Close()
conn = Nothing
cmd = Nothing
End Try
Return dV
End Function
Similar Technique in VB that I just wrote, saw the tenary operator in there. Very Fancy, but I left the parameter the same, forgot to modify that.
So I don't need the try catch?
Public Shared Function check_4_Duplicate_AccountName_II( _
ByVal p_Test_AccountName As String) As Boolean
Dim pCount As Integer = 0
Dim dV As Boolean = True
Dim connStr As String = ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString
Const query As String = _
" SELECT " & _
" COUNT(AccountName) " & _
" FROM CUSTOMER_ACCOUNTS " & _
" WHERE AccountName = @Test_AccountName"
Using conn As New SqlConnection(connStr)
Using cmd As New SqlCommand(query, conn)
Dim param_Test_AccountName As SqlParameter
param_Test_AccountName = New SqlParameter("@Test_AccountName", SqlDbType.VarChar, 80)
param_Test_AccountName.Value = p_Test_AccountName
cmd.Parameters.Add(param_Test_AccountName)
Try
conn.Open()
Dim cmdResult As Object = cmd.ExecuteScalar()
dV = If(cmdResult Is Nothing Or Convert.IsDBNull(cmdResult), False, True)
Catch ex As Exception
Finally
conn.Close()
End Try
End Using
End Using
Return dV
End Function
|
|
|
|
|
You don't need the Finally - the Using block will close the connection for you.
The Catch block is swallowing all exceptions. If something goes wrong with your query, your method will return the default value, with no indication that something went wrong.
You should only Catch exceptions which you can handle in your code, or which you want to log and ignore. Everything else should be allowed to propagate up the call-stack until it either meets some code which can handle it, or reaches the entry-point of the thread/process.
The only exception would be at the entry point of the thread or process, where you might want to log every unhandled exception before terminating the program. You still need to be careful, since your logging code might also fail, depending on how messed-up the application state is.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks for the clarification on the subject.
I still have more to learn here.
So I don't' need the conn.close() then, since using will close the connection for me.
And if the query is correct, then I don't need try catch.
Alright, I get it now.
|
|
|
|
|
The Veracode Scaner finally did not find this as an SQL injection error. The final fix:
namespace TAMIS.Business.Common
{
public class MyParam
{
public SqlParameter SqlParam { get; set; }
public string Name { get; set; }
public object Value { get; set; }
public int Type { get; set; }
}
public class QueryContainer
{
string _query;
public List<myparam> parameterList = new List<myparam>();
public QueryContainer(string query) { _query = query; }
public SqlDbType AddParameterType(int type)
{
switch (type)
{
case 1:
return (SqlDbType) Enum.Parse( typeof(SqlDbType) , "int" , true );
case 2:
return (SqlDbType)Enum.Parse(typeof(SqlDbType), "NVarChar", true);
}
return SqlDbType.VarChar;
}
public string Query
{
get
{
return _query;
}
set { _query = value; }
}
}
public int GetAccountSortByAccountCode(int account)
{
QueryContainer Instance = new QueryContainer("SELECT ac_sort_order FROM lkup_account_codes where ac_code = <a href="http:
MyParam myParam = new MyParam();
myParam.SqlParam = new SqlParameter("@account", Instance.AddParameterType(1));
myParam.SqlParam.Value = account;
Instance.parameterList.Add(myParam);
return Convert.ToInt32(ExecuteScaler(Instance, 1));
protected Object ExecuteScaler(QueryContainer Instance, int i)
{
object returnValue = null;
if (!_iserror)
{
if (_trace)
{
}
if (_connection == null || _connection.State == ConnectionState.Closed)
{
OpenConnection();
}
DbCommand command = _provider.CreateCommand();
command.Connection = _connection;
{
command.CommandText = Instance.Query;
command.CommandType = CommandType.Text;
foreach (var p in Instance.parameterList)
{
command.Parameters.Add(p.SqlParam);
}
if (_useTransaction) { command.Transaction = _transaction; }
try
{
returnValue = command.ExecuteScalar();
}
catch (Exception ex)
{
if (ex is EntryPointNotFoundException)
throw ex;
RollBack();
LogBLL bll = new LogBLL();
bll.WriteErrorLog(ex);
_iserror = true;
}
finally
{
if ((!KeepAlive && _connection.State == ConnectionState.Open) || _iserror == true)
{
CloseConnection();
}
}
}
}
else
{
returnValue = -1;
}
return returnValue;
}
<pre>
modified 21-May-15 11:18am.
|
|
|
|
|
Hi I'm using JQuery AJAX to call a web service and this webservice gets data from a database. Everything seems fine; I was able to get the data that the web service got from a database and display it on the front end.
The problem is when I deploy my application to IIS, my ajax calls returns "Request Failed!" which indicates that it cannot communicate with my web service. The following is my AJAX
$.ajax({
type: "post",
url: 'h t t p: // localhost:48690/MyWebService.asmx/MyWebserviceMethod',
data: JSON.stringify({newText: newText, pageURL: pageURl }),
contentType: "Application/json; charset=utf-8",
dataType: "json",
success: function (response) {
alert(response);
},
error: function (response, status) {
alert('Request Failed!');
}
});<br />
My question is, is there anything wrong with the way I write my URL in the AJAX method, if not what do you think is causing my web service to fail when I deploy my app.
If you are wondering why there are gaps in "http" in my url, that is so I can dispay my message without the anchor tag getting added to my post automatically. Thanks in advance for your replies.
modified 15-May-15 5:19am.
|
|
|
|
|
You're requesting localhost:48690 in your code, so chances are your call is failing due to cross-domain issues. Drop the host from the url so it requests from the current domain
url: '/MyWebService.asmx/MyWebserviceMethod',
|
|
|
|
|
label field will not update in database
control is :
<asp:label id="RiskRatingLabel" runat="server" text="<%# Bind("RISKST_Description") %>" cssclass="RiskRatingLabel" forecolor="Black" font-bold="true">
jquery updates the label via onchange events of other controls:
$('.RiskRatingLabel').text(RatedRiskText);
change is visible in page on certain events firing
controlparameter used in update statement:
<asp:controlparameter name="RiskRatingLabel" controlid="FormView1$RiskRatingLabel" propertyname="Text" type="String">
updatecommand = "update table set field1 = @value1, field2 = @value2, etc,...,
RiskRating = @RiskRatingLabel, etc...
This particular field as well as many other fields are Nullable,
all other fields are being updated except this one...
I can see the label value being updated on the page, yet it doesn't seem to pass through to the update command...
Any ideas?
|
|
|
|
|
Labels aren't form controls, they are not sent to the server when you submit a form. Use an asp:Hidden element to store your new data (you can still keep your label and update that, but you also need to update the value of the hidden variable). When the form is submitted you can read the data from your hidden variable.
|
|
|
|
|
Hello,
I need to upload files to a location that supports WebDav connection. How can I do that using C#/ASP.NET?
Thanks.
|
|
|
|
|
|
Hi guys,
I am looking to develop an ASP.Net application that will allow a user to reset Windows domain passwords for anyone they are the manager of. For example: Finance manager can reset the password of anyone he is listed as a manager of in Active Directory.
I was wondering what the best way to do this was in terms of accessing Active Directory. Should I have a service account created which I call using my code, should I give all users access to reset AD passwords (they are unable to install software - like RSAT - on their terminals so this seems pretty safe), or should I use a third method I am not aware of?
If anyone could please tell me the best solution for this that would be great.
Thanks!
|
|
|
|
|
Hi,
Here's my ASPX page:
<Quote:
<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/DEF.Master" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApplication1._Default" %>
<asp:content id="Content2" contentplaceholderid="PlaceHolderBodyAreaClass" runat="server">
<asp:content id="Content1" contentplaceholderid="PlaceHolderAdditionalPageHead" runat="server">
<script type="text/javascript" src="Scripts/js/jquery-1.11.2.js"></script>
<script type="text/javascript" src="Scripts/js/jquery-ui.js"></script>
<asp:content id="BodyContent" contentplaceholderid="PlaceHolderMain" runat="server">
<asp:updatepanel id="UpdatePanel4" runat="server">
<contenttemplate>
<asp:label id="lblError" runat="server" forecolor="Red" text="">
<asp:button clientidmode="Static" id="Button1" runat="server" text="DIALOG"
="" onclientclick="Dlg();return false;">
<asp:updatepanel id="UpdatePanel6" runat="server">
<contenttemplate>
Group: | <asp:textbox id="txtGroup" runat="server">
| <asp:button id="btnQuestSave" runat="server" usesubmitbehavior="false" text="Save ASYNC" onclick="btnQuestSave_Click">
|
<script type="text/javascript">
function Dlg()
{
$('#dialogQuests').dialog({ minWidth: 840, resizable: true });
}
</script>
When I click on "Save ASYNC" button txtGroup comes empty in btnQuestSave_Click. I wonder what's wrong with my project.
|
|
|
|
|
I look at the top code block, and there are no input elements to send back to the server, called submit.
You can only submit input elements, that have to be inside the update panel.
Plus on the button, it said run the client script, and return false , canceling the the submit.
So a round trip to the server will never occur.
I don't know what to make of the bottom code, looks worst than the top code block.
But you turn off submit behavior, so it would fire the update panel.
|
|
|
|
|
Yes, I want to have my txtGroup submitted along with btnQuestSave click. And this textbox is located in the same updatepanel with the button. Regarding another button, Button1, I didn't need it to produce submit, only dlgbox popup.
|
|
|
|
|
The solution is HERE
Sys.WebForms.PageRequestManager.getInstance().add_endRequest(EndRequestHandler);
function EndRequestHandler(sender, args) {
var elId = sender._activeElement.id;
if (elId.indexOf("btnAddTypeQuest") > -1 || elId.indexOf("btnEditQuest") > -1) {
$('#dialogQuests').dialog({
maxHeight: 600,
minWidth: 840,
open: function (type, data) {
$(this).parent().appendTo("form");
}
});
}
if (args.get_error() != undefined) {
args.set_errorHandled(true);
}
}
The crucial thing is in bold
|
|
|
|
|
when I go back home , the default.aspx has also written within domain name.
for example:-
http://www.domaindname.com/default.aspx
how to remove default.aspx to the domain name.
|
|
|
|
|
Why do you want to? People can still type in http://www.domainname.com/ and it works, right? Who cares if default.aspx gets added on? You do have to load a page.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
I mean to say for example when I open asp.net c# website in local host the Default.apx page is also written within local host how to remove Default.aspx from the local host
http://localhost:50050/Default.aspx
in above line Default.aspx remove the main page.
|
|
|
|
|
I care.
It is plain ugly.
In this case there is no use in having two URI's pointing to the same resource.
When people can, they will keep links around to the URI including the aspx bit. Which means that all those links go broken should I change the default to index or whatever.
Having the default.aspx bit reveals details about the implementation that are totally irrelevant to visitors.
And those details do not serve any purpose other than increasing the burden of changing the technology stack implementing a site and that means increasing the lock-in with that technology (in other words: increasing the inertia with regard to changing it).
Pretty url's do not exist for the sole reason of being pretty...
---
http://softwareonastring.com
|
|
|
|
|
URL rewriting may be what you want then.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
How can i Add Dynamic Field in Database or XML File , when we add any Control into the Page in MVC 4.0. and when we add or Delete the new Field then no any effect made on Previous Entered Record.
Please Help me ,Very Important
|
|
|
|
|
Your question is far from being precise enough to be answered. If it's so important for you, you should take the time to explain it properly.
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|