如何从MS SQL Server检索数据 [英] How to retrive data from ms sql server

问题描述

例如我有一个下拉框，
例如:我在下拉菜单中选择产品名称，即:冰淇淋
冰淇淋的价格必须显示在文本框中，而数据必须从数据库中检索.

这是我使用过的代码，但无法检索数据:
我需要解决方案，我需要您的帮助.

for example i am having a drop down box ,
eg: i am selecting product name in drop down, ie:ice cream
The price of the ice cream have to display in text box , that data have to retrieve from database.

This is the code , which i used , but data not retrieve :
I need the solution , i need your help.

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Configuration;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Net;
using System.Net.Mail;
using System.Data.SqlClient;


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

    public string connectionString = WebConfigurationManager.ConnectionStrings["conWeb"].ConnectionString;
    public SqlConnection cn;
    public SqlCommand cmd;

    #region DECLARATIONS
    private string mailTo;
    private string mailFrom;
    private string mailMessage;
    private string mailSubject;
    private string mailReply;
    #endregion

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            try
            {
                cn = new SqlConnection(connectionString);
                string strQuery = "Select id,Tabletname,Price from tb_addproduct";
                SqlDataAdapter da = new SqlDataAdapter(strQuery, cn);
                DataSet ds = new DataSet();
                cn.Open();
                da.Fill(ds);
                ddllist.DataSource = ds;
                ddllist.DataTextField = "Tabletname";
                txtprice.ReadOnly.ToString();
                ddllist.DataValueField = "id";
                ddllist.DataBind();
                ddllist.Items.Insert(0, "--Select Tablet--");
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
    protected void btsubmit_Click(object sender, EventArgs e)
    {
       
        if (SubmitDetails())
        {
            lblMsg.Visible = true;
            lblMsg.ForeColor = System.Drawing.Color.Blue;
            lblMsg.Font.Bold = true;
            lblMsg.Font.Size = 10;
            lblMsg.Text = "Your request has been submitted";
            //ClearValues(this);
            ddllist.Text = "";
            Qty1.Text = "";
            txtprice.Text = "";
            email.Text = "";
            ads.Text = "";
        }
        else
        {
            lblMsg.Visible = true;
            lblMsg.ForeColor = System.Drawing.Color.Red;
            lblMsg.Font.Bold = true;
            lblMsg.Font.Size = 14;
            lblMsg.Text = "Try Again Sometime Later";
        }
    }
    private bool SubmitDetails()
    {
        try
        {
            mailTo = "[e-mail]";
            mailFrom = email.Text;
            mailSubject = "Order Details";
            mailReply = email.Text;
            mailMessage = "<table><tr><td height=''4''>&nbsp;</td></tr><tr><td></td></tr><tr><td height=''8''></td></tr><tr><td align=''center''><table><tr><td>Tablet 1:</td><td>"
                + txtprice.Text + "</td></tr><tr><td>Price:</td><td>"
                + ddllist.Text + "</td></tr><tr><td>Quantity:</td><td>"
                + Qty1.Text + "</td></tsr><tr><td>Emailid:</td><td>"
                + email.Text + "</td></tsr><tr><td>Address:</td><td>"
                + ads.Text + "</td></tr></table></td></tr></table>";
            MailMessage myMailMessage = new MailMessage(mailFrom, mailTo);
            myMailMessage.Subject = mailSubject;
            myMailMessage.Body = mailMessage;
            myMailMessage.IsBodyHtml = true;
            myMailMessage.ReplyTo = new MailAddress(mailReply);
            // They recommend all mails from this mail ID...
            System.Net.NetworkCredential mailAuthentication = new System.Net.NetworkCredential("[e-mail]", "test1234");
            //They provide this host name and port no....
            System.Net.Mail.SmtpClient mailClient = new System.Net.Mail.SmtpClient("smtp.gmail.com", 587);
            mailClient.EnableSsl = true;
            mailClient.UseDefaultCredentials = false;
            mailClient.Credentials = mailAuthentication;
            mailClient.Send(myMailMessage);
            return true;
        }
        catch
        {
            return false;

        }
    }
    protected void ddllist_SelectedIndexChanged(object sender, EventArgs e)
    {
       
        try
        {
            cn = new SqlConnection(connectionString);
            string strQuery = "Select id,Price from tb_addproduct where Price=''" + txtprice.Text + "''";
            SqlDataAdapter da = new SqlDataAdapter(strQuery, cn);
            DataSet ds = new DataSet();
            cn.Open();
            da.Fill(ds);
            da.SelectCommand.Parameters.AddWithValue("id", Convert.ToInt32(ddllist.SelectedValue.ToString()));
            
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    void FullGrid()
    {
        try
        {
            cn = new SqlConnection(connectionString);
            string strQuery = "Select id,Tabletname,Price from tb_addproduct where pid=''" + ddllist.SelectedItem.Value + "'' and Price =''"  + txtprice.Text + "''" ;
            SqlDataAdapter da = new SqlDataAdapter(strQuery, cn);
            DataSet ds = new DataSet();
            cn.Open();
            da.Fill(ds);
          
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public void datashow()
    {


        string nameshow = ddllist.Text.ToString().Trim();

        cmd.CommandText = "select * from tb_addproduct where Tabletname=''" + nameshow + "'' ";

        cmd.Connection = cn;
        SqlDataAdapter da = new SqlDataAdapter();

        DataSet ds = new DataSet();
        da = new SqlDataAdapter(cmd);

        da.Fill(ds);

        cn.Open();

        cmd.ExecuteNonQuery();
        ddllist.DataSource = ds;
        ddllist.DataBind();
        cn.Close();
    }
}

推荐答案

protected void ddllist_SelectedIndexChanged(object sender, EventArgs e)
{
  try
  {
    cn = new SqlConnection(connectionString);
    string strQuery = "Select id,Price from tb_addproduct where Price=''" + txtprice.Text + "''";
    DatatTable dt=new ClsForConnection().GenerateDataTable(strQuery );
    
    if(dt.Rows.Count>0)
    {
      txtprice.Text=dt.Rows[0]["Price"].ToString();
    }
            
  }
  catch (Exception ex)
  {
    throw ex;
  }
}

//in clsForConnection Class
 public DataTable GenerateDataTable(string SqlStr)
    {
        try
        {
            SqlDataAdapter m_SdaDept = new SqlDataAdapter(SqlStr, cn);
            Mcon.Open();
            DataTable m_DtDept = new DataTable();
            m_SdaDept.Fill(m_DtDept);
            return m_DtDept;
        }
        catch (Exception)
        {
            return null;
        }
        finally
        {
            Mcon.Close();
        }
    }

ClsFor Connection是我在其中编写连接编码的类.创建该类.最好在代码页本身而不是DatatTable中添加函数GenerateDataTable dt = new ClsForConnection().GenerateDataTable(strQuery);写
DatatTable dt = GenerateDataTable(strQuery);

< pre>公共数据表GenerateDataTable(字符串SqlStr)
{
试试
{
SqlDataAdapter m_SdaDept =新的SqlDataAdapter(SqlStr，cn);
cn.Open();
DataTable m_DtDept = new DataTable();
m_SdaDept.Fill(m_DtDept);
返回m_DtDept;
}
catch(Exception)
{
返回null;
}
终于
{
cn.Close();
}
}</pre>

ClsFor Connection is a class where i write codings for connection.. You need not create that class. Better u add the function GenerateDataTable in your code page itself and instead of DatatTable dt=new ClsForConnection().GenerateDataTable(strQuery ); write
DatatTable dt=GenerateDataTable(strQuery );

<pre>public DataTable GenerateDataTable(string SqlStr)
{
try
{
SqlDataAdapter m_SdaDept = new SqlDataAdapter(SqlStr, cn);
cn.Open();
DataTable m_DtDept = new DataTable();
m_SdaDept.Fill(m_DtDept);
return m_DtDept;
}
catch (Exception)
{
return null;
}
finally
{
cn.Close();
}
}</pre>

这篇关于如何从MS SQL Server检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！