Vb.net check DB for value then run IF statement

Question

0.00/5 (No votes)

See more:

I have a problem running a query which is "Select data from DB where data = textbox AND dbdata = True" The query is needed to check to see if the data from the textbox is equal to what the result of the query is. If it is true or equal to then run an if statement to show an error message and stop the rest of the script from running or simply exit or close.

If that makes sense....

I have tried using the USING statement with OleDBDataReader functions but no luck...

The if statement would look something like:

If assestbarcode.text = query then me.close messagebox.show("This item is already checked out")

I have deleted all of what I have tried and only left the code that writes data to a DB which should only run if the if statement is false.

What I have tried:

Private Sub CheckOut_Click_1(sender As Object, e As EventArgs) Handles CheckOut.Click

    Dim str As String
    str = "INSERT INTO TrackingInfo (EmployeeName, AssetName, BadgeNumber, AssetBarcode, CheckedOut, DateNTime) values (DisplayName, AssetName, ChkOutBadge, ChkOutBarcode, True, TimeStamp)"

    Dim cmd As OleDbCommand = New OleDbCommand(str)

    cmd.Connection = objConn

    Try

        objConn.Open()

        cmd.Parameters.Add(New OleDbParameter("EmployeeName", CType(Me.Controls("Displayname").Text, String)))
        cmd.Parameters.Add(New OleDbParameter("AssetName", CType(Me.Controls("DisplayAsset").Text, String)))
        cmd.Parameters.Add(New OleDbParameter("BadgeNumber", CType(Me.Controls("ChkOutBadge").Text, String)))
        cmd.Parameters.Add(New OleDbParameter("AssetBarcode", CType(Me.Controls("ChkOutBarcode").Text, String)))
        cmd.Parameters.Add(New OleDbParameter("DateNTime", CType(Me.Controls("TimeStamp").Text, String)))

        cmd.ExecuteNonQuery()
        cmd.Dispose()
        objConn.Close()

        MsgBox("Loaner has been checked out successfully")

    Catch ex As Exception

        MsgBox(ex.Message)

    End Try

Posted 13-Jun-17 10:15am

KD209

Updated 15-Jun-17 8:52am

Add a Solution

Comments

KD209 14-Jun-17 14:33pm

Here is the code that I have been trying to use to get the desired result:

Dim fetchstr As String = connStr
Dim fetchconn As OleDbConnection = New OleDbConnection(fetchstr)
Dim cmdfetch As OleDbCommand = New OleDbCommand()
Dim fetch As String = "Select AssetBarcode from Assets Where AssetBarcode = ChkOutBarcode AND CheckedOut = True"
'cmdfetch.CommandText = "Select AssetBarcode from Assets Where AssetBarcode = ChkOutBarcode AND CheckedOut = True"

fetchconn.Open()

cmdfetch = New OleDbCommand(fetch, fetchconn)
cmdfetch.Parameters.AddWithValue("AssetBarcode", ChkOutBarcode.Text)

cmdfetch.ExecuteNonQuery()
fetchconn.Close()

If cmdfetch.ExecuteNonQuery > 0 Then
MessageBox.Show("This item has already been checked out. Please alert a Helpdesk team member.")
Me.Close()
End If

Richard Deeming 14-Jun-17 17:51pm

Try using a question-mark as the parameter placeholder:

Select AssetBarcode from Assets Where AssetBarcode = ? AND CheckedOut = True

Richard Deeming 14-Jun-17 17:53pm

You're also trying to call ExecuteNonQuery twice, and the second time is after you've closed the connection. That's not going to work. You need to store the result of the first call in a variable, and then test that variable in your If block.

KD209 15-Jun-17 13:28pm

I have rewritten the code, but now I get an error stating that the "ExecuteReader has not been initialized"

Code:

Dim fetchstr As String = connStr
Dim fetchconn As OleDbConnection = New OleDbConnection(fetchstr)
Dim cmdfetch As OleDbCommand = New OleDbCommand()
Dim fetch As String = "Select AssetBarcode from Assets Where AssetBarcode = ? AND CheckedOut = True"

fetchconn.Open()

cmdfetch.Parameters.AddWithValue("AssetBarcode", OleDbType.VarChar).Value = ChkOutBarcode

Dim fetchreader As OleDbDataReader = cmdfetch.ExecuteReader()

While fetchreader.Read()

If ChkOutBarcode.Text = (fetchreader("AssetBarcode")) Then

MessageBox.Show("Item has already been checked out. Please inform the Helpdesk")

Me.Close()

End If

End While

fetchreader.Close()
fetchconn.Close()

Richard Deeming 15-Jun-17 13:34pm

You've not set the command's Connection property.

You should also wrap the connection, command and reader objects in Using blocks, and use a case-insensitive string comparison to test whether the strings are equal.

Using fetchconn As New OleDbConnection(connStr)
    Using cmdfetch As New OleDbCommand()
        cmdfetch.Connection = fetchconn
        cmdfetch.CommandText = "Select AssetBarcode from Assets Where AssetBarcode = ? AND CheckedOut = True"
        cmdfetch.Parameters.AddWithValue("AssetBarcode", OleDbType.VarChar).Value = ChkOutBarcode
        
        fetchconn.Open()
        Using fetchreader As OleDbDataReader = cmdfetch.ExecuteReader(CommandBehavior.CloseConnection)
            While fetchreader.Read()
                If String.Equals(ChkOutBarcode.Text, fetchreader("AssetBarcode"), StringComparison.OrdinalIgnoreCase) Then
                    MessageBox.Show("Item has already been checked out. Please inform the Helpdesk")
                    Me.Close()
                    Return
                End If
            End While
        End Using
    End Using
End Using

KD209 15-Jun-17 13:41pm

When utilizing the Using functions I still run into the error "No value given for one or more required parameters." I'm guessing that query result is still not being captured to run an if statement against.

Richard Deeming 15-Jun-17 13:43pm

Sorry, I missed the part where you were trying to pass the entire ChkOutBarcode control as the parameter value, rather than its text!

cmdfetch.Parameters.AddWithValue("AssetBarcode", OleDbType.VarChar).Value = ChkOutBarcode.Text

KD209 15-Jun-17 13:55pm

I am still getting the same exception error:

"System.Data.OleDb.OleDbException was unhandled
ErrorCode=-2147217904
HResult=-2147217904
Message=No value given for one or more required parameters.
Source=Microsoft Access Database Engine
StackTrace:
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at WindowsApplication1.DMNDTracker.CheckOut_Click_1(Object sender, EventArgs e) in C:\Users\KDavis\Documents\Visual Studio 2013\Projects\CheckOutSystem\CheckOutSystem\Form1.vb:line 115
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at WindowsApplication1.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
at System.Activator.CreateInstance(ActivationContext activationContext)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException: "

Richard Deeming 15-Jun-17 13:59pm

You're passing the wrong value to AddWithValue:

cmdfetch.Parameters.AddWithValue("AssetBarcode", ChkOutBarcode.Text)

You could also try using 1 instead of True in the query.

And verify that ChkOutBarcode.Text has a value.

KD209 15-Jun-17 14:06pm

I figured that would caused me problems. I have corrected that but the code is still giving the error of: "No value given for one or more required parameters" on the line of: "Using fetchreader As OleDbDataReader = cmdfetch.ExecuteReader(CommandBehavior.CloseConnection)"

Also,

The database field/column is a Yes/No field, will the 1 or 0 integers work the same?

Richard Deeming 15-Jun-17 14:20pm

I can never remember how Access treats boolean columns! A quick test shows that, no, 1 won't work. You need to use True instead.

The "no value given" error seems to come up when you misspell a column name. Are you absolutely certain that your Assets table contains columns called AssetBarcode and CheckedOut?

KD209 15-Jun-17 14:45pm

Richard if you were here right now you would strangle me. I needed to run the query against the "TrackingInfo" table, HAHAHA! It is all working now.

One last question that I have is how to I allow the form to reinitialize instead of doing the me.close()?

Richard Deeming 15-Jun-17 14:48pm

It's always the simple things! :D

You just need to write a method to reset the controls to their initial state, and call that method instead of Close. How easy that is to do will depend on how complicated your form is.

KD209 15-Jun-17 14:50pm

Got it! Thanks for all the help I really appreciate it.

Can you paste the code into the solution field so I can make it as resolved/answered.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2017-06-15T08:52:00

Solution from the comments:

VB.NET

Using fetchconn As New OleDbConnection(connStr)
    Using cmdfetch As New OleDbCommand()
        cmdfetch.Connection = fetchconn
        cmdfetch.CommandText = "Select AssetBarcode from TrackingInfo Where AssetBarcode = ? AND CheckedOut = True"
        cmdfetch.Parameters.AddWithValue("AssetBarcode", ChkOutBarcode.Text)
        
        fetchconn.Open()
        Using fetchreader As OleDbDataReader = cmdfetch.ExecuteReader(CommandBehavior.CloseConnection)
            While fetchreader.Read()
                If String.Equals(ChkOutBarcode.Text, fetchreader("AssetBarcode"), StringComparison.OrdinalIgnoreCase) Then
                    MessageBox.Show("Item has already been checked out. Please inform the Helpdesk")
                    Me.Close() ' Or your custom method to reset the form...
                    Return
                End If
            End While
        End Using
    End Using
End Using

As suggested in your follow-up question[^], the check in the code is redundant. You can simplify this to: