Re: simple Connection to a database

PeteNet — Sun, 21 Jun 2009 04:49:41 GMT

here's what you can use:

        // conn and reader declared outside try
		// block for visibility in finally block
		SqlConnection conn   = null;
		SqlDataReader reader = null;

		try
		{
            string connstr = ConfigurationManager.ConnectionStrings["UnityConnectionString"].ToString();
			// instantiate and open connection
			conn =  new 
				SqlConnection(connstr);
			conn.Open();

			// 1. declare command object with parameter
			SqlCommand cmd = new SqlCommand(
                "Select * from LG_006_CLCARD WHERE DEFINITION_= @DEF", conn);
   //assuming your table in the database is indeed called 'DEFINITION_'


			// 2. define parameters used in command object
			SqlParameter param  = new SqlParameter();
            param.ParameterName = "@DEF";
            param.Value = txtAccountCode.Text;

			// 3. add new parameter to command object
			cmd.Parameters.Add(param);

			// get data stream
			reader = cmd.ExecuteReader();

			// write each record
			while(reader.Read())
			{
                //Response.Write("{0}, {1}", 
                //    reader["column1"],
                //    reader["column1"]);
                Label6.Text = txtAccountCode.Text;  //will display the value in the last record if there are multiple rows found for the select criteria
			}

            //GridView1.DataSource = reader;
            //GridView1.DataBind();
		}
		finally
		{
			// close reader
			if (reader != null)
			{
				reader.Close();
			}

			// close connection
			if (conn != null)
			{
				conn.Close();
			}
		}

you don't need the gridview to simply test a select, step through the while(reader.Read()) loop or let it write to the label

 here's a link that might help: http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson06.aspx

besides, there are many tutorials on this site itself that might help you work your way up.http://www.asp.net/learn/data-access/

simple Connection to a database

khparhami — Sun, 21 Jun 2009 03:31:03 GMT

Hi,

I'm realy lazy in database programming and I don't know why! I would like to know the best way of retrieving data from a database to fill out a few tex boxes. I have a form that will be filled by users but the header sould be filled from a database according a selected code:

I tried a few codes I found on the net but I don't know what the problem is because the data has not being shown. to test if the data is available I used a gridview as follow:

SqlConnection sqlCon;

SqlCommand sqlComm;

SqlDataReader SqlDr;

SqlParameter paramDef;

string strConstr = ConfigurationManager.ConnectionStrings["UnityConnectionString"].ConnectionString;

string strComm = "Select * from LG_006_CLCARD WHERE DEFINITION_= @DEF";sqlCon = new SqlConnection();

sqlCon.ConnectionString = strConstr;

sqlComm = new SqlCommand();

sqlComm.CommandText = strComm;

sqlComm.CommandType = CommandType.Text;

sqlComm.Connection = sqlCon;

paramDef = new SqlParameter();

paramDef.ParameterName = "@DEF";paramDef.SqlDbType = SqlDbType.VarChar;

paramDef.Size = 17;

paramDef.Direction = ParameterDirection.Input;

paramDef.Value = txtAccountCode.Text;

sqlComm.Parameters.Add(paramDef);

sqlComm.Connection.Open();

SqlDr = sqlComm.ExecuteReader(CommandBehavior.CloseConnection);

Label6.Text = txtAccountCode.Text;

GridView1.DataSource = SqlDr;

GridView1.DataBind();

sqlComm.Dispose();

sqlCon.Dispose();

please advise how to do it and how to make my database knowledge more powerfull!

Data Presentation Controls

Re: simple Connection to a database

simple Connection to a database