Solved

How to Query a specific amount of Digits From VB.net Form to Access DB

Posted on 2006-06-15
17
239 Views
Last Modified: 2010-04-23
Hey guys I have a simple question to ask.

how do you query a specific amount of digits within a SQL query?!?

Example I'm developing a small app which carries the VIN number..although I don't want my user to remember all 17 digits I would like to know how to query only the last 6 Digits within the VIN number...

Think of it the same way when your credit card company ask you to only give the last 4 digits of your SSN or etc

Well this is what I have so far

With SearchCmd
            .Connection = myConnection
            .CommandText = "Select Stock_ID,CarVIN From InventoryTable" & _
                " Where CarVIN Like @CarVIN "
            .CommandType = CommandType.Text




            .Parameters.Add(New OleDbParameter("@Stock_ID", OleDbType.Char, 5))
            .Parameters.Add(New OleDbParameter("@CarVIN", OleDbType.Char, 18))


  .Parameters("@Stock_ID").Value = "%" & VIN & "%"
            .Parameters("@CarVIN").Value = "%" & VIN & "%"

        End With


        'Now to declare a OLEDBDataAdapter object
        Dim Adapter As New OleDbDataAdapter

        'Now to declare a dataset
        Dim DataSet As New DataSet

        'Now to apply my Command to the DataAdapter
        Adapter.SelectCommand = SearchCmd




        Try ' The reason we use try is to see if the database can open and what can happen


            Adapter.Fill(DataSet)
            ' insertCmd.ExecuteNonQuery()



            'To bind the Dataset to the DataGrid :)
            grdResults.DataSource = DataSet

            'Tell the DataGrid which table in the Dataset to use
            grdResults.DataMember = DataSet.Tables(0).TableName


            'Setting the Alternating Colors property to the Grid
            grdResults.AlternatingBackColor = Color.WhiteSmoke

            'Set the GridLineStyle Property :)
            grdResults.GridLineStyle = DataGridLineStyle.None

            'Set the SelectionBackColor and the Selection ForeColor Properties
            grdResults.SelectionBackColor = Color.LightGray
            grdResults.SelectionForeColor = Color.Black

            'DataSet.Tables(0).Rows.Count()

        Catch OleDbExceptionErr As OleDbException
            Debug.WriteLine(OleDbExceptionErr.Message)
        Catch InvalidOperationExceptionErr As InvalidOperationException
            Debug.WriteLine(InvalidOperationExceptionErr.Message)



        End Try


        'Cleaning up the Mess
        SearchCmd.Dispose()
        SearchCmd = Nothing
        Adapter.Dispose()
        Adapter = Nothing
        DataSet.Dispose()
        DataSet = Nothing

        myConnection.Dispose()
        myConnection = Nothing


////////////////////////////

Everything else works..(Connections , etc etc)

Even my SQL code works because it uses the Like clause...but again right now it  has to take all digits in order to do a search. I would like to know if SQL has a way to do a search on the last 6 digits if possible.

Please tell me what you think of this.

Take care experts :)


0
Comment
Question by:masterat03
  • 11
  • 5
17 Comments
 
LVL 10

Expert Comment

by:bchoor
Comment Utility
Did u try to use the RIGHT keyword?

Select Stock_ID, RIGHT(CarVIN. 6) From InventoryTable" & _
                " Where CarVIN Like @CarVIN
0
 

Author Comment

by:masterat03
Comment Utility
Hmm I just tried it but it didnt work..actually it didnt even run lol
is there another way to do this?

0
 

Author Comment

by:masterat03
Comment Utility
here is a small update to how i wrote the sql statement



////////////

 Dim VIN = txtVINnumber.Text.Trim



        Dim connString As String = " Provider = Microsoft.JET.OLEDB.4.0; Data Source = X:\members.mdb; "
        Dim myConnection As OleDbConnection = New OleDbConnection
        Dim SearchCmd As OleDbCommand = New OleDbCommand

        myConnection.ConnectionString = connString
        myConnection.Open()

        With SearchCmd
            .Connection = myConnection
            .CommandText = "Select Stock_ID,RIGHT(CarVIN.6)From InventoryTable Where CarVIN Like @CarVIN " & _
                            " Where CarVIN LIKE @CarVIN "
               
            .CommandType = CommandType.Text

           .Parameters.Add(New OleDbParameter("@Stock_ID", OleDbType.Char, 5))
            .Parameters.Add(New OleDbParameter("@CarVIN", OleDbType.Char, 18))

             .Parameters("@Stock_ID").Value = "%" & VIN & "%"
            .Parameters("@CarVIN").Value = "%" & VIN & "%"

////////

any suggestions ?


0
 

Author Comment

by:masterat03
Comment Utility
Opps sorry for the wrong syntax
.Connection = myConnection
            .CommandText = "Select Stock_ID,RIGHT(CarVIN.6)From InventoryTable Where CarVIN Like @CarVIN " & _
                            " Where CarVIN LIKE @CarVIN "
               
I corrected it to this

.Connection = myConnection
            .CommandText = "Select Stock_ID,RIGHT(CarVIN.6)From InventoryTable Where CarVIN Like @CarVIN "

///but it still doesnt do anything :(

                           
               
0
 

Author Comment

by:masterat03
Comment Utility
I wanted to ask what exactly does the RIGHT clause do..?
0
 

Author Comment

by:masterat03
Comment Utility
So um..anyone willing to lend a helping hand here
0
 
LVL 12

Expert Comment

by:omegaomega
Comment Utility
Hello, masterat03,

I'm an old man with poor eyesight, but that looks to me like a "." (period) delimiting "CarVIN" from "6" in your "Right" function.  Try using a "," (comma) instead.

Cheers,
Randy
0
 

Author Comment

by:masterat03
Comment Utility
Hey you can be an old man but the force still is strong with you.

I tried this out omegaomega

 With SearchCmd
            .Connection = myConnection
            .CommandText = "Select RIGHT(CarVIN,6)as VINnumber,Stock_ID From InventoryTable Where CarVIN LIKE @CarVIN "

            .CommandType = CommandType.Text

             .Parameters.Add(New OleDbParameter("@Stock_ID", OleDbType.Char, 5))
            .Parameters.Add(New OleDbParameter("@CarVIN", OleDbType.Char, 18))

            .Parameters("@Stock_ID").Value = "%" & VIN & "%"
            .Parameters("@CarVIN").Value = "%" & VIN & "%"


        End With

/////////////

Now the funny thing is that if I only leave let say I leave "CarVIN" enabled and I quote the other I can see the value of carVIN , but the way it is now when I put the last 6 digits I see my datagrid activate, but no data is retrieved :(

0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 12

Expert Comment

by:omegaomega
Comment Utility
Hello, masterat03,

I'm not quite clear on what you're saying.  Are you saying that

            .CommandText = "Select CarVIN as VINnumber,Stock_ID From InventoryTable Where CarVIN LIKE @CarVIN "

works (i.e. loads data into the GataGrid), but

            .CommandText = "Select RIGHT(CarVIN,6)as VINnumber,Stock_ID From InventoryTable Where CarVIN LIKE @CarVIN "

doesn't?

Cheers,
Randy
0
 

Author Comment

by:masterat03
Comment Utility
Sorry i will explain better.

For instance if I do this

With SearchCmd
            .Connection = myConnection
            .CommandText = "Select RIGHT(CarVIN,6)as VINnumber,Stock_ID From InventoryTable Where CarVIN LIKE @CarVIN "

            .CommandType = CommandType.Text

             .Parameters.Add(New OleDbParameter("@Stock_ID", OleDbType.Char, 5))
            .Parameters.Add(New OleDbParameter("@CarVIN", OleDbType.Char, 18))

            .Parameters("@Stock_ID").Value = "%" & VIN & "%"
            .Parameters("@CarVIN").Value = "%" & VIN & "%"


        End With

////

Nothing comes out..I see the name of the rows..but no data

Now if I do this

///////

With SearchCmd
            .Connection = myConnection
            .CommandText = "Select RIGHT(CarVIN,6)as VINnumber From InventoryTable Where CarVIN LIKE @CarVIN "

            .CommandType = CommandType.Text

           
            .Parameters.Add(New OleDbParameter("@CarVIN", OleDbType.Char, 18))

            .Parameters("@CarVIN").Value = "%" & VIN & "%"


        End With
/////

Then I see the data for the Vin numbers

0
 
LVL 12

Expert Comment

by:omegaomega
Comment Utility
Hello, masterat03,

If the second method works, is there a reason to not use it?  I guess so, or you wouldn't still be posting ;-)  

I've never used the DataGrid, but it seems like having the second field "Stock_ID" in your Select clause is causing some interference.  Is it possible that the data are rejected because there isn't a column in the DataGrid for them?

Perhaps you can try your query directly through the Access User Interface to see what results it gives you.  That might provide a clue to whether the problem lies with the query or with the DataGrid.

I'm also a bit confused about your Where clause.  In an earlier post you said "I would like to know if SQL has a way to do a search on the last 6 digits if possible." but it looks like you are still searching on the whole CarVIN field.  Do you really want something like:

            .CommandText = "Select RIGHT(CarVIN,6)as VINnumber From InventoryTable Where VINnumber LIKE @CarVIN "
            .  .  .  .

            .Parameters("@CarVIN").Value = "%" & VIN & "%"

so that your search is matching the final 6 characters of CarVIN to any sub-string within VIN?  Or is it possibly that you want something like:

            .CommandText = "Select RIGHT(CarVIN,6)as VINnumber From InventoryTable Where CarVIN LIKE @CarVIN "
            .  .  .  .

            .Parameters("@CarVIN").Value = Right("%" & VIN, 6)

so that you are searching for values of CarVIN that match the final 6 characters of VIN?


Cheers.
Randy
0
 

Author Comment

by:masterat03
Comment Utility
In reality I need only the last 6 Digits of the VIN number..the reason I had this like this
.Parameters("@CarVIN").Value = "%" & VIN & "%"


is because I know that the LIKE clause works with that type of syntax.
But since I didnt know if SQL can actually search specific amount of digits then well I decided to do it like that.

Although we are getting close to the objective and yes (i do need the stock id with it).. It becomes relevant to see the ID in the table to which vin it belongs to .

Also I tried the other method
  .Parameters("@CarVIN").Value = Right("%" & VIN, 6)

but I had to rewrite it like this
 .Parameters("@CarVIN").Value = ("%" & VIN)
This works and searches the last 6 digits, but yet again when I decide to do this

.Connection = myConnection
            .CommandText = "Select RIGHT(CarVIN,6)as CarVIN From,Stock_ID InventoryTable Where CarVIN Like @CarVIN "
            .CommandType = CommandType.Text

  .Parameters.Add(New OleDbParameter("@Stock_ID", OleDbType.Char, 5))
            .Parameters.Add(New OleDbParameter("@CarVIN", OleDbType.Char, 18))

.Parameters("@Stock_ID").Value = ("%" & VIN)
            .Parameters("@CarVIN").Value = ("%" & VIN)

/////
The datagrid doesnt even budge at all....

Now the funny thing is that I tried to see perhaps there was something wrong within my Access table
so I query it with this in Access
SELECT RIGHT(CarVIN,6) AS VINnumber, Stock_ID
FROM InventoryTable
WHERE VINnumber Like [@CarVIN];

and this works lovely.

Also let me tell you that my Stock_ID Field is a (NUMBER) field in access..

Would that have to do anything with it in our query in VB???





0
 

Author Comment

by:masterat03
Comment Utility
so what do you think of this situation then??
0
 
LVL 12

Accepted Solution

by:
omegaomega earned 225 total points
Comment Utility
Hello, masterat03,

I guess that it is more relevant whether or not the query that you actually need will work within the Access interface.  I would suggest that you try this.

If it does work in Access, then this indicates some problem related to the interaction of the query and the DataGrid.  As mentioned, this is not something that I'm familiar with.

Also, this seems to be rather different from the question in your original post, so you are likely to get a better response if you post it as a new question with a relevant subject line.

Good luck!

Cheers,
Randy
0
 

Author Comment

by:masterat03
Comment Utility
Well its ok at least we try to figure it out. Although im so close to the objective of finding out why doesnt it work. I thought that perhaps since the Stock_ID might of been transfering String values instead of numeric then that could be one reason why it isnt working.

Like i said when I did the same query it does work in access...so i now believe that the sql string is not the problem, but perhaps the values that need to be presented are.

Also when i tried to put in only the vin number without (stockID) the last 6 digit value works very well
but as soon as i apply another field name to the SQl string (stock_ID) things just go bad.

as for the posting I believe we were doing pretty much well from the original objective which is to try to get the last 6 digits within the VIN field .

The only thing that caused havoc was putting another field name within the SQL statement
0
 

Author Comment

by:masterat03
Comment Utility

**Update**

hey omegaomega
Just wanted to keep you updated and let you know that for some crazy reason if I do this

"Select CarYear,RIGHT(CarVIN,6)as CarVIN From InventoryTable Where CarVIN = @CarVIN "

This works!!!

but if go back and i put in

"Select Stock_ID,CarYear,RIGHT(CarVIN,6)as CarVIN From InventoryTable Where CarVIN = @CarVIN "

This does not.

Which means that the SQL statement has trouble to interpret my Primary KEY for Stock_ID

So Long and behold this I know you will LMAO at when I tell you what actually FIXED IT!!!!

////////////
Do you remember this part of the code?
             .Parameters.Add(New OleDbParameter("@Stock_ID", OleDbType.Char))
            .Parameters.Add(New OleDbParameter("@CarVIN", OleDbType.Char, 18))
            .Parameters.Add(New OleDbParameter("@CarYear", OleDbType.Char, 5))

Well the first Line i did a small change to it
Before it was like this
.Parameters.Add(New OleDbParameter("@Stock_ID", OleDbType.Char, 5))

For Some Crazy and Please don't ask why lol...it didnt work with this ...I decided to play around with this and changed it to this
.Parameters.Add(New OleDbParameter("@Stock_ID", OleDbType.Char))

The reason I did this is because I knew there had to be a problem with the Primary ID like I stated before.
Also the bottom part which is this
            .Parameters("@Stock_ID").Value = ("%" & VIN)
            .Parameters("@CarVIN").Value = ("%" & VIN)

I changed it back to this
            .Parameters("@Stock_ID").Value = "%" & VIN & "%"
            .Parameters("@CarVIN").Value = "%" & VIN & "%"

This is pretty good and works well because let say my users put in the first 6 digits or like the objective the last 6 digits it gives me still the last 6 digits which pertain to that particular VIN number...so I kinda got 2 features in 1.

Tell me what you think of all this crazyness.

Also I thankyou for your time for I know we both learn something from all of this lol.






0
 
LVL 12

Expert Comment

by:omegaomega
Comment Utility
Hello, masterat03,

I think that your assignment of "Accepted Answer" is incorrect.  The credit belongs to bchoor who provided the first response.

I also think that his answer deserves better than a "C", even acknowleding the obvious typing error ("." instead of ",").

Cheers,
Randy
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now