?
Solved

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

Posted on 2005-03-15
4
Medium Priority
?
188 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

801 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