none
Convert an Access stored procedure to a vb.net parameter command RRS feed

  • Question

  • Hi all,
    I have a stored procedure in an Access 2010 database.I'd like to bypass that and just perform the query directly from the vb.net front end.

    This is the Access stored procedure:
    PARAMETERS [JOBIDVaL] Text ( 255 ), [DESCVAL] Text ( 255 );
    INSERT INTO TBLJOB ( JOBID, JOBDESCRIPTION )
    SELECT DISTINCT [JOBIDVaL] AS Expr1, [DESCVAL] AS Expr2
    FROM TBLTRANSACT
    WHERE (((Now())<=#31/12/2018#)) OR (((TBLTRANSACT.CATALOG)="XYZ"));

    I have this code at the moment for vb.net

    Dim com As New OleDbCommand("INSERT INTO TBLJOB VALUES (@jobid, @jobdisc,@tdate, @jobuser);", con)
            com.CommandType = CommandType.Text
            com.Parameters.Add("@jobid", OleDbType.VarChar).Value = TxtJob.Text
            com.Parameters.Add("@njobdisc", OleDbType.LongVarWChar).Value = TxtJobDisc.Text
            com.Parameters.Add("@tdate", OleDbType.Date).Value = Now
            com.Parameters.Add("@jobuser", OleDbType.VarChar).Value = "User1"
    I'm not sure how to add the SELECT DISTINCT or the WHERE clause
    I need the date and catalog id to be parameters.
    Anyone know how to add these?

    Friday, February 23, 2018 6:00 PM

Answers

  • Maybe the smart thing would be to query the TBLTRANSACT to see if the XYZ criteria returns any rows, if not check the date criteria. If either one of them returns true then do a simple insert.

    Yes, I would recommend using a separate SELECT and INSERT statement. The INSERT statement I used appears to work in Access but not through OLEDB and Visual Basic.NET. Just an FYI, here is what I tried:

            Dim accessConnection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                                        "Data Source=C:\Users\...\Documents\Database\Access\db1.accdb")
            
            accessConnection.Open()
            Dim sqlString As String = "INSERT INTO TBLJOB (JOBID, JOBDESCRIPTION) SELECT TOP 1 @JOBIDVaLParam AS Expr1, @DESCVALParam AS Expr2 FROM TBLTRANSACT WHERE Now()<=@DateParam OR TBLTRANSACT.CATALOG=@CatalogParam"
            Dim accessCommand As New OleDbCommand(sqlString, accessConnection)
            accessCommand.Parameters.AddWithValue("@JOBIDVaLParam", "10")
            accessCommand.Parameters.AddWithValue("@DESCVALParam", "Some description")
            accessCommand.Parameters.AddWithValue("@DateParam", #10/25/2017#)
            accessCommand.Parameters.AddWithValue("@CatalogParam", "AAA")
    
            accessCommand.ExecuteNonQuery()
    
            accessConnection.Close()
    It always inserts a row regardless of whether the criteria is satisfied.


    Paul ~~~~ Microsoft MVP (Visual Basic)



    Monday, February 26, 2018 5:28 PM

All replies

  • Creating the command SQL would be easy enough, but I don't understand why the original Access query (what you refer to as a "stored procedure") is so complicated.  As near as I can tell, it will always insert exactly one record into TBLJOB, with the values given for the two parameters JOBIDVal and DESCVAL, so long as either (a) the current date is before or equal to midnight of December 31, 2018, or (b) there's a record in TBLTRANSACT for which the CATALOG field = ""XYZ".  That is so bizarre that I want to make sure the query is doing what it's supposed to before suggesting the appropriate VB.Net equivalent.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Sunday, February 25, 2018 9:31 PM
  • I kind of feel the same way as Dirk. Your QueryDef doesn't make much sense as it is written. You are just inserting a single row from the JOBIDVAL and DESCVAL parameter values based upon the results of the WHERE clause. The DISTINCT clause would be irrelevant since you are not pulling any rows from TBLTRANSACT.

    Is it possible that you want to append from the TBLTRANSACT to the TBLJOB based upon the values of four parameters (in your WHERE clause)?


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, February 26, 2018 2:24 AM
  • Hi,

    Not sure how I ended up with that query. It originally came from this thread I think:
    https://social.msdn.microsoft.com/Forums/en-US/4251d29a-0093-4d1f-9418-362632f12e83/sql-can-i-execute-an-insert-into-query-based-on-the-result-of-another-query?forum=accessdev

    I seem to remember the query trying to insert a lot of rows but that may have been a different cause.

    It could be that the serial number appears more than once in the TBLTRANSACT table.
    If it can be simplified I'd be grateful.

    The query should just insert one record if either the date or catalog criteria are true

    Monday, February 26, 2018 8:17 AM
  • The query as written will always insert at most one record into TBLJOB, but I'm still not sure that it's doing what you want -- that is, I gather from what you say that the Access query is not failing, but since that record would contain no data from TBLTRANSACT, and the date criterion has nothing to do with TBLTRANSACT at all, it seems very strange.  Could you please describe, in words or by example, exactly what you want to get from TBLTRANSACT and what the result in TBLJOB should be?

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, February 26, 2018 2:08 PM
  • There are two tables:
    TBLJOB Has a fields JOBID (Primary) & JOBDESCRIPTION
    TBLTRANSACT Has fields TRANSACTID (Primary, Auto) & CATALOG

    I want to insert one record into TBLJOB with values for TBLJOB.JOBID & TBLJOB.JOBDESCRIPTION
    Where either the date is before the 1st March or there is the value "XYZ" in TBLTRANSACT.CATALOG

    So there are four parameters required 
    TBLJOB.JOBID                  (To be inserted)
    TBLJOB.JOBDESCRIPTION (To be inserted)

    TBLTRANSACT.CATALOG (for the where clause)
    The date criteria             (for the where clause)

    The Stored procedure seems to be doing what it should.

    I made a mistake in the original post saying I just needed two parameters

    Monday, February 26, 2018 2:52 PM
  • Thanks, that's much more informative. I only have one question left. You stated your date criterion as "Where ... the date is before the 1st March."  What date?  Do you really mean the date on which the query is being run, or a date entered by a user?  In either of those cases you could know the date before running the query and just decide whether to insert a record or not, so it seems odd to have it as a query criterion.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, February 26, 2018 3:01 PM
  • Hi Dirk,

    Sorry, the date (#31/02/2018#) should be passed as a parameter from the vb.net app (As it will change from time to time)  and would be compared against the current date when the query is run. 

    Monday, February 26, 2018 3:18 PM
  • Maybe the smart thing would be to query the TBLTRANSACT to see if the XYZ criteria returns any rows, if not check the date criteria. If either one of them returns true then do a simple insert.
    Monday, February 26, 2018 5:04 PM
  • Maybe the smart thing would be to query the TBLTRANSACT to see if the XYZ criteria returns any rows, if not check the date criteria. If either one of them returns true then do a simple insert.

    Yes, I would recommend using a separate SELECT and INSERT statement. The INSERT statement I used appears to work in Access but not through OLEDB and Visual Basic.NET. Just an FYI, here is what I tried:

            Dim accessConnection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                                        "Data Source=C:\Users\...\Documents\Database\Access\db1.accdb")
            
            accessConnection.Open()
            Dim sqlString As String = "INSERT INTO TBLJOB (JOBID, JOBDESCRIPTION) SELECT TOP 1 @JOBIDVaLParam AS Expr1, @DESCVALParam AS Expr2 FROM TBLTRANSACT WHERE Now()<=@DateParam OR TBLTRANSACT.CATALOG=@CatalogParam"
            Dim accessCommand As New OleDbCommand(sqlString, accessConnection)
            accessCommand.Parameters.AddWithValue("@JOBIDVaLParam", "10")
            accessCommand.Parameters.AddWithValue("@DESCVALParam", "Some description")
            accessCommand.Parameters.AddWithValue("@DateParam", #10/25/2017#)
            accessCommand.Parameters.AddWithValue("@CatalogParam", "AAA")
    
            accessCommand.ExecuteNonQuery()
    
            accessConnection.Close()
    It always inserts a row regardless of whether the criteria is satisfied.


    Paul ~~~~ Microsoft MVP (Visual Basic)



    Monday, February 26, 2018 5:28 PM
  • Maybe the smart thing would be to query the TBLTRANSACT to see if the XYZ criteria returns any rows, if not check the date criteria. If either one of them returns true then do a simple insert.

    This code might be a model for that:

    ' These are your parameters.
    Dim dateCutoff As Date
    Dim catalog As String
    Dim jobID As String
    Dim jobDescription As String
    
    ' I assume the following values will be passed or set externally,
    ' rather than set to literal values as they are here.
    catalog = "XYZ"
    dateCutoff = #12/31/2018#
    jobID = "MyJobID"
    jobDescription = "MyJobDescription"
    
    ' This flag variable will be set if a record should be created.
    Dim createJobRecord As Boolean = False
    
    ' Create and open a connection object.
    Using con As New OleDb.OleDbConnection("<your connection string here")
    
        con.Open()
    
        If Date.Today <= dateCutoff Then
            createJobRecord = True
        Else
            Using cmd As New OleDb.OleDbCommand("SELECT TOP 1 TBLTRANSACT.[CATALOG] FROM TBLTRANSACT WHERE TBLTRANSACT.[CATALOG]=@catalog", con)
                cmd.Parameters.AddWithValue("@catalog", catalog)
                Dim t As Object
                t = cmd.ExecuteScalar()
                If Not t Is Nothing Then
                    createJobRecord = True
                End If
            End Using
        End If
    
        If createJobRecord Then
            Using cmd As New OleDb.OleDbCommand("INSERT INTO TBLJOB (JOBID, JOBDESCRIPTION) VALUES (@jobID, @jobDescription)", con)
                cmd.Parameters.AddWithValue("@jobID", jobID)
                cmd.Parameters.AddWithValue("@jobDescription", jobDescription)
                cmd.ExecuteNonQuery()
            End Using
        End If
    
        con.Close()
    
    End Using
    


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, February 26, 2018 11:07 PM
  • Hi Paul,
    You say you solution always inserts row regardless is not what I found.
    If the value exists in TBLTRANSACT.CATALOG then the row is inserted otherwise it doesn't.
    So that bit seems to be working correctly.
    The part that doesn't work is the date criteria. If the .catalog value isn't there then no matter what the date value is there is no row inserted. It seems to be just ignoring the date part of the criteria.

    I don't think I have changed anything, though I did create another database and app just for testing.

    Anyway it's half way there!

    Tuesday, February 27, 2018 4:04 PM
  • Ok, so I changed this line to specify that it is a date type and it seems to be functioning now.

    accessCommand.Parameters.Add("@DateParam", OleDbType.Date).Value = strDate
    I'll check in more detail later

    Tuesday, February 27, 2018 4:18 PM
  • Hi AndyNakamura,

    It seems your issue has been resolved, I would suggest you mark the helpful reply as answer to close this thread.

    If you have any further issue, please feel free to post a new thread.

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 28, 2018 7:08 AM