Saving data using ado vb.net after checking 2 fields access 2003 db

Question

0.00/5 (No votes)

See more:

Gents,

I want to save the products table after checking two conditions.

In products table I have fields STOCKCODE (datatype TEXT) (Allows duplicate entries) and BranchName = Data type Text.

Lets say I have already saved a StockCode Ms Office software in BRANCHNAME NEW BRANCH. Now, I want to save another StockCode with same stock code i.e Ms Office software but in another branch called OLD BRANCH in the same Products table.

I am using the following code but its giving error

Code under the MainModule.vb

VB

Public Function IsStockExists(ByVal stockname As String, ByVal BranchName As String) As Boolean


       stockname = Replace(stockname, " ", "")
       Dim Dbf As New ADODB.Recordset
       Dbf.Open("Select * from stockdbf where tempstockcode='" + stockname + "' and BranchName=" & BranchName, Conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)



--------->> Error    (Syntax error (missing operator) in query expression 'tempstockcode='La457' and Branchname='.)

       If Dbf.RecordCount > 0 Then
           Dbf.Close()
           Return True
       Else
           Dbf.Close()
           Return False
       End If
   End Function

Code under ProductsForm on AddButton

VB

 Private Sub UserButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAdd.Click
If IsStockExists(txtstockcode.Text, cmbBranches.Text) = True Then
            MsgBox("The Stock Item Already exists, Please Try Again")
            Exit Sub
        End If

        If MsgBox(" Do u want to save ?                    ", MsgBoxStyle.YesNo + MsgBoxStyle.Information) = MsgBoxResult.Yes Then
            SaveStock()
            txtstockcode.Focus()
        End If
    End Sub

Can anyone help me out on the above issue. Thanks in advance..

Posted 7-Feb-14 6:08am

UCP_2005

Add a Solution

Comments

Richard C Bishop 7-Feb-14 12:15pm

I am guessing it exists within your query where you are using "+" instead of "&" as you should be in VB.Net.

G-code101 7-Feb-14 12:36pm

Richard it is actually possible to use the + and & operand in vb.net.

Richard C Bishop 7-Feb-14 12:38pm

Very well, the OP should try putting tick marks around the second half of their WHERE clause don't you think?

G-code101 7-Feb-14 12:51pm

That's what I said in a lot of words LOL

G-code101 7-Feb-14 12:34pm

Replace this ...

Dbf.Open("Select * from stockdbf where tempstockcode='" + stockname + "' and BranchName=" & BranchName, Conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)

With this...

Dbf.Open("Select * from stockdbf where tempstockcode='" + stockname + "' and BranchName='" & BranchName & "'", Conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)

NOTE : MAKE SURE THAT BRANCHNAME IS NOT NOTHING...

Secondly go to your database and open it with access and in the products table set your stockcode column to allow duplicate values... NOT RECOMMENDED THOUGH, unless its not used as a selection argument...

UCP_2005 7-Feb-14 12:55pm

Thanks for the help G-code101.

Stockcode in database is already set to Yes (Duplicates OK).

Now it is accepting to save data but without the BranchName check. I mean, it is only detecting the stockcode if same data already exists it gives message MsgBox("The Stock Item Already exists, Please Try Again") otherwise it saves data with all data provided by the user.

Please help me . it should save data even if the same stockcode is already available in the ProductsTable but with different BRANCHNAME parameter.

Thanks

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)

G-code101 · Accepted Answer · 2014-02-07T07:17:00

Currently you are checking if a row exists and not the actual data...

Replace your function with mine and edit where necessary... This should ease your troubles.

VB

Public Function IsStockExists(ByVal stockname As String, ByVal BranchName As String) As Boolean

    Dim stockExists As Boolean
    Dim DatabaseLocation As String = "" ' ADD YOUR DATABASE PATH HERE!!!
    ' Assuming if you have a password on your database the connection string will look like this...
    ' "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DatabaseLocation & ";Jet Oledb:Database Password=yourpassword"
    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DatabaseLocation
    Dim accessConnection As New OleDbConnection(ConnectionString)
    Dim query As String = "Select * from [DATABASE TABLE] where [tempstockcode]='" + stockname + "' and [BranchName]='" + BranchName + "'"
    Dim objcmd As New OleDbCommand(query, accessConnection)

    Try
        If accessConnection.State = ConnectionState.Closed Then
            accessConnection.Open()
        End If
        Dim objReader As OleDbDataReader = objcmd.ExecuteReader
        If objReader.Read = True Then
            stockExists = True
        Else
            stockExists = False
        End If
    Catch ex As Exception
        MessageBox.Show(ex.Message & ex.StackTrace)
    End Try
        If accessConnection.State = ConnectionState.Open Then
            accessConnection.Close()
        End If
    Return stockExists
End Function