?
Solved

save and update in visual basic 2005

Posted on 2008-06-25
13
Medium Priority
?
158 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

764 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