Solved

save and update in visual basic 2005

Posted on 2008-06-25
13
157 Views
Last Modified: 2013-11-26
I am developing with visual basic .net 2005 and access 2003.
I have successfully implemented a function that allows me to save and update the records.  Howeever, what I have an issue with is for instance I create a new record by saving it fresh and then without closing it MY GOAL IS to update it the second time the Save button is pressed, but what happens the record is saved second time, which results in duplication.  But after I open a saved client on another client which in return populates on the form that I used to save the client and hit save button the record is appropriately updated.  what is  the cause for that?
thank you
0
Comment
Question by:systems_ax
[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
  • 7
  • 6
13 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 21872133
Do you have a primary key in your table ? Is this automatically filled ?
When you save your record, you need to get the primary key for that record and use that one.

Example
You create a record --> it will get a primary key value of 100
Return this value to your screen (into a variable).
When pressing the button again, check if the variable is filled and use this variable to update the values !
0
 
LVL 1

Author Comment

by:systems_ax
ID: 21874985
Dhaest,
I do have a primary key.  when I save my client I also retrieve an id for it into the hidden text box on the form, can I use that text field's value.
I will try it out.
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 21875044
How do you update ? Do you perform a query or is it throug databound controls

FE:
private function Save()
 ... insert into ...
end function

--> will be:
private function Save()
  if Key <> null then
        update ...
  else
        insert into ...
  end if
0
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!

 
LVL 1

Author Comment

by:systems_ax
ID: 21875427
this is my original code before posting this question which is described in my original question:
I will post the code I tried which still does not do what I need in a few minutes.
thank you
 Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim LastName_Selected As String = txtLast.Text
        Dim firstname_selected As String = txtFirst.Text
        Dim middlename_selected As String = txtMiddle.Text
 
        Dim sql As String = String.Empty
        If _clientID = 0 Then
            sql = "insert into tblClients(ClientFirstName, ClientLastName, ClientMiddleName)" & "values('" & txtFirst.Text & "', '" & txtLast.Text & "', '" & txtMiddle.Text & "')"
            MsgBox("You have successfully saved " & firstname_selected & " " & middlename_selected & " " & LastName_Selected & "!", MsgBoxStyle.Information, "Client Saved")
 
        Else
            sql = "update tblClients set ClientFirstName='" & txtFirst.Text & "', ClientLastName= '" & txtLast.Text & " ', ClientMiddleName='" & txtMiddle.Text & "' where ClientId=" & _clientID
            MsgBox("You have successfully updated " & firstname_selected & " " & middlename_selected & " " & LastName_Selected & "!", MsgBoxStyle.Information, "Client Saved")
        End If
        conn.Open()
        Dim command As New OleDbCommand(sql, conn)
        command.ExecuteNonQuery()
        conn.Close()
 
    End Sub

Open in new window

0
 
LVL 1

Author Comment

by:systems_ax
ID: 21875449
this is the code that I tried but I have open/close connection problems:

thank you

Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim LastName_Selected As String = txtLast.Text
        Dim firstname_selected As String = txtFirst.Text
        Dim middlename_selected As String = txtMiddle.Text
 
        Dim sql As String = String.Empty
        If _clientID = 0 Then
            sql = "insert into tblClients(ClientFirstName, ClientLastName, ClientMiddleName)" & "values('" & txtFirst.Text & "', '" & txtLast.Text & "', '" & txtMiddle.Text & "')"
 
 
            Dim command1 As New OleDbCommand(sql, conn)
            conn.Open()
            Dim x1 As Integer = command1.ExecuteNonQuery()
 
            Dim scalar As New OleDbCommand("SELECT MAX(ClientId) AS lastID FROM tblClients", conn)
            Dim result As Integer = scalar.ExecuteScalar()
 
            txtHiddenId.Text = result
 
            MsgBox("You have successfully saved " & firstname_selected & " " & middlename_selected & " " & LastName_Selected & "!", MsgBoxStyle.Information, "Client Saved")
 
        Else
            sql = "update tblClients set ClientFirstName='" & txtFirst.Text & "', ClientLastName= '" & txtLast.Text & " ', ClientMiddleName='" & txtMiddle.Text & "' where ClientId=" & _clientID
            MsgBox("You have successfully updated " & firstname_selected & " " & middlename_selected & " " & LastName_Selected & "!", MsgBoxStyle.Information, "Client Saved")
        End If
        conn.Open()
        Dim command As New OleDbCommand(sql, conn)
        command.ExecuteNonQuery()
        conn.Close()
 
    End Sub

Open in new window

0
 
LVL 1

Author Comment

by:systems_ax
ID: 21878300
Dhaest,
any ideas I still cannot figure it out.
thank you
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 21881518
I see your problem. If you don't have a clientID, you perform a sql-insert. Afterward you try to execute the sql again.
Try this below (moved the end if)
Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim LastName_Selected As String = txtLast.Text
        Dim firstname_selected As String = txtFirst.Text
        Dim middlename_selected As String = txtMiddle.Text
 
        Dim sql As String = String.Empty
        If _clientID = 0 Then
            sql = "insert into tblClients(ClientFirstName, ClientLastName, ClientMiddleName)" & "values('" & txtFirst.Text & "', '" & txtLast.Text & "', '" & txtMiddle.Text & "')"
 
 
            Dim command1 As New OleDbCommand(sql, conn)
            conn.Open()
            Dim x1 As Integer = command1.ExecuteNonQuery()
 
            Dim scalar As New OleDbCommand("SELECT MAX(ClientId) AS lastID FROM tblClients", conn)
            Dim result As Integer = scalar.ExecuteScalar()
 
            txtHiddenId.Text = result
 
            MsgBox("You have successfully saved " & firstname_selected & " " & middlename_selected & " " & LastName_Selected & "!", MsgBoxStyle.Information, "Client Saved")
 
        Else
            sql = "update tblClients set ClientFirstName='" & txtFirst.Text & "', ClientLastName= '" & txtLast.Text & " ', ClientMiddleName='" & txtMiddle.Text & "' where ClientId=" & _clientID
            MsgBox("You have successfully updated " & firstname_selected & " " & middlename_selected & " " & LastName_Selected & "!", MsgBoxStyle.Information, "Client Saved")
        conn.Open()
        Dim command As New OleDbCommand(sql, conn)
        command.ExecuteNonQuery()
        conn.Close()
        End If
 
    End Sub

Open in new window

0
 
LVL 1

Author Comment

by:systems_ax
ID: 21885386
Dhaest,
your code did not work but thank you for trying. I figured it out on my own, so now everything works as desired.  can you look over my code and let me know if this code follows basic standard of programming, meaning it is not going to crush in a long run.
thank you
Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim LastName_Selected As String = txtLast.Text
        Dim firstname_selected As String = txtFirst.Text
        Dim middlename_selected As String = txtMiddle.Text
 
        Dim sql As String = String.Empty
        If _clientID = 0 And txtClientTrueId.Text = "" Then
            sql = "insert into tblClients(ClientFirstName, ClientLastName, ClientMiddleName)" & "values('" & txtFirst.Text & "', '" & txtLast.Text & "', '" & txtMiddle.Text & "')"
            Dim command1 As New OleDbCommand(sql, conn)
            conn.Open()
            Dim x1 As Integer = command1.ExecuteNonQuery()
            Dim scalar As New OleDbCommand("SELECT MAX(ClientId) AS lastID FROM tblClients", conn)
            Dim result As Integer = scalar.ExecuteScalar()
            txtClientTrueId.Text = result
            MsgBox("You have successfully saved " & firstname_selected & " " & middlename_selected & " " & LastName_Selected & "!", MsgBoxStyle.Information, "Client Saved")
            conn.Close()
        Else
            sql = "update tblClients set ClientFirstName='" & txtFirst.Text & "', ClientLastName= '" & txtLast.Text & " ', ClientMiddleName='" & txtMiddle.Text & "' where ClientId=" & _clientID
            MsgBox("You have successfully updated " & firstname_selected & " " & middlename_selected & " " & LastName_Selected & "!", MsgBoxStyle.Information, "Client Saved")
            conn.Open()
            Dim command As New OleDbCommand(sql, conn)
            command.ExecuteNonQuery()
            conn.Close()
        End If
    End Sub

Open in new window

0
 
LVL 53

Accepted Solution

by:
Dhaest earned 500 total points
ID: 21897118
What's the difference with the code I've provided... I only see 1 difference and that's the test-case (If _clientID = 0 And txtClientTrueId.Text = "" Then).
So you, your code looks good and should be working for a long time.
The only thing I would recommend is using sqlParameters instead of the complete sql-query to make sure that your code is safe against sql-injection. You even don't have to bother about the syntax for adding " for strings, ...
More info: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx
A small example of using sql-parameters
sql = "update tblClients set ClientFirstName= @ClientFirstName, ClientLastName= @ClientLastName, ClientMiddleName= @ClientMiddleName where ClientId= @clientID"
 
Dim command As New OleDbCommand(sql, conn)
command.Parameters.AddWithValue("@ClientFirstName", txtFirst.Text)
command.Parameters.AddWithValue("@ClientLastName", txtLast.Text)
command.Parameters.AddWithValue("@ClientMiddleName", txtMiddle.Text )
command.Parameters.AddWithValue("@ClientId", _ClientId )

Open in new window

0
 
LVL 1

Author Comment

by:systems_ax
ID: 21900879
Dhaest,
thank you.
what is sql-injection
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 21905249
SQL injection is a technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is in fact an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another.
More info: http://en.wikipedia.org/wiki/SQL_injection

Short said: if someone with bad intentions enters for example the following in a textbox:
"; drop database;" --> it's will be possible that youre sql-statement will be like this:
update yourtable set yourcolumn = 'A'; drop database
--> so there will happen an update and a drop of your database
0
 
LVL 1

Author Comment

by:systems_ax
ID: 21913753
Dhaest,
thank you.  Do I only use the sql-parameters for UPDATES to avoid the sql injection, or do I use parameters for INSERT AND DELETE ALSO?  also, what does it mean when you say that the database is dropped?
thank you
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 21913762
The best way is to always work with sql-parameters.
With drop database I mean the statement that will delete your entire database-data and database-structure.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

719 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