Solved

How do I get the value from Sql command

Posted on 2008-10-21
8
784 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

821 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