Regards
Fredr!k
Thanks,
BrianI was doing some further reading and I am hearing that you set case
sensitivity when you first install SQL by choosing an ANSI set and the
only way to change this is to re-install SQL. Is this correct? There
has to be another way around this...|||See "Specifying Collations" and "Collation Precedence" in Books
Online. You can change the collation at the database or column level
(see ALTER DATABASE and ALTER TABLE), or in your queries (see COLLATE).
Personally, I would modify the queries (or perhaps create a view)
rather than have one or two columns in a database in a different
collation from the rest.
Simon|||There is another way in SQL2000. Collation is determined at column
level so you can alter the case-sensitivity and other collation
properties at any time. For example:
ALTER TABLE YourTable
ALTER COLUMN last_name VARCHAR(50)
COLLATE Latin1_General_CI_AS
Read the Collations topics in Books Online to understand the collation
syntax and how this affects comparisons between columns of different
collation.
--
David Portas
SQL Server MVP
--|||I used your syntax and everything works like a charm except for one
thing, now when I do a search, such as "W" in the lastname field, it
pulls every records that contains a "W" in the last name, rather than
names that start with "W". How do you correct this? it needs to search
from left to right.
Thanks,
Brian|||What's the SQL statement you are using to SELECT? It sounds like
you're putting a wildcard in front of and behind the character you are
searching on, e.g.:
SELECT ColName
FROM Table
WHERE ColName LIKE '%W%'
when it sounds like you want the wildcard after
SELECT ColName
FROM Table
WHERE ColName LIKE 'W%'
Your collation settings should only affect the case sensity of the
database; not how your LIKE comparisons perform. Am I
misunderstanding?
Stu
Ok, I'm not quite sure how to approach this one. This is a VB.NET console app in which I want to capture each row and throw it into a table. The reason being, they want a report on what was processed...which I'll be able to do easily in Reporting Services 2005 once this crap is in a table where it should be.
1) What should I use to do this, dataset? I want to use stored procedures also, not inline SQL
Function here takes an incoming file, and splits it up into separate files. I want to insert each row that is succesfully split
Public Sub ProcessFiles(ByVal sIncomingfile As String, ByVal sOutputDirectory As String)
If sIncomingfile <> "" And sOutputDirectory <> "" Then
Dim f As New Security.Permissions.FileIOPermission(Security.Permissions.PermissionState.None)
f.AllLocalFiles = Security.Permissions.FileIOPermissionAccess.Read
Dim file As New IO.FileInfo(sIncomingfile)
Dim filefs As IO.FileStream = Nothing
If file.Exists Then
Try
filefs = New IO.FileStream(file.FullName, IO.FileMode.Open) 'Place: 1
Catch ex As Exception
SendEmail("Incoming .mnt or .naf Filename Invalid or not found", "Place: 1")
Application.Exit()
End Try
End If
Dim reader As New IO.StreamReader(filefs)
Dim counter As Integer = 0
Dim CurrentFS As IO.FileStream
Dim CurrentWriter As IO.StreamWriter
Dim extension As String = IO.Path.GetExtension(file.FullName)
If extension = ".mnt" Then
While Not reader.Peek < 0
Dim Line As String = reader.ReadLine
If IsNumeric(Line.Substring(0, 1)) Then
Dim Parts() As String = Line.Split(" "c) ' split row into parts
If Parts(0).Length = 8 Then ' if first part is 8 then know we hit another header so cut and then write to file
counter += 1
If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()
CurrentFS = New IO.FileStream(IO.Path.Combine(IO.Path.GetDirectoryName(sOutputDirectory), Line.Substring(59, 4) & "[" & counter.ToString & "]" & Now.ToString("MM-dd-yyyy") & IO.Path.GetExtension(file.FullName)), IO.FileMode.Create)
CurrentWriter = New IO.StreamWriter(CurrentFS)
End If
If Not CurrentWriter Is Nothing Then
CurrentWriter.WriteLine(Line)
End If
End If
End While
If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()
MoveFilesFTP(sOutputDirectory, "mnt")
ElseIf extension = ".naf" Then
While Not reader.Peek < 0
Dim Line As String = reader.ReadLine
If Not IsNumeric(Line.Substring(0, 1)) Then ' if first part is not a number, then we know it's a header so split the file
counter += 1
If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()
CurrentFS = New IO.FileStream(IO.Path.Combine(IO.Path.GetDirectoryName(sOutputDirectory), Line.Substring(6, 4) & "[" & counter.ToString & "]" & Now.ToString("MM-dd-yyyy") & IO.Path.GetExtension(file.FullName)), IO.FileMode.Create)
CurrentWriter = New IO.StreamWriter(CurrentFS)
End If
If Not CurrentWriter Is Nothing Then
CurrentWriter.WriteLine(Line)
End If
End While
If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()
MoveFilesFTP(sOutputDirectory, "naf")
End If
Else
'input file not valid
SendEmail("Incoming .mnt or .naf Filename Invalid", "Place: 1")
End If
End Sub
You don't need a console application to import the data into SQL Server if you are in SQL Server 2000 you need a DTS package and in SQL Server 2005 you need an Integration services package. The only important thing to note is SQL Server being a RDBMS(relational database management systems) sees a text file as having Null values so you import your data into a Temp table then do INSERT INTO your destination table. Try the link below for sample DTS and Integration services code. Hope this helps.
http://www.sqlis.com/
|||Put this somewhere at the top:
Dim conn as new sqlconnection("Your connect string")
conn.open
Dim cmd1 as new sqlcommand("INSERT INTO ProcessedFiles(Filename) VALUES (@.Filename) SELECT SCOPE_@.IDENTITY()")
cmd1.parameters.add("@.Filename",sqldbtype.varchar)
dim cmd2 as new sqlcommand("INSERT INTO ProcessedLines(FileID,LineNum,LineData) VALUES (@.FileID,@.LineNum,@.LineData)",conn)
cmd2.parameters.add("@.FileID",sqldbtype.int32)
cmd2.parameters.add("@.LineNum",sqldbtype.int32)
cmd2.parameters.add("@.LineData",sqldbtype.varchar)
Then after you get the filename in your code:
cmd1.parameters("@.Filename").value={Your filename variable}
cmd2.parameters("@.FileID").value = cmd1.executescaler
Then after you read a line of data from the file:
cmd2.parameters.add("@.LineNum").value=counter
cmd2.parameters.add("@.LineData").value=line
cmd2.executenonquery
And at the end of your program:
conn.close
of course, this assumes you have a table named processedfiles that has a Filename column, as well as an identity field. I would put a ProcessedDate field in there too, that defaults to GetUTCDate(). And a table named ProcessedLines that has three columns (FileID,LineNum,LineData).
Is that what you were looking for?
I can't update a table in my app, i did this once, but i didn't save the changes, and now i cant do it, i don't even remember if i needed something more in my code, i'm updating using the TableAdapter.
I'm using .NET 2.0 and the changes in the table are made directly trough the Datagrid, the update statement is like this:
ClientTableAdapter.Update(Club_DataDataSet.Client);
Thank you, I'd appreciate your help
ooooh i think i found what the problem was
I added the database to my project, but if i copy the data file to the project the source points to the DB in my project, and it doesn't seem to be updating when i run the app in VS because the dataset, tableadapter and bindingsource are pointing to the same DB but in SQL Server.( i mean, NOT in the data file in my project jeje)
Everything works fine when the app is installed. 
i just found and read something about that and i think it help me a lot to understand some things about local data files
...i'm new at databases using VS, so if i'm wrong in anything i wrote in this post, please let me know
|||Ok....what's wrong? i added the data file to my project, but my app can't load the DB if i stop the SQL service in my computer, and neither if i install my app in another PC (i just tried that today) that doesn't have SQL installed (that's my main goal) ...i mean, the DB it's suppose to be in my project, IT IS in the folder...i was told that clickonce allows to use an SQL DB as an Access DB...in what aspects? or what i'm i doing wrong?
And when i added the data file, the only files i can add is Northwind and Pubs and maybe some others...but why i can't add my own DBs? 
Please help me, i think i'm really lost at this
Thank you
