holemania
asked on
vb.net datagridview insert with windows form
I created a windows form application with a datagridview. I can delete, update, as well as insert a record. However, I am running into an issue where I can only add 1 record at a time. For example if I click on a new row and do an insert, it will only insert that one row. Is it possible to do a loop so that it would update all rows that's been created? Another thing is that I also need it to auto-increment the ID to the next highest number. Below is what I have done as far as inserting and auto-increment.
Imports System.Data.SqlClient
Public Class Form1
Dim myDA As SqlDataAdapter
Dim myDataSet As DataSet
Public WithEvents strDETAIL_ID As String
Dim builder As SqlCommandBuilder
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim con As SqlConnection = New SqlConnection("Data Source=SRVRNAME;persist security info=False;initial catalog=DATABASENAME;UID=sa;PWD=PASSWORD")
Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM TIME_DETAIL", con)
con.Open()
myDA = New SqlDataAdapter(cmd)
'Automatically generate deletecommand, updatecommand, insertcommand
'Dim builder As SqlCommandBuilder = New SqlCommandBuilder(myDA)
builder = New SqlCommandBuilder(myDA)
myDataSet = New DataSet()
myDA.Fill(myDataSet, "TIME_DETAIL")
myDataSet.Tables("TIME_DETAIL").Columns("EMP_ID").ColumnMapping = MappingType.Hidden
myDataSet.Tables("TIME_DETAIL").Columns("TIME_ID").ColumnMapping = MappingType.Hidden
myDataSet.Tables("TIME_DETAIL").Columns("DETAIL_ID").ReadOnly = True
dgViewDetail.DataSource = myDataSet.Tables("TIME_DETAIL").DefaultView
'Set this to auto-increment, but not working
myDataSet.Tables("TIME_DETAIL").Columns("DETAIL_ID").AutoIncrement = True
con.Close()
con = Nothing
End Sub
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Me.myDataSet.GetChanges()
builder.GetInsertCommand()
Me.myDA.Update(myDataSet, "TIME_DETAIL")
Me.myDA.Fill(Me.myDataSet, "TIME_DETAIL")
End Sub
Have you tried specifying the AutoIncrementSeed and AutoIncrementStep?
ASKER
I tried that, but it starts from 0 and then each time i click it would increment by 1. Doesn't seem to increment from currentrow or something. I manage to hardcode an autoincrement using sql query to find max ID and than adding a 1 to it.
Now issue is I can't just Insert. I need to select all the lines first and then insert new record. Is there a way to modify below code so that I don't have to select all the new line I create for it to loop and insert one by one?
Now issue is I can't just Insert. I need to select all the lines first and then insert new record. Is there a way to modify below code so that I don't have to select all the new line I create for it to loop and insert one by one?
Dim i As Integer
Try
For i = Me.dgViewDetail.SelectedRows.Count - 1 To 0 Step -1
cmd.CommandText = "INSERT INTO TIME_DETAIL VALUES(" & strID & ", 2, 2, " & _
"'" & dgViewDetail.SelectedRows(i).Cells("START").Value & "', " & _
"'" & dgViewDetail.SelectedRows(i).Cells("END").Value & "', " & _
"'" & dgViewDetail.SelectedRows(i).Cells("JOB_ID").Value & "', " & _
"'" & dgViewDetail.SelectedRows(i).Cells("OPERATION").Value & "', " & _
"'" & dgViewDetail.SelectedRows(i).Cells("PIECE_NO").Value & "', GETDATE())"
con.Open()
cmd.ExecuteNonQuery()
con.Close()
GetID()
Next
MsgBox("Save Successful.")
Catch ex As Exception
MsgBox(ex.Message)
End Try
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the help. Manage to reverse engineer to insert on cell edit leave if there are any data.
ASKER
Didn't give me the information I need, but did attempt to help so I'll award the points.