Solved

Add/Update/Delete Record in VB.NET

Posted on 2008-10-23
6
3,760 Views
Last Modified: 2012-08-13
How do I Add/update a record in VB.NET for a table that does not have a primary key ? I want to update the record without using the SqlCommand.
0
Comment
Question by:msbhatt1
  • 2
6 Comments
 
LVL 18

Expert Comment

by:UnifiedIS
Comment Utility
Do you mean without using the sqlcommandbuilder?  You can set your insert/update SQL as the sqlcommand.text and then use sqlcommand.exectuenonquery to send it to your database.
0
 
LVL 41

Accepted Solution

by:
graye earned 250 total points
Comment Utility
That's gonna be tough...   Using just the normal ADO.Net, you can perform an Insert into a table without a primary key, but you'd need a primary key to identify the desired row in order to perform an Update or Delete operation on that row.  Recall that ADO.Net inherently uses a "disconnected model" (which is the part of the puzzle that requires a primary key).
An easy alternative would be to use the older "classic" ADO... since it's "connected" model has no such requirement for a primary key.
0
 
LVL 3

Assisted Solution

by:Mezillinu
Mezillinu earned 250 total points
Comment Utility
Hi, the following are examples, on how to INSERT, UPDATE and DELETE using SQL from VB. NET

When deleting, or updating, you can use names, but it is not the recommended way of updating and deleting. the recommended way is to have an ID which is unique. in names, you can have duplicates, which could lead of you deleting not the intended one.

so for future practise, its better to stick to primary keys with unique ID's, so you use them when updating and deleting.

cheers
ADDING
 

 Try

            Dim SQL_Connection As New SqlClient.SqlConnection

            Dim SQL_Command As New SqlClient.SqlCommand

            Dim sSQL_Command As String = ""
 

            Dim tools As New Tools

            Dim databaseConnectionString As String = tools.getXmlValue("DBConnStringErrorHandlingException")

            SQL_Command.Connection.ConnectionString = databaseConnectionString
 

            'create the insert 

            sSQL_Command = "INSERT INTO tbl_EmailTemplates ( " & _

                           "     [TemplateName]        " & _

                           "   , [TemplateDescription]        " & _

                           "   , [TemplateContent]          " & _

                           ") " & _

                           "VALUES ( " & _

                           "     @TemplateName        " & _

                           "   , @TemplateDescription        " & _

                           "   , @TemplateContent          " & _

                                                ")"
 

            With SQL_Command

                .Parameters.AddWithValue("@TemplateName", Me.txtTemplateName.Text)

                .Parameters.AddWithValue("@TemplateDescription", Me.txtDescription.Text)

                .Parameters.AddWithValue("@TemplateContent", Me.htmlEmailTemplateEditor.Value.ToString)

                .CommandText = sSQL_Command

            End With
 

            SQL_Connection.Open()

            SQL_Command.ExecuteNonQuery()

            SQL_Connection.Close()

        Catch ex As Exception

            Me.lblStatus.Text = ex.Message

        End Try

        
 

DELETING - USING OLE DB, CHANGE AS ABOVE
 

Try
 

                Dim SQL_Connection As New OleDbConnection

                Dim SQL_Command As New OleDbCommand

                Dim sSQL_Command As String = ""
 

                Dim tools As New Tools

                Dim databaseConnectionString As String = tools.getXmlValue("DBConnStringErrorHandlingException")

                SQL_Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & databaseConnectionString

                SQL_Command.Connection = SQL_Connection
 

                'create the insert 

                sSQL_Command = "delete from tbl_emailTemplates where templatename = @templatename"
 

                With SQL_Command

                    .Parameters.AddWithValue("@TemplateName", Me.txtTemplateName.Text)

                    .CommandText = sSQL_Command

                End With
 

                SQL_Connection.Open()

                SQL_Command.ExecuteNonQuery()

                SQL_Connection.Close()

                refreshApp()

            Catch ex As Exception

                Me.lblStatus.Text = ex.Message

            End Try
 

UPDATING - ALSO USING OLE DB, CHANGE AS SQL CLIENT'S SQLCOMMAND AND SO ON
 

 Try

            Dim SQL_Connection As New OleDbConnection

            Dim SQL_Command As New OleDbCommand

            Dim sSQL_Command As String = ""
 

            Dim tools As New Tools

            Dim databaseConnectionString As String = tools.getXmlValue("DBConnStringErrorHandlingException")

            SQL_Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & databaseConnectionString

            SQL_Command.Connection = SQL_Connection
 

            'create the insert 

            sSQL_Command = "update tbl_EmailTemplates " & _

                           "  set TemplateContent = @templatecontent " & _

                           " where TemplateName = @templatename"
 

            With SQL_Command

                .Parameters.AddWithValue("@TemplateName", Me.txtTemplateName.Text)

                .Parameters.AddWithValue("@TemplateContent", Me.htmlEmailTemplateEditor.Value.ToString)

                .CommandText = sSQL_Command

            End With
 

            SQL_Connection.Open()

            SQL_Command.ExecuteNonQuery()

            SQL_Connection.Close()
 

            SQL_Command.Dispose()

            SQL_Connection.Dispose()

        Catch ex As Exception

            Me.lblStatus.Text = ex.Message
 

        End Try

Open in new window

0
 
LVL 3

Expert Comment

by:Mezillinu
Comment Utility
note that in the above examples, the only one using SQL, is the first insertion, so you have to convert the ole db command to sql, like in the first example.

its not so hard, you should cope :-)
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

771 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