Solved

save and update in visual basic 2005

Posted on 2008-06-25
13
152 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
  • 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

939 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

10 Experts available now in Live!

Get 1:1 Help Now