Solved

Can't find all sqladapter parts to match oledb

Posted on 2004-09-21
68
345 Views
Last Modified: 2010-04-23
I'm trying to convert oledb statements into sql - here's the trouble:

Me.OleDbDataAdapter1.UpdateCommand = Me.OleDbUpdateCommand1

                        Me.OleDbUpdateCommand1.CommandText = "UPDATE users SET field = ?, field2 = ?, [user] = ? WHERE (Id = ?)"
                        Me.OleDbUpdateCommand1.Connection = Me.OleDbConnection1
                        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("field", System.Data.OleDb.OleDbType.VarWChar, 50, "field"))
                        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("field2", System.Data.OleDb.OleDbType.VarWChar, 50, "field2"))
                        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("user", System.Data.OleDb.OleDbType.VarWChar, 50, "user"))
                        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Id", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Id", System.Data.DataRowVersion.Original, Nothing))

I see Me.SqlAdapter1.Commandtext
I see Me.SqlDataAdapter1.UpdateCommand = ???

I don't see Me.SqlUpdateCommand1 - what do I need to do to convert those lines to SqlAdapter?

JP
0
Comment
Question by:gleznov
  • 35
  • 33
68 Comments
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12112034
If you want to use sql than don't use oledb, from your code I assume that you dragged an oledbdataadpter to your form where you should have dragged an sqldataadapter.

like

Me.SqlUpdateCommand1.CommandText = "UPDATE users SET field = @field,field2 = @field2 WHERE (Id = @ID")"
        Me.SqlUpdateCommand1.Connection = Me.SqlConnection1
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@field", System.Data.SqlDbType.NVarChar, 50, "field"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@field2", System.Data.SqlDbType.NVarChar, 50, "field2"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Id", System.Data.SqlDbType.TinyInt, 1, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Id", System.Data.DataRowVersion.Original, Nothing))
0
 

Author Comment

by:gleznov
ID: 12112203
Well the old code was connected to a form with an oledb adapter.  The new form has a sqladapter, but I dragged the old code over to make converting easier.  

It doesn't like the Me.SqlUpdateCommand1 part of those commands.  Is there something to define it like:

Me.OleDbDataAdapter1.UpdateCommand = Me.OleDbUpdateCommand1
did for oledb?


JP
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12112384
sorry forgot that

add
dim sqlupdatecommand1 as new sqlclient.sqlcommand
and
me.sqldataadapter1.updatacommand = sqlupdatacommand1
0
 

Author Comment

by:gleznov
ID: 12112510
Thanks, but the other block of commands still has a squiggly under Me.SqlUpdateCommand1

(I changed your above from datacommand to datecommand and updatacommand to updatecommand, so it's not that)

frmChangepassword.vb(301): 'SqlUpdateCommand1' is not a member of 'OEL.frmChangepassword'.

??

JP
0
 

Author Comment

by:gleznov
ID: 12112566
So I've got this:

                        Dim SqlUpdateCommand1 As New SqlClient.SqlCommand
                        Me.SqlDataAdapter1.UpdateCommand = sqlupdatecommand1

                        Me.SqlUpdateCommand1.CommandText = "UPDATE users SET password = " & txtnewpass.Text & "WHERE (user = " & usercode & ")"
                        Me.SqlUpdateCommand1.Connection = Me.SqlConnection1

Once I resolve the squigglies in the last two lines, how do I make this actually update the database?  And do I need those Me.SqlUpdateCommand1.Parameter listings?  What are they?

JP
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12113319
Stop what you are doing gleznov, because I will explain first how ado works. Because if I look at what you are trying you don't understand the basics yet.

There are a couple of classes in ado.net that allow you to interact with your database

sqldataadapter, use this if you want to fill a dataset and interact with the data as a whole
sqldatareader use this if you want to just read data in a fast way

if you use a dataadapter you have to add the parameters because the dataadapter will update your entire table
(deletes,updates and inserts) so you can not set an individual record like you are trying to do
what you are trying to do doesn't work with a dataadapter,

you can do that, but by using just the executenonquery method of the sqlcommand
so
Dim SqlUpdateCommand1 As New SqlClient.SqlCommand
SqlUpdateCommand1.CommandText = "UPDATE users SET password = " & txtnewpass.Text & "WHERE (user = " & usercode & ")"
SqlUpdateCommand1.Connection = Me.SqlConnection1
dim x as integer = sqlupdatecommand1.executenonquery




0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12113398
this is better

SqlUpdateCommand1.CommandText = "UPDATE users SET password = " & txtnewpass.Text & " WHERE (user = " & usercode & ")"
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12113537
Here is a good site, he are the basics of ado.net explained

http://visualbasic.about.com/library/weekly/aa041203a.htm
0
 

Author Comment

by:gleznov
ID: 12115586
I read through the basics, but I still don't understand what I need to do to update a database using sqladapter.

JP
0
 

Author Comment

by:gleznov
ID: 12115653
Using this:

Me.SqlCommand1.CommandText = "UPDATE users SET field = @field WHERE (Id = @ID)"
                        Me.SqlCommand1.Connection = Me.SqlConnection1
                        Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@field", System.Data.SqlDbType.NVarChar, 50, "field"))
                        Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Id", System.Data.SqlDbType.TinyInt, 1, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Id", System.Data.DataRowVersion.Original, Nothing))

(I went into the properties of the sqladapter and there was no update command, so I told it to make a new one, called SqlCommand1 - I would have made it SqlUpdateCommand1 but I can't seem to change the name - I assume for now that that's what was meant)

So how do I use this?

In the first line, do I change "field" to "password" and "Id" to "user"
In the third line, change "field" to txtnewpass.text
In the fourth line, change "Id" to txtUserID.text

I also have this from the tutorial:

'Me.BindingContext(Me.DataSet11, "Users").EndCurrentEdit()
                        'Me.SqlDataAdapter1.Update(Me.DataSet11, "Users")
                        'Me.DataSet11.AcceptChanges()

Do I need to use some variety of this code to make that SqlUpdateCommand stuff go into effect?  

JP



0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12120404
Hi Gleznov,

I will describe 3 methods on how to do this.

1. Drag an sqldataadapter to your form and configure it (this will create the update, delete, insert statement for you)
2. Create the dataadapter and commands yourself in code
3. or if you just want to update (not add, or delete) create just a command and use the nonexecute query

then 2 options

1. bind your textboxes to your dataset
2. or fill your textboxes yourself

I will give you an example for all options, I will post them in seperate comments
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12120411
here I dragged an sqldataadapter to my form, configured the dataadapter, and bound the textboxes to the dataset

Public Class Form7
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents txtuserid As System.Windows.Forms.TextBox
    Friend WithEvents txtnewpass As System.Windows.Forms.TextBox
    Friend WithEvents Button1 As System.Windows.Forms.Button
    Friend WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
    Friend WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents SqlUpdateCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents SqlDeleteCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.txtuserid = New System.Windows.Forms.TextBox
        Me.txtnewpass = New System.Windows.Forms.TextBox
        Me.Button1 = New System.Windows.Forms.Button
        Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
        Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlUpdateCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlDeleteCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
        Me.SuspendLayout()
        '
        'txtuserid
        '
        Me.txtuserid.Location = New System.Drawing.Point(143, 36)
        Me.txtuserid.Name = "txtuserid"
        Me.txtuserid.Size = New System.Drawing.Size(114, 20)
        Me.txtuserid.TabIndex = 0
        Me.txtuserid.Text = ""
        '
        'txtnewpass
        '
        Me.txtnewpass.Location = New System.Drawing.Point(144, 72)
        Me.txtnewpass.Name = "txtnewpass"
        Me.txtnewpass.Size = New System.Drawing.Size(115, 20)
        Me.txtnewpass.TabIndex = 1
        Me.txtnewpass.Text = ""
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(139, 106)
        Me.Button1.Name = "Button1"
        Me.Button1.Size = New System.Drawing.Size(124, 31)
        Me.Button1.TabIndex = 2
        Me.Button1.Text = "Button1"
        '
        'SqlDataAdapter1
        '
        Me.SqlDataAdapter1.DeleteCommand = Me.SqlDeleteCommand1
        Me.SqlDataAdapter1.InsertCommand = Me.SqlInsertCommand1
        Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
        Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "users", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("user", "user"), New System.Data.Common.DataColumnMapping("password", "password")})})
        Me.SqlDataAdapter1.UpdateCommand = Me.SqlUpdateCommand1
        '
        'SqlSelectCommand1
        '
        Me.SqlSelectCommand1.CommandText = "SELECT [user], password FROM users"
        Me.SqlSelectCommand1.Connection = Me.SqlConnection1
        '
        'SqlInsertCommand1
        '
        Me.SqlInsertCommand1.CommandText = "INSERT INTO users([user], password) VALUES (@Param1, @password)"
        Me.SqlInsertCommand1.Connection = Me.SqlConnection1
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Param1", System.Data.SqlDbType.TinyInt, 1, "user"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@password", System.Data.SqlDbType.VarChar, 10, "password"))
        '
        'SqlUpdateCommand1
        '
        Me.SqlUpdateCommand1.CommandText = "UPDATE users SET [user] = @Param2, password = @password WHERE ([user] = @Original" & _
        "_user)"
        Me.SqlUpdateCommand1.Connection = Me.SqlConnection1
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Param2", System.Data.SqlDbType.TinyInt, 1, "user"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@password", System.Data.SqlDbType.VarChar, 10, "password"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_user", System.Data.SqlDbType.TinyInt, 1, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "user", System.Data.DataRowVersion.Original, Nothing))
        '
        'SqlDeleteCommand1
        '
        Me.SqlDeleteCommand1.CommandText = "DELETE FROM users WHERE ([user] = @Original_user)"
        Me.SqlDeleteCommand1.Connection = Me.SqlConnection1
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_user", System.Data.SqlDbType.TinyInt, 1, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "user", System.Data.DataRowVersion.Original, Nothing))
        '
        'SqlConnection1
        '
        Me.SqlConnection1.ConnectionString = "workstation id=RONALD;packet size=4096;integrated security=SSPI;data source=ronal" & _
        "d;persist security info=False;initial catalog=Aramis2"
        '
        'Form7
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(292, 266)
        Me.Controls.Add(Me.Button1)
        Me.Controls.Add(Me.txtnewpass)
        Me.Controls.Add(Me.txtuserid)
        Me.Name = "Form7"
        Me.Text = "Form7"
        Me.ResumeLayout(False)

    End Sub

#End Region


    Dim ds As New DataSet

    Private Sub Form7_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        SqlDataAdapter1.Fill(ds)
        txtuserid.DataBindings.Add("Text", ds.Tables(0), "user")
        txtnewpass.DataBindings.Add("Text", ds.Tables(0), "password")
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Me.BindingContext(ds.Tables(0)).EndCurrentEdit()
        SqlDataAdapter1.Update(ds)
        ds.AcceptChanges()
    End Sub

End Class
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12120456
Here I create my own dataadapter, and updatecommand, and fill the textboxes manually.

Public Class Form7
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents txtuserid As System.Windows.Forms.TextBox
    Friend WithEvents txtnewpass As System.Windows.Forms.TextBox
    Friend WithEvents Button1 As System.Windows.Forms.Button
    Friend WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.txtuserid = New System.Windows.Forms.TextBox
        Me.txtnewpass = New System.Windows.Forms.TextBox
        Me.Button1 = New System.Windows.Forms.Button
        Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
        Me.SuspendLayout()
        '
        'txtuserid
        '
        Me.txtuserid.Location = New System.Drawing.Point(143, 36)
        Me.txtuserid.Name = "txtuserid"
        Me.txtuserid.Size = New System.Drawing.Size(114, 20)
        Me.txtuserid.TabIndex = 0
        Me.txtuserid.Text = ""
        '
        'txtnewpass
        '
        Me.txtnewpass.Location = New System.Drawing.Point(144, 72)
        Me.txtnewpass.Name = "txtnewpass"
        Me.txtnewpass.Size = New System.Drawing.Size(115, 20)
        Me.txtnewpass.TabIndex = 1
        Me.txtnewpass.Text = ""
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(139, 106)
        Me.Button1.Name = "Button1"
        Me.Button1.Size = New System.Drawing.Size(124, 31)
        Me.Button1.TabIndex = 2
        Me.Button1.Text = "Button1"
        '
        'SqlConnection1
        '
        Me.SqlConnection1.ConnectionString = "workstation id=RONALD;packet size=4096;integrated security=SSPI;data source=ronal" & _
        "d;persist security info=False;initial catalog=Aramis2"
        '
        'Form7
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(292, 266)
        Me.Controls.Add(Me.Button1)
        Me.Controls.Add(Me.txtnewpass)
        Me.Controls.Add(Me.txtuserid)
        Me.Name = "Form7"
        Me.Text = "Form7"
        Me.ResumeLayout(False)

    End Sub

#End Region


    Dim ds As New DataSet
    Dim da As SqlClient.SqlDataAdapter
    Private Sub Form7_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        da = New SqlClient.SqlDataAdapter("Select * from users", SqlConnection1)
        da.Fill(ds)
        txtuserid.Text = ds.Tables(0).Rows(0).Item("user")
        txtnewpass.Text = ds.Tables(0).Rows(0).Item("Password")
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ds.Tables(0).Rows(0).Item("user") = txtuserid.Text
        ds.Tables(0).Rows(0).Item("Password") = txtnewpass.Text

        Dim comUpdate As New SqlClient.SqlCommand
        comUpdate.CommandText = "UPDATE users SET password = @password WHERE ([user] = @user)"
        comUpdate.Connection = Me.SqlConnection1
        comUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@password", System.Data.SqlDbType.NVarChar, 50, "password"))
        comUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@user", System.Data.SqlDbType.TinyInt, 1, "user"))
        da.UpdateCommand = comUpdate
        Try
            da.Update(ds)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        ds.AcceptChanges()
    End Sub

End Class
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12120500
here I execute a nonquery to directy put the value in the database

Public Class Form7
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents txtuserid As System.Windows.Forms.TextBox
    Friend WithEvents txtnewpass As System.Windows.Forms.TextBox
    Friend WithEvents Button1 As System.Windows.Forms.Button
    Friend WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.txtuserid = New System.Windows.Forms.TextBox
        Me.txtnewpass = New System.Windows.Forms.TextBox
        Me.Button1 = New System.Windows.Forms.Button
        Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
        Me.SuspendLayout()
        '
        'txtuserid
        '
        Me.txtuserid.Location = New System.Drawing.Point(143, 36)
        Me.txtuserid.Name = "txtuserid"
        Me.txtuserid.Size = New System.Drawing.Size(114, 20)
        Me.txtuserid.TabIndex = 0
        Me.txtuserid.Text = ""
        '
        'txtnewpass
        '
        Me.txtnewpass.Location = New System.Drawing.Point(144, 72)
        Me.txtnewpass.Name = "txtnewpass"
        Me.txtnewpass.Size = New System.Drawing.Size(115, 20)
        Me.txtnewpass.TabIndex = 1
        Me.txtnewpass.Text = ""
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(139, 106)
        Me.Button1.Name = "Button1"
        Me.Button1.Size = New System.Drawing.Size(124, 31)
        Me.Button1.TabIndex = 2
        Me.Button1.Text = "Button1"
        '
        'SqlConnection1
        '
        Me.SqlConnection1.ConnectionString = "workstation id=RONALD;packet size=4096;integrated security=SSPI;data source=ronal" & _
        "d;persist security info=False;initial catalog=Aramis2"
        '
        'Form7
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(292, 266)
        Me.Controls.Add(Me.Button1)
        Me.Controls.Add(Me.txtnewpass)
        Me.Controls.Add(Me.txtuserid)
        Me.Name = "Form7"
        Me.Text = "Form7"
        Me.ResumeLayout(False)

    End Sub

#End Region


    Dim ds As New DataSet
    Dim da As SqlClient.SqlDataAdapter
    Private Sub Form7_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        da = New SqlClient.SqlDataAdapter("Select * from users", SqlConnection1)
        da.Fill(ds)
        txtuserid.Text = ds.Tables(0).Rows(0).Item("user")
        txtnewpass.Text = ds.Tables(0).Rows(0).Item("Password")
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ds.Tables(0).Rows(0).Item("user") = txtuserid.Text
        ds.Tables(0).Rows(0).Item("Password") = txtnewpass.Text

        Dim dc As New SqlClient.SqlCommand
        dc.CommandText = "UPDATE users SET password = '" & txtnewpass.Text & "' WHERE ([user] = " & CInt(txtuserid.Text) & ")"
        dc.Connection = Me.SqlConnection1
        Me.SqlConnection1.Open()
        Try
            dc.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        Me.SqlConnection1.Close()
        ds.AcceptChanges()
    End Sub

End Class
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12120502
I hope I helped you out a bit with these examples, If you need any additional help let me know.

Ronald
0
 

Author Comment

by:gleznov
ID: 12125983
I think what's most confusing to me is the actual SQL statement with @params and then the parameter listings -

For instance, now I'm working on a form to the side that gives a datagrid with users, their passwords, and 6 other columns of info about them.  I allow editing or adding rows to the table, and then when they hit Update, I want new rows written in their entirety (Insert I guess?) and old rows changed in the database, even if they change every piece of info in it - it changes the right spot in the dataset, but maybe that's too much?  I also suppose I could hold an old copy of the username before changed, and then replace all the new info over the old record in the database where user = old_user_variable

But set me up an example of creating the sqlcommand where you want to update more than one value in a row?  or do you just have to do several different statements?  I'm good with SQL, but I become poor at translating SQL through VB and ADO.NET.

JP
0
 

Author Comment

by:gleznov
ID: 12126036
I take it Insert and Delete statements are very similar to Update?

JP
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12131277
Hi gleznov,

But set me up an example of creating the sqlcommand where you want to update more than one value in a row?  or do you just have to do several different statements?  I'm good with SQL, but I become poor at translating SQL through VB and ADO.NET.

I already gave you 2 of those. only the last will update just one record.

 Me.SqlUpdateCommand1.CommandText = "UPDATE users SET [user] = @Param2, password = @password WHERE ([user] = @Original" & _
        "_user)"
        Me.SqlUpdateCommand1.Connection = Me.SqlConnection1
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Param2", System.Data.SqlDbType.TinyInt, 1, "user"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@password", System.Data.SqlDbType.VarChar, 10, "password"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_user", System.Data.SqlDbType.TinyInt, 1, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "user", System.Data.DataRowVersion.Original, Nothing))
        '

this will update all records that have changes
0
 

Author Comment

by:gleznov
ID: 12132160
Is user in brackets because it's a primary key?

JP
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12132212
No, it is because user is a reserved word.
0
 

Author Comment

by:gleznov
ID: 12132214
Still trying to understand how this works:

        Me.SqlCommand1.CommandText = "UPDATE users SET [user] = @Param2, password = @password WHERE ([user] = @Original" & "_user)"
        Me.SqlCommand1.Connection = Me.SqlConnection1
        Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Param2", System.Data.SqlDbType.TinyInt, 1, "user"))
        Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@password", System.Data.SqlDbType.VarChar, 10, "password"))
        Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_user", System.Data.SqlDbType.TinyInt, 1, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "user", System.Data.DataRowVersion.Original, Nothing))

1)        Me.SqlCommand1.CommandText = "UPDATE users SET [user] = @Param2, password = @password WHERE ([user] = @Original" & "_user)"
This tells it to update the real table setting user to @param2, password to @password, and to do so where user in the table = @original user

2)        Me.SqlCommand1.Connection = Me.SqlConnection1
Opens connection (my update command is called SqlCommand1 - the main reason it's not SqlUpdateCommand1 is that I can't change it...)

3)        Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Param2", System.Data.SqlDbType.TinyInt, 1, "user"))
This tells the UPDATE command that the value of @param2 is "user" (?)  is "user" a reference to a dataset object or a string?  Should I replace "user" with txtUserID.text?

4)        Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@password", System.Data.SqlDbType.VarChar, 10, "password"))
This tells the UPDATE command that the value of  @password is "password" or else the "password" column in the dataset - again, not sure which

5)        Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_user", System.Data.SqlDbType.TinyInt, 1, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "user", System.Data.DataRowVersion.Original, Nothing))

I can't really follow all the parameters in this one - I take it I need to hold a variable with the unchanged user name for the update and plug it in where "user" is in the end of that statement??

JP
0
 

Author Comment

by:gleznov
ID: 12132269
OK also I see those are sourcecolumns at the end of 3), 4) and 5)  ("user", "password", and "user" respectively)

So how does 5) know the old user vs. the newly written dataset value for user?

JP

BTW, dunno if it helps, but I've plugged the code in as is and run it and nothing happened
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12132319
Hi Gleznov,

You are searching to far, I'll try to explain using this example I gave before

 Dim ds As New DataSet
    Dim da As SqlClient.SqlDataAdapter
    Private Sub Form7_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        da = New SqlClient.SqlDataAdapter("Select * from users", SqlConnection1)
        da.Fill(ds)
        txtuserid.Text = ds.Tables(0).Rows(0).Item("user")
        txtnewpass.Text = ds.Tables(0).Rows(0).Item("Password")
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

'****Here I assign the textboxes back to the dataset***

        ds.Tables(0).Rows(0).Item("user") = txtuserid.Text
        ds.Tables(0).Rows(0).Item("Password") = txtnewpass.Text

'*** here I build an Update command with 2 parameters. You do not have to tell the update command wich records to update because ADO.net will figure that out by himself using the rowstate of your records.

        Dim comUpdate As New SqlClient.SqlCommand
        comUpdate.CommandText = "UPDATE users SET password = @password WHERE ([user] = @user)"
        comUpdate.Connection = Me.SqlConnection1
        comUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@password", System.Data.SqlDbType.NVarChar, 50, "password"))
        comUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@user", System.Data.SqlDbType.TinyInt, 1, "user"))
        da.UpdateCommand = comUpdate

        Try
' When you call the update ADO.net will go trough all the record that have a rowstate  'Modified' and will update that record
            da.Update(ds)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        ds.AcceptChanges()
    End Sub
0
 

Author Comment

by:gleznov
ID: 12132451
It doesn't yield an error, but when I close that form after making the update (which calls all that stuff from your Update Command down to the ds.AcceptChanges), and reopen the form and it repopulates itself, the new records are gone and the edited records are back to the way they were before the change.  Is there anything else I have to do after that to absolutely set the database values to change?

JP
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12132688
Could you post your code so I can have a look at it ?
0
 

Author Comment

by:gleznov
ID: 12132990
I'm not sure what all you need, but here is the "Update" command button's code (I took out code to make sure name and password are filled in and that everything in combo1 - combo6 is of value 0 or 1.  Also, text1 = txtUserID and text2 = txtPassword - I've used UserID and Password here to keep things unambiguous.  SelIndex is the selected index which is updated whenever the datagrid is clicked on.  This bit of code updates the dataset from any changes made - there's one block of code for when the user has edited a previous record, and one block for adding new items.  After that is the code to update the actual database from the dataset changes.  My DataAdapter is SqlDataAdapter1, the dataset is dataset_Users1, the table is "Users", the fields in users are all listed in the dr.item commands immediately below:

        ' Add vs. Edit - Writing to the dataset
        If IsAdd = False Then    ' Edit
            Dim dr As DataRow
            dr = DataSet_Users1.Tables("Users").Rows(SelIndex)
            dr.Item("User") = text1.Text
            dr.Item("Password") = text2.Text
            dr.Item("Files") = Val(Combo1.Text)
            dr.Item("Transactions") = Val(Combo2.Text)
            dr.Item("Reports") = Val(Combo3.Text)
            dr.Item("Database") = Val(Combo4.Text)
            dr.Item("UserSetup") = Val(Combo5.Text)
            dr.Item("Supplies") = Val(Combo6.Text)
            DataSet_Users1.Tables("Users").Rows(SelIndex).AcceptChanges()
        Else                     ' Add
            DataSet_Users1.Tables("Users").DefaultView.Sort = "User"
            Dim x As Integer = DataSet_Users1.Tables("Users").DefaultView.Find(text1.Text)
            If x >= 0 Then
                MsgBox("That user already exists.  Please try again.")
                text1.Focus()
                Exit Sub
            Else
                'add record
                Dim dr As DataRow
                dr = DataSet_Users1.Tables("Users").Rows(SelIndex)
                dr.Item("User") = text1.Text
                dr.Item("Password") = text2.Text
                dr.Item("Files") = Val(Combo1.Text)
                dr.Item("Transactions") = Val(Combo2.Text)
                dr.Item("Reports") = Val(Combo3.Text)
                dr.Item("Database") = Val(Combo4.Text)
                dr.Item("UserSetup") = Val(Combo5.Text)
                dr.Item("Supplies") = Val(Combo6.Text)
                DataSet_Users1.Tables("Users").Rows(SelIndex).AcceptChanges()
            End If
        End If

        ' Write dataset changes to database
'Dim comUpdate As New SqlClient.SqlCommand
        comUpdate.CommandText = "UPDATE users SET password = @password WHERE ([user] = @user)"
        comUpdate.Connection = Me.SqlConnection1
        comUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@password", System.Data.SqlDbType.NVarChar, 50, "password"))
        comUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@user", System.Data.SqlDbType.TinyInt, 1, "user"))
        SqlDataAdapter1.UpdateCommand = comUpdate

        Try
        ' When you call the update ADO.net will go trough all the record that have a rowstate  'Modified' and will update that record
             SqlDataAdapter1.Update(DataSet_Users1)
             Catch ex As Exception
             MsgBox(ex.Message)
        End Try

        DataSet_Users1.AcceptChanges()


JP
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12133036
Hi Gleznov,

Remove the 2 lines with the acceptchanges only keep the one at the end, Why, because if you call this it will commit the changes to the dataset and will set the rowstate back to unchanged, so if you than call the update, it thinks nothing has changed and won't update, you call acceptchanges after you update

 If IsAdd = False Then    ' Edit
            Dim dr As DataRow
            dr = DataSet_Users1.Tables("Users").Rows(SelIndex)
            dr.Item("User") = text1.Text
            dr.Item("Password") = text2.Text
            dr.Item("Files") = Val(Combo1.Text)
            dr.Item("Transactions") = Val(Combo2.Text)
            dr.Item("Reports") = Val(Combo3.Text)
            dr.Item("Database") = Val(Combo4.Text)
            dr.Item("UserSetup") = Val(Combo5.Text)
            dr.Item("Supplies") = Val(Combo6.Text)
            DataSet_Users1.Tables("Users").Rows(SelIndex).AcceptChanges()  ' ******** REMOVE THIS
        Else                     ' Add
            DataSet_Users1.Tables("Users").DefaultView.Sort = "User"
            Dim x As Integer = DataSet_Users1.Tables("Users").DefaultView.Find(text1.Text)
            If x >= 0 Then
                MsgBox("That user already exists.  Please try again.")
                text1.Focus()
                Exit Sub
            Else
                'add record
                Dim dr As DataRow
                dr = DataSet_Users1.Tables("Users").Rows(SelIndex)
                dr.Item("User") = text1.Text
                dr.Item("Password") = text2.Text
                dr.Item("Files") = Val(Combo1.Text)
                dr.Item("Transactions") = Val(Combo2.Text)
                dr.Item("Reports") = Val(Combo3.Text)
                dr.Item("Database") = Val(Combo4.Text)
                dr.Item("UserSetup") = Val(Combo5.Text)
                dr.Item("Supplies") = Val(Combo6.Text)
                DataSet_Users1.Tables("Users").Rows(SelIndex).AcceptChanges() ' *** AND REMOVE THIS
            End If
        End If
0
 

Author Comment

by:gleznov
ID: 12133104
OK, removed those two (there was one more in the delete command button, I killed it too since I'll need it gone later), but same results - no error, but no permanent update either.

JP
0
 

Author Comment

by:gleznov
ID: 12133117
Here's a question - I imported an access database into SQL Server to do this.  It's possible there's some permissions I need to set - but if the permissions weren't there, wouldn't I receive some kind of error message?  Also, I changed permissions on all listed rows to allow everything and it didn't work earlier...  I just tried again, setting all listed roles to have full permissions, but still no changes.  This may not be the problem - after all, these roles originally didn't have Select permissions listed, and I haven't had any problems reading in the data with a select statement in the program.

JP
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12133150
Add these 2 endedits aswell

If IsAdd = False Then    ' Edit
            Dim dr As DataRow
            dr = DataSet_Users1.Tables("Users").Rows(SelIndex)
            dr.Item("User") = text1.Text
            dr.Item("Password") = text2.Text
            dr.Item("Files") = Val(Combo1.Text)
            dr.Item("Transactions") = Val(Combo2.Text)
            dr.Item("Reports") = Val(Combo3.Text)
            dr.Item("Database") = Val(Combo4.Text)
            dr.Item("UserSetup") = Val(Combo5.Text)
            dr.Item("Supplies") = Val(Combo6.Text)
dr.endedit  '**** ADD THIS
        Else                     ' Add
            DataSet_Users1.Tables("Users").DefaultView.Sort = "User"
            Dim x As Integer = DataSet_Users1.Tables("Users").DefaultView.Find(text1.Text)
            If x >= 0 Then
                MsgBox("That user already exists.  Please try again.")
                text1.Focus()
                Exit Sub
            Else
                'add record
                Dim dr As DataRow
                dr = DataSet_Users1.Tables("Users").Rows(SelIndex)
                dr.Item("User") = text1.Text
                dr.Item("Password") = text2.Text
                dr.Item("Files") = Val(Combo1.Text)
                dr.Item("Transactions") = Val(Combo2.Text)
                dr.Item("Reports") = Val(Combo3.Text)
                dr.Item("Database") = Val(Combo4.Text)
                dr.Item("UserSetup") = Val(Combo5.Text)
                dr.Item("Supplies") = Val(Combo6.Text)
dr.endedit  '**** ADD THIS

            End If
        End If
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12133190
Also I don't see you adding a new record in this part ?
does selindex here point to a new record ?

'add record
                Dim dr As DataRow
                dr = DataSet_Users1.Tables("Users").Rows(SelIndex)
                dr.Item("User") = text1.Text
                dr.Item("Password") = text2.Text
                dr.Item("Files") = Val(Combo1.Text)
                dr.Item("Transactions") = Val(Combo2.Text)
                dr.Item("Reports") = Val(Combo3.Text)
                dr.Item("Database") = Val(Combo4.Text)
                dr.Item("UserSetup") = Val(Combo5.Text)
                dr.Item("Supplies") = Val(Combo6.Text)
0
 

Author Comment

by:gleznov
ID: 12133259
Oh sorry - yes, when they click "Add", it creates a new record, points SelIndex to it and then points dataset_Users1.currentIndex to SelIndex.  Then they're filling in the data and if they hit cancel, I just delete that new row back out, and if they hit Save, then they get that block of code above.

JP
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12133271
And does it work now ?
0
 

Author Comment

by:gleznov
ID: 12133288
Think maybe we're getting to the bottom of something:

Now when I Edit a record, even if I make no changes at all and immediately just hit Update, it does this:

it message boxes me: "Input String was not in a correct format"
and puts an exclamation point in a red circle on the row-tab in the datagrid...  You saw my code though - it sets name/pass to text and the rest to a val(string) version - the database is setup like this:

User and Password are nvarchar(50)
and the others are all int(4)

What would cause that error?  And does that error keep the database from upgrading?

JP
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12133352
ok what is displayed in combo4 does it show a number or a text

dr.Item("Database") = Val(Combo4.Text)
0
 

Author Comment

by:gleznov
ID: 12133405
Combo1, Combo2, Combo3, Combo4, Combo5, and Combo6 are all comboboxes with two choices - 0 and 1, 0 for no, 1 for yes, relating to privileges (certain screens users are or are not allowed to access in the rest of the program (this form is one of many, and the supplies box, Combo6 tells whether or not a user can access this form, for instance)) If Update is pressed while any value other than 0 or 1 is entered for any of those boxes, there is a message telling the user to choose 0 or 1 and then the focus is set back on that box and the sub is exited.  

JP
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12133469
could you put a break point in the edit part and step through it (F10) and let me know where the error happens.


Dim dr As DataRow
                dr = DataSet_Users1.Tables("Users").Rows(SelIndex)
' put breakpoint here
                dr.Item("User") = text1.Text
                dr.Item("Password") = text2.Text
                dr.Item("Files") = Val(Combo1.Text)
                dr.Item("Transactions") = Val(Combo2.Text)
                dr.Item("Reports") = Val(Combo3.Text)
                dr.Item("Database") = Val(Combo4.Text)
                dr.Item("UserSetup") = Val(Combo5.Text)
                dr.Item("Supplies") = Val(Combo6.Text)

0
 

Author Comment

by:gleznov
ID: 12133532
BTW - Add now works!!!  Woohoo!!!  I'll do that breakpoint real quick - brb

JP
0
 

Author Comment

by:gleznov
ID: 12133572
Do you want me to put the breakpoint on the line before or after your comment 'Put breakpoint here

Or can I stick one somehow on a blank line?

JP
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12133595
sorry, before or after, you choose, you can put a break point on a blank line
0
 

Author Comment

by:gleznov
ID: 12133597
OK I stuck one on all of those lines, but the one that actually gives the error message is the msgbox(ex.message) in the catch portion of the code you gave me.  So I don't know if the error was just not showing up before that was there or what, but none of the above lines seem to cause an error

JP
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12133605
sorry I meant you can not put a break point on a blank line
0
 

Author Comment

by:gleznov
ID: 12133620
After this works, I'm going to open a question to get a quickfix to make my Delete button work - it should be pretty easy compared to this one - feel free to come take those points ;)

JP
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12133719
hold the phone,

is this the exact code you use ?

       If IsAdd = False Then    ' Edit
            Dim dr As DataRow
            dr = DataSet_Users1.Tables("Users").Rows(SelIndex)
            dr.Item("User") = text1.Text
            dr.Item("Password") = text2.Text
            dr.Item("Files") = Val(Combo1.Text)
            dr.Item("Transactions") = Val(Combo2.Text)
            dr.Item("Reports") = Val(Combo3.Text)
            dr.Item("Database") = Val(Combo4.Text)
            dr.Item("UserSetup") = Val(Combo5.Text)
            dr.Item("Supplies") = Val(Combo6.Text)
            dr.endedit
        Else                     ' Add
            DataSet_Users1.Tables("Users").DefaultView.Sort = "User"
            Dim x As Integer = DataSet_Users1.Tables("Users").DefaultView.Find(text1.Text)
            If x >= 0 Then
                MsgBox("That user already exists.  Please try again.")
                text1.Focus()
                Exit Sub
            Else
                'add record
                Dim dr As DataRow
                dr = DataSet_Users1.Tables("Users").Rows(SelIndex)
                dr.Item("User") = text1.Text
                dr.Item("Password") = text2.Text
                dr.Item("Files") = Val(Combo1.Text)
                dr.Item("Transactions") = Val(Combo2.Text)
                dr.Item("Reports") = Val(Combo3.Text)
                dr.Item("Database") = Val(Combo4.Text)
                dr.Item("UserSetup") = Val(Combo5.Text)
                dr.Item("Supplies") = Val(Combo6.Text)
               dr.endedit
            End If
        End If

        ' Write dataset changes to database
'Dim comUpdate As New SqlClient.SqlCommand
        comUpdate.CommandText = "UPDATE users SET password = @password WHERE ([user] = @user)"
        comUpdate.Connection = Me.SqlConnection1
        comUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@password", System.Data.SqlDbType.NVarChar, 50, "password"))
        comUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@user", System.Data.SqlDbType.TinyInt, 1, "user"))
        SqlDataAdapter1.UpdateCommand = comUpdate

        Try
        ' When you call the update ADO.net will go trough all the record that have a rowstate  'Modified' and will update that record
             SqlDataAdapter1.Update(DataSet_Users1)
             Catch ex As Exception
             MsgBox(ex.Message)
        End Try

        DataSet_Users1.AcceptChanges()
0
 

Author Comment

by:gleznov
ID: 12133819
Yes - are you looking at the TinyInt, 1  ?

JP
0
 

Author Comment

by:gleznov
ID: 12133861
Hmm, Int, 1 and Int, 4 didn't seem to work either...  Why do you ask?  What do you see?

JP
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12133880
Yes, but not alone that, you say the add works but I do not see an insert command, which lets me believe that it already exist somewhere else, did you by any change drag the sqladapter1 from the toolbox and configured it, if so than the updatecommand already exist too. Which means this should be enough

       If IsAdd = False Then    ' Edit
            Dim dr As DataRow
            dr = DataSet_Users1.Tables("Users").Rows(SelIndex)
            dr.Item("User") = text1.Text
            dr.Item("Password") = text2.Text
            dr.Item("Files") = Val(Combo1.Text)
            dr.Item("Transactions") = Val(Combo2.Text)
            dr.Item("Reports") = Val(Combo3.Text)
            dr.Item("Database") = Val(Combo4.Text)
            dr.Item("UserSetup") = Val(Combo5.Text)
            dr.Item("Supplies") = Val(Combo6.Text)
            dr.endedit
        Else                     ' Add
            DataSet_Users1.Tables("Users").DefaultView.Sort = "User"
            Dim x As Integer = DataSet_Users1.Tables("Users").DefaultView.Find(text1.Text)
            If x >= 0 Then
                MsgBox("That user already exists.  Please try again.")
                text1.Focus()
                Exit Sub
            Else
                'add record
                Dim dr As DataRow
                dr = DataSet_Users1.Tables("Users").Rows(SelIndex)
                dr.Item("User") = text1.Text
                dr.Item("Password") = text2.Text
                dr.Item("Files") = Val(Combo1.Text)
                dr.Item("Transactions") = Val(Combo2.Text)
                dr.Item("Reports") = Val(Combo3.Text)
                dr.Item("Database") = Val(Combo4.Text)
                dr.Item("UserSetup") = Val(Combo5.Text)
                dr.Item("Supplies") = Val(Combo6.Text)
               dr.endedit
            End If
        End If

        Try
        ' When you call the update ADO.net will go trough all the record that have a rowstate  'Modified' and will update that record
             SqlDataAdapter1.Update(DataSet_Users1)
             Catch ex As Exception
             MsgBox(ex.Message)
        End Try

        DataSet_Users1.AcceptChanges()
0
 

Author Comment

by:gleznov
ID: 12133924
Hmm, I did drag an adapter, configure it, and enabled Update, Delete, and Insert objects...  But when I change the code to that, Add still works, but for Edit I get this:

Update requires the UpdateCommand to have a connection object.  The connection property of the UpdateCommand has not been initialized.
<OK>


?
0
 

Author Comment

by:gleznov
ID: 12133970
Oddly (possibly unrelated, maybe not) now if I add a record, when I Update it, at the bottom where the new record is, it fills in with record(0)'s information - if I tell anything to Edit then cancel the edit, they disappear from the dataset.  If I Close the form and reopen, everything's back to normal, and the newly added object is on there.  Not sure what that is, but probably a glitch in some of my other code...

JP
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12133978
could you post the windows generated code of your form
0
 

Author Comment

by:gleznov
ID: 12134009
#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents Label1 As System.Windows.Forms.Label
    Friend WithEvents Label2 As System.Windows.Forms.Label
    Friend WithEvents Label3 As System.Windows.Forms.Label
    Friend WithEvents Label4 As System.Windows.Forms.Label
    Friend WithEvents GroupBox1 As System.Windows.Forms.GroupBox
    Friend WithEvents Label5 As System.Windows.Forms.Label
    Friend WithEvents Label6 As System.Windows.Forms.Label
    Friend WithEvents text1 As System.Windows.Forms.TextBox
    Friend WithEvents text2 As System.Windows.Forms.TextBox
    Friend WithEvents GroupBox2 As System.Windows.Forms.GroupBox
    Friend WithEvents Label7 As System.Windows.Forms.Label
    Friend WithEvents Label8 As System.Windows.Forms.Label
    Friend WithEvents Label9 As System.Windows.Forms.Label
    Friend WithEvents Label10 As System.Windows.Forms.Label
    Friend WithEvents Label11 As System.Windows.Forms.Label
    Friend WithEvents Label12 As System.Windows.Forms.Label
    Friend WithEvents GroupBox3 As System.Windows.Forms.GroupBox
    Friend WithEvents DBGrid1 As System.Windows.Forms.DataGrid
    Friend WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
    Friend WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
    Friend WithEvents text7 As System.Windows.Forms.TextBox
    Friend WithEvents text3 As System.Windows.Forms.TextBox
    Friend WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents DataSet_Users1 As OEL.DataSet_Users
    Friend WithEvents Combo4 As System.Windows.Forms.ComboBox
    Friend WithEvents Combo2 As System.Windows.Forms.ComboBox
    Friend WithEvents Combo5 As System.Windows.Forms.ComboBox
    Friend WithEvents Combo3 As System.Windows.Forms.ComboBox
    Friend WithEvents Combo1 As System.Windows.Forms.ComboBox
    Friend WithEvents Combo6 As System.Windows.Forms.ComboBox
    Friend WithEvents cmdDelete As System.Windows.Forms.Button
    Friend WithEvents cmdClose As System.Windows.Forms.Button
    Friend WithEvents cmdUpdate As System.Windows.Forms.Button
    Friend WithEvents cmdEdit As System.Windows.Forms.Button
    Friend WithEvents cmdAdd As System.Windows.Forms.Button
    Friend WithEvents cmdCancel As System.Windows.Forms.Button
    Friend WithEvents SqUpdatelCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents SqlDeleteCommand1 As System.Data.SqlClient.SqlCommand
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.Label1 = New System.Windows.Forms.Label
        Me.Label2 = New System.Windows.Forms.Label
        Me.Label3 = New System.Windows.Forms.Label
        Me.Label4 = New System.Windows.Forms.Label
        Me.GroupBox1 = New System.Windows.Forms.GroupBox
        Me.text2 = New System.Windows.Forms.TextBox
        Me.DataSet_Users1 = New OEL.DataSet_Users
        Me.text1 = New System.Windows.Forms.TextBox
        Me.Label6 = New System.Windows.Forms.Label
        Me.Label5 = New System.Windows.Forms.Label
        Me.GroupBox2 = New System.Windows.Forms.GroupBox
        Me.Combo4 = New System.Windows.Forms.ComboBox
        Me.Combo2 = New System.Windows.Forms.ComboBox
        Me.Combo5 = New System.Windows.Forms.ComboBox
        Me.Combo3 = New System.Windows.Forms.ComboBox
        Me.Combo1 = New System.Windows.Forms.ComboBox
        Me.Combo6 = New System.Windows.Forms.ComboBox
        Me.Label12 = New System.Windows.Forms.Label
        Me.Label11 = New System.Windows.Forms.Label
        Me.Label10 = New System.Windows.Forms.Label
        Me.Label9 = New System.Windows.Forms.Label
        Me.Label8 = New System.Windows.Forms.Label
        Me.Label7 = New System.Windows.Forms.Label
        Me.cmdAdd = New System.Windows.Forms.Button
        Me.cmdCancel = New System.Windows.Forms.Button
        Me.cmdDelete = New System.Windows.Forms.Button
        Me.cmdClose = New System.Windows.Forms.Button
        Me.cmdUpdate = New System.Windows.Forms.Button
        Me.cmdEdit = New System.Windows.Forms.Button
        Me.GroupBox3 = New System.Windows.Forms.GroupBox
        Me.DBGrid1 = New System.Windows.Forms.DataGrid
        Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
        Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
        Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
        Me.text7 = New System.Windows.Forms.TextBox
        Me.text3 = New System.Windows.Forms.TextBox
        Me.SqUpdatelCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlDeleteCommand1 = New System.Data.SqlClient.SqlCommand
        Me.GroupBox1.SuspendLayout()
        CType(Me.DataSet_Users1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.GroupBox2.SuspendLayout()
        Me.GroupBox3.SuspendLayout()
        CType(Me.DBGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'Label1
        '
        Me.Label1.BackColor = System.Drawing.SystemColors.AppWorkspace
        Me.Label1.Location = New System.Drawing.Point(0, 0)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(448, 48)
        Me.Label1.TabIndex = 0
        '
        'Label2
        '
        Me.Label2.BackColor = System.Drawing.SystemColors.AppWorkspace
        Me.Label2.Font = New System.Drawing.Font("Haettenschweiler", 15.75!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label2.ForeColor = System.Drawing.Color.White
        Me.Label2.Location = New System.Drawing.Point(16, 8)
        Me.Label2.Name = "Label2"
        Me.Label2.Size = New System.Drawing.Size(152, 24)
        Me.Label2.TabIndex = 1
        Me.Label2.Text = "User Setup"
        '
        'Label3
        '
        Me.Label3.BackColor = System.Drawing.Color.White
        Me.Label3.Location = New System.Drawing.Point(24, 32)
        Me.Label3.Name = "Label3"
        Me.Label3.Size = New System.Drawing.Size(424, 3)
        Me.Label3.TabIndex = 2
        '
        'Label4
        '
        Me.Label4.BackColor = System.Drawing.SystemColors.ControlDark
        Me.Label4.Location = New System.Drawing.Point(0, 48)
        Me.Label4.Name = "Label4"
        Me.Label4.Size = New System.Drawing.Size(448, 432)
        Me.Label4.TabIndex = 3
        '
        'GroupBox1
        '
        Me.GroupBox1.Controls.Add(Me.text2)
        Me.GroupBox1.Controls.Add(Me.text1)
        Me.GroupBox1.Controls.Add(Me.Label6)
        Me.GroupBox1.Controls.Add(Me.Label5)
        Me.GroupBox1.Location = New System.Drawing.Point(16, 56)
        Me.GroupBox1.Name = "GroupBox1"
        Me.GroupBox1.Size = New System.Drawing.Size(288, 80)
        Me.GroupBox1.TabIndex = 4
        Me.GroupBox1.TabStop = False
        '
        'text2
        '
        Me.text2.DataBindings.Add(New System.Windows.Forms.Binding("Text", Me.DataSet_Users1, "Users.Password"))
        Me.text2.Location = New System.Drawing.Point(88, 48)
        Me.text2.Name = "text2"
        Me.text2.Size = New System.Drawing.Size(144, 20)
        Me.text2.TabIndex = 3
        Me.text2.Text = ""
        '
        'DataSet_Users1
        '
        Me.DataSet_Users1.DataSetName = "DataSet_Users"
        Me.DataSet_Users1.Locale = New System.Globalization.CultureInfo("en-US")
        '
        'text1
        '
        Me.text1.DataBindings.Add(New System.Windows.Forms.Binding("Text", Me.DataSet_Users1, "Users.User"))
        Me.text1.Location = New System.Drawing.Point(88, 24)
        Me.text1.Name = "text1"
        Me.text1.Size = New System.Drawing.Size(144, 20)
        Me.text1.TabIndex = 2
        Me.text1.Text = ""
        '
        'Label6
        '
        Me.Label6.Location = New System.Drawing.Point(24, 48)
        Me.Label6.Name = "Label6"
        Me.Label6.Size = New System.Drawing.Size(64, 16)
        Me.Label6.TabIndex = 1
        Me.Label6.Text = "Password"
        '
        'Label5
        '
        Me.Label5.Location = New System.Drawing.Point(24, 24)
        Me.Label5.Name = "Label5"
        Me.Label5.Size = New System.Drawing.Size(32, 16)
        Me.Label5.TabIndex = 0
        Me.Label5.Text = "User"
        '
        'GroupBox2
        '
        Me.GroupBox2.Controls.Add(Me.Combo4)
        Me.GroupBox2.Controls.Add(Me.Combo2)
        Me.GroupBox2.Controls.Add(Me.Combo5)
        Me.GroupBox2.Controls.Add(Me.Combo3)
        Me.GroupBox2.Controls.Add(Me.Combo1)
        Me.GroupBox2.Controls.Add(Me.Combo6)
        Me.GroupBox2.Controls.Add(Me.Label12)
        Me.GroupBox2.Controls.Add(Me.Label11)
        Me.GroupBox2.Controls.Add(Me.Label10)
        Me.GroupBox2.Controls.Add(Me.Label9)
        Me.GroupBox2.Controls.Add(Me.Label8)
        Me.GroupBox2.Controls.Add(Me.Label7)
        Me.GroupBox2.Location = New System.Drawing.Point(16, 136)
        Me.GroupBox2.Name = "GroupBox2"
        Me.GroupBox2.Size = New System.Drawing.Size(400, 104)
        Me.GroupBox2.TabIndex = 5
        Me.GroupBox2.TabStop = False
        '
        'Combo4
        '
        Me.Combo4.DataBindings.Add(New System.Windows.Forms.Binding("Text", Me.DataSet_Users1, "Users.Database"))
        Me.Combo4.Location = New System.Drawing.Point(264, 48)
        Me.Combo4.Name = "Combo4"
        Me.Combo4.Size = New System.Drawing.Size(88, 21)
        Me.Combo4.TabIndex = 16
        '
        'Combo2
        '
        Me.Combo2.DataBindings.Add(New System.Windows.Forms.Binding("Text", Me.DataSet_Users1, "Users.Transactions"))
        Me.Combo2.Location = New System.Drawing.Point(264, 24)
        Me.Combo2.Name = "Combo2"
        Me.Combo2.Size = New System.Drawing.Size(88, 21)
        Me.Combo2.TabIndex = 15
        '
        'Combo5
        '
        Me.Combo5.DataBindings.Add(New System.Windows.Forms.Binding("Text", Me.DataSet_Users1, "Users.UserSetup"))
        Me.Combo5.Location = New System.Drawing.Point(88, 72)
        Me.Combo5.Name = "Combo5"
        Me.Combo5.Size = New System.Drawing.Size(88, 21)
        Me.Combo5.TabIndex = 14
        '
        'Combo3
        '
        Me.Combo3.DataBindings.Add(New System.Windows.Forms.Binding("Text", Me.DataSet_Users1, "Users.Reports"))
        Me.Combo3.Location = New System.Drawing.Point(88, 48)
        Me.Combo3.Name = "Combo3"
        Me.Combo3.Size = New System.Drawing.Size(88, 21)
        Me.Combo3.TabIndex = 13
        '
        'Combo1
        '
        Me.Combo1.DataBindings.Add(New System.Windows.Forms.Binding("Text", Me.DataSet_Users1, "Users.Files"))
        Me.Combo1.Location = New System.Drawing.Point(88, 24)
        Me.Combo1.Name = "Combo1"
        Me.Combo1.Size = New System.Drawing.Size(88, 21)
        Me.Combo1.TabIndex = 12
        '
        'Combo6
        '
        Me.Combo6.DataBindings.Add(New System.Windows.Forms.Binding("Text", Me.DataSet_Users1, "Users.Supplies"))
        Me.Combo6.Location = New System.Drawing.Point(264, 72)
        Me.Combo6.Name = "Combo6"
        Me.Combo6.Size = New System.Drawing.Size(88, 21)
        Me.Combo6.TabIndex = 10
        '
        'Label12
        '
        Me.Label12.Location = New System.Drawing.Point(192, 72)
        Me.Label12.Name = "Label12"
        Me.Label12.Size = New System.Drawing.Size(56, 16)
        Me.Label12.TabIndex = 5
        Me.Label12.Text = "Supplies"
        '
        'Label11
        '
        Me.Label11.Location = New System.Drawing.Point(192, 48)
        Me.Label11.Name = "Label11"
        Me.Label11.Size = New System.Drawing.Size(56, 16)
        Me.Label11.TabIndex = 4
        Me.Label11.Text = "Database"
        '
        'Label10
        '
        Me.Label10.Location = New System.Drawing.Point(192, 24)
        Me.Label10.Name = "Label10"
        Me.Label10.Size = New System.Drawing.Size(56, 16)
        Me.Label10.TabIndex = 3
        Me.Label10.Text = "Transact"
        '
        'Label9
        '
        Me.Label9.Location = New System.Drawing.Point(24, 72)
        Me.Label9.Name = "Label9"
        Me.Label9.Size = New System.Drawing.Size(56, 16)
        Me.Label9.TabIndex = 2
        Me.Label9.Text = "Users"
        '
        'Label8
        '
        Me.Label8.Location = New System.Drawing.Point(24, 48)
        Me.Label8.Name = "Label8"
        Me.Label8.Size = New System.Drawing.Size(64, 16)
        Me.Label8.TabIndex = 1
        Me.Label8.Text = "Reports"
        '
        'Label7
        '
        Me.Label7.Location = New System.Drawing.Point(24, 24)
        Me.Label7.Name = "Label7"
        Me.Label7.Size = New System.Drawing.Size(40, 16)
        Me.Label7.TabIndex = 0
        Me.Label7.Text = "Files"
        '
        'cmdAdd
        '
        Me.cmdAdd.Location = New System.Drawing.Point(24, 256)
        Me.cmdAdd.Name = "cmdAdd"
        Me.cmdAdd.Size = New System.Drawing.Size(80, 24)
        Me.cmdAdd.TabIndex = 6
        Me.cmdAdd.Text = "&Add"
        '
        'cmdCancel
        '
        Me.cmdCancel.Location = New System.Drawing.Point(104, 256)
        Me.cmdCancel.Name = "cmdCancel"
        Me.cmdCancel.Size = New System.Drawing.Size(80, 24)
        Me.cmdCancel.TabIndex = 7
        Me.cmdCancel.Text = "&Cancel"
        '
        'cmdDelete
        '
        Me.cmdDelete.Location = New System.Drawing.Point(184, 256)
        Me.cmdDelete.Name = "cmdDelete"
        Me.cmdDelete.Size = New System.Drawing.Size(80, 24)
        Me.cmdDelete.TabIndex = 8
        Me.cmdDelete.Text = "&Delete"
        '
        'cmdClose
        '
        Me.cmdClose.Location = New System.Drawing.Point(264, 256)
        Me.cmdClose.Name = "cmdClose"
        Me.cmdClose.Size = New System.Drawing.Size(72, 24)
        Me.cmdClose.TabIndex = 9
        Me.cmdClose.Text = "Close"
        '
        'cmdUpdate
        '
        Me.cmdUpdate.Location = New System.Drawing.Point(24, 256)
        Me.cmdUpdate.Name = "cmdUpdate"
        Me.cmdUpdate.Size = New System.Drawing.Size(80, 24)
        Me.cmdUpdate.TabIndex = 10
        Me.cmdUpdate.Text = "&Update"
        Me.cmdUpdate.Visible = False
        '
        'cmdEdit
        '
        Me.cmdEdit.Location = New System.Drawing.Point(104, 256)
        Me.cmdEdit.Name = "cmdEdit"
        Me.cmdEdit.Size = New System.Drawing.Size(80, 24)
        Me.cmdEdit.TabIndex = 11
        Me.cmdEdit.Text = "&Edit"
        '
        'GroupBox3
        '
        Me.GroupBox3.Controls.Add(Me.DBGrid1)
        Me.GroupBox3.Location = New System.Drawing.Point(8, 288)
        Me.GroupBox3.Name = "GroupBox3"
        Me.GroupBox3.Size = New System.Drawing.Size(424, 176)
        Me.GroupBox3.TabIndex = 12
        Me.GroupBox3.TabStop = False
        '
        'DBGrid1
        '
        Me.DBGrid1.BackgroundColor = System.Drawing.Color.DarkGray
        Me.DBGrid1.DataMember = "Users"
        Me.DBGrid1.DataSource = Me.DataSet_Users1
        Me.DBGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.DBGrid1.Location = New System.Drawing.Point(8, 16)
        Me.DBGrid1.Name = "DBGrid1"
        Me.DBGrid1.ReadOnly = True
        Me.DBGrid1.Size = New System.Drawing.Size(408, 160)
        Me.DBGrid1.TabIndex = 0
        '
        'SqlDataAdapter1
        '
        Me.SqlDataAdapter1.DeleteCommand = Me.SqlDeleteCommand1
        Me.SqlDataAdapter1.InsertCommand = Me.SqlInsertCommand1
        Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
        Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "Users", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("User", "User"), New System.Data.Common.DataColumnMapping("Password", "Password"), New System.Data.Common.DataColumnMapping("Files", "Files"), New System.Data.Common.DataColumnMapping("Transactions", "Transactions"), New System.Data.Common.DataColumnMapping("Reports", "Reports"), New System.Data.Common.DataColumnMapping("Database", "Database"), New System.Data.Common.DataColumnMapping("UserSetup", "UserSetup"), New System.Data.Common.DataColumnMapping("Supplies", "Supplies")})})
        Me.SqlDataAdapter1.UpdateCommand = Me.SqUpdatelCommand1
        '
        'SqlInsertCommand1
        '
        Me.SqlInsertCommand1.CommandText = "INSERT INTO Users([User], Password, Files, Transactions, Reports, [Database], Use" & _
        "rSetup, Supplies) VALUES (@Param1, @Password, @Files, @Transactions, @Reports, @" & _
        "Param2, @UserSetup, @Supplies); SELECT [User], Password, Files, Transactions, Re" & _
        "ports, [Database], UserSetup, Supplies FROM Users ORDER BY USER"
        Me.SqlInsertCommand1.Connection = Me.SqlConnection1
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Param1", System.Data.SqlDbType.NVarChar, 50, "User"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Password", System.Data.SqlDbType.NVarChar, 50, "Password"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Files", System.Data.SqlDbType.Int, 4, "Files"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Transactions", System.Data.SqlDbType.Int, 4, "Transactions"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Reports", System.Data.SqlDbType.Int, 4, "Reports"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Param2", System.Data.SqlDbType.Int, 4, "Database"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@UserSetup", System.Data.SqlDbType.Int, 4, "UserSetup"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Supplies", System.Data.SqlDbType.Int, 4, "Supplies"))
        '
        'SqlConnection1
        '
        Me.SqlConnection1.ConnectionString = "workstation id=GA1BERTTXP;packet size=4096;integrated security=SSPI;data source=G" & _
        "A1CDC02;persist security info=False;initial catalog=crmdb"
        '
        'SqlSelectCommand1
        '
        Me.SqlSelectCommand1.CommandText = "SELECT [User], Password, Files, Transactions, Reports, [Database], UserSetup, Sup" & _
        "plies FROM Users ORDER BY USER"
        Me.SqlSelectCommand1.Connection = Me.SqlConnection1
        '
        'text7
        '
        Me.text7.Location = New System.Drawing.Point(512, 264)
        Me.text7.Name = "text7"
        Me.text7.Size = New System.Drawing.Size(72, 20)
        Me.text7.TabIndex = 13
        Me.text7.Text = ""
        '
        'text3
        '
        Me.text3.Location = New System.Drawing.Point(520, 288)
        Me.text3.Name = "text3"
        Me.text3.Size = New System.Drawing.Size(56, 20)
        Me.text3.TabIndex = 14
        Me.text3.Text = ""
        '
        'frmUserSetup
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(448, 478)
        Me.Controls.Add(Me.text3)
        Me.Controls.Add(Me.text7)
        Me.Controls.Add(Me.GroupBox3)
        Me.Controls.Add(Me.cmdEdit)
        Me.Controls.Add(Me.cmdUpdate)
        Me.Controls.Add(Me.cmdClose)
        Me.Controls.Add(Me.cmdDelete)
        Me.Controls.Add(Me.cmdCancel)
        Me.Controls.Add(Me.cmdAdd)
        Me.Controls.Add(Me.GroupBox2)
        Me.Controls.Add(Me.GroupBox1)
        Me.Controls.Add(Me.Label4)
        Me.Controls.Add(Me.Label3)
        Me.Controls.Add(Me.Label2)
        Me.Controls.Add(Me.Label1)
        Me.Name = "frmUserSetup"
        Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterParent
        Me.GroupBox1.ResumeLayout(False)
        CType(Me.DataSet_Users1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.GroupBox2.ResumeLayout(False)
        Me.GroupBox3.ResumeLayout(False)
        CType(Me.DBGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12134058
Regarding the add problem it is better to do it this way
             Dim dr As DataRow
             Dim dr As DataRow
                dr = DataSet_Users1.Tables("users").newrow
                dr.Item("User") = text1.Text
                dr.Item("Password") = text2.Text
                dr.Item("Files") = Val(Combo1.Text)
                dr.Item("Transactions") = Val(Combo2.Text)
                dr.Item("Reports") = Val(Combo3.Text)
                dr.Item("Database") = Val(Combo4.Text)
                dr.Item("UserSetup") = Val(Combo5.Text)
                dr.Item("Supplies") = Val(Combo6.Text)
               dr.endedit
               DataSet_Users1.Tables("users").rows.add(dr)
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12134180
Ok, from your windows generated code I can see there is only an insert command, no update and delete command

which can mean 3 things,

1. your table doesn't have a primary key
2. your table didn't have a primary key when you dragged the sqldataadapter but does now
3. you deleted the update and delete command

which one is it ?
0
 

Author Comment

by:gleznov
ID: 12134213
   Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click
        Dim dr As DataRow
        dr = DataSet_Users1.Tables("Users").NewRow
        dr.Item("User") = ""
        dr.Item("Password") = ""
        dr.Item("Files") = "0"
        dr.Item("Transactions") = "0"
        dr.Item("Reports") = "0"
        dr.Item("Database") = "0"
        dr.Item("UserSetup") = "0"
        dr.Item("Supplies") = "0"
        DataSet_Users1.Tables("Users").Rows.Add(dr)
        SelIndex = DataSet_Users1.Tables("Users").Rows.Count - 1
        DBGrid1.CurrentRowIndex = SelIndex

And the reason I don't set them equal to the textboxes to start with is that the textboxes are bound to the dataset, so they're never actually empty.  Unless I stick a whole series of identical invisible boxes behind them and switch back n forth for this, I figured it was just easier to add the row then allow editing of that row.

JP
0
 

Author Comment

by:gleznov
ID: 12134227
And to your other question, I believe the answer is 2

JP
0
 

Author Comment

by:gleznov
ID: 12134247
Although, oddly, when I click the DataAdapter, it shows those, because I told it to add them recently - clicked on the property for them and selected New, then renamed them to SqlUpdateCommand1 and SqlDeleteCommand1 respectively.  Why they haven't been hardcoded into the windows code, dunno.

JP
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12134272
If there are bound then why do you do this

Dim dr As DataRow
            dr = DataSet_Users1.Tables("Users").Rows(SelIndex)
            dr.Item("User") = text1.Text
            dr.Item("Password") = text2.Text
            dr.Item("Files") = Val(Combo1.Text)
            dr.Item("Transactions") = Val(Combo2.Text)
            dr.Item("Reports") = Val(Combo3.Text)
            dr.Item("Database") = Val(Combo4.Text)
            dr.Item("UserSetup") = Val(Combo5.Text)
            dr.Item("Supplies") = Val(Combo6.Text)
            dr.endedit

because that would be doing the thing twice ?
0
 

Author Comment

by:gleznov
ID: 12134291
Yeah, for some reason at first the grid (dataset) didn't seem to reflect the changes, but I realized later it did seem redundant - I left that code in because

1) I may need something like it later and
2) it didn't seem to hurt anything

Should I go ahead and rem it out?

JP
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12134306
Ok then delete your dataadapter and connection under your form (not in code)

and add them again, that should solve it.
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12134322
It doesn't hurt anything, but why do it twice ?
0
 

Author Comment

by:gleznov
ID: 12134340
I just remmed them out and when I Add a new record, after I Update, it leaves the user and password blank for some reason.  

JP
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12134388
Bye the way I assume your comboboxes are bound to because otherwise you do need to do it that way.

I'm going to have a bite to eat now but I'll be back ;-)

We will crack this thing if its the last thing I do
0
 
LVL 25

Accepted Solution

by:
RonaldBiemans earned 500 total points
ID: 12134409
Could you post your entire code (including the windows generated code, so I can reproduce it here)
0
 

Author Comment

by:gleznov
ID: 12134460
0
 

Author Comment

by:gleznov
ID: 12134498
Consider it cracked ;P  Thank you so much!  If I can get the delete part working now, I can throw the rest of this whole system together in a few more days.  The link above is to my new question, and hopefully between these two questions the points will have made it worth your while - you've been incredibly helpful and attentive.

JP
0
 

Author Comment

by:gleznov
ID: 12135497
I could use your help at the above-listed link.  Someone came in there and dropped pages of an example program that I can't imaging sifting through (lol)  

 Private Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDelete.Click
        'cmdDelete.Enabled = False
        'Exit Sub

        Dim ans As String
        ans = MsgBox("Are you sure you want to delete this user?", MsgBoxStyle.YesNo, "Attention!")
        If ans = vbYes Then

            Dim dr As DataRow
            dr = DataSet_Users1.Tables("Users").Rows(SelIndex)
            DataSet_Users1.Tables("Users").Rows.Remove(dr)
            'DataSet_Users1.Tables("Users").Rows(SelIndex).AcceptChanges()
            dr.EndEdit()

            Try
                ' When you call the update ADO.net will go trough all the record that have a rowstate  'Modified' and will update that record
                SqlDataAdapter1.Update(DataSet_Users1)
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

            DataSet_Users1.AcceptChanges()

            SelIndex = SelIndex - 1
            If SelIndex < 0 Then SelIndex = 0
            DBGrid1.CurrentRowIndex = SelIndex
            DBGrid1.Refresh()
        End If
    End Sub

Doesn't permanently delete the record - drop in on the above-listed link if you get a chance.  Thanks again!

JP
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12140601
sorry, about not getting to you sooner (had to get some sleep) ;-), so is it working now ?
0
 

Author Comment

by:gleznov
ID: 12141750
It's all good now :)

JP
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

757 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now