asked on

VC#.NET - insertion and updation of information through textboxes to the SQL SERVER 2000

hello sir,
i am very beginner to the programming
i am explaing my test project conditions

SQL Server Service Manager Interface is
Server : TOMS\TOMS
Services: SQL Server
SQL is installed on a desktop XP system

Form1 Details:
label1(text=enter your name) corresponding textbox1
label2(text=enter your address) corresponding textbox2
label3(text=enter your phone number) correspondig textbox3

button1(text=update)
button2(text=show details)
button3(text=insert)
button4(text=next)

database details:
servername: TOMS\TOMS
user id: sa
password :123456

database name : toms
table name :users
columns are :STUDENT_ID,ADDRESS,PHONE

i already inserted 2 rows of information in this table.

my needs are:
( 1) i am entering a name in the 'textbox1' and pressing ' showdetails button '.then i want to show the address and phone informations on the corresponding textboxes....then if i am pressing
' next button' then i want to show next row.....

(2) when i am entering the all 3 fields and pressing the insert button then i want to insert data into the same table 'users' in the same database ' toms' and show a message "your data saved".
(3) now i want to change address and phone details for a particular user ...for this i will enter a name ,address and phone in the corresponding textboxes.....if this name is not in the users table , i want to throw an exception message "the name does not exists" continue the processes....otherwise do update the database and show a message "your data has been updated..."

hopely waiting for a solution in VC#.NET in connection oriented data access method and disconnected data access method....in exact coding with my details i have explained above because i am very beginner to the programming......
please give me a replay as soon as possible

thanking you

toms

dungla

First thing, your table does not have Name field. Here is an example

database name : test
table name : users
columns are : STUDENT_ID (Identity, AutoIncrement by 1), STUDENT_NAME (Unique), ADDRESS, PHONE

here is the code of form

private const string CONNECTION_STRING = "Provider=SQLOLEDB;Data Source=(local);User ID=sa;Password=khongbiet;Initial Catalog=test;";
private void btnInsert_Click(object sender, System.EventArgs e)
{
      SqlConnection cn = null;
      SqlCommand cmd = null;
      // check empty field first
      if (txtName.Text.Trim() == string.Empty)
      {
            MessageBox.Show("Name cannot be null");
            txtName.Focus();
            return;
      }
      try
      {
            string strSql = "INSERT INTO users (STUDENT_NAME, ADDRESS, PHONE) VALUES(?, ?, ?)";
            cn = new SqlConnection(CONNECTION_STRING);
            cmd = new SqlCommand(strSql, cn);
            // add parameter to command
            cmd.Parameters.Add(new SqlParameter("STUDENT_NAME", SqlDbType.VarChar));
            cmd.Parameters["STUDENT_NAME"].Value = txtName.Text.Trim();
            cmd.Parameters.Add(new SqlParameter("ADDRESS", SqlDbType.VarChar));
            cmd.Parameters["ADDRESS"].Value = txtAddress.Text.Trim();
            cmd.Parameters.Add(new SqlParameter("PHONE", SqlDbType.VarChar));
            cmd.Parameters["PHONE"].Value = txtPhone.Text.Trim();
            // assign query string to command
            cmd.CommandText = strSql;
            // open connection
            cmd.Connection.Open();
            // execute command
            cmd.ExecuteNonQuery();
      }
      catch (SqlException ex)
      {
            // if existed user, command will throw exception for duplicate value in NAME field
            // we no need to check for existed user
            MessageBox.Show(ex.Message);
      }
      finally
      {
            if (cn != null)
            {
                  if (cn.State != ConnectionState.Closed)
                  {
                        cn.Close();
                  }
            }
      }
}

dungla

Here is the Update button

private void btnUpdate_Click(object sender, System.EventArgs e)
{
      SqlConnection cn = null;
      SqlCommand cmd = null;
      try
      {
            string strSql = "UPDATE users SET STUDENT_NAME = ? , ADDRESS = ?, PHONE = ? WHERE STUDENT_ID = ?";
            cn = new SqlConnection(CONNECTION_STRING);
            cmd = new SqlCommand(strSql, cn);
            // add parameter to command
            cmd.Parameters.Add(new SqlParameter("STUDENT_NAME", SqlDbType.VarChar));
            cmd.Parameters["STUDENT_NAME"].Value = txtName.Text.Trim();
            cmd.Parameters.Add(new SqlParameter("ADDRESS", SqlDbType.VarChar));
            cmd.Parameters["ADDRESS"].Value = txtAddress.Text.Trim();
            cmd.Parameters.Add(new SqlParameter("PHONE", SqlDbType.VarChar));
            cmd.Parameters["PHONE"].Value = txtPhone.Text.Trim();
            cmd.Parameters.Add(new SqlParameter("STUDENT_ID", SqlDbType.Int));
            cmd.Parameters["STUDENT_ID"].Value = txtID.Text.Trim(); // txtID is the textbox to store ID of student and it will invisible
            // assign query string to command
            cmd.CommandText = strSql;
            // open connection
            cmd.Connection.Open();
            // execute command
            cmd.ExecuteNonQuery();
      }
      catch (SqlException ex)
      {
            // if existed user, command will throw exception for duplicate value in NAME field
            // we no need to check for existed user
            MessageBox.Show(ex.Message);
      }
      finally
      {
            if (cn != null)
            {
                  if (cn.State != ConnectionState.Closed)
                  {
                        cn.Close();
                  }
            }
      }
}

ASKER CERTIFIED SOLUTION

dungla

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

toms4u

ASKER

really sorry for dalaying to replay.it was by my system was dead last weeks