DataSource Controls :: To Write Safe Code Sqlcommand.ExecuteNonquery?

Jan 6, 2010

how to write code for database transaction(eg; Insert,delete,update) i want to write a better code

eg:sqlcommand.Commandtext="some sql insert query";

sqlcommand.parameter.Add(new sqlparameter("@cmdname","someValue"));


sqlcommand.ExecuteNonquery();

how to write the above code in better and safe way, i am expecting .net master also answer this question

put his view(pros and cons)

View 2 Replies


Similar Messages:

DataSource Controls :: Benefits Of Using SqlCommand.ExecuteNonQuery Instead Of

Mar 29, 2010

[Code]....

is used for update, insert and delete operations.Besides the fact

View 3 Replies

Benefits Of Using SqlCommand.ExecuteNonQuery?

Mar 29, 2010

a) SqlCommand.ExecuteNonQuery is used for update, insert and delete operations.

Besides the fact that by using ExecuteNonQuery instead of ExecuteReader we automatically know there won't be any query results returned, are there some other benefits/reasons why ExecuteNonQuery should be used?

b) Similarly, if we want a database operation to return a single value, we should use ExecuteScalar instead of ExecuteNonquery ,where with the latter result would be returned via SqlParameter. Is there any particular reason why we should prefer ExecuteScalar over ExecuteNonQuery?

View 2 Replies

DataSource Controls :: Why Does Cmd.ExecuteNonQuery() Return -1

Mar 3, 2010

I have created a stored procedure that reset my tables' identity value. The store procedure works fine when I didrectly run it in my ms sql server 2008. It reset my table Identity value However, when I call it from my function reset, it returns -1, and does not reset my table identitity value. The function to restet my identity value is:

[Code]....

My stored procedure is

[Code]....

To call the the function:

Dim result
As
Integer = util.resetTableIdentity("myTable", "id", 60)
util.print(result)

I got result is -1, and it does ot reset my table identity. Why? How to resove it?

View 4 Replies

DataSource Controls :: Use ArrayList In SqlCommand

May 1, 2010

I want to add array list in sqlcommand.but i don't no how to pass it. My code is.

ArrayList
ProductId = new
ArrayList();
for (int i = 0; i < ShipmentPackage.Items.Count; i++)
{
ProductId.Add(ShipmentPackage.Items[i].ProductVariant.ProductID);
}
select * from TableName where id= ProductId(ProductId is my Arraylist)

View 3 Replies

DataSource Controls :: How To Set Variable In Asp:sqldatasource In Sqlcommand In C#

Jun 28, 2010

i want to ask how to i set variable in asp:sqldatasource in sqlcommand in asp.net c#....
This is my code

asp.net
<dx:ASPxListBox ID="lsAssignToko" runat="server" DataSourceID="SqlDataSource6"
TextField="NAMA" ValueField="ID" AutoPostBack="true"
EnableCallbackMode="True" SelectionMode="CheckColumn"
OnSelectedIndexChanged="lsAssignToko_SelectedIndexChanged">
<Columns>
<dx:ListBoxColumn FieldName="ID"/>
<dx:ListBoxColumn FieldName="NAMA" />
</Columns>
</dx:ASPxListBox>
<asp:SqlDataSource ID="SqlDataSource6" runat="server"
ConnectionString="<%$ ConnectionStrings:Ora2010 %>"
ProviderName="<%$ ConnectionStrings:Ora2010.ProviderName %>"
SelectCommand="SELECT ID, NAMA FROM REF_TOKO WHERE ID IN ( :inTOKO ) ORDER BY NAMA">
<SelectParameters>
<asp:SessionParameter Name="inTOKO" SessionField="inTOKO" Size="200" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
c#
string dummy = "";
string data = "";
string inTK = "";
string inTOKO = "";
//string inTOKO1 = "";
int pot;
for (int i = lsToko.SelectedItems.Count - 1; i >= 0; i--)
{
string tok = lsToko.SelectedItem.Text.ToString();
data = lsToko.SelectedItem.Value.ToString();
dummy = dummy + "" + data + ",";
int index = lsToko.SelectedItem.Index;
lsToko.Items.RemoveAt(index);
}
if (lsToko.SelectedItems.Count <= 1)
{
inTK = dummy.Length.ToString();
pot = int.Parse(dummy.Length.ToString()) - 1;
inTOKO = dummy.Substring(0, pot);
}
Session["inTOKO"] = inTOKO;

the problem is where in my variable inTOKO consist of couple of value...
Example
while variable inTOKO = one value success but while value inTOKO = two value error
inTOKO = 1101111 => success
inTOKO = 1101111,1211321 => not success

View 1 Replies

DataSource Controls :: How To Dump Sql Statement From Sqlcommand

Jan 13, 2010

i have the followng code:

[code]...

Now what I want is how can I print the complete sql statement and try to run it in sql query of

Sql server. How can I print Sql statement from sqlcommand. the cmd.commandtext seems notworking.

View 2 Replies

DataSource Controls :: Table Isn't Updating Using Executenonquery?

Jan 20, 2010

I have stepped through this code to test it and I am puzzled as to why the update isn't going through to the DB. When I run the SQL statement in SQL editor it updates fine. All variables are getting the proper values when I step through.

[Code]....

View 5 Replies

DataSource Controls :: ExecuteNonQuery And Stored Procedure?

Jun 28, 2010

Im using above to makes sure that record is inserted to the SQL database before executing another function, but for some reason even if the record is NOT inserted other function has been called.(Other function is t update a Flag)

look at my code below and see where it goes wrong??

Part of the .Net Code
=============================================================
Private Sub GetAndInsert()[code]....

I have a feeling it could be that Rolling back is done in both .NET and SP??

View 2 Replies

DataSource Controls :: Write The Connection Code And Cofigure SQL Server To Use The Programe In Internet?

Oct 14, 2010

i am using sqlserver 2008

in my computer, sql server name is "LOCALHOET-PC" AND SECOND ONE "LOCALHOST-PCSQLEXPRESS"

I have write a code in my software like

SqlConnection conn;
conn = new SqlConnection("Data Source=LOCALHOST-PC;Initial Catalog=n4netsALT;Integrated Security=sspi");
conn.Open();

and it is working working smoothly in my computer

View 2 Replies

DataSource Controls :: Can Capture Return Value From A SQL By Using SqlConnection SqlCommand

Apr 15, 2010

can capture return value from a SQL by using SqlConnection SqlCommand

[Code]....

View 3 Replies

DataSource Controls :: ExecuteNonQuery - Declare And Execute All On One Line?

Apr 14, 2010

I'm looking for a way of being able to declare and execute a SqlCommand all on one line. At the moment I do something like:

Dim Cmd as New SqlCommand("....", Conn)
Cmd.ExecuteNonQuery

How can I do something like:

(New SqlCommand("....", Conn)).ExecuteNonQuery

View 3 Replies

DataSource Controls :: ExecuteNonQuery Requires An Open And Available Connection?

Feb 4, 2010

I've a web form named contest, where users are to register for it. When the register is clicked on, details entered by the user will be saved into the database, but before it will check if the user has already participated in the contest (using AJAX). If the user has already participated, the button will be disabled. The problem is there is an error when I click on the register button.

The error:

ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

Here's the codes I use:

[Code]....

View 7 Replies

DataSource Controls :: SqlCommand With The SqlConnection Declared Inside Or Out?

Mar 25, 2010

I'm trying to determine whether it is better to declare the connection outside with it's own using statement or to create it inside the sqlcommand itself. This is in regards to a single command interaction with the database (no loops).

[Code]....

Or

[Code]....

View 6 Replies

DataSource Controls :: How To Convert Empty Textbox Text To Null On SqlCommand

Mar 22, 2010

How would I convert an empty textbox.text to null when updating/inserting using an SqlCommand? I've got this to populate the textbox:

If IsDBNull(dr("data")) Then
TextBox1.Text = ""
Else
TextBox1.Text = dr("data")
End If

Here's the SET of the SqlCommand:

[Code]....

View 5 Replies

DataSource Controls :: Return Number Of Rows Affected For Multiple Queries In One SQLCommand?

Feb 22, 2010

I am accepting query/queries from user (our support team) in a text box where user can enter only one query or multiple queries. I need to display result of all queries entered in the textbox. If it is SELECT statement then result of that statement in grid which is done. If UPDATE/INSERT/DELETE then total number of rows affected which is also done but if only one UPDATE/INSERT/DELETE statement is entered in the textbox.

If user enters 2 UPDATE statements and then a SELECT statement then how can I get number of rows affected for individual UPDATE statements just like SQL Server Qury Analyzer displays messages in its result pane.

e.g.

(6 row(s) affected) -- first UPDATE statement

(4 row(s) affected) -- second UPDATE statement

(16 row(s) affected) -- for SELECT statement (grid will also be displayed along with these messages).

I tried almost everything, SqlDataSource (returns count of first statement only), SqlCommand.ExecuteNonQuery and DataAdapter.Fill (returns count of last statement only).

View 3 Replies

Forms Data Controls :: URL Encoding / URL Safe Code Overwritten When Databind Using Eval

Nov 3, 2010

In a website I'm working on, there is a link to ~/Products/1/2%20Inch%20Tube.aspx (1/2 Inch Tube.aspx). As I'm using a fake path the name of the URL is not a problem appart from the fact that there is a slash in the name. The URL safe code for it is %2F, and I use a replace to check for this in my SQL query (REPLACE(ProductName, '/','%2F')) AS Link, and before that in the eval statement (NavigateURL='<%# "~/Products/" & Eval("Link").ToString.Replace('/','%2F') %>'). Sadly when databinding this using an eval to a Hyperlink, the %2F changes back to a / (Slash)

Is there a work around for this or should I just not allow for slashes when saving products.

View 4 Replies

DataSource Controls :: Type Safe SQL Parameters And Update/ Insert Of Database

Feb 1, 2010

I have been in the process of updating my code with security methods, and I've been learning this from [URL] (or "Security Guidelines: ASP.NET 2.0"). In the middle of the page under "When Constructing SQL Queries, Use Type Safe SQL Parameters" it says "Use type safe parameters when constructing SQL queries to avoid possible SQL injection attacks that can occur with unfiltered input". Now, what they suggested was to use code like:

"DataSet userDataset = new DataSet();
SqlDataAdapter myCommand = new SqlDataAdapter(LoginStoredProcedure", connection);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
myCommand.SelectCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11);........"

But, I was already using code like:

"var dataSource = (SqlDataSource)form1.FindControl("sqlDataSource5") ;
dataSource.UpdateParameters.Add("someVal", val);"

So now, to use type safe parameters, I decided to include it like:

"var dataSource = (SqlDataSource)form1.FindControl("sqlDataSource5") ;
dataSource.UpdateParameters.Add("@someVal", DbType.Int16, val);
dataSource.UpdateParameters["@someVal"].Size = 1;"

So, that would be how I would modify my current code base to use type safe parameters in sql updating/inserting.

Getting to my actual question, as it was said "Use type safe parameters when constructing SQL queries to avoid possible SQL injection attacks that can occur with unfiltered input". First off, this suggests that this should apply to unfiltered input. Also, in their example they only did this for an ID.

So, what I'd like to know, when it comes to "unfiltered input", does this mean as long as the input is unfiltered I must use type safe parameters, or even filtered input shall have this (just to be sure), like, input that has been ran through a regularexpression check? Shall I do this for all values I insert/update into the database, or just IDs and important things?

The way I see it right now is that it would be a good precaution to just do type safe checks on everything (literally) that updates/inserts into the database just to be extra safe. But, I really am unsure if this is really the best idea, because if I did, would this possibly cause overprocessing of information? Can this cause too much strain on server resources? If my fears serve true, what would be a good suggestion of how I could implement this properly without having to worry about what I said?

View 1 Replies

ADO.NET :: Would It Be Safe To Use Entity Framework Code First CTP5 In Public Release

Jan 18, 2011

Im a fan of the EF code first and with its last preview of the CTP5 I wonder if it would be safe for me to use this for a smaller site for customer? I would love to get your opinions on this? And any good sources for tutorials and information would be sweet. I'm currently reading the post on scottgu's blog about it.

View 1 Replies

DataSource Controls :: How To Write SQL Datasource Programatically In VB

Aug 20, 2010

<asp:SqlDataSource ID="DS" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT * FROM [Products] WHERE ([ProductID] = @QSID)">
<SelectParameters>
<asp:QueryStringParameter DefaultValue="" Name="QSID" QueryStringField="ID"
Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>

View 1 Replies

Forms Data Controls :: Can Write An URL In A Hyperlink From Code

Aug 28, 2010

How can I write an url in a Hyperlink from code as in

Label1.Text="My Text";

I would like to be able to do something like

string MyLink;
MyLink="myurl.aspx";
ListView1.FindControl("HyperLink1").Url=MyLink;

View 10 Replies

DataSource Controls :: How To Write SQL Queries

Mar 5, 2010

SELECT 1000000*QuantityNC/Quantity AS PPM FROM [table1] However I need my WHERE clause to do thisI need the above calculation depending on BusUnit, which is linked to PartNumber, which is in the above table. also it has to be by Company (which is selected from a dropdown). And also based on the current month and year.

PartNumber and CompanyName are in [table1]
BusUnitID, PartNumber, SupplierID, are in tblParts
BusUnitID and BusUnit are in tblBusUnits

View 2 Replies

DataSource Controls :: How To Write The Sql Query Or Have To Do Programmatically

Mar 3, 2010

I have a table with columns A and B, values as follows..

A B
-- --
1 11
1 12
1 13
2 21
2 22
2 23

View 7 Replies

DataSource Controls :: Read Or Write Array In Sql

Mar 30, 2010

i have a db with two simple tables:

tbl_events:
-Event_ID int
-Event_Name varchar
-Event_Organiser varchar tbl_events_organisers
-Organiser_ID int
-Organiser_name varchar

In event_organiser of tbl_events i want to store the organiser_ID's of the second table in an array, so a record in the first table will like something like:

-Event_ID=18
-Event_Name=My event name
-Event_Organiser= 15,31,109,21

View 4 Replies

DataSource Controls :: How To Write A Function Or A Storeprocedure

Feb 17, 2010

I have a tbale in database tow tables city and Intrest I need to calculate somethign like this

city1*intrest1 + city2*Intrest2 + city3*intreste3

final result I ned to asing in other column

View 7 Replies







Copyrights 2005-15 www.BigResource.com, All rights reserved