Sql Symbols?

[devonknows]

Im writing a DB application with ADODB Reference, but the problem im having is that i have a description field in my app wich allows people to enter symbols etc etc like Square brackets or ' and stuff like that, however when executing the sql command it throws errors like "Syntax Incorrect" etc etc.

What i was wondering is, Is there any way to Change the Relevant symbols when inserting into the database and Revert the symbols when Selecting DB records.

I have a very long way around it i wont post it as im too ashamed :P and it is resource intensive aswell basically checking for each of the symbols and changing them appropriatly. However this is no good as my DB is getting quite large and it is processing multiple description fields per second so its causing my App to hang while it loads the information. Even DoEvents doesnt prove much help.

Any help is greatly appreciated.

Devon

[techgnome]

how are you inserting the data (specifically, what does the SQL look like and how are you calling it? And what is the datatype of the field?

-tg

[brucevde]

If you use the ADO Command object and parameterized queries you wouldn't have to change anything or even worry about what the user enters. Basically something like

Dim cmd as ADODB.Command
With cmd
   .CommandText = "Insert Into TableName (DescriptionField) Values(?)"
   .CommandType = adCommandText
   .Parameters.Add .CreateParameter("@Description", advarchar, adParamInput, 500)
   .Paramters("@Description").Value = txtSomeTextBox.Text
   .Execute ,,adExecuteNoRecords
End With

The user can enter anything they want and the update will succeed. I only show 1 parameter but you can of course have as many as you need.

[devonknows]

just a quick example, source code is recompiling at moment

txtDescription = "MyDescription'''[]!¬"
Set RS = Conn.Execute("INSERT INTO myTable (Title,Description) VALUES (" & txtTitle.Text & "," & txtDescription,Text & ")")

The database field type is Memo (for long texts)


What i want is something to remove the symbols that interupt the syntax in Sql if thats poss, maybe a class or a module maybe or even a snippet lol.

Devon

[devonknows]

If you use the ADO Command object and parameterized queries you wouldn't have to change anything or even worry about what the user enters. Basically something like

Dim cmd as ADODB.Command
With cmd
   .CommandText = "Insert Into TableName (DescriptionField) Values(?)"
   .CommandType = adCommandText
   .Parameters.Add .CreateParameter("@Description", advarchar, adParamInput, 500)
   .Paramters("@Description").Value = txtSomeTextBox.Text
   .Execute ,,adExecuteNoRecords
End With

The user can enter anything they want and the update will succeed. I only show 1 parameter but you can of course have as many as you need.

Im not familiar with the way you use Command, i have a Syntax module i wrote that takes a load of vars into consideration and buils the sql syntax
on the fly. Is it possible to do it on the fly the way cus i have to enter a multitude of data into tables.
for example - (First Screen)
Box, Sleeve, Title, Genre, Type, AR, Desc, STB, WAV, ExInfo.

and at the minute im having to use my EXTREMELY resource intensive symbol replacer on each of the text fields as to make sure that the sql doesnt bug out
So anyhelp would be great again

[techgnome]

That's the point... if you used parameterized queries, you WOULDN'T have to do that symbol replacing...

So your insert would look something like this:

Dim cmd as ADODB.Command
With cmd
   .CommandText = "Insert Into myTable(Title,Description) Values(?,?)"
   .CommandType = adCommandText
   .Parameters.Add .CreateParameter("@title", advarchar, adParamInput, 500)
   .Parameters.Add .CreateParameter("@Description", advarchar, adParamInput, 500)
   .Paramters("@title").Value = txtTitle.Text
   .Paramters("@Description").Value = txtDescription.Text
   .Execute ,,adExecuteNoRecords
End With

=tg

[devonknows]

Ah right i get it, But how does the CMD know which connection to use? like for example

Public ConA as New ADODB.Connection
Public ConB as New ADODB.Connection
Public RsA as New ADODB.RecordSet
Public RsB as New ADODB.Recordset

after building the connection params for ConA and use ConA.Open / ConB.Open how does the Command function know which connection to use?

Devon

[techgnome]

you tell it. The command object has a property called ".ActiveConnection" ... set that property to the connection you want to run against.

-tg

[si_the_geek]

There is some info about using Command objects you may find useful in our Database Development FAQs/Tutorials (at the top of this forum):

Why should I use Parameters instead of putting values into my SQL string?
How do I use an ADO Command object?

[devonknows]

Thank you very much for your help the Command idea is working great for me it cuts out my symbol converter that is hugely resource consuming so woop woop.

Thank you all for the quick response ive been looking for something like this for absolutely ages.

Also im not sure if i should create another topic for this however, I was wondering if there is a way to store a large amount of data in like an array without using collections becuase at the minutes ive got each tables columns in a seperate collection each.

I have a multitude of tables and columns with data that needs to be loaded but having one collection per column is quite unprofessional and time consuming entering each of the collections names, i tried using a MultiDimensional array however you cant re-dim MultiDimensional Arrays to the number of records that are stored in the Database,
again i call upon your help .

Devon

[techgnome]

As a rule of thumb, one issue/problem, one thread. Makes indexing and finding answers later a lot easier. I have a comment about your last post, but I'll hold it for the other thread (if you want PM the link to the new thread, that way I'll be sure to see it.)

-tg

[devonknows]

I understand ill create a new thread regarding the matter however i have another question with the ADODB.Command. Is it possible to create a table with this? as im using a MySql server to store all the data and when i distribute it i want to be able to create new tables as and when needed for when i do updates

Devon

[si_the_geek]

You can use a Command object to execute any SQL statement, including ones like Create Table.

However, the SQL statements you will be running in that situation will presumably be hard-coded rather than needing parameters, so instead of using a Command object you may as well just use the .Execute method of the Connection