asked on

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

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 :)

bchoor

Did u try to use the RIGHT keyword?

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

masterat03

ASKER

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

masterat03

ASKER

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 ?

masterat03

ASKER

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 :(

masterat03

ASKER

I wanted to ask what exactly does the RIGHT clause do..?

masterat03

ASKER

So um..anyone willing to lend a helping hand here

omegaomega

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

masterat03

ASKER

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 :(

omegaomega

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

masterat03

ASKER

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

omegaomega

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

masterat03

ASKER

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???

masterat03

ASKER

so what do you think of this situation then??

ASKER CERTIFIED SOLUTION

omegaomega

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

masterat03

ASKER

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

masterat03

ASKER

**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.

omegaomega

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