Posts by Marco Souza • 12,304 points
506 posts
-
2
votes3
answers27590
viewsA: How to encrypt and decrypt MD5 data using C#?
MD5 is a one-way hash function, that is, it only encrypts, what you can do is encrypt saved in the database, after that you can receive the password from a login screen (EX;) and do the encryption…
-
0
votes1
answer446
viewsA: C# Transpose Columns in rows
Using Miniature to change the dozens foreach you can use the COUNT(), something like. acertos = seq.Count(s => (LFConcursos.Any(a => a.IdConcurso == i && (a.Bola1 == s.dezena ||…
-
1
votes1
answer58
viewsA: How to work with more than one dropdownlist to mount a C#URL?
User Uribuilder public static String EditUrl() { UrlBuilder URL = new UrlBuilder("~/Painel/ResultadoBusca.aspx"); if(ddlOperacao.SelectedValue > 0) URL.AppendArgument("codigoOperacao",…
-
0
votes2
answers2233
viewsA: How to make Join of 2 tables without repeating data?
The only solution was to create a cursor as follows. declare @SR7010 table ( R7_FILIAL varchar(20), R7_MAT varchar(20), R7_DESCFUN varchar (100), R7_DATA date, R3_VALOR numeric(18,2) ) declare…
-
0
votes4
answers676
viewsA: How to make a Select from two 1-N tables and return only one record from the second?
You can create a sub select to join with a LIMIT 1 SELECT p.*, d.id_imagem, d.nome_imagem, d.caminho FROM PRODUTO p JOIN ( select * from IMAGEM m where m.ID_PRODUTO = p.ID_PRODUTO ORDER BY…
mysqlanswered Marco Souza 12,304 -
2
votes1
answer3703
viewsA: Error: Process or Function has Too Many Arguments specified
This problem can only be in passing your parameters to the proc, when you do your if (nome != null) if (cpf != null) if (cpf != null) this can be null and with that the settings will not be added to…
-
1
votes1
answer455
viewsA: How to make a Count in Mysql using case or if?
If that’s what I mean, you have to use COUNT() before the 1 increment case if the condition is true . SELECT COUNT(CASE WHEN (pl_de_manutencao = 'Liberado' || pl_de_manutencao = 'N/A' AND ra_padrao…
mysqlanswered Marco Souza 12,304 -
0
votes1
answer373
viewsA: How to check if there is scheduling in a time range?
You can create a check before doing the Insert, checking whether your table already contains the information you are passing on. declare @table table ( cod_prestador int, DateTimeInicio DateTime ,…
-
6
votes1
answer625
viewsA: How to update fields with fields from another table?
You can make a UPDATE WITH JOIN with the two tables to update the 3 columns, user or Where if it is a single record that has to be changed. UPDATE users_sg as sg join mdl_user as user_ on user_.Id =…
-
1
votes3
answers8116
viewsA: How to join 2 tables in a select?
If that’s what I got right, you want to add the column values (Qtd), but not show the same product id. If so you can use the SUM() functions to sum the values, GROUP BY to group the data. Thus;…
mysqlanswered Marco Souza 12,304 -
3
votes2
answers654
viewsA: How to list the data of an SQL query grouped in order of data presentation
One way to do this would be a course, with temporary tables to store the data from your physical table, it would look like this; declare @municipios table ( municipio varchar(100), data datetime )…
-
2
votes2
answers1413
viewsA: How to verify which is the name of the spreadsheet tab Excel C#
You can use this to get the names from the tabs of a. xlsx file. Go through all the names to read the tabs one by one. foreach (DataRow dr in ds.Rows) { string sht = dr[2].ToString().Replace("'",…
-
0
votes1
answer270
viewsA: SQL does not return all results
Try changing your query so that it sees what’s in the other table instead of doing these loops with no needs. SELECT * FROM municipios M WHERE exists ( SELECT * FROM municipios_ibge Mibge where…
-
3
votes1
answer457
viewsA: Asp.net and C# problem image gallery to display the photo
Right-click on the image and see in the html of the page if the image path is correct. As your field public string Foto { get; set; } is a string so I suppose the image path is stored in the…
-
2
votes3
answers280
viewsA: Log tables should have Foreign Keys?
Instead of me being totally out of log log, I’d better create log tables without Fks, so you can delete the logs without generate Foreign key error? It doesn’t make sense that you have an orphaned…
-
1
votes2
answers40
viewsA: How to change the value of fields in a column?
In Mysql you can use the function IFNULL(), As follows: SELECT IFNULL(UnitsOnOrder,0)) FROM Products When the Unitsonorder field is null the value will be changed to 0. IFNULL can receive various…
-
8
votes4
answers10625
viewsA: Constructing SQL tables - use or not fields with Not Null?
Need to use Not Null? You should create a column like Not Null when you see the need that that field has to be filled in at the time of the Insert. For example, imagine an address table a crucial…
-
2
votes3
answers2186
viewsA: Error "... is not a recognized built-in Function name" in SQL Server
TO_DATE is a usanda function in ORACLE, the corresponding sql server would be the use of Convert(datetime,'2015-04-08 00:00:00'), but this if its date was not in the correct form, as in the example…
sql-serveranswered Marco Souza 12,304 -
0
votes1
answer346
viewsA: Number of lines increases after use of JOIN
Your question is not very clear, but I believe it can only be because you have a 1-N (ONE TO MANY) relationship between your tables vegetable or desc. For example. Suppose your table vegetable has…
-
1
votes1
answer2207
viewsA: Effect select with dynamic column
You can’t use a FUNCTION or any other procedure within a select returns a column name of the table or view that you are using, because the return within a select in this case it is already the value…
sql-serveranswered Marco Souza 12,304 -
0
votes1
answer759
viewsA: How to make a label receive a text from the database
You can use the Checked property to set the value of your radiobutton, but you need to know what kind of data is coming from your bank. The Checked property expects a boolean as value, so if you are…
-
3
votes2
answers381
viewsA: SELECT issues with postgresql
Your mistake is because you want to use a columnar that does not exist in your context(table), ie the total column does not exist yet, you are renaming it from your sub select, if you want to use it…
-
21
votes7
answers26094
viewsA: What is the difference between parameter and argument?
Parameters Parameter represents a value that the procedure expects you to pass when you call it. The declaration of the procedure defines its parameters. Arguments An argument represents the value…
-
1
votes1
answer1048
viewsA: Can I invoke Function on a Trigger?
I can invoke Function on a trigger? Yes you can call a Function no problem. In your case to use your if you can recolpera the values of INSERT, UPDATE, DELETE of TRIGGER with the (SELECT IdProduto…
-
1
votes2
answers165
viewsA: If - Datatable condition
Another way would be to contact the ret_orc Datatable for an Asenumerable. /// Convert to AsEnumerable var qrIn = from row in ret_orc .AsEnumerable() select new { xrCheckBox1.Checked =…
-
4
votes1
answer6005
viewsA: Using Case When with Leftjoin
You can use the field itself F.COD_FORNECEDOR when the value is null. LEFT JOIN FORNECEDOR F ON F.ID_FORNECEDOR = T.ID_FORNECEDOR AND F.COD_FORNECEDOR = (CASE WHEN @COD_FORNECEDOR_FILTRO IS NOT NULL…
-
1
votes1
answer102
viewsA: Processing of text files
Just you pass a bool for your method. Controller if ((arquivosSabemi != null) && (arquivosSabemi.Any(f => f != null))) { bool ckfim = true; foreach (var arquivo in arquivosSabemi.Where(s…
-
1
votes2
answers985
viewsA: Create a property for a bool variable that handles string and int
You can create two properties one of type Objct and the other bool, the first will serve as the basis for arrow the value of the other. namespace stackoverflow { public partial class AlterarTipo :…
-
0
votes2
answers805
viewsA: Group by by two fields ordered by a third
You can use a JOIN with a subselect on it, as follows. DECLARE @TABELA TABLE ( USER_ INT, ID INT, DEVICEID VARCHAR(50), DATE_ DATETIME ) INSERT INTO @TABELA VALUES ( 7 , 14450 , 'aa6603ceef3b397c'…
-
1
votes1
answer1253
viewsA: PIVOT SQL - Dynamic Columns with Variable
In order to be able to do your PIVOT by passing parameters you will have to pass your select to a varchar variable and then use execute to execute the query as follows. declare @MES2 varchar(2) =…
-
2
votes1
answer776
viewsA: Allocation Space for Mysql Fields
When the Entity Framework generates the tables in your database it checks the types of each field, in the case of the STRING type when you specify the size it makes the same specification for the…
-
0
votes2
answers943
viewsA: Mysql Error 1215: Cannot add Foreign key Constraint
You need to use Unsigned in your table consultations create table consultas( data date not null, hora time not null, idcodm int(15) Unsigned not null, idcodp int(15) Unsigned not null, foreign key…
mysqlanswered Marco Souza 12,304 -
0
votes3
answers18706
viewsA: Use of select with SUM() function
I don’t know why you use the SUM() function since you don’t have more than one line for each user, when in reality just use the + to sum the values. declare @boletim_escolar table ( id_codaluno int,…
-
1
votes4
answers118
viewsA: select dinamico
You can do it this way; declare @tabela table ( id int, valor int, nome_campo varchar(20), item_id INT ) insert into @tabela values (1,9,'campo_1' , 22), (2,10,'campo_2' , 21), (3,2,'campo_2' , 22),…
mysqlanswered Marco Souza 12,304 -
0
votes3
answers89
viewsA: SQL - Doubt in a query
You can use a SubSelect to do this, the idea and group your data in the internal select using Min() to take the lowest value and then do the Count() no select from outside. As follows. select pront,…
-
2
votes2
answers497
viewsQ: How to concatenate two Data?
How to concatenate two dates of the type DateTime, one of which may have its value equal to NULL, I’m trying this way; DECLARE @DATA1 DATETIME = GETDATE(), @DATA2 DATETIME select…
-
2
votes2
answers785
viewsA: How to create a Stored Procedure for two tables
Yes, you can do any CRUD with a Stored Procedure for two tables or even more or even the use of CRUD (create, read, update and Delite) in a single Stored Procedure. Your mistake is because you are…
mysqlanswered Marco Souza 12,304 -
4
votes2
answers15372
viewsA: Conversion of an nvarchar data type into a datetime data type resulted in a value outside the range
Try to use the DATEFORMAT , to set the date format. SET DATEFORMAT ymd; SELECT CAST(N'2016-02-23 06:20:24.000' AS DateTime) In your query would look like this; SET DATEFORMAT ymd; INSERT…
-
2
votes2
answers80
viewsA: Query Between Mysql does not return correct!
Use the DATE() Function to convert your Datetime to a Date, so you won’t count the hours of the day. select financ_conta_id, financ_conta_cadastro from financ_conta where DATE(financ_conta_cadastro)…
mysqlanswered Marco Souza 12,304 -
1
votes1
answer206
viewsA: Comparing Columns in SQL Server
You can use a tool to compare databases. A good one is from Red-Gate has a trial version that has 14 days free license. Or Sqldbdiff This tool is available in two versions: freeware version:…
-
0
votes1
answer5678
viewsA: Return higher value of a specific SQL record
You can use the group by to group the same data and the max to get the last date. SELECT max (cob.id), animais.nome, max(cob.data) FROM animais JOIN animais_movimento cob ON animais.id =…
-
1
votes2
answers309
viewsA: MYSQL - Using SUM with Join
You have to do the SUM with the weight fields,QTD, maybe your values are coming wrong by the fact of Join. See if this solves your problem. select count(total), SUM(peso) as peso, SUM(qtd) as qtd…
mysqlanswered Marco Souza 12,304 -
2
votes1
answer1045
viewsA: Remove old Mysql records automatically
You can create an EVENT with your intervalor CURRENT_TIMESTAMP + INTERVAL 1 DAY Below are some examples of creating "Events" in Mysql: CREATE EVENT `Dropar tabela t` ON SCHEDULE AT CURRENT_TIMESTAMP…
-
0
votes2
answers323
viewsA: Show command executed when SQL error occurs
A solution and you create a variable to store the description of the block being executed and concatenate in the message. declare @BlocoExe varchar(max) BEGIN TRY -- Generate a divide-by-zero error.…
-
1
votes3
answers189
viewsA: SQL - Restrict query data
You can load the Dropdownlist from your Companies table on Page_Load, and fill in the property IsPostBack true. To load the Contributors Dropdownlist, Voce can use the event SelectedIndexChanged of…
-
2
votes2
answers232
viewsA: What is the best way to create tables with users who may have multiple emails?
The best way is for you to create your customer table with your fields, then create the email table with your fields and a secondary client key. You just need to edit your email table-client instead…
mysqlanswered Marco Souza 12,304 -
3
votes2
answers499
viewsQ: How to add one more condition in a query using Linux?
How can I add one more condition to a Join ? I have the consultation below; var consultaVeiculo = (from e in dm.Tabela_SegVeiculos join r in dm.Tabela_VeiculoRefers on e.Modelo equals…
-
2
votes1
answer50
viewsA: How to update recovering value from another table?
If the field is of the same table and the same key, you only need to set the field with the value of the other column. update contas set status='0', valor_recebido= valor_dasuacoluna where id IN…
mysqlanswered Marco Souza 12,304 -
1
votes1
answer204
viewsA: Delete rows in multiple tables
As you did not specify the database, I am responding in SQL SERVER, and tables from my database; You can take advantage of the "deleted id" table something like: begin transaction; declare…
-
1
votes1
answer2356
viewsA: Incorrect syntax error near the keyword 'FROM'
Your select this with a syntax error, when you select to set the value of select in some variable ( SET @Total =) , you need to involve all select between parentheses SET @Total = (select campo from…
sql-serveranswered Marco Souza 12,304