|
Are you talking about a query or filtering a datatable?
SQL in .Net use the '%' wildcard
e.g. Select LastName From tblUsers Where LastName Like '%bri%'
This will retrieve all rows with 'bri' in the last name.
DataTable in .Net the '%' wildcard is used as well.
Dim dv as DataView
Dim dt as DataTable
Dim dr as DataRow
Dim r as Integer 'loop for each row
'Populate the DataTable
...
...
'Now take the populated dt and make it a DataView and Filter it.
dv = dt.DefaultView
dv.RowFilter = 'LastName Like '%bri%'
For r = 0 to dv.Count-1 'Rows in the dataview
dr = dv.item(r).Row
'Print the values for LastName column
System.Diagnostics.debug.WriteLine(dr.Item("LastName"))
Next
Now this was taken off the top of my head so I am sure there may be some syntax errors, but you should get the idea.
Hope this is what you were looking for.
Michael
|
|
|
|
|
I have a table with the field type as datetime. So, this stores the date with time upto msec precession..
I have a searvh query that will look for records created in certain time period... How do I do this 'between' kind of query.. please help
I was born intelligent
Education ruined me!.
|
|
|
|
|
You don't need to give the string up tp msec. There is no problem with giving date value in BETWEEN.
SELECT * FROM mytable WHERE datecolumn BETWEEN '24/01/2004' AND '24/01/2004'
You can give time in long format or other formats,SQL have no problem with that. I don't understand whats your problem , it seems you know the way.
Mazy
No sig. available now.
|
|
|
|
|
Mazdak wrote:
it seems you know the way
Blah.... I was messing around with the dates by the '#' symbol and didnt realise that I have come to SQL server a long time before 
I was born intelligent
Education ruined me!.
|
|
|
|
|
<br />
SELECT ...<br />
WHERE<br />
cast(Convert(nvarchar, tr.TransDate, 1) as datetime) <= cast(Convert(nvarchar, @dtTo, 1) as datetime) AND<br />
cast(Convert(nvarchar, tr.TransDate, 1) as datetime) >= cast(Convert(nvarchar, @dtFrom, 1) as datetime)<br />
this code, I think, is better cuz it will take into account locale.
Regards,
Serge (Logic Software)
|
|
|
|
|
I want to check if the user name and password exists in the database. Table name is tblUser. User name is brendanvogt, and password is for example codeProject. I have a problem in that I can type codeproject in capital letters, and it will still accept the password. I can write a mixture of capitol letter and small letters, and it still accepts the password. I tried using the LIKE keyword, and it still does not work. Here is my SQL statement. I am using an Access database.
SELECT User_Username FROM tblUser WHERE User_Username = 'brendanvogt' AND User_Password LIKE('codeProject')
|
|
|
|
|
Misleading title - it isn't that password validation doesn't work, it's just that the SQL query is not case sensitive. You'd have to retrieve the password for a given username and compare it in code. I'm not aware of a way to make Access case sensitive, although you can do it for SQL Server (and hence MSDE).
Steve S
|
|
|
|
|
It is refer to your database(or table or column) COLLATION. You have to set it to a case sensitive value. You can change collation with ALTER TABLE or in Enterprise Manager in the Design View of table for each column at the bottom of page you can change it. You have to select one of values which contain CS , means Case Sensetive.
Mazy
No sig. available now.
|
|
|
|
|
You should never store passwords in clear text. If anyone manages to obtain the database (downloading the file, executing a SELECT via a SQL injection attack, etc), your entire authentication mechanism is compromised. Users will not be happy if they've used the same password for multiple systems/websites/whatever, and you've disclosed it.
You should calculate a secure hash (using a cryptographic hashing/message digest scheme, not one you invent yourself) and store that in the database (you'll probably need to convert the bytes to hexadecimal). When authenticating a user, you again hash the provided password and compare with the stored one.
To calculate a cryptographic hash, you can use the CryptHashData function in the Windows API, the CAPICOM object library (if using VB 6 or a scripting language), or the System.Security.Cryptography.HashAlgorithm .NET Framework class.
There are various other techniques that are an extension of this which can provide more security, such as encrypting the hash, and/or adding a random 'salt' to the password before hashing.
|
|
|
|
|
Hi everyone,
Really new to using SQL Server. I've installed MSDE and I'm using the osql utility to access the db and I had a question about formatting output. I know in Oracle I could do something like
column Item format a18 and that would format the column so that it was only 18 character wide. Does anyone know how I would accomplish this same thing with MSDE using the osql utility?
Thanks.
- monrobot13
|
|
|
|
|
LEFT(<field_name>, 18) or SUBSTRING(<field_name>, 0, 18)
|
|
|
|
|
Exactly what I was looking for. Thanks!
- monrobot13
|
|
|
|
|
Hi,
I'm trying to use the SqlDataAdapter, but I need to know how many rows the SqlDataAdapter had. There is some way to know more easy that:
<br />
SqlDataAdapter datareader;<br />
int i = 0;<br />
while(dataReader.Read()){<br />
i++;<br />
}<br />
Thanks
----
hxxbin
|
|
|
|
|
I'm not sure there is way to get the count of rows on a sqlDataAdapter.
The sqlDataAdapter.Fill method fills a Dataset or a DataTable. Once the dataset or table is populated you can get the row count from the
Dataset.Tables(0).Rows.Count or DataTable.Rows.Count method.
Dim ds As Dataset
sqlDA.SelectCommand.Connection = cn
sqlDA.Fill(ds)
sqlDA.SelectCommand.Connection = Nothing
Return dataset
With the dataset or DataTable populated you can obtain the rowcount by doing the following:
ds.Tables(0).Rows.Count
dt.Rows.Count
Michael
|
|
|
|
|
I think you meant SqlDataReader (the rest of the code looks like that).
There's no way to find out in advance how many rows are going to be retrieved. The SqlDataReader won't allow you to rewind anyway.
If you need to store the data temporarily (maybe you need to do multiple passes over it?), consider a DataTable. If that has too much overhead for you, use some automatically-growing data structure such as an ArrayList - you can always use the ArrayList's CopyTo method to copy it to an array once you've retrieved all the data.
|
|
|
|
|
Yeah, I meant SqlDataReader, sorry. And I going change my code to SqlDataAdapter.
Thanks. 
----
hxxbin
|
|
|
|
|
Hello!
I'm in the process of porting a DAO application to ADO and I'm a bit stuck.
In DAO I can get the name of the database using CDaoDatabase::GetName().
How do I do this in ADO?
Anders Eriksson
Sonork 100.21825
|
|
|
|
|
Hi, I know this is mainly for SQL, but I am hoping someone can help. I have the following problem, which I need to find a solution for. I have a table with unique transaction ids, customer ids, payment dates and payment amounts. I need to find out what someones last pay date was. I cant seem to find out how to group by the customer ID, showing only their most recent pay date. That would get me to solve the problem. Just remember that each customer can have multiple transactions.
If you can help me, please do. I have no idea how to do this query. I have been trying, but have not been succesful.
|
|
|
|
|
Without knowing your tables fields etc:
Select * from table a
where a.date = (select max(b.date) from table b where a.cust_id = b.cust_id)
Hope this helps.
Grtz, Guus
|
|
|
|
|
Hi, just to clarify all the data is in one table. Would this still work?
|
|
|
|
|
Just a swag:
Fetch the recordset with:
SELECT * FROM tablename WHERE custID = customernumber ORDER BY transdate
then use MoveLast to find the last payment by the selected customer.
Heard in Bullhead City - "You haven't lost your girl -
you've just lost your turn..." [sigh] So true...
|
|
|
|
|
What's wrong with this?
SELECT MAX(PayDate)
FROM Table1
GROUP BY CustomerID Untested, but my reading of the Access (2003) help file indicates it should be supported. Access 2003 uses the same Jet 4.0 engine and query processor as Access 2000 and XP.
|
|
|
|
|
How do I view the contents of a .mdb file? I've searched all over, and the only program I found was shareware and just listed the field names, not their values. I'm brand-new to database programming, so please share even the most basic advice. If it helps, I'm trying to access Adobe Album 2's database file that stores the locations and tags of photos; the file ends in .psa, but I was told by a developer at Adobe that it's just a simple Access database (which it appears to be).
|
|
|
|
|
Can't you open it with Access? 
You can view the Bitmap fields by Paint from Access, otherwise simply write an application that read image fields and show each picture there. (You said you know the name of fileds)
Mazy
No sig. available now.
|
|
|
|
|
The problem is, I don't have Access (the Academic version of Office 2003 doesn't include Access), and I don't think the shareware app listed all the fields. The database file is only ~300kb for ~50 photos, so I think it just has filenames and metadata.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
