sqlcommand

Calling sqlCommand in a loop increasing exec time every step

Hi All, I've got a loop that executes the stored procedure in a loop with over 40,000 iterations, like so: SqlCommand command = new SqlCommand("WriteDataToDB"); command.Connection = _connection; command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@SignalID", SqlDbType.Int).Value = Arg_Si...

What SqlCommand.Parameters.AddWithValue really does?

Hi, What changes SqlCommand.Parameters.AddWithValue() does with the query? I expect that: It replaces every ' character by '', If a parameter value is a string or something which must be converted to a string, it surrounds the value by ', so for example select * from A where B = @hello will give select * from A where B = 'hello world...

How does dotNet handle parameterised dates where programmatic date has no time but sql date has time

Dreadful title right? Thought I'd see if Stack overflow is quicker than me testing something while I get a thousand interruptions from other work :) I'm updating an old VB net application and trying to refactor some of the logic as I go. The app looks for data from a single date across a few tables and writes that view to a file. Writi...

SQL String query

hey all i have this insert query im tryin to do but it isn't working. no matter how many variations, and variations of variations i try, it always has a problem with my code. Set rstSimple = cnnSimple.Execute("insert into SALT (Email, Username, FirstName, LastName, ActivationCode, TransactionID, ClientID) VALUES (<%=Request.QueryString...

SQL/ASP - Invalid column name 'Email'.

hey all, i am having trouble adding stuff into the Email column. I can add stuff into the Username column but for some reason i get the following error: Microsoft OLE DB Provider for SQL Server error '80040e14' Invalid column name 'Email'. When I use this code: Set rstSimple = cnnSimple.Execute("insert into SALT (Email, Username, Fi...

SQL/ASP - Expected: " - But it's there?

hey all, can somebody please help me figure this out? It's giving me error after error after error and I have no idea what its problem is. My code: <% Dim cnnSimple ' ADO connection Dim rstSimple ' ADO recordset Set cnnSimple = Server.CreateObject("ADODB.Connection") ' DSNLess cnnSimple.Open "MY CONNECTIONS STRING INFO HERE" cnnSim...

Which pattern is better for SqlConnection object?

Hi! Which pattern is better for SqlConnection object? Which is better in performance? Do you offer any other pattern? class DataAccess1 : IDisposable { private SqlConnection connection; public DataAccess1(string connectionString) { connection = new SqlConnection(connectionString); } public void Execute(stri...

C# - Output SqlParameter uses different values then the ones given??

I have a SqlCommand which runs a stored procedure that contains two integer output parameters. Right before the SqlCommand runs I can see that the output parameters are set to the correct values, however when the command actually executes, it uses a NULL for parameter1 and 0 for parameter2 (verified using SQL Profiler) regardless of what...

SqlCommand.Cancel() causes a performance boost?

I have seen this show up several places in code, never with an explanation, just a cryptic comment above it (Declaration and execution included for an idea of context. It's just a standard procedure of running a SqlCommand): //SqlCommand cmd = new SqlCommand(); //cmd.ExecuteReader(); //Read off the results //Cancel the command. This i...

.Net SQLCommand times out when executing Stored Procedure which returns no records.

Hi guys, I wonder if any one may be able to help? I have an issue with a SqlCommand object that when it executes a stored procedure that returns no records, will timeout. The stored procedure is not complicated, it is just a simple SELECT ColumnA, ColumnB, ... FROM TableA WHERE Id = @Id type of thing. If I run the SP in Sql Managment ...

SqlCommand-class force SELECT only?

Hi, I'm developing a application that's for SQL-surveillance, it's just a console application that will run each 5min on a bunch of servers. It has a config file with connection string and sql query. The thing is I would like the program to accept SELECT queries only. I colleague told me he thought I could set something in the SqlComma...

Insert with select subquery using SqlCommand

I am having a problem when using an INSERT statement with a nested select. The query works when executing it in the SQLManagement Studio but returns an error when executing it in code. The query looks like : INSERT INTO [Projects] VALUES ('1', 'None', '2', '2010/09/08 10:36:30 AM', 4, 1, 4, '6', '', 'n/a', 'no', 'n/a', 'None', 0,...

SqlCommand Parameter eating +

I have this: string a = "a+a"; SqlCommand q = new SqlCommand("SELECT * FROM table WHERE a = @a", conn); q.Parameters.AddWithValue("@a", a); But the parameterization totally erases the + from a, leaving me with a a instead of the desired a+a. I need that + in place; I just want it escaped, not removed. Is there a way I can tell C# to...

How do I add multiple rows in a table?

string date = p_text_data.Text; string sql = @"INSERT INTO Warehouse (title,count,price,date) "; try { using (SqlConnection connection = ConnectToDataBase.GetConnection()) { SqlCommand command = new SqlCommand(sql, connection); for (int i = 0; i < mdc.Count; i++) { sql += "SELECT @title" + i + ...

Why has the overloaded Add been replaced with AddWithValue?

The overloaded method SqlParameterCollection.Add(String, Object) has been marked as deprecated since version 3.5 SP1 of the .NET Framework. You should use the AddWithValue() method that was introduced with version 2.0. Is there a good reason why the Add(String, Object) has been replaced with a AddWithValue()? I'm fine with the change, b...

Why both SqlConnection and SqlTransaction are present in SqlCommand constructor?

I wonder, what is the reason to have this SqlCommand constructor overload: public SqlCommand( string cmdText, SqlConnection connection, SqlTransaction transaction ) ? When I need to create an internal method that does its bit using a transaction provided as an argument, I always find it sufficient to only pass an SqlTrans...

How to pass variable into SqlCommand statement and insert into database table

Hello, I'm writing a small program in C# that uses SQL to store values into a database at runtime based on input by the user. The only problem is I can't figure out the correct Sql syntax to pass variables into my database. private void button1_Click(object sender, EventArgs e) { int num = 2; using (SqlCeConnection...