Solved

How do I get the value from Sql command

Posted on 2008-10-21
8
782 Views
Last Modified: 2013-11-26
I have the following code Sub on an Visual Studio 2008 project.
How do I assigned the result value of the SQL Query to a variable
I'm new into to datasets so I'm trying to understand them a little
I think I need to create a dataset and fill it with the resulst of the query and search trhough there.
I was just wondering if there is a straight way of doing this without creating a data set
sample code expected!!

Private Sub CHECKMAXID()
        Dim cmd As OleDb.OleDbCommand
        sSql = ""
        sSql = "SELECT MAX(ID) FROM MASTER"
        If Con.State = ConnectionState.Closed Then Con.Open()
        cmd = New OleDb.OleDbCommand(sSql, Con)
        cmd.ExecuteNonQuery()

    End Sub
0
Comment
Question by:ranhell
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 27

Expert Comment

by:VBRocks
ID: 22769043
You can use ExecuteScalar():

    Dim value as string = cmd.ExecuteScalar()
0
 
LVL 7

Expert Comment

by:the_bachelor
ID: 22769055
yes instead of  cmd.ExecuteNonQuery()
you can have somthn like
Dim retValue as Integer  = cmd.ExecuteScalar()
0
 
LVL 10

Author Comment

by:ranhell
ID: 22769318
thanks both for UR help it worked just fine.
I will assigned the points anyways

Now, I don't mean to be excesive here by asking a related question
but If you don't mind, can I ask a couple o question since I kind new to VB.net,
I come from the VB 6 sintax, So

1.-What is the concept behind
cmd.ExecuteNonQuery() and cmd.ExecuteScalar()
Where and Why should I use these commands.

2.-Is there any other method other than SQL commands for inserting , updateing records etc.. or is VB2008 based on SQL Comands, the thing is that I come from VB6 using Index, Seek, Addnew instrunctions using Recordsets.

Also could you take a look at another question I have open and see I you have any suggestions
http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_23833316.html



0
 
LVL 27

Accepted Solution

by:
VBRocks earned 400 total points
ID: 22769407
ExecuteNonQuery() is used when you perform an action query, like an Update or Delete Query.  It always returns the number of rows affected (as an integer)

So for example, if you delete a record with an Id of "0", then there will only be 1 row affected.  But, if you delete all rows with an Id < 10, then it will return 10, because 10 rows were deleted.

ExecuteScalar() returns only 1 value:  the value that is in row 1, column 1.  So, if you execute a query that only returns 1 value (like you did in your example), then use this method.

You can perform SELECT, INSERT, UPDATE, DELETE commands with a SqlCommand object.  You just need to change the syntax as is appropriate, and add parameters as necessary.

Example:
Dim SQL as String = "INSERT INTO testTable (item) VALUES (@Item)"
Dim cnString as String = "Connection String"

Dim cmd as New SQLCommand(SQL, cnString)
cmd.Parameters.AddWithValue("@Item", "My new value")

Dim iRowsAffected As Integer = cmd.ExecuteNonQuery  'Insert

if iRowsAffected > 0 Then
    MsgBox "You inserted " & iRowsAffected & " new row(s)"
Else
    MsgBox "Insert did not complete successfully..."
End If


0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 18

Assisted Solution

by:UnifiedIS
UnifiedIS earned 100 total points
ID: 22769534
#2 ExecuteReader is similar to using recordsets with VB6 but is read only
dim r as SqlDataReader =cmd.ExecuteReader
while r.read
console.writeline(r(0))
end while
0
 
LVL 10

Author Comment

by:ranhell
ID: 22769550
Last question
Could you give me a brief explanation of Why and Where should I use Fill.Datasets
compare to using Recordsets back in VB 6, or is there good comparison chart or tutorial that approaches this question.
I already assigned the points, is mostly a follow up question.

thank you very much for UR help
0
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 22769659
Datasets are good when you need to read and update data like if you fill a datagridview and you want to allow users to change and save the data.  
Using ExecuteNonQuery is for update/delete.
SqlDataReader is good for read only data pulls like populating a dropdown list.
Use ExecuteScalar when you only want a single value read.
Use datasets when you need to handle more advanced database-like tasks.

0
 
LVL 27

Expert Comment

by:VBRocks
ID: 22769946
Basically, you can use DataSets and/or DataTables whenever you are working with data.  It actually has more benefit with Windows forms than ASP.NET.

You can think of your DataTable as an in-memory representation of the data that is in the database.  You can insert new records into it, modify existing records, and delete existing records.  But, because the data is only in memory, it doesn't actually make any changes to the database.  

You can use a DataAdapter to work with the data in a database and the data in a dataset.  The DataAdapter is kind of like a "go-between" between the database and dataset.  You can use the DataAdapter to get the data from the database and fill the dataset with it (SELECT command), and you can use it to UPDATE your database with the changes made to the dataset, DELETE records and INSERT new records as well.

Here's a little example:

    Private Sub Form1_Load(ByVal sender As System.Object, _
                       ByVal e As System.EventArgs) Handles MyBase.Load

        SelectRecords()

    End Sub


    Private Sub SelectRecords()

        'Select the records from the database
        Dim SQL As String = "SELECT * FROM Employees"

        Dim cnString As String = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"

        Dim table As New DataTable()

        Using adapter As New SqlClient.SqlDataAdapter(SQL, cnString)

            adapter.Fill(table)

        End Using

        Me.DataGridView1.DataSource = table

    End Sub


    Private Sub Button2_Click(ByVal sender As System.Object, _
                          ByVal e As System.EventArgs) Handles Button2.Click

        ModifyRecords()

        SelectRecords()

    End Sub


    Private Sub ModifyRecords()

        'Modify the records in the database

        Dim cnString As String = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"
        Dim cn As New SqlClient.SqlConnection(cnString)

        Dim table As New DataTable()

        Dim adapter As New SqlClient.SqlDataAdapter()
        adapter.SelectCommand = New SqlClient.SqlCommand()
        With adapter.SelectCommand
            .CommandText = "SELECT * FROM Employees"
            .CommandType = CommandType.Text
            .Connection = cn
        End With

        adapter.UpdateCommand = New SqlClient.SqlCommand()
        With adapter.UpdateCommand
            .CommandText = "UPDATE Employees SET FirstName=@FirstName, LastName=@LastName " & _
                          "WHERE EmployeeId=@EmployeeId"
            .CommandType = CommandType.Text
            .Connection = cn

            With .Parameters
                .Add("@EmployeeId", SqlDbType.Int, 0, "EmployeeId")
                .Add("@FirstName", SqlDbType.VarChar, 0, "FirstName")
                .Add("@LastName", SqlDbType.VarChar, 0, "LastName")
            End With
        End With

        adapter.InsertCommand = New SqlClient.SqlCommand()
        With adapter.InsertCommand
            .CommandText = "INSERT INTO Employees (FirstName, LastName) " & _
                     "VALUES (@FirstName, @LastName)"
            .CommandType = CommandType.Text
            .Connection = cn

            With .Parameters
                .Add("@FirstName", SqlDbType.VarChar, 0, "FirstName")
                .Add("@LastName", SqlDbType.VarChar, 0, "LastName")
            End With
        End With

        adapter.DeleteCommand = New SqlClient.SqlCommand()
        With adapter.DeleteCommand
            .CommandText = "DELETE FROM Employees WHERE EmployeeId = @EmployeeId"
            .CommandType = CommandType.Text
            .Connection = cn

            With .Parameters
                .Add("@EmployeeId", SqlDbType.Int, 0, "EmployeeId")
            End With
        End With

        adapter.Fill(table)

        'Modify a row
        table.Rows(0).Item("FirstName") = "VBRocks"
        table.Rows(0).Item("LastName") = ""


        'Add a row
        Dim row As DataRow = table.NewRow()
        row.Item("FirstName") = "ranhell"
        row.Item("LastName") = ""
        table.Rows.Add(row)

        'Delete a row
        table.Rows(5).Delete()


        'Update the database with the changes:
        adapter.Update(table)

        'Release resources used by adapter
        adapter.Dispose()

    End Sub

0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Normally the drop down box control found in the .Net framework tools is able to select just one data and value at a time, which is displayed on the text area.   But what if you want to have multiple values to be selected in the drop down box? As …
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

762 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