How to transform an input string using substring

Question

0.00/5 (No votes)

See more:

hello and good day..
I have a textbox that will receive an input and store it in database. However, i need to convert my input to get the real data format that I want..

for example, i have an input 00000155P8333 and i want to convert it into ABCP8333.

this is my query but it does not function and I don't get any error messages and i put it in cls.Insertdesk file .. "en" is the input value..

"Insert into deskmst(desk_no, lastdate)  values('" + en + "', sysdate) AND subst('" + en + "',9,5) = ABC" ;

this is my aspx.cs file

protected void InvisButton_Click(object sender, EventArgs e)
      {
          string desk = Scancuba.Text;
          resultscan.Text = "";


          if (desk == "" || desk.Length == 13)
          {
              resultscan.Text = "Please Scan again";
              resultscan.Style.Add("background-color", "#FF0000");

          }
         else
          {

              bool result = Cls.Insertdesk(desk);
              if (result == true)
              {
                  resultscan.Text = "Insert Success   " + desk;
                  resultscan.Style.Add("background-color", "#90ee90");
                  //ModalPopupExtenderEditMessage.Show();
              }
              else
              {

                  resultscan.Text = "Insert Fail   " + desk + "   already exists !  "  ;
                  resultscan.Style.Add("background-color", "#FF0000");
                  //ModalPopupExtenderEditMessage.Show();
              }
          }
         // Scancuba.Focus();
          DataView dv = GetData();
          Jadual.DataSource = dv;
          Jadual.DataBind();
      }

when i run the code,any input will display "Insert fail" message.. Does anyone know what I did wrong here? I'm a beginner so I'm sorry if my mistakes is silly

What I have tried:

i have tried to code the query in the .cs file but it give me an error as i need to use 3 arguments substring..

Posted 20-Nov-19 22:21pm

Member 14659146

Updated 20-Nov-19 22:43pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RickZeeland · Accepted Answer · 2019-11-20T22:41:00

Solution 1

For SQL Server see example here: SQL Server SUBSTRING() Function[^]
So that would be:

SELECT 'ABC' + SUBSTRING('00000155P8333', 9, 5) AS ExtractString;

Posted 20-Nov-19 22:41pm

RickZeeland

OriginalGriff · Accepted Answer · 2019-11-20T22:44:00

Don't do it like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?