Solved

save and update in visual basic 2005

Posted on 2008-06-25
13
151 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

708 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

20 Experts available now in Live!

Get 1:1 Help Now