none
Queries and Code......Again RRS feed

  • Question

  • I am diligently attempting to understand how to use direct connections to a database and queries run in the code.  I am having a little trouble  (perhaps a lot) really understanding what is going on.

    So here is where I am at,  I have placed the following out into a class in a namespace:

            Public Property DataTable As DataTable
            Public Builder As New OleDbConnectionStringBuilder With
                {
                .Provider = "Microsoft.ACE.OLEDB.12.0",
                .DataSource = IO.Path.Combine(Application.StartupPath, "F:\SiTechMasterBase1.0\MasterBase3.0.accdb")
                }
            Public Sub LoadbyChangeRequestIDOnly()
                Using cn As New OleDbConnection With
                    {
                    .ConnectionString = Builder.ConnectionString
                    }
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText =
                        <SQL>
                        SELECT lnkChangeRequest.intChangeID
                        FROM  lnkChangeRequest 
                        WHERE lnkChangeRequest.intChangeID = ?
                    </SQL>.Value
                        cn.Open()
                        DataTable.Load(cmd.ExecuteReader)
                    End Using
                End Using
            End Sub

    I then am ready to call this at a specific point where I want the query run in an event, I think.

                 ItemQuery.LoadbyChangeRequestIDOnly()

    OK, so what do I have when I make this call and run the query and what can I do with it?

    What I really want is to make a variable (glbintCRNum) = the results of this query.  So how do I do that?  I already know that it isn't just simply:

    ItemQuery.LoadbyChangeRequestIDOnly(glbintCRNum)
    I have been doing some research, but it is obviously still way over my head.  Can anyone provide an assist?


    gwboolean


    • Edited by gwboolean Monday, September 18, 2017 5:37 PM
    Monday, September 18, 2017 5:33 PM

Answers

  • @ signifies a parameter

    In regards to displaying, you must use a SELECT as an INSERT does not return data. That is why the class I presented has many methods.


    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

    • Marked as answer by gwboolean Tuesday, September 19, 2017 10:12 PM
    Tuesday, September 19, 2017 9:55 PM
    Moderator

All replies

  • Perhaps this will clear it up

    Imports System.Data.OleDb
    Public Class Demo
        Public Sub GetData()
            Dim ops As New DataOperations
            ops.LoadbyChangeRequestIDOnly(100)
            Dim dt As DataTable = ops.DataTable
        End Sub
    End Class
    Public Class DataOperations
        Public Property DataTable As DataTable
        Public Builder As New OleDbConnectionStringBuilder With
            {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = IO.Path.Combine(Application.StartupPath, "F:\SiTechMasterBase1.0\MasterBase3.0.accdb")
            }
        Public Sub LoadbyChangeRequestIDOnly(ByVal pId As Integer)
            DataTable = New DataTable
    
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                    <SQL>
                        SELECT intChangeID
                        FROM  lnkChangeRequest 
                        WHERE intChangeID = @intChangeID
                    </SQL>.Value
    
                    cmd.Parameters.AddWithValue("@intChangeID", pId)
                    cn.Open()
    
                    DataTable.Load(cmd.ExecuteReader)
    
                End Using
            End Using
        End Sub
    End Class
    


    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

    • Marked as answer by gwboolean Monday, September 18, 2017 7:45 PM
    • Unmarked as answer by gwboolean Tuesday, September 19, 2017 5:40 PM
    Monday, September 18, 2017 5:57 PM
    Moderator
  • Thanks Karen, That really helps.  Ever since your last input to me on the methodology of working directly with the database and using queries in the event/routine code I have been interested in this.  But yesterday, after messing up my dataset and then losing every single query I had in there my motivation level to learn this has exponentially increased.  

    So I will be going back to your last offering and study through that (by the way, I actually understand what you did here) so that I can move data in and out of the database (directly).  I also got me some reading material on this which I have yet to get any understanding of.

    Anything else you can add to this about creating rows, inputting data, and extracting data, directly (that I can understand) would be cool.

    Again, thanks for this.  It helps a lot and moves me quite a ways down the road away from the dark side.


    gwboolean

    Monday, September 18, 2017 7:45 PM
  • Food for thought

    Imports System.Data.OleDb
    Public Class DataOperations
        Public Property DataTable As DataTable
        Public Builder As New OleDbConnectionStringBuilder With
            {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = IO.Path.Combine(Application.StartupPath, "F:\SiTechMasterBase1.0\MasterBase3.0.accdb")
            }
        Public Sub Load(ByVal pId As Integer)
            DataTable = New DataTable
    
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                    <SQL>
                        SELECT intChangeID
                        FROM  lnkChangeRequest 
                        WHERE intChangeID = @intChangeID
                    </SQL>.Value
    
                    cmd.Parameters.AddWithValue("@intChangeID", pId)
                    cn.Open()
    
                    DataTable.Load(cmd.ExecuteReader)
    
                End Using
            End Using
        End Sub
        Public Sub Load()
            DataTable = New DataTable
    
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                    <SQL>
                        SELECT intChangeID
                        FROM  lnkChangeRequest 
                    </SQL>.Value
    
                    cn.Open()
    
                    DataTable.Load(cmd.ExecuteReader)
    
                End Using
            End Using
        End Sub
        ''' <summary>
        ''' 
        ''' </summary>
        ''' <param name="pName"></param>
        ''' <param name="pContact"></param>
        ''' <param name="pIdentfier">Declare an integer in the caller, pass it in here, when this method finishes it will have the new primary key</param>
        ''' <returns></returns>
        Public Function Add(ByVal pName As String, ByVal pContact As String, ByRef pIdentfier As Integer) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
    
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "INSERT INTO Customer (CompanyName,ContactName) Values(@CompanyName,@ContactName)"
    
                        cmd.Parameters.AddWithValue("@CompanyName", pName)
                        cmd.Parameters.AddWithValue("@ContactName", pContact)
    
                        cn.Open()
    
                        Affected = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            cmd.CommandText = "Select @@Identity"
                            pIdentfier = CInt(cmd.ExecuteScalar)
                        End If
                    End Using
                End Using
            Catch ex As Exception
                Success = False
            End Try
    
            Return Success
    
        End Function
        Public Function Update(ByVal pId As Integer, ByVal pName As String, ByVal pContact As String) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "UPDATE Customer SET CompanyName = @CompanyName, ContactName = @ContactName WHERE Identifier = @Identifier"
    
                        cmd.Parameters.AddWithValue("@CompanyName", pName)
                        cmd.Parameters.AddWithValue("@ContactName", pContact)
                        cmd.Parameters.AddWithValue("@Identifier", pContact)
    
                        cn.Open()
    
                        Affected = cmd.ExecuteNonQuery()
                        Success = (Affected = 1)
                    End Using
                End Using
            Catch ex As Exception
                Success = False
            End Try
    
            Return Success
    
        End Function
        Public Function Delete(ByVal pId As Integer) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "DELETE FROM Customers WHERE Identifier = @Identifier"
    
                        cmd.Parameters.AddWithValue("@Identifier", pId)
    
                        cn.Open()
    
                        Affected = cmd.ExecuteNonQuery()
                        Success = (Affected = 1)
                    End Using
                End Using
            Catch ex As Exception
                Success = False
            End Try
    
            Return Success
    
        End Function
    End Class
    
    


    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

    Monday, September 18, 2017 9:18 PM
    Moderator
  • That's a whole Dinner.  Thanks Karen.

    gwboolean

    Monday, September 18, 2017 11:01 PM
  • Karen,

    Working diligently on this, but I have scratched most of the hair off my head.  I am still working with the first one:

    Public Class DataOperations
        Public Property DataTable As DataTable
        Public Builder As New OleDbConnectionStringBuilder With
                {
                .Provider = "Microsoft.ACE.OLEDB.12.0",
                .DataSource = IO.Path.Combine(Application.StartupPath, "F:\SiTechMasterBase1.0\Database\MasterBase3.0.accdb")
                }
        Public Sub LoadByChangeRequestIDOnly(ByVal pID As Integer)
            DataTable = New DataTable
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT intChangeID
                            FROM lnkChangeRequest
                            WHERE intChangeID = @intChangeID
                        </SQL>.Value
                    cmd.Parameters.AddWithValue("@intChangeID", pID)
                    cn.Open()
                    DataTable.Load(cmd.ExecuteReader)
                End Using
            End Using
        End Sub
    End Class

    So I made a form and put this class in it.  I believe I am OK, so far. 

    So I am not entirely sure what is going on, but it is my thought that this should add a record to the table with a new intChangeID (change record number) to the table.  First question, is that correct?

    Believing this to be the case I did the following in the form class:

    Public Class qryCRNumOnly
        Private ChangeRequestAdd As New DataOperations
        Dim _IntCRNum As Integer = 10001
        Private Sub qryCRNumOnly_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            ChangeRequestAdd.LoadByChangeRequestIDOnly(_IntCRNum)
        End Sub
    End Class

    I ran it no errors.

    So on the assumption that I have any idea what is going on I would have expected to have a new record in the table, lnkChangeRequest, with the value 10001.  I had no new record.  So what am I really doing here and where have I gone wrong???



    • Edited by gwboolean Tuesday, September 19, 2017 5:39 PM
    Tuesday, September 19, 2017 5:35 PM
  • In my last reply the following adds a record

    ''' <summary>
        ''' 
        ''' </summary>
        ''' <param name="pName"></param>
        ''' <param name="pContact"></param>
        ''' <param name="pIdentfier">Declare an integer in the caller, pass it in here, when this method finishes it will have the new primary key</param>
        ''' <returns></returns>
        Public Function Add(ByVal pName As String, ByVal pContact As String, ByRef pIdentfier As Integer) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
    
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "INSERT INTO Customer (CompanyName,ContactName) Values(@CompanyName,@ContactName)"
    
                        cmd.Parameters.AddWithValue("@CompanyName", pName)
                        cmd.Parameters.AddWithValue("@ContactName", pContact)
    
                        cn.Open()
    
                        Affected = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            cmd.CommandText = "Select @@Identity"
                            pIdentfier = CInt(cmd.ExecuteScalar)
                        End If
                    End Using
                End Using
            Catch ex As Exception
                Success = False
            End Try
    
            Return Success
    
        End Function
    

    The function in your last reply selects one or more records based on pId parameter.

    But in your case with no auto-incrementing field you would use

    Public Function Add1(ByVal pName As String, ByVal pContact As String, ByRef pIdentfier As Integer) As Boolean
        Dim Success As Boolean = True
        Dim Affected As Integer = 0
    
    
        Try
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = "INSERT INTO Customer (Id,CompanyName,ContactName) Values(@Id,@CompanyName,@ContactName)"
    
                    cmd.Parameters.AddWithValue("@Id", pIdentfier)
                    cmd.Parameters.AddWithValue("@CompanyName", pName)
                    cmd.Parameters.AddWithValue("@ContactName", pContact)
    
                    cn.Open()
    
                    Affected = cmd.ExecuteNonQuery()
    
                End Using
            End Using
        Catch ex As Exception
            Success = False
        End Try
    
        Return Success
    
    End Function


    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, September 19, 2017 7:40 PM
    Moderator
  • Man, this is really hard.  But I am progressing (well maybe).  By the way, I quit using the autonumber, I just don't like it that much.

    First I need to understand this one:

    cmd.Parameters.AddWithValue("@ContactName", pContact)

    So this would add pContact to ContactName in the last record in the table..... right?  And I am still not clear on the @ symbol and what that does.

                  cmd.CommandText = "INSERT INTO Customer (CompanyName,ContactName) Values(@CompanyName,@ContactName)"
    
                        cmd.Parameters.AddWithValue("@CompanyName", pName)
                        cmd.Parameters.AddWithValue("@ContactName", pContact)

    Now based on this I would think that INSERT is what gives me a new line and the AddWithValue is what populates the named fields with the value.... right?

    On the assumption this is all correct (assumptions never work out for me), then how would I display this in, say a datagridview?  Or would I just use a binding source after I had completed the addition of the record?


    gwboolean

    Tuesday, September 19, 2017 9:43 PM
  • @ signifies a parameter

    In regards to displaying, you must use a SELECT as an INSERT does not return data. That is why the class I presented has many methods.


    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

    • Marked as answer by gwboolean Tuesday, September 19, 2017 10:12 PM
    Tuesday, September 19, 2017 9:55 PM
    Moderator
  • OK, I understand the @ and partially understand the rest.  As soon as I get done beating my head on against the wall I am going to get back on this and see what else I can figure out.  I am really interested, but damn this is hard.

    Can you point me to any reading material that might help me with all of this.  If I can ever get the hang of all of this I can kick my dataset habit.

    Thanks,

    GW


    gwboolean

    Tuesday, September 19, 2017 10:12 PM
  • I have lots of code samples for these operations yet they are all in C# and SQL-Server.

    Perhaps I will put together something for VB.NET and MS-Access but it will be using auto-incrementing primary keys as 99.99 percent of developers do this.


    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, September 19, 2017 10:25 PM
    Moderator
  • I would love that.  As for the auto incrementing fields I will still use them, but I will no longer use them for anything more than they are designed for.  That is where one runs into problems with those.

    gwboolean

    Tuesday, September 19, 2017 11:00 PM