Re: Basic SQL question - Database Discussion Boards

ganesamoorthidhayalan4-Jun-07 2:20

4-Jun-07 2:20

cursor means to select the set of record, and mainpulate that record and stored in againg to the database.

for example

declare cursor s1 as select * from emp where salary>1000;

if s1 is the cursor name. The cursor having the records in salary is greater than 1000. we can trace each record and to manipulate the record and to again store to database.

Homework alert

leckey4-Jun-07 4:06

leckey

4-Jun-07 4:06

Based on your last several posts it looks like you are wanting people to answer your homework questions. Use Google for your broad questions.

__________________
Bob is my homeboy.

Re: Homework alert

originSH4-Jun-07 5:19

originSH

4-Jun-07 5:19

This is why most of my answers involve simply a google search lol, unless a question is specific and indicates the author has atleast looked for information and a soltuon of course Smile | :)

Re: Homework alert

leckey4-Jun-07 9:55

leckey

4-Jun-07 9:55

I think that's fine for maybe the first or second post, but I think this user has been warned before.

__________________
Bob is my homeboy.

SQL 2000 Reporting Services - Blank pages while rendering matrix in PDF format

Ravindranath Kini3-Jun-07 19:07

Ravindranath Kini

3-Jun-07 19:07

I am using a matrix control in a report containing 2 subreports. The matrix control is present in one of the subreports. When I export the full report into the PDF,if the matrix are rendered in a single page, the report is fine and there are no blank pages. But when the number of columns exceeds the first page and moves into the second page then there are 3 blank pages in the report for every page with content. The number of blank pages after each page with content is equal to the number of times the matrix would scroll into a new page.

This problem occurs only when I try to generate the whole report in PDF. No blank pages appear when I render subreports into PDFs individually.

I am using SQL Reporting Services 2000 SP1.

Thanks in advance.

-- Ravi

DTS Package..??

Xandip3-Jun-07 17:23

Xandip

3-Jun-07 17:23

Hi Guys..

Can anyone tell me why this DTS Package is used for, and how to implement it..???

The name is Sandeep

Basic SQL question

eggie53-Jun-07 14:42

eggie5

3-Jun-07 14:42

I have some code (C#) that runs an SQL update query that sets the value of a column to what the user passes. So, this causes an error when anything the user passes in has a ' character in it. I'm sure there's other characters that'll break it too. So, I was wondering, how do I get around this? Is there some commonly accepted regex pattern that will make the value safe to run in an SQL query? How can I take care of any values that need to be escaped?

I'm not using any fancy ado.net objects:

string sql= [whatever the user passes in]

SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionString].ToString());
        connection.Open();

        SqlCommand command = connection.CreateCommand();
        command.CommandType = CommandType.Text;
        command.CommandText = sql;


        try
        {
            int result = command.ExecuteNonQuery();

            if (result != 1)
            {
                Response.StatusCode = 500;
                Response.Write("The file has been uploaded, but we could not update the DB");
                Response.End();
            }
        }
        catch (InvalidOperationException)
        {
            Response.Clear();
            Response.Write("error");
            Response.StatusCode = 500;
            Response.End();
        }

        connection.Close();

/\ |_ E X E GG

Re: Basic SQL question

Arun.Immanuel3-Jun-07 14:51

Arun.Immanuel

3-Jun-07 14:51

You have to use either Parameters in the update command.

But the best practice is to pass the users input
to a "STORED PROCEDURE" and let the SP do the job.

Regards,
Arun Kumar.A

Re: Basic SQL question

Christian Graus3-Jun-07 14:53

Christian Graus

3-Jun-07 14:53

The problem is that ' ends your quotes for your string value. In fact, if you typed this "value' GO drop database mydatabase GO", you'd find that the SQL after the ' GO is going to execute. You need to read the articles on SQL injection attacks that exist here on CP.

The short answer is to replace ' with ''. But, please read the article, your code is not safe.

Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )

Re: Basic SQL question

eggie53-Jun-07 15:00

eggie5

3-Jun-07 15:00

Can you recommend a good one?

/\ |_ E X E GG

Re: Basic SQL question

Pete O'Hanlon3-Jun-07 23:46

Pete O'Hanlon

3-Jun-07 23:46

This[^] is the best article for it on CP.

Deja View - the feeling that you've seen this post before.

Mofify a colunm from Nvarchar(50) to DateTime

steve_rm3-Jun-07 9:27

steve_rm

3-Jun-07 9:27

Hello,

VS 2005

I have a column that is a currently set as nvarchar(50) and is called DateEmployed.
There are over a hundred rows that contain dates which is in nvarchar format.

This column now needs to be changed to a DateTime datatype. (Don't ask me it was not set
to a dateTime when this was first designed - I wasn't here)

However, I have to change this column to a DateTime without destroying the data.

Is there any easy way to write some script or use studio management to change this.

Currently the data is displayed like this in this column dd/MM/yyyy i.e. 25/8/2007.

The method I am using to try and change this is by going to studio management clicking
modify on the column and changing the datatype from a nvarchar(50) to a DateTime.

I get this following error message:
- Unable to modify table.
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

Any suggestions would be most grateful,

Thanks,

Steve

Re: Mofify a colunm from Nvarchar(50) to DateTime

Mike Dimmick3-Jun-07 12:00

Mike Dimmick

3-Jun-07 12:00

It's most likely that the data is being interpreted in US English (MM/dd/yyyy) format.

You could try using a query window (right-click the database and select New Query) and using the following

SET LANGUAGE British
 
ALTER TABLE table
  ALTER COLUMN column datetime

where table is the name of the table and column the name of the column to alter.

If that doesn't work, try adding a new column of type datetime and then using an UPDATE statement with SET newcolumn = CONVERT( datetime, oldcolumn, 103 ) to do the conversion, then drop the old column.

Stability. What an interesting concept. -- Chris Maunder

Access of Office 2007 as a DB in .NET

Tzumer Edo3-Jun-07 4:27

Tzumer Edo

3-Jun-07 4:27

Hi guys,

I have a website that works with an Access DB,
now, I've moved it to a computer that has "only"
office 2007, and when the site tries to open a connection
to the DB, I get :
'Could not find installable ISAM.' message

Anyone ?

Regards,
Tzumer Edo.

Re: Access of Office 2007 as a DB in .NET

originSH3-Jun-07 23:30

originSH

3-Jun-07 23:30

It could be that you connection string is wrong or the MDAC installation o nthat machine is b0rked.

Theres plenty of hits to help you debug on google:

http://www.google.co.uk/search?hl=en&q=Could+not+find+installable+ISAM.&meta=[^]

Also you cna check your connection string is correct here:

http://www.connectionstrings.com/[^]

Specificly:

http://www.connectionstrings.com/?carrier=access2007[^]

Adding a new column is DBNULL

steve_rm2-Jun-07 4:06

steve_rm

2-Jun-07 4:06

Hello,

VS 2005

I am developing the database application. This is a live database and is being used by the customer.

I am to release a new version and I had to add new columns to fit the requirements DateStarted (DateTime), and TotalHours(Int) into a database table.

There are already over a 1000 rows in this table. When the customer wants to look at a record in this table and insert the value into the text boxes (Front-end), when it gets to the DateStarted or TotalHours it comes up with a error message:
"The value for column 'DateStarted' in table 'IncidentTask' is DBNull."

The method for inserting is:
Me.dtDateStarted.Value = Me.DsIncidentsControl.IncidentTask(0).DateStarted

What are the possible solutions to this problem? Would it mean checking for a DBNULL before displaying in the textboxes? Or updating the new column rows with an date:
UPDATE IncidentTask SET DateStarted = '1/1/2005' WHERE (DateStarted IS NULL)

Many thanks for any suggestions,

Steve

Re: Adding a new column is DBNULL

Rob Graham2-Jun-07 5:53

Rob Graham

2-Jun-07 5:53

Or you could specify a default value for the date and total hours in the alter table script that adds the new columns...the DateStarted should probably not allow nulls in the first place.

Alter table [IncidentTask] add Datestart Datetime DEFAULT 20050101 NOT NULL, TotalHours INT DEFAULT 0 NOT NULL

Re: mysql vs sqlserver

Rob Graham2-Jun-07 4:55

Rob Graham

2-Jun-07 4:55

Please read "how to get an answer to your question" before posting questions you should have answered for yourself.
Google is your friend: http://www.google.com/search?q=mysql+vs+sql+server+2005[^]

SQL variable in MS Access

Blue_Boy2-Jun-07 2:25

Blue_Boy

2-Jun-07 2:25

Can I declare variables in MS Access same as we can do in T-SQL.

Example:

declare @i  as Integer<br />
set @i=1;<br />
<br />
select tablename.ColName where Col.ID=@i

I Love SQL

Re: SQL variable in MS Access

Krish - KP2-Jun-07 2:30

Krish - KP

2-Jun-07 2:30

VBA is used in MS Access so this usage is not possible.

Regards
KP

Re: SQL variable in MS Access

Blue_Boy2-Jun-07 2:50

Blue_Boy

2-Jun-07 2:50

Thanks , I will try to find another solution without using t-sql in ms access.

Regards

I Love SQL

Re: SQL variable in MS Access

Dave Kreskowiak2-Jun-07 9:28

Dave Kreskowiak

2-Jun-07 9:28

Access doesn't support true stored procedures like this. Anything you do like this has to be implemented in your own application code.

A guide to posting questions on CodeProject[^]

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

Re: SQL variable in MS Access

Ilya Verbitskiy5-Jun-07 4:49

Ilya Verbitskiy

5-Jun-07 4:49

Access doesn't support T-SQL, not only stored procedures.

Select statement......,

HemMagesh1-Jun-07 20:11

HemMagesh

1-Jun-07 20:11

SELECT dbo.tblEmployeeMaster.empId, dbo.tblEmployeeMaster.empName, dbo.tblTimeCardmain.carddate,
dbo.tblTimeCardmain.reportingtime, dbo.tblTimeCardmain.leavingtime, dbo.tblsalarydetails.Whrs,
dbo.tblTimeCardmain.shift, dbo.tblTimeCardmain.projectid FROM dbo.tblEmployeeMaster INNER JOIN
dbo.tblTimeCardmain ON dbo.tblEmployeeMaster.empId = dbo.tblTimeCardmain.empid AND
dbo.tblEmployeeMaster.projectId = dbo.tblTimeCardmain.projectid INNER JOIN
dbo.tblsalarydetails ON dbo.tblTimeCardmain.empid = dbo.tblsalarydetails.EmpId INNER JOIN
dbo.tblMonthHourDetails ON dbo.tblTimeCardmain.empid = dbo.tblMonthHourDetails.EmpId AND
dbo.tblTimeCardmain.carddate = dbo.tblMonthHourDetails.SDate AND
dbo.tblTimeCardmain.projectid = dbo.tblMonthHourDetails.ProjectId
WHERE (dbo.tblTimeCardmain.carddate = CONVERT(DATETIME, '2007-06-04 00:00:00', 101)) AND
(dbo.tblTimeCardmain.app1 = 1) AND (dbo.tblTimeCardmain.projectid = 100)

Hi i amn't well in sqlserver so hlp me...,
See the above query in that i am checkng "dbo.tblTimeCardmain.carddate = dbo.tblMonthHourDetails.SDate"...,
if both dates are equal then those dates cant display how to do that?
same time it want to check other conditions also...,
Plz hlp me...,

Mag

Re: Select statement......,

Krish - KP2-Jun-07 2:28

Krish - KP

2-Jun-07 2:28

the table structure is not fully understood.
however, from remove join on date ...
"dbo.tblTimeCardmain.carddate = dbo.tblMonthHourDetails.SDate"
and use it in where with not equal
"dbo.tblTimeCardmain.carddate <> dbo.tblMonthHourDetails.SDate"

check whether is solves

Regards
KP

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