如何使用所选值保存数据？我可以在我的数据库中保存数据，但保存的数据是ID。 [英] How can I save the data with selected value? I can save data in my database but the saved one is the ID.

问题描述

我的问题是终端和目的地被保存为ID。我该如何解决？谢谢你的帮助。



它是ListFieldStatusCodes和DropDownListStatusCodes_SelectedIndexChanged



这是我的代码的cs



我尝试了什么：

My problem here is that the terminal and Destination is saved as a ID. How can i Fix it? Thank you for your help.

It is ListFieldStatusCodes and DropDownListStatusCodes_SelectedIndexChanged

Here is the cs of my Codes

What I have tried:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

//add the following
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;


public partial class TripInsert : System.Web.UI.Page
{

  
    string connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
    DataSet ds = new DataSet();
    protected static System.IO.Stream fs;
    protected static System.IO.BinaryReader br;
    protected static Byte[] bytes;
    public class clsUIUtility
    {

        public static DataTable ExecuteQuery(string SQLstring)
        {
            string sConstr = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
            SqlConnection Conn = new SqlConnection(sConstr);
            DataTable dt = new DataTable("tbl");

            using (Conn)
            {
                Conn.Open();
                SqlCommand comm = new SqlCommand(SQLstring, Conn);
                comm.CommandTimeout = 0;
                SqlDataAdapter da = new SqlDataAdapter(comm);
                da.Fill(dt);
            }

            return dt;
        }

        public static void FillCombo(DropDownList dropDownList, string dataValueField, string dataTextField, DataTable dataTbl, bool bHasBlank)
        {
            dropDownList.DataTextField = dataTextField;
            dropDownList.DataValueField = dataValueField;
            dropDownList.DataSource = dataTbl;
            dropDownList.DataBind();

            if (bHasBlank)
                dropDownList.Items.Insert(0, new ListItem());
        }
    }
    
   
        

       


    private void ListFieldStatusCodes()
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            string cmdstr = "SELECT TerminalID, Terminal FROM Terminal";
            SqlCommand cmd = new SqlCommand(cmdstr, connection);
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            SqlDataReader rdr;
            DataTable dt = new DataTable("tbl");
            rdr = cmd.ExecuteReader();

            DropDownListStatusCodes.Items.Clear();

            if (rdr.HasRows)
            {
                while (rdr.Read())
                {
                    DropDownListStatusCodes.Items.Add(rdr["Terminal"].ToString());
                }
            }
            DropDownListStatusCodes.DataSource = dt;
            DropDownListStatusCodes.DataTextField = "Terminal";
            DropDownListStatusCodes.DataValueField = "TerminalID";
            DropDownListStatusCodes.DataBind();
            clsUIUtility.FillCombo(DropDownListStatusCodes, "TerminalID", "Terminal", clsUIUtility.ExecuteQuery("SELECT TerminalID, Terminal FROM Terminal"), false);
            DropDownListStatusCodes.Items.Insert(0, new ListItem(""));

            DropDownListStatusCodes.SelectedIndex = 0;


            connection.Close();
            rdr.Close();
        }
    }




    private void ListFieldStatusCodes2()
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            string cmdstr = "SELECT DepartureTime FROM DepartureTime";
            SqlCommand cmd = new SqlCommand(cmdstr, connection);
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            SqlDataReader rdr;

            rdr = cmd.ExecuteReader();

            DropDownListStatusCodes2.Items.Clear();

            if (rdr.HasRows)
            {
                while (rdr.Read())
                {
                    DropDownListStatusCodes2.Items.Add(rdr["DepartureTime"].ToString());
                }
            }

            DropDownListStatusCodes2.SelectedIndex = 0;

            connection.Close();
            rdr.Close();
        }
    }

    private void ListFieldStatusCodes3()
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            string cmdstr = "SELECT BusType FROM BusType";
            SqlCommand cmd = new SqlCommand(cmdstr, connection);
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            SqlDataReader rdr;

            rdr = cmd.ExecuteReader();

            DropDownListStatusCodes3.Items.Clear();

            if (rdr.HasRows)
            {
                while (rdr.Read())
                {
                    DropDownListStatusCodes3.Items.Add(rdr["BusType"].ToString());
                }
            }

            DropDownListStatusCodes3.SelectedIndex = 0;

            connection.Close();
            rdr.Close();
        }
    }
    private void ListFieldStatusCodes4()
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            string cmdstr = "SELECT Fare FROM Fare";
            SqlCommand cmd = new SqlCommand(cmdstr, connection);
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            SqlDataReader rdr;

            rdr = cmd.ExecuteReader();

            DropDownListStatusCodes4.Items.Clear();

            if (rdr.HasRows)
            {
                while (rdr.Read())
                {
                    DropDownListStatusCodes4.Items.Add(rdr["Fare"].ToString());
                }
            }

            DropDownListStatusCodes4.SelectedIndex = 0;

            connection.Close();
            rdr.Close();
        }
    }

    private void ListFieldStatusCodes5()
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            string cmdstr = "SELECT BusID FROM Bus WHERE Availability='Available'";
            SqlCommand cmd = new SqlCommand(cmdstr, connection);
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            SqlDataReader rdr;

            rdr = cmd.ExecuteReader();

            DropDownListStatusCodes5.Items.Clear();

            if (rdr.HasRows)
            {
                while (rdr.Read())
                {
                    DropDownListStatusCodes5.Items.Add(rdr["BusID"].ToString());
                }
            }

            DropDownListStatusCodes5.SelectedIndex = 0;

            connection.Close();
            rdr.Close();
        }
    }
    protected void Page_Load(object sender, EventArgs e)
    {


        if (!IsPostBack)
        {
            ListFieldStatusCodes();
            ListFieldStatusCodes2();
            ListFieldStatusCodes3();
            ListFieldStatusCodes4();
            ListFieldStatusCodes5();
            //DropDownListStatusCodes.SelectedValue = "Available";

            lblSuccessInsert.Visible = false;


        }

    }


    protected void btnSave_Click(object sender, EventArgs e)
    {

        try
        {


            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(
                "INSERT INTO Trip (Terminal, DepartureTime, Destination , " +
                "BusType, Fare, BusID) " +

                "VALUES (@Terminal, @DepartureTime, @Destination, @BusType, " +
                "@Fare, @BusID)", connection);


             
                command.Parameters.Add("@Terminal",
                SqlDbType.NVarChar, 50).Value = DropDownListStatusCodes.Text;

                command.Parameters.Add("@DepartureTime",
                 SqlDbType.NVarChar, 50).Value = DropDownListStatusCodes2.Text;

                command.Parameters.Add("@Destination",
                            SqlDbType.NVarChar, 50).Value = DropDownListStatusCodes1.Text;

                command.Parameters.Add("@BusType",
               SqlDbType.NVarChar, 50).Value = DropDownListStatusCodes3.Text;

                command.Parameters.Add("@Fare",
               SqlDbType.NVarChar, 50).Value = DropDownListStatusCodes4.Text;

                command.Parameters.Add("@BusID",
              SqlDbType.Int).Value = DropDownListStatusCodes5.Text;

                connection.Open();
                command.ExecuteNonQuery();

                connection.Close();



                lblSuccessInsert.Visible = true;
            }
        }
        catch (Exception ex)
        {
            string source,
            message;
            source = ex.Source.ToString().Replace(System.Environment.NewLine, " ");
            message = ex.Message.ToString().Replace(System.Environment.NewLine, " ");

            Response.Redirect("/messages/error.aspx?source=" + source + "&message=" + message);
        }

    }


    protected void btnClear_Click(object sender, EventArgs e)
    {
     
     


        lblSuccessInsert.Visible = false;
    }




    protected void btnSelect_Click(object sender, EventArgs e)
    {

    }


    protected void DropDownListStatusCodes_SelectedIndexChanged(object sender, EventArgs e)
    {


        if (DropDownListStatusCodes.SelectedIndex > 0)

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string cmdstr = "SELECT DestinationID, Destination FROM Destination WHERE TerminalID=" + DropDownListStatusCodes.SelectedValue + " ORDER BY Destination";
                SqlCommand cmd = new SqlCommand(cmdstr, connection);
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                SqlDataReader rdr;
                DataTable dt = new DataTable("tbl");
                rdr = cmd.ExecuteReader();

                DropDownListStatusCodes1.Items.Clear();

                if (rdr.HasRows)
                {
                    while (rdr.Read())
                    {
                        DropDownListStatusCodes1.Items.Add(rdr["Destination"].ToString());
                    }
                }
                DropDownListStatusCodes1.DataSource = dt;
                DropDownListStatusCodes1.DataTextField = "Destination";
                DropDownListStatusCodes1.DataValueField = "DestinationID";
                DropDownListStatusCodes1.DataBind();
                clsUIUtility.FillCombo(DropDownListStatusCodes1, "DestinationID", "Destination", clsUIUtility.ExecuteQuery("SELECT DestinationID, Destination FROM Destination WHERE TerminalID=" + DropDownListStatusCodes.Text + " ORDER BY Destination"), false);




                connection.Close();
                rdr.Close();
            }
        else
        {
            DropDownListStatusCodes1.Items.Clear();
        }
    }
}

推荐答案

更正

corrections

protected void DropDownListStatusCodes_SelectedIndexChanged(object sender, EventArgs e)
   {
       if (DropDownListStatusCodes.SelectedIndex > 0)

           using (SqlConnection connection = new SqlConnection(connectionString))
           {
               connection.Open();
               string cmdstr = "SELECT DestinationID, Destination FROM Destination WHERE TerminalID=@TerminalID ORDER BY Destination";
               SqlCommand cmd = new SqlCommand(cmdstr, connection);
               cmd.Parameters.AddWithValue("@TerminalID",DropDownListStatusCodes.SelectedValue);
               SqlDataAdapter adp = new SqlDataAdapter(cmd);
               DataTable dt = new DataTable("tbl");
               adp.Fill(dt);
               DropDownListStatusCodes1.DataSource = dt;
               DropDownListStatusCodes1.DataTextField = "Destination";
               DropDownListStatusCodes1.DataValueField = "DestinationID";
               DropDownListStatusCodes1.DataBind();

           }
       else
       {
           DropDownListStatusCodes1.Items.Clear();
       }
   }

private void ListFieldStatusCodes()
       {
           using (SqlConnection connection = new SqlConnection(connectionString))
           {

               string cmdstr = "SELECT TerminalID, Terminal FROM Terminal";
               SqlCommand cmd = new SqlCommand(cmdstr, connection);
               SqlDataAdapter adp = new SqlDataAdapter(cmd);
               DataTable dt = new DataTable("tbl");
               adp.Fill(dt);
               DropDownListStatusCodes.DataSource = dt;
               DropDownListStatusCodes.DataTextField = "Terminal";
               DropDownListStatusCodes.DataValueField = "TerminalID";
               DropDownListStatusCodes.DataBind();
               DropDownListStatusCodes.Items.Insert(0, new ListItem(""));
               DropDownListStatusCodes.SelectedIndex = 0;
           }
       }

注意：

格式化sql查询字符串易受攻击到 SQL注入 [ ^ ]攻击

始终使用参数化查询防止SQL Server中的SQL注入攻击 [ ^ ]

Note:
Formatting the sql Query string is vulnerable to SQL Injection[^] attacks
always use Parameterized queries to prevent SQL Injection Attacks in SQL Server[^]

这篇关于如何使用所选值保存数据？我可以在我的数据库中保存数据，但保存的数据是ID。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！