none
Insert datagridview in SQl database

    Question

  • Hello ,

    With this code shows data from a database in a datagridview.

    And update it with the button.

    But how can i insert new data that i put in the datagridview?

    Imports System.Data.SqlServerCe
    Public Class Form1
        Dim myDA As SqlCeDataAdapter
        Dim myDataSet As DataSet
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Dim con As SqlCeConnection = New SqlCeConnection("data source=C:\Users\BAS\Documents\Visual Studio 2010\Projects\WindowsApplication11\WindowsApplication11\bin\Debug\Spullen.sdf")
            Dim cmd As SqlCeCommand = New SqlCeCommand("SELECT * FROM Inkoop", con)
        End Sub
    
    
        
    
        Private Sub Update_Click(sender As System.Object, e As System.EventArgs) Handles Update.Click
            Me.Validate()
            Me.myDA.Update(Me.myDataSet.Tables("Inkoop"))
            Me.myDataSet.AcceptChanges()
        End Sub
    
        Private Sub Laden_Click(sender As System.Object, e As System.EventArgs) Handles Laden.Click
            Dim con As SqlCeConnection = New SqlCeConnection("data source=C:\Users\BAS\Documents\Visual Studio 2010\Projects\WindowsApplication11\WindowsApplication11\bin\Debug\Spullen.sdf")
            Dim cmd As SqlCeCommand = New SqlCeCommand("SELECT * FROM Inkoop", con)
            con.Open()
            myDA = New SqlCeDataAdapter(cmd)
            'One CommandBuilder object is required, which automatically generates DeleteCommand,UpdateCommand and InsertCommand for DataAdapter object   
            Dim builder As SqlCeCommandBuilder = New SqlCeCommandBuilder(myDA)
            myDataSet = New DataSet()
            myDA.Fill(myDataSet, "Inkoop")
            DataGridView1.DataSource = myDataSet.Tables("Inkoop").DefaultView
            con.Close()
            con = Nothing
        End Sub
    End Class
    

    Saturday, April 1, 2017 3:50 PM

All replies

  • If you have not surpressed that, then the datagridview has in the bottom a new line part. 



    Success
    Cor

    Saturday, April 1, 2017 4:03 PM
  • So, as you know, there are four main things you can do with SQL: Select, Insert, update, and Delete.  It sounds like you know how to do the Select and Update.  Here is the Insert code.

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    using System.Configuration;
    
    namespace WindowsFormsApplication1
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            SqlConnection con;
            SqlCommand cmd;
            SqlDataReader dr;
            int a;
            private void button1_Click(object sender, EventArgs e)
            {
                SqlConnection con = new SqlConnection("Server=EXCEL-PC\\EXCELDEVELOPER;Database=AdventureWorksLT2012;Trusted_Connection=True"); 
                
                try
                {
                    cmd = new SqlCommand("insert into [dbo].[Student] values(@a,@b,@c)", con);
                    cmd.Parameters.AddWithValue("@a", int.Parse(textBox1.Text));
                    cmd.Parameters.AddWithValue("@b", textBox2.Text);
                    cmd.Parameters.AddWithValue("@c", textBox3.Text);
                    con.Open();
                    a = cmd.ExecuteNonQuery();
                    if (a > 0)
                    {
                        MessageBox.Show("Data Submited");
                    }
    
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    con.Close();
                }
               
            }
         }
     }

    That's kind of generic.  Here is the code to do the same for a DatGridView. 

    Note: Button1 is to select the data from the source and load it into the DataGridView, and Button2 is to Update the data from the datGridView.

    Imports System.Data.SqlClient
    Imports System.Data.OleDb
    
    Public Class Form1
        Dim myDA As OleDbDataAdapter
        Dim myDataSet As DataSet
    
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=C:\Users\Ryan\Desktop\Coding\Microsoft Access\Powerful Access Files\Nwind.mdb")
            Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Customers", con)
            con.Open()
            myDA = New OleDbDataAdapter(cmd)
            'Automatically generates DeleteCommand,UpdateCommand and InsertCommand for DataAdapter object
            Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(myDA)
    
            myDataSet = New DataSet()
    
            myDA.Fill(myDataSet, "MyTable")
            DataGridView2.DataSource = myDataSet.Tables("MyTable").DefaultView
    
            con.Close()
            con = Nothing
        End Sub
    
    
        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            Me.Validate()
            Me.myDA.Update(Me.myDataSet.Tables("MyTable"))
        End Sub
    
    End Class
    


    MY BOOK

    Sunday, April 2, 2017 1:57 AM
  • Hello,

    Yes i know how to insert a textbox in the database.

    I hope this explainse what i mean.

    I fill the datagridview with data from the database , i change it or fill the bottom line in and use Update.

    That works.

    But how do i  insert the datagridview when it is empty and i fill new data in and then insert it into the rest in the database ?

    Sunday, April 2, 2017 5:01 AM
  • Use a Where clause in your select

    Success
    Cor

    Sunday, April 2, 2017 9:03 AM
  • Hi Halfords,

    According to your description, you want to insert data in the database and show the data in the datagridview from database, I suggest you to use SqlCommandBuilder Class to get it. I do a simple sample that you can refer to.

    I put one DataGridView and two Button Controls in form,

    Imports System.Data.SqlClient
    Public Class Form1
        Public dt As DataTable
        Public sda As SqlDataAdapter
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            loaddate()
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim scb = New SqlCommandBuilder(sda)
            sda.Update(dt)
            MessageBox.Show("Update OK")
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            DataGridView1.Rows.RemoveAt(DataGridView1.CurrentCell.RowIndex)
            Dim scb = New SqlCommandBuilder(sda)
            sda.Update(dt)
            MessageBox.Show("Remove OK")
        End Sub
        Private Sub loaddate()
            Dim conn = New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C# and VB Support\Demo(VB.NET)\Demo(VB.NET)\Database1.mdf;Integrated Security=True")
            sda = New SqlDataAdapter("Select * from Demo", conn)
            dt = New DataTable
            sda.Fill(dt)
            DataGridView1.DataSource = dt
        End Sub
    End Class
    

    Best Regards,

    Cherry Bu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 3, 2017 4:45 AM
    Moderator
  • Hi Halfords,

    Please remember to close your thread by marking helpful post as answer, it is beneficial to the other communities who have same issue.

    Thanks for your understanding.

    Best Regards,

    Cherry Bu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 12, 2017 7:28 AM
    Moderator