Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2005-03-15
4
Medium Priority
?
195 Views
Last Modified: 2010-04-16
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
0
Comment
Question by:toms4u
  • 3
4 Comments
 
LVL 13

Expert Comment

by:dungla
ID: 13550849
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();
                  }
            }
      }
}
0
 
LVL 13

Expert Comment

by:dungla
ID: 13550884
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();
                  }
            }
      }
}
0
 
LVL 13

Accepted Solution

by:
dungla earned 1000 total points
ID: 13550908
Here is the Next and Detail button

private void btnNext_Click(object sender, System.EventArgs e)
{
      SqlConnection cn = null;
      SqlCommand cmd = null;
      try
      {
            string strSql = "SELECT * FROM users WHERE STUDENT_ID = ?";
            cn = new SqlConnection(CONNECTION_STRING);
            cmd = new SqlCommand(strSql, cn);
            // current id
            int intID = int.Parse(txtID.Text.Trim());
            // add parameter to command
            cmd.Parameters.Add(new SqlParameter("STUDENT_ID", SqlDbType.Int));
            cmd.Parameters["STUDENT_ID"].Value = intID + 1; // next value will have id greater than current id one value
            // assign query string to command
            cmd.CommandText = strSql;
            // open connection
            cmd.Connection.Open();
            // execute command
            SqlDataReader reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                  // fill data to form
                  txtID.Text = reader["STUDENT_ID"].ToString().Trim();
                  txtName.Text = reader["STUDENT_NAME"].ToString().Trim();
                  txtAddress.Text = reader["ADDRESS"].ToString().Trim();
                  txtPhone.Text = reader["PHONE"].ToString().Trim();
            }
            else
            {
                  MessageBox.Show("No more record!");
            }
      }
      catch (SqlException ex)
      {
            MessageBox.Show(ex.Message);
      }
      finally
      {
            if (cn != null)
            {
                  if (cn.State != ConnectionState.Closed)
                  {
                        cn.Close();
                  }
            }
      }
}

private void btnDetail_Click(object sender, System.EventArgs e)
{
      SqlConnection cn = null;
      SqlCommand cmd = null;
      try
      {
            string strSql = "SELECT * FROM users WHERE STUDENT_NAME = ?";
            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();
            // assign query string to command
            cmd.CommandText = strSql;
            // open connection
            cmd.Connection.Open();
            // execute command
            SqlDataReader reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                  // fill data to form
                  txtID.Text = reader["STUDENT_ID"].ToString().Trim();
                  txtName.Text = reader["STUDENT_NAME"].ToString().Trim();
                  txtAddress.Text = reader["ADDRESS"].ToString().Trim();
                  txtPhone.Text = reader["PHONE"].ToString().Trim();
            }
            else
            {
                  MessageBox.Show("No ecord!");
            }
      }
      catch (SqlException ex)
      {
            MessageBox.Show(ex.Message);
      }
      finally
      {
            if (cn != null)
            {
                  if (cn.State != ConnectionState.Closed)
                  {
                        cn.Close();
                  }
            }
      }
}
0
 

Author Comment

by:toms4u
ID: 13640227
really sorry for dalaying to replay.it was by my system was dead  last weeks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question