Solved

vb.net datagridview insert with windows form

Posted on 2009-05-06
5
519 Views
Last Modified: 2012-05-06
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

Open in new window

0
Comment
Question by:holemania
[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
  • 2
5 Comments
 
LVL 10

Expert Comment

by:cdebel
ID: 24321422
Have you tried specifying the AutoIncrementSeed and AutoIncrementStep?
0
 

Author Comment

by:holemania
ID: 24336777
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?  
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

Open in new window

0
 
LVL 10

Accepted Solution

by:
cdebel earned 500 total points
ID: 24338209
about the auto-increment,  there's some explanations here:
http://www.experts-exchange.com/Programming/Languages/.NET/Q_23248836.html

But for the your loop, i can't tell.  I'm not familiar with this.   Some other Experts might help you
0
 

Author Comment

by:holemania
ID: 24391416
Thanks for the help.  Manage to reverse engineer to insert on cell edit leave if there are any data.
0
 

Author Closing Comment

by:holemania
ID: 31578701
Didn't give me the information I need, but did attempt to help so I'll award the points.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…

734 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