REPLACE function doesn't always work - Database Discussion Boards

Re: SQL Execution Error Failed to Convert from dateTime to TimeSpan

AAGTHosting25-Mar-08 11:40

25-Mar-08 11:40

I looked in the wizard and I can't find a place to select a dataType. One of my table columns is a UDT named time that I created from dateTime. I did not create an assembly. Also the dateTime datatype that I pass in is a dateTime that adds a timespan to change the startTime.

Is it possible that the time dataType could cause this? I also increment startTime with timeSpan which is a dateTime dataType in VB.NET. Could this cause the problem?

direct link to install SQL Server 2005 Express

KrisnNala25-Mar-08 8:59

KrisnNala

25-Mar-08 8:59

Hi please can someone help me.

I've got an application that I've written in VB 2008 Express. To Install the App. I'm using Inno Setup 5.

I would like to have a direct link to the SQL Server 2005 express. So, if the Install checks and finds that the Sql Server Express is not installed then if the user clicks ok it goes direct to installation. I've found the link for .Net Framework 2.0 but no luck for SQL Server Express 2005.

Any help would be greatly appreciated.

Thanks alot Roll eyes | :rolleyes:

Kris MCP

Re: direct link to install SQL Server 2005 Express

Dave Kreskowiak25-Mar-08 10:00

Dave Kreskowiak

25-Mar-08 10:00

You mean this[^]? Or this[^]??

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Re: direct link to install SQL Server 2005 Express

KrisnNala25-Mar-08 22:10

KrisnNala

25-Mar-08 22:10

Hi Dave

Gather you're a bit confused sorry about that. I need a direct link to SQLServer 2005 express.

I've tried going to the web site but when you click to download it shows the error address. They've obviously got problems.

Thanks alot

D'Oh! | :doh:

Kris MCP

Re: direct link to install SQL Server 2005 Express found Solution

KrisnNala26-Mar-08 2:18

KrisnNala

26-Mar-08 2:18

Hi Dave

Just to let you know I've finally found the link it's "http://go.microsoft.com/fwlink/?LinkId=65212">http://go.microsoft.com/fwlink/?LinkId=65212

Took a bit of finding so if anyone ever needs it I've pasted it in.

Thanks for your help. Big Grin | :-D

Kris MCP

Re: direct link to install SQL Server 2005 Express

Dave Kreskowiak26-Mar-08 13:34

Dave Kreskowiak

26-Mar-08 13:34

Strange. All the links on those pages worked for me. I clicked on each one before I posted the message.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Insert bulk data

zafax_25-Mar-08 7:45

zafax_

25-Mar-08 7:45

Microsoft SQL 2005
---------------------
I created a table name items.
which has to be updated with multiple rows at once, only the ID is Unique.
(more details)
SO when i updated a record there would be six rows with the same items but with different ID.
GIVEN BELOW IS A SAMPLE (6 times)
---------------------------------
ID | Name | Model| Price
---------------------------------
0001 MotorCar Mazda 62000.00
0002 MotorCar Mazda 62000.00
0003 MotorCar Mazda 62000.00
0004 MotorCar Mazda 62000.00
0005 MotorCar Mazda 62000.00
0006 MotorCar Mazda 62000.00

I guess a range(eg.1 to 15) is required to validate the number of time You may required to update a record.

Any suggestion on this would be great indeed.

thanks.

so much of happy ending...

REPLACE function doesn't always work

adi.rusu25-Mar-08 5:56

adi.rusu

25-Mar-08 5:56

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

Re: REPLACE function doesn't always work

PIEBALDconsult25-Mar-08 6:29

PIEBALDconsult

25-Mar-08 6:29

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>, '''', '`')

Re: REPLACE function doesn't always work

adi.rusu25-Mar-08 6:51

adi.rusu

25-Mar-08 6:51

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.

Re: REPLACE function doesn't always work

PIEBALDconsult25-Mar-08 13:12

PIEBALDconsult

25-Mar-08 13:12

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?

Re: REPLACE function doesn't always work

adi.rusu25-Mar-08 22:17

adi.rusu

25-Mar-08 22:17

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) WTF | :WTF:

. It's funny that I don't seem to find ’ (22) on my keyboard Poke tongue | ;-P

. Anyway thanks for your help Smile | :)

Insert Quert Creates problem

Syed Shahid Hussain24-Mar-08 21:38

Syed Shahid Hussain

24-Mar-08 21:38

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

Re: Insert Quert Creates problem

Christian Graus25-Mar-08 0:05

Christian Graus

25-Mar-08 0:05

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 )

Re: Insert Quert Creates problem

Vimalsoft(Pty) Ltd25-Mar-08 10:34

Vimalsoft(Pty) Ltd

25-Mar-08 10:34

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

Query Problem

Sadaf Naeem24-Mar-08 20:53

Sadaf Naeem

24-Mar-08 20:53

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

Re: Query Problem

Christian Graus24-Mar-08 21:02

Christian Graus

24-Mar-08 21:02

TRy using parameterised queries, then you can be sure that your data is going to be presented in a format your source understands.

Re: Query Problem

Sadaf Naeem24-Mar-08 23:10

Sadaf Naeem

24-Mar-08 23:10

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

Re: Query Problem

Christian Graus25-Mar-08 0:02

Christian Graus

25-Mar-08 0:02

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.

Re: Query Problem

Sadaf Naeem24-Mar-08 23:14

Sadaf Naeem

24-Mar-08 23:14

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

Re: Query Problem

Sadaf Naeem25-Mar-08 20:17

Sadaf Naeem

25-Mar-08 20:17

Thanks for ur response . I am very grateful 2 u

there r 2 problems now

how to create stored procedures in MS ACCESS 2007?
when I searched it over GOOGLE, there was a button to create stored procedure in the CREATE beside the query design and query wizard but that option is not available in the copy I am running on my pc?

secondly due to security risks some of the content is disabled which prevents me from updating the database such as password of a particular user when I run the update query but as soon as i enabled the content , it allows me to run the update query.

when i close the database the settings are again disable which restrict me to update my database through application. Confused | :confused:

I hv no clue how to overcome this problem

Kindly provide me with ur assistance. Thanks

Sadaf

How to get a list of temporary tables for a particular session.

Sallu174924-Mar-08 20:14

Sallu1749

24-Mar-08 20:14

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

Re: How to get a list of temporary tables for a particular session.

Mark J. Miller25-Mar-08 6:52

Mark J. Miller

25-Mar-08 6:52

Here's some links to a couple articles which should be helpful:

Tempdb usage by session id:
http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/tempdb/sql05vb039.mspx?mfr=true[^]

Controlling size of tempdb:
http://sqlserver2000.databases.aspfaq.com/why-is-tempdb-full-and-how-can-i-prevent-this-from-happening.html[^]

Of course, if you just want to find out if a specific temp table exists for the current session this always works really well:

<br />
IF OBJECT_ID(N'tempdb..#tempTableName') IS NULL BEGIN<br />
    -- take some action<br />
END<br />

Mark's blog: developMENTALmadness.blogspot.com

Asking for Help

EvilInside24-Mar-08 19:15

EvilInside

24-Mar-08 19:15

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

Re: Asking for Help

SimulationofSai24-Mar-08 20:29

SimulationofSai

24-Mar-08 20:29

SQL Server 2005 : Select * from sys.columns

SQL Server 2000 : Select * from syscolumns

SG
Cause is effect concealed. Effect is cause revealed.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.