Click here to Skip to main content
15,901,284 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi,
How to get the position of a character using OleDB Command line ...

here is my code below
dim pos1 as interger
dim line as string = textbox1.text

using cmd as new oledb.oledbcommand ("SELECT * from Table1 WHERE Instr('" & Textbox1.text & "', [column1] ",connstring)

cmd.parameters. addwithvalue("",Line)

if cInt(cmd.executescalar()) <> 0 then 
something ....  
else 
something...
end if 


So this was the case i dont know how to use pos1 variable in the command to get the position of a character ....

thnxxa in advance

any help should be appreciated....
Posted
Updated 5-Jul-15 22:24pm
v2

1 solution

Wow - That code is very broken :S

I'll start by at least correcting the syntax:
VB
dim pos1 as interger
dim line as string = textbox1.text 


''Only ever use parameters (but it looks like your just trying to debug and this isn't your final code)
using cmd as new oledb.oledbcommand ("SELECT * from Table1 WHERE Instr(?, [column1] )",connstring) ''Don't forget closing brackets in the query
 
''Good practice to name every param in case you choose to add later
cmd.parameters. addwithvalue("@line",Line)
 
''The above query does not look like a scalar select.  Be careful
if cInt(cmd.executescalar()) <> 0 then 
something ....  
else 
something...
end if 



The easiest way is to retest the string you get back with IndexOf

If you really want it in your query then look at
CHARINDEX('bar', 'foobar') == 4
PATINDEX('%bar%', 'foobar') == 4

But if you want the index and the '*' then it will not be a scalar query

Hope that helps ^_^
Andy

UPDATE: Details explanation for the less experienced:

A Scalar query is one that returns 1 row, 1 column. If more that one row or column is returned then a Scalar will only read the first row and first column.

If this is what you need then any string in Textbox1 should result in a unique string, or you should order your data is some way. I can only infer this from your code.

Variable assignments always have the variable on the left, and the value on the right. This is true of all programming languages.

I must make the following assumption about what you are trying to do:
1: Either each query returns a unique row or you don't care which row is found
2: You only want 1 value from the query, the first index of the string

So here it is:
dim pos1 as interger ''We will assign the result to this
dim line as string = textbox1.Text 


''Only ever use parameters. SQL Injection is a real danger!
using cmd as new oledb.oledbcommand ("SELECT CHARINDEX(?,[column1]) from Table1 WHERE Instr(?, [column1] )",connstring) ''Don't forget closing brackets in the query
 
''Good practice to name every param in case you choose to add later
cmd.parameters. addwithvalue("@line",line)
cmd.parameters. addwithvalue("@line2",line)
''This is why we name params.  Because you are using oledbm and we use the parameter twice, we must add it as two unique params
 
cmd.Connection.Open()
pos1 = cmd.executescalar()
cmd.Connection.Close()

if cInt(pos1) IS NOT NULL then ''Will be -1 if not found or null if no rows returned.  It cannot be -1 because there is the InStr() to make sure that it does exist
something ....  
else 
something...
end if 



This has not been tested. You will need to make sure that the syntax is correct
 
Share this answer
 
v3
Comments
Rohit Malhotra 6-Jul-15 4:54am    
i didn't get you ... wat i want is pos1 variable in the query how to add that ....
Andy Lanng 6-Jul-15 4:59am    
Did you see the update? it's 10 minutes old. CharIndex or PatIndex
Member 10521418 6-Jul-15 5:06am    
u mean like this ...

CHARINDEX('bar', 'foobar') == Pos1
Andy Lanng 6-Jul-15 5:08am    
what? wait! What is Pos1. I think we have our lines crossed :P
Member 10521418 6-Jul-15 5:09am    
pos1 is a variable which i want to declare to get the position of a character

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900