Search for fields in sql server 2008 from C # [closed]

Question

Search for fields in sql server 2008 from C # [closed]

Navigation

#1 by (2 votes)

0

My problem is this:

I have a connection with a data base sql server 2008 which extracts a number (manufacturing order) only if that number is in the database, if the number is not found send me a Messagebox where it indicates that the number is invalid. the code runs well, when I digit a number that if it is in the database it does what I need and when the number is not in the database it tells me that the number is not valid but only if I entry less than 8 characters, if I digit 9 or more the code does not compile and shows me an error in - > if (reader1.Read() == false) .

this is my code ...

class formclass
{
    SqlConnection con = new SqlConnection();
    public string ordr ;
    public string prt ;


    public string limpio()
    {
        // limpiar el estring que se escanea para hacer procesos de busqueda
        ordr = Regex.Replace(ordr, @"^~200\|(.*)~$", "$1");
        //conectar con la base de datos para verificar si existe el manufacturing order id
        SqlConnection con = new SqlConnection("Data Source=misdatabase;Integrated Security=true;");
        con.Open();
        SqlCommand co = new SqlCommand("SELECT  mfgOrder.ManufacturingOrderId FROM ShopFloorControl.ManufacturingOrder_base mfgOrder WHERE mfgOrder.ManufacturingOrderId = '" + ordr + "'", con);
        using (SqlDataReader reader1 = co.ExecuteReader())
        {
            if (co != null)
            {
                if (reader1.Read() == false)
                {
                    MessageBox.Show("Manufacturing Order invalid or empty", "", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                }
            }
            con.Close();
        }
        return ordr;
    }

If someone can help me, thank you.

c# sql-server-2008

asked by use2105 17.11.2016 в 16:41

source

1 answer

Javascript - Onclick in links Functions in c ++

score 2 · Accepted Answer

Answering your question, first of all you must validate the size of the field in the MaxLength of your TextBox so that you do not enter a number that does not contemplate the size of your field if it is a CHAR(8) in your TextBox put MaxLength = 8

On the other hand, before doing the process you indicate, you should ask the db if that number exists.

public static bool Exist(string numero)
    {
        using (var cn = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
        {
            cn.Open();
            using (var cmd = cn.CreateCommand())
            {
                cmd.CommandText = "SELECT COUNT(*) FROM MyTable WHERE codigo=@numero";
                cmd.Parameters.AddWithValue("@numero", numero);

                var count = Convert.ToInt32(cmd.ExecuteScalar());
                if (count == 0)
                    return false;
                else
                    return true;
            }
        }
    }

You use it in the following way.

bool exist = Exist(numero);
            if(exist)
                //ME ESTRAE NÚMERO
            else
                //MUESTRO EL MESSAGEBOX

On the other hand I recommend that you enclose the connection in a block using .

By enclosing the declaration of the object Connection between a block Using ... End Using , we will be sure to close the connection and destroy the resources used by the object, at the end of that block (when it is executed End Using ). With this we do not have to be aware of calling the method Close (to close the connection), nor the method Dispose (to destroy the object), therefore, it is not necessary to have an external procedure for such tasks.