January 8, 2022 at 3:18 pm
Hello Everyone,
I have created an SP to update data in a table "LisconfigSerialNumbers" as follows:
USE [SensorConfigTool]
GO
/****** Object: StoredProcedure [dbo].[CheckAndUpdateSerialNumberTable] Script Date: 08.01.2022 14:30:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CheckAndUpdateSerialNumberTable]
@date datetime,
@Machine_Number VARCHAR(20),
@SNDrehgeber VARCHAR(8),
@SNNeigungsgeber VARCHAR(8),
@SNAbstützung1 VARCHAR(8),
@SNAbstützung2 VARCHAR(8),
@SNAbstützung3 VARCHAR(8),
@SNAbstützung4 VARCHAR(8),
@SNArm1 VARCHAR(8),
@SNArm2 VARCHAR(8),
@SNArm3 VARCHAR(8),
@SNArm4 VARCHAR(8),
@SNArm5 VARCHAR(8),
@User VARCHAR(20)
AS
BEGIN
With cte as (
SELECT TOP 1 * FROM LisconfigSerialNumbers WHERE ([Machine Number] = @Machine_Number) Order by ID DESC)
UPDATE cte SET [Date]= @date,
[SN Rotary Sensor]= IsNull(@SNDrehgeber,[SN Rotary Sensor]),
[SN Tilt Sensor]= IsNull(@SNNeigungsgeber,[SN Tilt Sensor]),
[SN Outrigger1]=IsNull(@SNAbstützung1,[SN Outrigger1]),
[SN Outrigger2]=IsNull(@SNAbstützung2,[SN Outrigger2]),
[SN Outrigger3]=IsNull(@SNAbstützung3,[SN Outrigger3]),
[SN Outrigger4]= IsNull(@SNAbstützung4,[SN Outrigger4]),
[SN Boom1]=IsNull(@SNArm1,[SN Boom1]),
[SN Boom2]=IsNull(@SNArm2,[SN Boom2]),
[SN Boom3]=IsNull(@SNArm3,[SN Boom3]),
[SN Boom4]= IsNull(@SNArm4,[SN Boom4]),
[SN Boom5]= IsNull(@SNArm5,[SN Boom5]),
[User Email]=@User
END
And I have provided the values of the parameters using VB.Net Programming as follows:
'Process of updating latest row
Dim Cmmd As SqlCommand = New SqlCommand("CheckAndUpdateSerialNumberTable", Conn)
With Cmmd
.CommandType = CommandType.StoredProcedure
.Parameters.AddWithValue("@date", WorkDate)
.Parameters.AddWithValue("@Machine_Number",Machine_Number)
Select Case Sensor
Case "Rotary"
.Parameters.AddWithValue("@SNDrehgeber",Serial_Number)'Update SNDrehgeber column
.Parameters.AddWithValue("@SNNeigungsgeber",DBNull.Value)
.Parameters.AddWithValue("@SNAbstützung1",DBNull.Value) 'Other fields will remain same as before
.Parameters.AddWithValue("@SNAbstützung2",DBNull.Value)
.Parameters.AddWithValue("@SNAbstützung3",DBNull.Value)
.Parameters.AddWithValue("@SNAbstützung4",DBNull.Value)
.Parameters.AddWithValue("@SNArm1",DBNull.Value)
.Parameters.AddWithValue("@SNArm2",DBNull.Value)
.Parameters.AddWithValue("@SNArm3",DBNull.Value)
.Parameters.AddWithValue("@SNArm4",DBNull.Value)
.Parameters.AddWithValue("@SNArm5",DBNull.Value)
Case "Tilt"
.Parameters.AddWithValue("@SNDrehgeber",DBNull.Value)
.Parameters.AddWithValue("@SNNeigungsgeber",Serial_Number) 'If sensor type is Tilt, then update the serial number with offline data
.Parameters.AddWithValue("@SNAbstützung1",DBNull.Value) 'Other fields will remain same as before
.Parameters.AddWithValue("@SNAbstützung2",DBNull.Value)
.Parameters.AddWithValue("@SNAbstützung3",DBNull.Value)
.Parameters.AddWithValue("@SNAbstützung4",DBNull.Value)
.Parameters.AddWithValue("@SNArm1",DBNull.Value)
.Parameters.AddWithValue("@SNArm2",DBNull.Value)
.Parameters.AddWithValue("@SNArm3",DBNull.Value)
.Parameters.AddWithValue("@SNArm4",DBNull.Value)
.Parameters.AddWithValue("@SNArm5",DBNull.Value)
Case "Outrigger 1"
.Parameters.AddWithValue("@SNDrehgeber",DBNull.Value)
.Parameters.AddWithValue("@SNNeigungsgeber",DBNull.Value)
.Parameters.AddWithValue("@SNAbstützung1",Serial_Number)
.Parameters.AddWithValue("@SNAbstützung2",DBNull.Value)
.Parameters.AddWithValue("@SNAbstützung3",DBNull.Value)
.Parameters.AddWithValue("@SNAbstützung4",DBNull.Value)
.Parameters.AddWithValue("@SNArm1",DBNull.Value)
.Parameters.AddWithValue("@SNArm2",DBNull.Value)
.Parameters.AddWithValue("@SNArm3",DBNull.Value)
.Parameters.AddWithValue("@SNArm4",DBNull.Value)
.Parameters.AddWithValue("@SNArm5",DBNull.Value)
Case "Outrigger 2"
.Parameters.AddWithValue("@SNDrehgeber",DBNull.Value)
.Parameters.AddWithValue("@SNNeigungsgeber",DBNull.Value)
.Parameters.AddWithValue("@SNAbstützung1",DBNull.Value)
.Parameters.AddWithValue("@SNAbstützung2",Serial_Number)
.Parameters.AddWithValue("@SNAbstützung3",DBNull.Value)
.Parameters.AddWithValue("@SNAbstützung4",DBNull.Value)
.Parameters.AddWithValue("@SNArm1",DBNull.Value)
.Parameters.AddWithValue("@SNArm2",DBNull.Value)
.Parameters.AddWithValue("@SNArm3",DBNull.Value)
.Parameters.AddWithValue("@SNArm4",DBNull.Value)
.Parameters.AddWithValue("@SNArm5",DBNull.Value)
some other cases..............
I have some more cases to add and it's making my VB.Net Programming messy and long.
Is there any way to shorten my VB.Net Program?
I tried the following but got the error: "Procedure or function CheckAndUpdateSerialNumberTable has too many arguments specified"
Dim Cmmd As SqlCommand = New SqlCommand("CheckAndUpdateSerialNumberTable", Conn)
With Cmmd
.CommandType = CommandType.StoredProcedure
.Parameters.AddWithValue("@date", WorkDate)
.Parameters.AddWithValue("@Machine_Number",Machine_Number)
.Parameters.AddWithValue("@SNDrehgeber",DBNull.Value)
.Parameters.AddWithValue("@SNNeigungsgeber",DBNull.Value)
.Parameters.AddWithValue("@SNAbstützung1",DBNull.Value)
.Parameters.AddWithValue("@SNAbstützung2",DBNull.Value)
.Parameters.AddWithValue("@SNAbstützung3",DBNull.Value)
.Parameters.AddWithValue("@SNAbstützung4",DBNull.Value)
.Parameters.AddWithValue("@SNArm1",DBNull.Value)
.Parameters.AddWithValue("@SNArm2",DBNull.Value)
.Parameters.AddWithValue("@SNArm3",DBNull.Value)
.Parameters.AddWithValue("@SNArm4",DBNull.Value)
.Parameters.AddWithValue("@SNArm5",DBNull.Value)
Select Case Sensor
Case "Rotary"
.Parameters.AddWithValue("@SNDrehgeber",Serial_Number)'If sensor type is Rotary, then update the serial number with offline data
Case "Tilt"
.Parameters.AddWithValue("@SNNeigungsgeber",Serial_Number) 'If sensor type is Tilt, then update the serial number with offline data
Case "Outrigger 1"
.Parameters.AddWithValue("@SNAbstützung1",Serial_Number)
What is the best way of doing this task??
Thank you in advance.
Best Regards
Krishna
January 8, 2022 at 5:04 pm
once you add the parameter you can't add it again - but you can update it.
so on your last example where you have the case block you need to instead set the parameter value to your new value.
in reality you should create a function that you invoke passing the required values - and that function created and sets the cmd parameters accordingly just once before you invoke the sp
so your code would look like this (and ignore the fact that this is not valid vb code)
set all parms to null
Select Case Sensor
Case "Rotary"
set SNDrehgeber = Serial_Number
case "tilt"
set SNNeigungsgeber = Serial_number
end case
exec function execute_sp_CheckAndUpdateSerialNumberTable
private sub execute_sp_CheckAndUpdateSerialNumberTable (Machine_Number string, SNDrehgeber string, SNNeigungsgeber string, SNAbstützung1 string, SNAbstützung2 string, SNAbstützung3 string, SNAbstützung4 string, SNArm1 string, SNArm2 string, SNArm3 string, SNArm4 string, SNArm5 string, User string)
Dim Cmmd As SqlCommand = New SqlCommand("CheckAndUpdateSerialNumberTable", Conn)
With Cmmd
.CommandType = CommandType.StoredProcedure
.Parameters.AddWithValue("@date", WorkDate)
.Parameters.AddWithValue("@Machine_Number",Machine_Number)
if String.IsNullOrEmpty(Machine_Number)
.Parameters.AddWithValue("@Machine_Number",DBNull.Value)
else
.Parameters.AddWithValue("@Machine_Number", Machine_Number )
fi
repeat for all other parameters
January 8, 2022 at 6:46 pm
Hello frederico_fonseca,
Problem solved. Thank you for the idea.
January 9, 2022 at 5:40 pm
Before going much further - you need to understand the issues with .AddWithValue. There are some serious drawbacks to using that - and it will cause you issues that are almost certainly avoidable.
Here is one article: https://www.dbdelta.com/addwithvalue-is-evil/
Here is another article: https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/
One of the biggest issues with using AddWithValue is that strings can be sent as NVARCHAR and compared to a VARCHAR. This forces an implicit conversion in SQL and a table-scan even if there are indexes available.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
January 10, 2022 at 8:14 pm
One of the rules we had from the early days of structured programming was that a procedure should do one and only one thing, have one and only one entry point, etc. This means that any procedure that has "and" in its name is probably badly designed. You can look up "functional cohesion" in a good book on basic software engineering.
Another observation is that encoding schemes prefer to have fixed length columns. This allows for check digits, quick editing, and other basic design principles that ensure data quality . Finally, a table should not have repeated groups. Take a look at any of the first couple of chapters in the book on relational databases and look at "normalization", "normal forms", etc.
Why do you think data element names like "cte", or "date" are clear, exact, precise names? They beg the question as to what each of these things means. Is that a birthday? Ending date of the task? Beginning date of the task? The postfix "_date" is what we call a data characteristic, which modifies a data element. Back in the old days of COBOL and Fortran, there was no need to worry about this ISO convention because of the way the files were structured (flat or hierarchical, always local to a program, etc.), but that ended with RDBMS. . Another rule is that we don't leave embedded spaces in the name of the data element; that makes it impossible to use them anywhere else that doesn't follow the SQL "<data element name>" rules.
Good SQL programmers do not use proprietary features like TOP in their code. If your system lives for a few years, there's a good chance it will be moved to another platform or another release of the platform you're currently on, etc. Proprietary code destroys the ability to port your code.
The old proprietary Sybase ISNULL() was replaced many years ago with the ANSI/ISO standard COALESCE (). >> And I have provided the values of the parameters using VB.Net Programming as follows:..<<
This is an SQL Forum so we really don't care. In fact, the more we start writing our SQL to be dependent on one particular host programming language, the less portable and relational our code will become. You probably should post that part of your procedures on a VB forum. But you've already found out what a mess Mixing programming languages can be. Yur attempt at CREATE OROCEDURE reads kike a VB program, not SQL.
>> What is the best way of doing this task? <<
First, post actual DDL as to what your table looks like. We really get tired of guessing. Next, normalize this table. Have you ever had a course in basic software engineering? Follow the rules for correctly designing a stored procedure from that course.
Please post DDL and follow ANSI/ISO standards when asking for help.
January 10, 2022 at 9:50 pm
Good SQL programmers do not use proprietary features like TOP in their code. If your system lives for a few years, there's a good chance it will be moved to another platform or another release of the platform you're currently on, etc. Proprietary code destroys the ability to port your code.
GOOD SQL Developers use top in their code as well as anything else that their particular version of SQL Allows them to use - only things to avoid in any language are those that do not perform well.
If and when it moves to another platform then a new project will be implemented to do any code change as required - doing bad code or avoiding features thinking " in 30 years this will not work" is never an excuse or reason not to use a language feature.
January 10, 2022 at 11:27 pm
>> GOOD SQL Developers use TOP in their code as well as anything else that their particular version of SQL allows them to use - the only things to avoid in any language are those that do not perform well. <<
Why do you think that well-written code will not perform? However, one of the important principles of any kind of engineering is that if you optimize one characteristic, then you usually have to do it at the expense of all the others. The goal is to have a well-designed well-balanced system.
This is like saying it's a good idea to burn high-powered jet fuel in your automobile because it runs fast. It'll take a course in software engineering, one of the things will get into is the lifetime cost of the software. Did you know that 90% of the cost of any decent-sized software project is in maintaining it? This is based on Department of Defense and U.S. Army studies as well as several large commercial software projects. How do you feel about documentation? I hope you're not one of those "if this code was hard to write, then it should be hard to read and understand." people 🙂
>> If and when it moves to another platform then a new project will be implemented to do any code change as required - doing bad code or avoiding features thinking " in 30 years this will not work" is never an excuse or reason not to use a language feature. <<
In the modern world, it is not a matter of "if", but a matter of "when" the code will have to move to a new platform or new release of the same platform. Taking the attitude that my code should only last as long as I'm employed and I can get the hell out of there to the next job is not very professional. Would you hire an engineer who designed something which he knows is totally dependent on one and only one supplier?
Let me give an example in the SQL Server world. The BIT data type is highly proprietary and referred to what a programmer would think of as a bit; the only possible values were 0 and 1. A lot of SQL Server code was written with the assumption that such flags would default to zero. Later, Microsoft changed BIT to a numeric data type; all data types in SQL are null-able unless you explicitly declare them with a NOT NULL constraint. When Microsoft came up to ANSI/ISO standards, re-compiled code resulted in errors that were possible because columns were now defaulting to NULL. The old code ran just fine, but gave the wrong results.
Do you remember the old extended equality sign for outer joins? *= Is not the same as "
OUTER JOIN". When Microsoft was again coming up to standards, I put the new ANSI/ISO standard syntax into comments. The result was, instead of having to come up with a new project, my customer just had to drop the old text, and un-comment the correct code. Someone with your approach to programming would have had to do a new project and a lot of testing.
Could have been worse, there were other versions of the outer join different products. Informix, Gupta and Oracle were all different. But my customer found he could copy my ANSI ISO standard code almost directly into Oracle and DB2. My first paid programming job was over 55 years ago, and over those decades, I've made consulting money from repairing programs that could not port.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply