Solved

Select, Insert, Update With Parameters using Oledb

Posted on 2009-03-31
13
872 Views
Last Modified: 2012-05-06
Hi EE,

What is the best way to select, insert and update data in VB 2008?  
It seems tedious to write out all the values of an insert statement and I read somewhere that you could use parameters.
Can you use parameters with a MS Access backend?  
If yes, could you provide an examples?  I especially need an example of the insert statement that uses parameters and returns the "SELECT @@IDENTITY"?
Thank you
0
Comment
Question by:Sheritlw
  • 5
  • 5
  • 3
13 Comments
 
LVL 48

Expert Comment

by:jpaulino
ID: 24033973
Here you have some examples:
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.aspx
Select on the Remark the ExecuteReader, ExecuteNonQuery  and ExecuteScalar methods to see other examples.

' Use this and a ExecuteScalar method

Dim SQL As String = "INSERT INTO myTable([username]) VALUES (@username); Select @@IDENTITY;"

Open in new window

0
 
LVL 6

Accepted Solution

by:
ITHelper80 earned 250 total points
ID: 24034023
Here is how I capture the @@Identity
        myPath = Server.MapPath("yourdb.mdb")

        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data source=" & myPath & ";"
 

        Dim insCmd As New System.Data.OleDb.OleDbCommand
 

        insCmd.CommandText = "insert into Data (your code) values (your code)
 

        insCmd.Connection = con
 

        Dim idCmd As New System.Data.OleDb.OleDbCommand

        'Captures AutoID Number

        idCmd.CommandText = "select @@IDENTITY"
 

        idCmd.Connection = con

        con.Open()

        insCmd.ExecuteNonQuery()

        ' sets newid as value of @@Identity

        Dim newid As Integer = idCmd.ExecuteScalar()

        con.Close()

Open in new window

0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24034485
@ITHelper80,
You don't need to do two calls to the database. You can use only one ExecuteScalar() that inserts and gets the inserted ID.
0
 

Author Comment

by:Sheritlw
ID: 24034628
Hi ipaulino,
Do you have an example that you can provide here?  Everytime I click on a link my IE crashes.
Thanks,
0
 
LVL 6

Expert Comment

by:ITHelper80
ID: 24034634
Honeslty I am still learning this myself but I thought

Execute NonQuery..
1.It will not return any data.
2.It is used with insert and update.
3.It returns only the number of rows affected.

Execute Scaler..
1.It returns only one value.
2.That value will the first column first row value.

How can you perform an Insert/Update etc with the Execute Scaler command?
0
 

Author Comment

by:Sheritlw
ID: 24034670
Sorry I guess this question was for jpaulino.

Can you provide an example on using the ExecuteScaler command?

Thanks
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 6

Expert Comment

by:ITHelper80
ID: 24034688
My code shows you how to use the ExecuteScaler command to retrieve the @@Identity value.

The link JPaulino posted even said to use ExecuteNonQuery for Selects,Inserts and Updates so I am confused why he says not to use it.
0
 
LVL 48

Assisted Solution

by:jpaulino
jpaulino earned 250 total points
ID: 24034751
>> Honeslty I am still learning this myself but I thought
So do I ... always learning.
 
You code is right, but you can do all in one command. ExecuteScaler() also executes an T-SQL command and if you saw my first post you will see that I have used:
Dim SQL As String = "INSERT INTO myTable([username]) VALUES (@username); Select @@IDENTITY;"

You can do all like this:

            Using connection As New OleDbConnection(connString)

                Dim SQL As String = "INSERT INTO myTable([username]) VALUES (@username); Select @@IDENTITY;"

                Using command As New OleDbCommand(SQL, connection)
 

                    Dim result As Integer = command.ExecuteScalar

                    Debug.WriteLine(result.ToString)
 

                End Using

            End Using

Open in new window

0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24034769
You should use ExecuteNonQuery if you don't want need to retrieve any values from the T-SQL. The ExecuteNonQuery also returns the number of affected record.
If you do "INSERT INSERT INTO myTable([username]) VALUES (@username)"  the result should be 1
 

            Using connection As New OleDbConnection(connString)

                Dim SQL As String = "INSERT INTO myTable([username]) VALUES (@username);"

                Using command As New OleDbCommand(SQL, connection)

 

                    Dim result As Integer = command.ExecuteNonQuery 

                    Debug.WriteLine(result.ToString)

 

                End Using

            End Using

Open in new window

0
 

Author Comment

by:Sheritlw
ID: 24034811
Hi iPaulino,

Sorry I couldn't read your post... everytime I click on a link my IE crashes.
I will try this now.
Thanks
0
 

Author Comment

by:Sheritlw
ID: 24035432
Hi Paulino,

I tried the example you provided with the Select @@Identify at the end and got an error saying there were characters at the end of insert statement.
Here is the insert I used
str = "Insert into Company " _
       & " (CompanyName, Address1, Address2, CityName, StateID, Postal, " _
       & " CntryID, DefaultStateID, DefaultCntryID) Values " _
       & " ('" & msCompanyName & "', '" & msAdd1 & "','" & msAdd2 & "', " _
       & " '" & msCity & "'," & miStateID & ",'" & msPostal & "', " _
       & " " & miCntryID & ", " & miDefStateID & ", " & miDefCntryID & "); " _
       & " Select @@IDENTITY;"
0
 

Author Comment

by:Sheritlw
ID: 24035912
This is what I finally got to work.

            Using cmd As New OleDbCommand(GenerateSQLInsert(), oleCnn)
                Dim idCmd As New System.Data.OleDb.OleDbCommand
                oleCnn.Open()
                i = cmd.ExecuteNonQuery()
                idCmd.CommandText = "Select @@IDENTITY"
                idCmd.Connection = oleCnn
                i = idCmd.ExecuteScalar()
                oleCnn.Close()
            End Using

            Return i

I will split the points
Thanks for your help
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24036836
>> I tried the example you provided with the Select @@Identify at the end and got an error saying there were characters at the end of insert statement.
What code have you done for this ?
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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

863 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

18 Experts available now in Live!

Get 1:1 Help Now