|
Hi,
I'm trying to replace the ' character inside a varchar field with another character (such as `). For example, in the database I have a record of the form "Hello, I'm happy to meet you". I apply the REPLACE function to this record: REPLACE(<table_name>.<field_name>, '''', '`'). Theoretically, the result should be "Hello, I`m happy to meet you". But it's not. The record is not affected by the REPLACE. Does anyone know a logical explanation? Or is this voodoo? :P
Thanks
|
|
|
|
|
But you already have the correct character in the field, why replace it with an incorrect one?
Are you setting the field to the new value?
<table_name>.<field_name> = REPLACE(<table_name>.<field_name>, '''', '`')
|
|
|
|
|
Why is ` incorrect? I want to change the ' inside the varchar field because i want to generate an insert command, for example, inside a text file. If i don't change it, the command in the file would be something like this: INSERT INTO some_table VALUES (..., 'Hello, I'm happy to meet you')
And my problem is that the string ends right after "I". I also tried doubling the ' character but it didn't work that way either.
|
|
|
|
|
th3_d3vil wrote: doubling the ' character but it didn't work
It should. What is happening?
Are you storing commands in a file? Why not store the data as CSV and have a parameterized insert command?
What task are you trying to accomplish?
|
|
|
|
|
I found the problem. In the database I found ’ (ASCII code 22) instead of ' (ASCII code 39) and it seems that writing the results to the file using bcp changes the ’ (22) character with ' (39)  . It's funny that I don't seem to find ’ (22) on my keyboard  . Anyway thanks for your help 
|
|
|
|
|
I'm using SQL Server 2000 and performing some insertions. Now I'm facing very slow response and some time it hangs in the middle.
Please take me out from this problem.
Thanks
Syed Shahid Hussain
|
|
|
|
|
I'm always amazed by questions like this. How do you hope for us to help you ? We have no idea what your code looks like, how can we comment on it ?
What does 'hangs in the middle' mean, your app becomes unresponsive ?
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
hi
Are you Sure that your insert statement works?
show us the SQl statement that you use in the insert statement?
Is the Memory of your machine enough to handle SQl server ? and how big it is ?
what is you network Bandwith if you are not using it locally ?
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Hello friends,
I am trying to write a query to bring records from my ACCESS database and the field type of the column included in the where clause is date/time.
my query was:
command = "Select * FROM tblIncomingLetters where InwardDate Between " + dateTimePicker1.Value.Date+ " AND " + dateTimePicker2.Value.Date;
I am sending this query to another fucntion that will take this query as a command txt
but it is constantly giving me the error of missing operator
I was wondering if anyone of you can help me in resolving the issue.
Looking forward for your input.
Sadaf
|
|
|
|
|
TRy using parameterised queries, then you can be sure that your data is going to be presented in a format your source understands.
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
Thanks for ur co-operation but let me briefly describe what i want
my query is changing based on the filters used .
to accomodate this situation , i have made a function GetData() which is being called after the user clicks on the button (btnFind) and they query is decided based on the filters specified by the user which means the parameters also change .
---Function GetData()----
private void GetData(string selectCommand)
{
// Specify a connection string. Replace the given value with a
// valid connection string for a Northwind SQL Server sample
// database accessible to your system.
string currentDirectory = System.Environment.CurrentDirectory;
OleDbConnection con = new OleDbConnection();
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + currentDirectory + "\\dbLetters.mdb';Persist Security Info=False";
// Create a new data adapter based on the specified query.
dataAdapter= new OleDbDataAdapter(selectCommand, con);
// Create a command builder to generate SQL update, insert, and
// delete commands based on selectCommand. These are used to
// update the database.
OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(dataAdapter);
// Populate a new data table and bind it to the BindingSource.
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataAdapter.Fill(table);
bindingSource1.DataSource = table;
// Resize the DataGridView columns to fit the newly loaded content.
dataGridView1.AutoResizeColumns(
DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
dataGridView1.DataSource = bindingSource1;
}
-------END--------
-----query specified-----
string command = "";
#region Sender of the letter is specified
else if (chkFrom.Checked == true && chkDate.Checked == false && chkSubject.Checked == false)
{
if (cmbFrom.SelectedItem.ToString() == "Others")
{
command = "Select * from tblIncomingLetters where From LIKE ='" + this.txtFrom.Text+ "'";
}
else
{
command = "Select * from tblIncomingLetters where From LIKE '" + cmbFrom.SelectedItem.ToString() + "'";
}
}
#endregion
#region date is specified
else if (chkDate.Checked == true && chkFrom.Checked == false && chkSubject.Checked == false)
{
command = "Select * FROM tblIncomingLetters where InwardDate Between " + dateTimePicker1.Value.Date+ " AND " + dateTimePicker2.Value.Date;
}
#endregion
#region subject is specfied
else if (chkDate.Checked == false && chkFrom.Checked == false && chkSubject.Checked == true)
{
command = "Select * from tblIncomingLetters where Subject LIKE '" + this.txtSubject.Text + "'";
}
#endregion
-------
Now plz tell me how to solve it or is there any other better way to achieve the same result.
I shall be really thankful 2 u
Sadaf
|
|
|
|
|
First of all, what I said stands. Use parameterised queries if you must do SQL in your presentation layer. I think it's inexcusable, but that's another discussion.
Second, read this[^]. Your code is a disaster, and a malicious user can easily comprimise your database, erase it, or gain access to any data they want from it.
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
there is one thing I forgot to mention which is that
once the query to retrieve the data from database is decided (string command is formed) , I call the GetData function passing it the string command
GetData(command);
Kidnly correct me if you think this can done in other ways!
Sadaf
|
|
|
|
|
|
Hi All,
I want to list all the temporary tables for a perticular session in SQL Server 2005. I can see all the temporary tables in tempdb by using the following query
select * from tempdb..sysobjects
But i need to find out the tables which are created for a specific session.
Is there any way to do that?
Waiting for your reply.
Thanks in advance.
Regards,
Sulaman Riaz
|
|
|
|
|
|
Hi all
I am not sure to post this question is suitable or not.
How/Can I select the columns of entire database tables? I am going to clear about the question.
For select all columns of one tabble.
Select * from TableName.
Similarly, what is the query for select * from *(all tables inside a database).
Thanks
|
|
|
|
|
SQL Server 2005 : Select * from sys.columns
SQL Server 2000 : Select * from syscolumns
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Why do you want to do that ?
if you have lost the name of your table if, you are using SQl2005,2000 ,check on Enterprise manager and for 2005 Business intelligence, and tell me , if its an Application request, what was the thing that makes think its a right way to go by select all the Tables in the DB
?
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Hi,
Is it possible, with an SQL statement, to retrive a column's data type?
Note: working with MS Access 2000 database (OleDbCommand in C#).
Ron
|
|
|
|
|
Hi Ron,
You may try the following code snippet and see if this helps.
--------------------------------------------------------------------------
BEGIN CODE
using System;
using System.Data;
using System.Data.OleDb;
public class DatabaseInfo {
public static void Main ()
{
String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.\\DataBaseName.mdb";
OleDbConnection con = new OleDbConnection(connect);
con.Open();
Console.WriteLine("Made the connection to the database");
String cmd = "SELECT * FROM YourTableName";
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand(cmd, con);
DataSet ds = new DataSet();
adapter.Fill(ds, "YourTableName");
DataTable item = ds.Tables[0];
Console.WriteLine("Table name: {0}", item.TableName);
Console.WriteLine("Its columns are:");
foreach (DataColumn col in item.Columns)
Console.WriteLine("{0}\t{1}", col.ColumnName, col.DataType);
con.Close();
}
}
END CODE
Hope this helps .
--------------------------------------------------------------------------
Regards,
John Adams
ComponentOne LLC
|
|
|
|
|
Thanks John!
I thought there might be an issue if no rows were returned, but it works great, thanks!
Ron
|
|
|
|
|
how to add 7 days to my date filter
i want to add 7 days to date the user will select
select employee_Id from attendance where [date]='2005-01-10' + 07
Ahmed hassan
|
|
|
|
|
Are you using SQL Server? If so, look at DateAdd.
|
|
|
|
|
try with following Query
select employee_Id from attendance where [date]= dateadd(day,7,'2005-01-10')
smile
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
