Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Add/Update/Delete Record in VB.NET

Posted on 2008-10-23
6
Medium Priority
?
3,786 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
ID: 22787876
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 1000 total points
ID: 22798889
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 1000 total points
ID: 22916113
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
ID: 22916118
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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
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…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

972 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