Question Read Line from Text File - accept special character ' or "

[villy]

Hi experts,

I have a program to read line by line from text file.

strName = Trim(Mid(TextLine, 11, 50))

There is run time error while TextLine data has ' or "

May I know how to read the data with ' or " character?

Thanks.
The error is :

syntax error (missing operator) in query expression

 

[villy]

sorry.. i think the problem is not at read data, is at insert data with character ' into MS Access:

strSQL = "INSERT INTO NetSales (Name) VALUES ('" & strName & "')"

it has error is because strname value has character '', so the SQL command become incorrect after has character ''

any idea? Thanks.

 

[MattP]

Start here:

http://www.vbdotnetforums.com/database-general-discussion/18562-ins-outs-parameterized-queries.html

You'll end up with a command that looks like this:

cmd.CommandText = "INSERT INTO NetSales (Name) VALUES (?)"
cmd.Parameters.Add("?Name").Value = "John O'Something"

 

[cjard]

strictly speaking the ? in "?Name" for the parameter is not needed; you can call the parameter anything you like, it is the order of addition that is important

 

[MattP]

strictly speaking the ? in "?Name" for the parameter is not needed; you can call the parameter anything you like, it is the order of addition that is important

This is true. It helps the readability of the code for me. To each their own.

 

[villy]

objConn.Open()

objCmd.CommandText = "INSERT INTO NetSales (Community, Agent, CreatedDate) VALUES (?, ?, '" & Now() & "')"

objCmd.Parameters.Add(New OleDb.OleDbParameter("@Community", strCommunity))
objCmd.Parameters.Add(New OleDb.OleDbParameter("@Agent", strAgent))

objCmd.CommandType = CommandType.Text

objCmd.ExecuteNonQuery()
objConn.Close()
------------------------------------------------------------------------------
The above coding can be run and accept ' character, but now it only insert same value (duplicate value) into MS access.. when i do testing, the strAgent & strCommunity value are not duplicated, but will insert duplicated data same as first row data into MS access.

 

[MattP]

when i do testing, the strAgent & strCommunity value are not duplicated, but will insert duplicated data same as first row data into MS access.

Mind clarifying a little what you're saying here? When you step through the code what's shown for the CommandText after you've added your parameters?

I'm also confused why you'd concatenate your Now() onto the string rather than making it another parameter.

 

[cjard]

Stop using string concatenation to put values into SQLs!!


Also, if you want the current time you can use the access NOW() function:


objCmd.CommandText = "INSERT INTO NetSales (Community, Agent, CreatedDate) VALUES (?, ?, NOW() )"

The above coding can be run and accept ' character, but now it only insert same value (duplicate value) into MS access.. when i do testing, the strAgent & strCommunity value are not duplicated, but will insert duplicated data same as first row data into MS access.

You're probably not changing the value after you created the parameter with it. Read the DW2 link in my signature, section Creating a Simple Data App

 

[villy]

Hi all, problem solved:

Dim objcmd As New OleDb.OleDbCommand( _
"INSERT INTO NetSales (Community, Agent) VALUES (?, ?,)", _
objConn)

objcmd.Parameters.Add(New OleDb.OleDbParameter("Community", strCommunity))
objcmd.Parameters.Add(New OleDb.OleDbParameter("Agent", strAgent))

objCmd.ExecuteNonQuery()
objConn.Close()
Thanks all~~

 

[villy]

Mind clarifying a little what you're saying here? When you step through the code what's shown for the CommandText after you've added your parameters?

I'm also confused why you'd concatenate your Now() onto the string rather than making it another parameter.

Stop using string concatenation to put values into SQLs!!


Also, if you want the current time you can use the access NOW() function:


objCmd.CommandText = "INSERT INTO NetSales (Community, Agent, CreatedDate) VALUES (?, ?, NOW() )"



You're probably not changing the value after you created the parameter with it. Read the DW2 link in my signature, section Creating a Simple Data App

Ya.. for created data, shd be as this:
objcmd.Parameters.Add(New OleDb.OleDbParameter("CreatedDate", Now().ToString))

 

[cjard]

objcmd.Parameters.Add(New OleDb.OleDbParameter("CreatedDate", Now().ToString))

You would only do this if your dates in your database were stored as strings
If they are stored as dates, you would:
objcmd.Parameters.AddWithValue("CreatedDate", DateTime.Now)

Please note, this uses the clock on the client computer. Most the database systems I write use the clock on the database server when editing/adding records, to provide some uniformity. It stops users being able to fake the times that records were added by changing their clock, and it means that audit trails can be reliably implemented. Having one source of info for time is better than multiple