none
Using SQL Based commands RRS feed

  • Question

  • Hello,

    I may have some issues with accessing data by way of SQL string command.

    Here, I have the code snippet below:

    Private Sub btnDisplayRecord_Click(sender As Object, e As EventArgs) Handles btnDisplayRecord.Click
    
    
            Dim command As New SqlCommand()
            command.CommandText = "SELECT * FROM tblAddresses, ListOfNames WHERE tblAddresses.NameID=" & Me.NameIDTextBox.Text
            command.CommandType = CommandType.Text
    
            txtSpace.Text = command.CommandText
    
    
        End Sub

    ..and the screenshot below as well.

    So far, no runtime errors, but the output reads the following:

    SELECT * FROM tblAddresses, ListOfNames WHERE tblAddresses.NameID=1 when I click on the command button.

    Instead I want to output the following metadata instead, but I do not know how.

    Can anyone solve this issue?

    Regards,

    JohnDBCTX


    jp

    Tuesday, May 1, 2018 5:28 PM

Answers

  • The proper method is to use a INNER JOIN and use a parameter anytime you are working with a WHERE condition.

    Example, first write out your SQL SELECT in SQL-Server Management Studio or create a .SQL file in Visual Studio like the following

    DECLARE @ContactIdentifier AS INT = 1
    
    SELECT	C.ContactIdentifier, 
    		C.FirstName, 
    		C.LastName, 
    		ContactAddress.Street, 
    		ContactAddress.City
    FROM    Contact AS C INNER JOIN
            ContactAddress ON C.ContactIdentifier = ContactAddress.ContactIdentifier
    WHERE C.ContactIdentifier = @ContactIdentifier

    Output that validates the above query.

    Or

    Validate it works, write code e.g. a connection, command with a parameter then run the code.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Tuesday, May 1, 2018 7:57 PM
    Moderator

All replies

  • What do you mean by 

    Instead I want to output the following metadata instead, but I do not know how.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, May 1, 2018 5:37 PM
    Moderator
  • Here are the two tables below:

    Here are the two tables
    "NameID","LastName","FirstName"
    1,"Doe","John"
    2,"Doe","Jane"
    "AddressID","Address","NameID"
    1,"Anywhere Street",1
    2,"Nowhere Street",2

    I want to output them as stated below:

    Record #1 

    John Doe, Anywhere Street

    Record #2

    Jane Doe, Nowhere Street

    That would be when user clicks its command button following record selection on display.

    That would be even more descriptive.

    Regards,

    JohnDBCTX


    jp

    Tuesday, May 1, 2018 6:47 PM
  • Where could I upload my sample project to the developer network, so that they can figure out what I am demonstrating?

    Regards,

    JohnDBCTX


    jp

    Tuesday, May 1, 2018 6:56 PM
  • Where could I upload my sample project to the developer network, so that they can figure out what I am demonstrating?

    Regards,

    JohnDBCTX


    jp

    Demonstrate what? Are you sure you are doing things right?

    https://www.dotnetheaven.com/article/ado.net-command-object-in-vb.net

    Tuesday, May 1, 2018 7:22 PM
  • The proper method is to use a INNER JOIN and use a parameter anytime you are working with a WHERE condition.

    Example, first write out your SQL SELECT in SQL-Server Management Studio or create a .SQL file in Visual Studio like the following

    DECLARE @ContactIdentifier AS INT = 1
    
    SELECT	C.ContactIdentifier, 
    		C.FirstName, 
    		C.LastName, 
    		ContactAddress.Street, 
    		ContactAddress.City
    FROM    Contact AS C INNER JOIN
            ContactAddress ON C.ContactIdentifier = ContactAddress.ContactIdentifier
    WHERE C.ContactIdentifier = @ContactIdentifier

    Output that validates the above query.

    Or

    Validate it works, write code e.g. a connection, command with a parameter then run the code.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Tuesday, May 1, 2018 7:57 PM
    Moderator
  • Thanks, I'll try that.

    Regards,

    JohnDBCTX


    jp

    Tuesday, May 1, 2018 9:06 PM
  • I have tried it, and here is the updated code snippet.

      Private Sub btnDisplayRecord_Click(sender As Object, e As EventArgs) Handles btnDisplayRecord.Click
            Dim objConnX As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\johnd\Documents\NamesDBBackupOne.mdb")
            Dim objCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand
            Dim strSQL As String
            FileOpen(1, "OutputOne.txt", OpenMode.Output, OpenAccess.Default, OpenShare.Default)
    
            objConnX.Open()
            strSQL = "SELECT Address FROM tblAddresses AS C INNER JOIN ListOfNames ON C.NameID WHERE tblAddresses.NameID=" & NameIDTextBox.Text
    
            With objCmd
                .Connection = objConnX
                .CommandText = strSQL
            End With
    
    
        End Sub

    The next step I would need to perform is to create my own data set from its customized SQL command and display its data.

    Would that be the next step?

    Regards,

    JohnDBCTX


    jp

    Wednesday, May 2, 2018 3:32 AM
  • I have found an alternative:  Use the data reader type.

      Private Sub btnDisplayRecord_Click(sender As Object, e As EventArgs) Handles btnDisplayRecord.Click
            Dim objConnX As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\johnd\Documents\NamesDBBackupOne.mdb")
            txtSpace.Text = ""
            Using objConnX
                Dim command As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT * FROM ListOfNames", objConnX)
                objConnX.Open()
    
    
    
    
                Dim reader As OleDb.OleDbDataReader = command.ExecuteReader()
                ' Data is accessible through the DataReader object here.
                If reader.HasRows Then
                    Do While reader.Read
                        txtSpace.Text &= reader.GetString(2) & " " & reader.GetString(1) & vbNewLine
                    Loop
                Else
                    MsgBox("No Rows Found", MsgBoxStyle.Information, "Data Empty")
                End If
                reader.Close()
    
            End Using

    Everything worked.

    Regards,

    JohnDBCTX


    jp


    I just had to update the code snippet to using the same parameters but this time, by using the data reader type.
    • Edited by JohnDBCTX Wednesday, May 2, 2018 5:07 AM More information
    Wednesday, May 2, 2018 5:06 AM