922 questions
1
vote
1
answer
632
views
Valid SQL Server query returning empty in System.Data.SqlClient.SqlCommand
Here is my query, it is on 2014 Adventure works.
DECLARE @l_RunSQL VARCHAR(MAX);
SELECT @l_RunSQL = COALESCE(@l_RunSQL + ' UNION ALL SELECT * FROM [', 'SELECT * FROM [') + TABLE_SCHEMA +'].['+ ...
- 844
0
votes
1
answer
4k
views
How to pass null to SQL Server stored procedure param with NON-NULL default value
I have a SQL Server stored procedure inside a multi-tenant DB. It is used by all clients, but since I don't update them all at the same time I needed to provide default parameter values so clients ...
- 2,028
3
votes
1
answer
3k
views
How to pass a variable to ODBC SQL Command in SSDT 2017?
I couldn't find the option to pass a parameter to the ODBC Source? I could see lot of article related to this but I couldn't find the same option in the Data flow Task expression. The [ODBC Source].[...
0
votes
2
answers
3k
views
How to insert into SQL Server database in a batch?
We execute SQL Server stored procedure mySP to insert into the database (say it will call the stored procedure about 300,000 times).
ALTER PROCEDURE [dbo].[mySP]
@ThePath VARCHAR(255),
@TheID ...
- 1,179
1
vote
2
answers
488
views
Incorrect syntax near ',' when i call ExecuteScalar [closed]
I have a question. I know there are some topic which are simillar to this question, but I couldn´t find answer in this topics, so this is which I need help.
I got this query:
SqlCommand cmd = new ...
- 33
0
votes
1
answer
179
views
How to specify to use Convert.DBNull automatically as sql parameter value when the original parameter is null in c#?
I need to use Convert.DBNull for every single null value while adding parameters of sqlCommands. It's tedious because I have a lot of sql commands. So it would be lot easier if there is any way like ...
-1
votes
2
answers
111
views
'ExecuteNonQuery: Connection property has not been initialized.'
I have searched this forum, and have tried a multitude of possible solutions I found, but nothing is working. can anyone shed some light on this situation? Thanks!
SqlConnection con = new ...
- 1
1
vote
2
answers
603
views
T-SQL / SQL Server : rounding after convert (decimal) SqlCommand
I want to normalize my SQL Server column before adding to datagridview, I did it in my SQL query. But I have a problem.
If the value fully dividing (for example 100/100=1) I don't want to see this ...
- 79
-1
votes
1
answer
130
views
Login Comparing hash value in a database
I am attempting to make a simple sign on portion of an app I am creating. To confirm sign in, I am just attempting to make sure that the hash value of the password entered, matches that which is ...
- 27
0
votes
3
answers
11k
views
The variable name '@ID' has already been declared. Variable names must be unique within a query batch or stored procedure
I have built a search function that will be searching by ID and by last name.
This is my code so far:
SqlCommand cmd = new SqlCommand("SELECT [ID],[Name],[LastName],[FirstName],[FinalGrade] FROM ...
- 37
2
votes
1
answer
118
views
How to get some results mid query using SqlCommand
I am using SqlCommand to perform a query, however this query is very slow in general - taking about 50 seconds to complete - is there anyway I can read the results as they come in one by one?
using (...
- 297
2
votes
1
answer
1k
views
Dynamic SQL command not getting executed
I need to convert some tables as Temporal (System versioned) ones. For this purpose, I have written an SQL command to be executed in a dynamic manner.
The query does not throw an error, but it ...
- 2,298
0
votes
0
answers
39
views
Remove decimal places from varchar(32) result
I'm using SQL and windows batch script to download inventory from our POS and then upload it to a 3rd party platform. The file is successfully downloading and uploading, but the 3rd party platform is ...
- 3
1
vote
0
answers
1k
views
DELETE C# SqlCommand: Subquery returned more than 1 value
I have an error doing either a DELETE or INSERT using a C# SqlCommand; database is SQL server.
The SQL statement is
DELETE FROM [table_name]
WHERE id = 'some_id';
which is pretty simple.
id is a ...
- 747
0
votes
0
answers
634
views
Modifying any SQL command string to remove a specific column prior to an insert or update to database
I'm trying to remove columns, which exist in ALL of our tables, before adding or updating a record to the table. This is necessary due to some features of Azure, and synchronizing tables on mobile ...
- 956
-2
votes
1
answer
157
views
System.InvalidOperationException: 'There is already an open DataReader associated with this Command which must be closed first.'
ps : i tried to close connexions and datareaders but it didn't work , also tried the previous solutions here but still got the error
i even separed the connexions and it didn't work as well
what i'm ...
1
vote
2
answers
164
views
How to select a photo from the photo table for each post by query in MySQL
I have two table I want to show one photo for every post but when the run query show post for every photo
Photo table :
+------------------------------------------------------------+
| photo_id ...
- 65
1
vote
1
answer
192
views
In a `SqlCommand`, what timespan does the `CommandTimeout` actually represent?
I am querying a Microsoft SQL Database using the SqlConnection, SqlCommand and SqlReader, much like described in https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-data-using-a-...
- 15.8k
1
vote
1
answer
2k
views
Web API to query SQL Server and return result is not working as expected
I am trying to connect to SQL Server from the Web API and return a result set as JSON. But my code shown here is not working as expected. I am trying to return the entire query response as a JSON:
[...
- 2,177
2
votes
2
answers
2k
views
Web API to connect to SQL Server and return response in JSON
I am trying to create a Web API that queries the SQL Server and returns the response in a JSON. Below is what I am trying
[HttpGet]
public HttpResponseMessage Getdetails(string ROOM)
{
...
- 2,177
0
votes
1
answer
1k
views
ADO.NET database application run time Exception
Help please with understanding what these exceptions telling me and how I can resolve them?
I've created an application with ADO.NET. The project debugger don't show any errors, though, during run ...
user9419320
3
votes
2
answers
2k
views
Why does IS NULL work but SQL Parameter with DBNull.Value for DateTime does not?
I have a C# console application that is performing a database lookup on SQL Server 2014. It queries against a table with a DateTime column called EffectiveDate which allows nulls.
If I write a ...
- 145
-1
votes
1
answer
72
views
SqlCommand that inserts data has no effect [closed]
I am trying to insert data into an existing Microsoft SQL Server 2014 table using VB.NET 2017. The code does not give me any errors, but it does not insert the record either. Right now I just want to ...
0
votes
0
answers
500
views
Parameterized SQL query in Entity framework to avoid SQL Injection
I have been using below code to execute my SQL Query, which looks something like this
SELECT abc.... FROM .... (many joins).. WHERE userid =" + userId + " AND UserState = " +userState ...; (Other ...
- 6,578
0
votes
1
answer
292
views
C#/SQL Reusing parameter for list of values
How do I generate SqlCommand for list of parameters?
How to properly reuse same parameter with new OR clause?
public class InputList
{
public string col1 { get; set; }
public string col2 { ...
1
vote
0
answers
77
views
How to send selected values with ajax to controller to use as parameters in stored procedure
I am trying to collect selected values from two drop down boxes with multiselect. Then send the data with ajax as strings to a controller to use in stored procedure for a partial view. I can see that ...
- 11
6
votes
0
answers
2k
views
C# pass parameters to Postgres Npgsqlcommand DO block
I get the error "42883: operator does not exist: integer =@ integer" Npgsql.PostgresException
when trying to pass parameters to a DO block:
var cmd = new NpgsqlCommand();
cmd.CommandText =
@"DO $$
...
- 1,336
0
votes
1
answer
270
views
Optimize usage of SqlCommand
I execute a stored procedure from C# like this:
medianOfProjects = db.ExeSQLParamByDateTime("usp_TaskStatistics_Median_Calculation", parameters, "@TaskTypeTableType", 1, startDate, endDate
...
- 105
1
vote
0
answers
50
views
Is there a drawback to specifying a large size for SqlCommand parameters?
I'm writing an application that executes queries coming from special text files with parameters coming from a user.
I use code similar to the following to add parameters:
IDbCommand command = ...
- 139
0
votes
1
answer
1k
views
How to return a single integer from stored-procedure ?
I have a sql-server 2011 stored procedure which iterates over id's received from a xml parameter and performs insert operations on two relational tables based on those Id's from xml param. The ...
- 437
-4
votes
2
answers
87
views
SQL change word command
How to change the location of the letters 5 and 6 in sql. For example:
word:weather new word:weatehr
user8419319
-1
votes
1
answer
165
views
Parse date to SQL when not in stored procedure
I am trying to parse dates to SQL but the command appears to ignore parameters:
private void updateTasksDGV(DateTime? start, DateTime? end)
{
if (start == null)
{
...
- 11
1
vote
2
answers
676
views
SqlParameter : Insert a row with a null float value [duplicate]
I have a list of float? objects.
List<float?> myList = new List<float?>{1,null,3};
I'm trying to insert this objects in a table.
using (SqlCommand oCommand = myConnection.CreateCommand())...
- 954
0
votes
1
answer
85
views
Formatting a string from a source column in SQL Parameters
I'm trying to figure out if it's possible to format a string from a sourceColumn when adding a parameter to a SqlCommand.
So something like this
cmd.Parameters.Add("@sdate", SqlDbType.VarChar, 25, "...
- 169
1
vote
1
answer
545
views
Invalid Column name asp
This is my first time creating a web api from scratch and I'm trying to get a selected value in a drop down bow to trigger an sql search and make the appropriate item appear in a text box. below is ...
- 127
3
votes
2
answers
2k
views
Why insert statement is slow with SqlCommand?
I am using SqlCommand to insert multiple records to the database but it takes long time to insert 2000 records, I did the following code:
using (SqlConnection sql = new SqlConnection(connectionString)...
- 459
1
vote
2
answers
70
views
Run multiple sqlcommands
I have a problem with executing of multiple sqlcommands. I need to update data several times. The frist sql query regards for both scenarios if,else; but then the second and third sqlcommand will be ...
- 73
0
votes
1
answer
2k
views
Retrieving SQL error message from transaction.Commit()
I have a piece of code that iterates through a list of SQL commands that should be processed all as part of one transaction. I want to have a way of knowing if that transaction was successful for ...
- 169
0
votes
2
answers
470
views
ExecuteXmlReader Fails on Specific JSON from SQL Server
I have an existing working process which receives generated JSON from store procedures in SQL Server using the for JSON directive. But upon receiving a specific text in the column data, there is a ...
- 32.2k
1
vote
1
answer
2k
views
c# ADO set context_info for SQL INSERT TRIGGER
I have to use an existing INSERT trigger which uses CONTEXT_INFO...
How can I pass "context_info" to a c# SqlCommand so that this trigger keeps working correctly?
Usage in SQL-Trigger:
select @ctxt=...
- 1,376
2
votes
1
answer
162
views
Handle leak in C# ADO.Net Application [duplicate]
Here is a short code snippet to execute a query using ADO.Net
using(SqlConnection objSqlConnection = new SqlConnection(sConnectionString))
{
objSqlConnection.Open();
using (SqlCommand ...
- 21
1
vote
2
answers
605
views
SSIS passing paramater in SQL command with ODBC
I am trying to run a variable in an expression builder SQL command for OBDC.
The expression looks like this below
"SELECT Date(Curdate()) AS 'Load Date',
Sum(CASE
...
0
votes
1
answer
75
views
how to not lose tables values in asp.net when the function page_load Avoke
I created a dynamic table in VB.NET on asp.net page and its shows right .
But when I try to read the table on a click even I wrote: tablename.rows.count it shows 0 , in me page i see the table and the ...
2
votes
4
answers
7k
views
SQL query result to array/List
Following is the C# code for querying the DB to get the list of users id:
int id;
con = new SqlConnection(Properties.Settings.Default.ConnectionStr);
con.Open();
id = 180;
SqlCommand command = new ...
- 2,051
1
vote
1
answer
880
views
Procedure Returning Blank Data Table from ASP.NET
I'm having issues with a stored procedure I'm attempting to run from an ASP.NET console application.
This is how I'm attempting to get the results in the code:
public static DataTable ...
- 303
0
votes
3
answers
5k
views
SQLCommand, create table
I want to create table in my database after button click. In Button_Click function I have a code
SqlConnection conn = new SqlConnection(@"MyConnectionString");
conn.Open();
SqlCommand cmd = new ...
- 1
0
votes
1
answer
57
views
SqlCommand string to RowdataBound Header
I have created a method with a string "test" that holds my SqlCommand query.
test = '201813'
public void temp()
{
string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
...
- 514
0
votes
1
answer
448
views
If statement using SqlDataReader value not working
I am working on a cart/basket page of an e-commerce site. Specifically: "-" link button to decrease quantity of selected product by 1 in the cart, and later the "+" link button to increase quantity of ...
- 15
-2
votes
1
answer
369
views
Transaction not set
For some reason, SqlCommand.Transaction is not getting set. The code that sets it is definitely getting called (verified in debugger), but after the property is set, the property is still null.
Here'...
- 38.2k
2
votes
4
answers
3k
views
Disposing SqlCommand
Because SqlCommand implements IDisposable, I would normally approach an ADO query as follows.
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new ...
- 68.1k