Insert & Edit Data in DataGrid

Hi,
I use databinding.addnew() but when I try to type data in cell an error message apper(DBNULL Exception)....
Pls let me konw: How can I insert or edit data in DataGrid

Thanks

Hi,
To edit,delete and update data u have to write this codes. Drag n drop the appropriate button control needed.This code is in vb.net windows appln.

Imports System.Data.SqlClient
Imports System.Windows.Forms
Public Class num
Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
Dim con As New SqlClient.SqlConnection
Dim com As New SqlCommand
Dim adap As New SqlDataAdapter
Dim ds As New DataSet
con = New SqlConnection("data source=niqotine;initial catalog=record;uid=sa;pwd=mindworks")
con.Open()
com.CommandText = "insert into UserInfo values('" + txtname.Text + "','" + txtid.Text + "','" + cmbcolor.Text + "')"
com.Connection = con
com.ExecuteNonQuery()
adap = New SqlDataAdapter("select * from UserInfo", con)
adap.Fill(ds, "1")
DataGridView1.DataSource = ds.Tables("1")
End Sub
Private Sub num_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim con As New SqlConnection
Dim adap As New SqlDataAdapter
Dim ds As New DataSet
con = New SqlConnection("data source=niqotine;initial catalog=record;uid=sa;pwd=mindworks")
con.Open()
adap = New SqlDataAdapter("select * from UserInfo", con)
adap.Fill(ds, "1")
DataGridView1.AutoGenerateColumns = True
DataGridView1.DataSource = ds.Tables("1")
End Sub
Private Sub btnclear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnclear.Click
txtname.Text = ""
txtid.Text = ""
cmbcolor.Text = ""
End Sub
Private Sub DataGridView1_CellBeginEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellCancelEventArgs) Handles DataGridView1.CellBeginEdit
Dim con As New SqlConnection
Dim com As New SqlCommand
MsgBox("Password Needed")
Me.WindowState = FormWindowState.Minimized
pwd.Show()
End Sub
Private Sub DataGridView1_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellValueChanged
Dim con As New SqlConnection
Dim com As New SqlCommand
Dim ds As New DataSet
Dim adap As New SqlDataAdapter
con = New SqlConnection("data source=niqotine;initial catalog=record;uid=sa;pwd=mindworks")
con.Open()
MsgBox(e.RowIndex)
com.CommandText = "update UserInfo set sname='" & DataGridView1.CurrentRow.Cells(0).Value & "',color='" & DataGridView1.CurrentRow.Cells(2).Value & "' where idno='" & DataGridView1.CurrentRow.Cells(1).Value & "'"
com.Connection = con
MsgBox(e.GetHashCode())
com.ExecuteNonQuery()
adap = New SqlDataAdapter("select * from UserInfo", con)
adap.Fill(ds, "1")
DataGridView1.DataSource = ds.Tables("1")
End Sub
Private Sub btn_delete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_delete.Click
Dim con As New SqlConnection
Dim com As New SqlCommand
Dim adap As New SqlDataAdapter
Dim ds As New DataSet
Dim str As String
con = New SqlConnection("data source=niqotine;initial catalog=record;uid=sa;pwd=mindworks")
con.Open()
str = "delete from UserInfo where idno='" & DataGridView1.CurrentRow.Cells(1).Value & "'"
com.CommandText = str
com.Connection = con
com.ExecuteNonQuery()
adap = New SqlDataAdapter("select * from UserInfo", con)
adap.Fill(ds, "1")
DataGridView1.DataSource = ds.Tables("1")
End Sub
End Class

This code will work .If u have any queries send me reply.

Will the code listed above save all changes to the datagrid or will this only save the current cell?

Or will it work similar to the following:
http://www.daniweb.com/code/snippet836.html

Nevermind I answered my own question, and thank you for posting the actual code.

I was trying this since last two days but was unable to solve it.
Finally i got it thanx.;)

hi could any one give some example to how add data in Combobox in grid view and how to edit it.

& DataGridView1.CurrentRow.Cells(0).Value & this part will show the error
as below

Error 1 Operator '&' cannot be applied to operands of type 'string' and 'object' C:\Documents and Settings\gts\My Documents\Visual Studio 2005\First\WindowsApplication3\Form1.cs 128 35 WindowsApplication3

please help to how to solve this problem

Go through the following tutorial , it will help u.

http://vb.net-informations.com/datagridview/vb.net_datagridview_tutorial.htm

jerry.

& DataGridView1.CurrentRow.Cells(0).Value & this part will show the error
as below

Error 1 Operator '&' cannot be applied to operands of type 'string' and 'object' C:\Documents and Settings\gts\My Documents\Visual Studio 2005\First\WindowsApplication3\Form1.cs 128 35 WindowsApplication3

please help to how to solve this problem

this code is woking. but when i hit the btnsave its just populating the whole data in twise. after closing and repoen it is showing the saved data correctly.y?

The item "obj\Debug\Welcome.Addmarks.resources" was specified more than once in the "Resources" parameter. Duplicate items are not supported by the "Resources" parameter.

will u pls solve this? i can't able to understand this.....plsss

can you help me how to save new data in datagridview.

let say, i'll fill data in other form then it'll save and display into datagridview.

The save data code is given in the above thread....

Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
Dim con As New SqlClient.SqlConnection
Dim com As New SqlCommand
Dim adap As New SqlDataAdapter
Dim ds As New DataSet
con = New SqlConnection("data source=niqotine;initial catalog=record;uid=sa;pwd=mindworks")
con.Open()
com.CommandText = "insert into UserInfo values('" + txtname.Text + "','" + txtid.Text + "','" + cmbcolor.Text + "')"
com.Connection = con
com.ExecuteNonQuery()
adap = New SqlDataAdapter("select * from UserInfo", con)
adap.Fill(ds, "1")
DataGridView1.DataSource = ds.Tables("1")
End Sub

give a try to it...

the code says that u are adding the details in text box combox box and other different controls of the form and then displaying thr data is data grid view

i got an error message : Number of query values and destination fields are not the same.

Dim con As New OleDb.OleDbConnection(My.Settings.KK3DB)
        Dim com As New OleDb.OleDbCommand
        Dim adap As New OleDb.OleDbDataAdapter
        Dim ds As New DataSet
        con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\User\Desktop\dia\14052012\KK3MailingSystem\KK3MailingSystem\db\KK3MailingSystem.mdb;Persist Security Info=True")
        con.Open()
        com.CommandText = "insert into Staff values('" + txtId.Text + "','" + txtName.Text + "','" + txtIC.Text + "','" + txtExt.Text + "','" + txtHP.Text + "','" + txtEmail.Text + "','" + txtPosition.Text + "')"
        com.Connection = con
        com.ExecuteNonQuery() < error line
        adap = New OleDbDataAdapter("select * from Staff", con)
        adap.Fill(ds, "1")
        StaffDataGridView.DataSource = ds.Tables("1")

Talk about bringing back dead threads...

You have less or to many columns in your insert.

If less on purpose, you will have to set the values to their columns.

Example:

Dim sqls As String = "INSERT INTO table (column1, column3, column4) VALUES ('val1','val2,'val3')

finally my problem solve! but how to select the row means the last data we've inserted in datagridview.

urm, one more problem, after save the data i cant add more data. error in this code

 Me.StaffBindingSource.AddNew()

again, how to insert into two tables that related to each other. anyone? i'll try this, but it doesnt work.

 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

        Dim con As New OleDb.OleDbConnection(My.Settings.KK3DB)
        Dim com As New OleDb.OleDbCommand
        Dim adap As New OleDb.OleDbDataAdapter
        Dim ds As New DataSet
        con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\User\Desktop\dia\14052012\KK3MailingSystem\KK3MailingSystem\db\KK3MailingSystem.mdb;Persist Security Info=True")
        con.Open()

        com.CommandText = " INSERT INTO Staff (Staff_Id,Staff_Name, Staff_IC,Staff_Ext,Staff_HP,Staff_Email,Staff_Position) VALUES ('" + txtId.Text + "','" + txtName.Text + "','" + txtIC.Text + "','" + txtExt.Text + "','" + txtHP.Text + "','" + txtEmail.Text + "','" + txtPosition.Text + "')"
        com.CommandText = " INSERT INTO Login (Staff_Id, Staff_Username) VALUES ('" + txtId.Text + "','" + txtUsername.Text + "')"

        com.Connection = con
        com.ExecuteNonQuery() < error here
        adap = New OleDbDataAdapter("select * from Staff,Login", con)
        adap.Fill(ds, "1")
        StaffDataGridView.DataSource = ds.Tables("1")
        Me.StaffDataGridView.Rows(Me.StaffDataGridView.RowCount - 2).Selected = True

com.CommandText = " INSERT INTO Staff (Staff_Id,Staff_Name, Staff_IC,Staff_Ext,Staff_HP,Staff_Email,Staff_Position) VALUES ('" + txtId.Text + "','" + txtName.Text + "','" + txtIC.Text + "','" + txtExt.Text + "','" + txtHP.Text + "','" + txtEmail.Text + "','" + txtPosition.Text + "')"
com.CommandText = " INSERT INTO Login (Staff_Id, Staff_Username) VALUES ('" + txtId.Text + "','" + txtUsername.Text + "')"

You have two insert statements but do not execute the statement. You are just overwriting the first statement

I suggest you call com.executenonquery inbetween each statement

it solved. when i delete some data, then do command Refresh the data stil exist until you logout the system then run again. how to solve that problem.here's my command on Refresh button

StaffDataGridView.DataSource = Me.DsKK3DB.Staff

What you can do, is place all of the code that retreives the data for the gridview ( The select statement and data adapter ) into a custom function that you can call. That way, when you delete a row, the function will:

1) Clear the gridview
2) Fill the gridview

If you make the code modular, you will not have to worry about having to recode it everytime. You can just call the function/sub.

Hello all
I also want to contribute a quick tip here
you can also get tutorial with code example here
http://www.dapfor.com/en/net-suite/net-grid/tutorial

Hello All

I tried the Save code but i received an SQLexception error :" Incorrect syntax near ')'."

Here's the code:

Private Sub BtnOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnOk.Click
        Dim Con As New SqlClient.SqlConnection
        Dim Com As New SqlCommand
        Dim adap As New SqlDataAdapter
        Dim ds As New DataSet

        Con = New SqlConnection(" Server = USER-PC\SQLEXPRESS; Database = pharmacys;             Trusted_connection = true")

        Con.Open()
        Com.CommandText = "insert into patient  Values ('" + LblPatientId.Text + "','" + TxtFirstName.Text + "', '" + TxtLastName.Text + "' , '" + lblDate.Text + "' , '" + TxtAddress.Text + "','" + TxtNID.Text + "', '" + Dtpdob.Text + "',  '" + GbxGender.Text + "' ,'" + TxtAddress.Text + "', '" + TxtEmail.Text + "',)"

        Com.Connection = Con
        Com.ExecuteNonQuery()  < Error here
        adap = New SqlDataAdapter(" select * from patient", Con)
        adap.Fill(ds, "patient")
        DgvPatient.DataSource = ds.Tables("Patient")
    End Sub

Is it because i am using the wrong format for Gender '" + GbxGender.Text + "' I mean i have to choose between two radio buttons , that's why its throwing this error or it is something else :S ?

Please help guys , Thanks.

Hello,
i am having the same problem facing i ahve been using datagridview source in excel and access connection but i cannot insert,update the data on datagridview