Re: SQL and Images - Database Discussion Boards

First Prev Next

Re: creating view for Month filter facility [modified]

Krish - KP13-Feb-07 2:59

Krish - KP

13-Feb-07 2:59

while fetching from SQL Server it is possible, to filter for a specific month & year.

for example
if the modify_dt is the column with datetime datatype
where clause for fetching this can be
1. WHERE modify_dt BETWEEN <frmdt> AND <enddt>
2. WHERE month(modify_dt) = <mth> AND year(modify_dt) = <yr>
if is for previous month (current month is Feb then prev. is Jan)
WHERE month(modify_dt) = month(getdate) - 1
AND year(modify_dt) = year(getdate())

-- modified at 22:45 Tuesday 13th February, 2007

Re: creating view for Month filter facility

rrrriiizz13-Feb-07 19:36

rrrriiizz

13-Feb-07 19:36

Thanks For U r Reply. but i am declaring that month fields as nvarchar, am also uploaod all the databases from excel to Sql.
Now is it Possible to get Last month Details?

That table contain 42 fields and also No of records in it is nearly 850. Please help me to avoid olderdata's getting repeated. only i want last month Modified Records. Please tell me any other way to solve this Issue. Sniff | :^)

..

Thank You

Re: creating view for Month filter facility

Krish - KP15-Feb-07 3:14

Krish - KP

15-Feb-07 3:14

does the data belongs to same year ? if so ....
In SQL datepart(m, getdate()) returns current month number
where clause can be used
MonthField = datepart(m, getdate()) - 1

explicit way of triming string parameter

firestoper12-Feb-07 20:16

firestoper

12-Feb-07 20:16

Hi Guru's

I know how to trim varchar parameters ltrim(rtrim(@parameter)) but is there more explicit way of doing this? e.g. vb.net string.trim()

Thanks
Dom Wink | ;)

Re: explicit way of triming string parameter

Sylvester george13-Feb-07 0:36

Sylvester george

13-Feb-07 0:36

Good. Thinking beyond the SQL Server 2000 Professionals

Regards,
Sylvester G
Senior Software Engineer
Xoriant Solutions
sylvester_g_m@yahoo.com

Re: explicit way of triming string parameter

firestoper13-Feb-07 14:40

firestoper

13-Feb-07 14:40

Thanks, I guess its how this thing works.

Wink | ;)

SQL and Images

Illegal Operation12-Feb-07 19:11

Illegal Operation

12-Feb-07 19:11

I would like to find out how to insert images into a SQL table? I have an ID (int), Description(text) and Image(image).

Please help, I want to use a stored procedure to insert the data.

Thanks!

Illegal Operation

Making Computer Software Talk

Re: SQL and Images

Pete O'Hanlon12-Feb-07 22:42

Pete O'Hanlon

12-Feb-07 22:42

You just use the Image type as a parameter in your stored procedure. Then you can insert the information to your hearts content.

the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer

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

Trouble using DTSWizard.exe to Import data into SQL Database

9llla12-Feb-07 13:47

9llla

12-Feb-07 13:47

Hello,

I am using the DTSWizard.exe to import data into a SQL Server database from text files.

I have performed this operation several times before without incident. The database is running and by all accounts seems to be functioning fine. However, for some unknown reason, now when I try to select the database as the destination for my imported data, I get the error message pasted at the bottom of the page. Does anyone have advice for how I should proceed?

Your help would be greatly appreciated.

ERROR MESSAGE:

TITLE: SQL Server Import and Export Wizard
------------------------------

Could not retrieve table list.

------------------------------
ADDITIONAL INFORMATION:

One or more errors occurred during processing of command.
File activation failure. The physical file name "C:\PotentialBenchmarks\Benchmark Builder\benchmarkBuilder_v1d\benchmarkBuilder_v1d\RPU_log.ldf" may be incorrect.
Unable to open the physical file "C:\PotentialBenchmarks\Benchmark Builder\benchmarkBuilder_v1d\benchmarkBuilder_v1d\RPU_log.ldf". Operating system error 5: "5(Access is denied.)".
Unable to open the physical file "C:\PotentialBenchmarks\Benchmark Builder\benchmarkBuilder_v1d\benchmarkBuilder_v1d\RPU.mdf". Operating system error 5: "5(Access is denied.)". (Microsoft SQL Native Client)

------------------------------
BUTTONS:

OK
------------------------------

Re: Trouble using DTSWizard.exe to Import data into SQL Database

andyharman12-Feb-07 23:03

andyharman

12-Feb-07 23:03

I've not seen that before, but it sounds like permissions on the physical log and data files (or the folder they live in).
Check that the files can be accessed by the account that is used by SQL Server service to log on (see appropriate SQL Server service properties in "SQL Server Configuration Manager -> SQL Server Services".
Hope that helps.
Andy

rename the existed excel worksheet name

JaneQuestion12-Feb-07 13:33

JaneQuestion

12-Feb-07 13:33

Hello everyone,

In our project, we created a SSIS package to load data from Excel into SQL2005 database.

Right now, I want to write code (whatever is vb.net, Vbscrip, or storedprocedure code) to change the existed excel worksheet name. If anyone can give me any suggestion with it, I will really appreciate it.

Jane

Re: rename the existed excel worksheet name

Krish - KP12-Feb-07 16:55

Krish - KP

12-Feb-07 16:55

hi,

if is just for renaming the sheet name below code will work
objWorkSheet.Name = "mysheet1"
which i've tried in VB6 & VBA.

KP

Connection to Internal Access DB from External Host

Aaron S.12-Feb-07 11:03

Aaron S.

12-Feb-07 11:03

Scenario: A company has an internal Access database located on a server within their network. They would like to have a small read/write asp.net page within their website located a Hosting Server outside their network.

Question: Is it possible to setup a connection string from an external web application to an internal Access Database?

Any help would be greatly appreciated.

A Sandoval

Re: Connection to Internal Access DB from External Host

Rob Graham12-Feb-07 11:41

Rob Graham

12-Feb-07 11:41

The simple answer is no.

how do select 10 special records

mehran.asg12-Feb-07 10:09

mehran.asg

12-Feb-07 10:09

hi.
I want to select,for example, records from no 10 to 20.
I have no id column or ordered column .
i want this for paging in asp.net grid fro a table with one million records.
I wan retrive records 10 by 10

Re: how do select 10 special records

Christian Graus12-Feb-07 10:19

Christian Graus

12-Feb-07 10:19

A common way to do this is to do nested selects. If you want records from number 10, and you want 10 of them, you select the top 20 records, then you do a select on the result set of that query, which asks for the top 10, ordered in descending order. Finally, you want to select this in ascending order, if that's the order you want.

Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog

The multi-part identifier "..." could not be bound.

Quecumber25612-Feb-07 8:27

Quecumber256

12-Feb-07 8:27

I’m writing the following stored procedure:

USE Employees;
GO
IF OBJECT_ID('mrmsp_RetrieveEmployeeForEditting','P') IS NOT NULL
DROP PROCEDURE mrmsp_RetrieveEmployeeForEditting;
GO
CREATE PROCEDURE mrmsp_RetrieveEmployeeForEditting
@varEmployeeID INT,
@varTitleID INT OUTPUT,
@varDeptID INT OUTPUT,
@varLName VARCHAR(25) OUTPUT,
@varFName VARCHAR(15) OUTPUT,
@varMI VARCHAR(1) OUTPUT,
@varAddrTypeID INT OUTPUT,
@varAddress1 VARCHAR(40) OUTPUT,
@varAddress2 VARCHAR(40) OUTPUT,
@varCity VARCHAR(40) OUTPUT,
@varStateID INT OUTPUT,
@varZip VARCHAR(10) OUTPUT
AS
SELECT tblEmployees.[EmployeeID], tblEmployees.[TitleID],
tblEmployees.[DeptID], tblEmployees.[LName],
tblEmployees.[FName], tblEmployees.[MI],
tblAddresses.[AddressTypeID], tblAddresses.[Address1],
tblAddresses.[Address2], tblAddresses.[City],
tblAddresses.[StateID], tblAddresses.[ZipCode]
FROM Employees.dbo.tblEmployees
INNER JOIN Employees.dbo.tblAddresses
ON Employees.dbo.tblEmployees.EmployeeID =
Employees.dbo.tblAddresses.EmployeeID
WHERE Employees.dbo.tblEmployees.EmployeeID = @varEmployeeID
AND Employees.dbo.tblEmployees.Deleted = 0
ORDER BY Employees.dbo.tblEmployees.EmployeeID
SET @varTitleID = tblEmployees.[TitleID]
SET @varDeptID = tblEmployees.[DeptID]
SET @varLName = tblEmployees.[LName]
SET @varFName = tblEmployees.[FName]
SET @varMI = tblEmployees.[MI]
SET @varAddrTypeID = tblAddresses.[AddressTypeID]
SET @varAddress1 = tblAddresses.[Address1]
SET @varAddress2 = tblAddresses.[Address2]
SET @varCity = tblAddresses.[City]
SET @varStateID = tblAddresses.[StateID]
SET @varZip = tblAddresses.[ZipCode];

When I run the check routine it says there are no errors in the T-SQL statement, but when I try to run the SQL to place the stored procedure into the database I get the following errors:

Msg 4104, Level 16, State 1, Procedure mrmsp_RetrieveEmployeeForEditting, Line 28

The multi-part identifier "tblEmployees.TitleID" could not be bound.

Msg 4104, Level 16, State 1, Procedure mrmsp_RetrieveEmployeeForEditting, Line 29

The multi-part identifier "tblEmployees.DeptID" could not be bound.

Msg 4104, Level 16, State 1, Procedure mrmsp_RetrieveEmployeeForEditting, Line 30

The multi-part identifier "tblEmployees.LName" could not be bound.

Msg 4104, Level 16, State 1, Procedure mrmsp_RetrieveEmployeeForEditting, Line 31

The multi-part identifier "tblEmployees.FName" could not be bound.

Msg 4104, Level 16, State 1, Procedure mrmsp_RetrieveEmployeeForEditting, Line 32

The multi-part identifier "tblEmployees.MI" could not be bound.

Msg 4104, Level 16, State 1, Procedure mrmsp_RetrieveEmployeeForEditting, Line 33

The multi-part identifier "tblAddresses.AddressTypeID" could not be bound.

Msg 4104, Level 16, State 1, Procedure mrmsp_RetrieveEmployeeForEditting, Line 34

The multi-part identifier "tblAddresses.Address1" could not be bound.

Msg 4104, Level 16, State 1, Procedure mrmsp_RetrieveEmployeeForEditting, Line 35

The multi-part identifier "tblAddresses.Address2" could not be bound.

Msg 4104, Level 16, State 1, Procedure mrmsp_RetrieveEmployeeForEditting, Line 36

The multi-part identifier "tblAddresses.City" could not be bound.

Msg 4104, Level 16, State 1, Procedure mrmsp_RetrieveEmployeeForEditting, Line 37

The multi-part identifier "tblAddresses.StateID" could not be bound.

Msg 4104, Level 16, State 1, Procedure mrmsp_RetrieveEmployeeForEditting, Line 38

The multi-part identifier "tblAddresses.ZipCode" could not be bound.

I assume the SQL statement can’t bind the data from the indicated columns into the variables in the stored procedure.

What am I doing wrong?

Quecumber256

Re: The multi-part identifier "..." could not be bound.

Rob Graham12-Feb-07 8:42

Rob Graham

12-Feb-07 8:42

dbo.tblEmployees.TitleID if you use multipart identifiers, you have to supply all three parts...

Re: The multi-part identifier "..." could not be bound.

Rob Graham12-Feb-07 8:59

Rob Graham

12-Feb-07 8:59

Actually, I think the syntax is wrong (you don't want the SET business at all)

Select @varTitlID = tblEmployees.[TitleID],@varDeptID = tblEmployees.[DeptID]...
FROM Employees.dbo.tblEmployees
INNER JOIN Employees.dbo.tblAddresses
ON Employees.dbo.tblEmployees.EmployeeID =
Employees.dbo.tblAddresses.EmployeeID
WHERE Employees.dbo.tblEmployees.EmployeeID = @varEmployeeID
AND Employees.dbo.tblEmployees.Deleted = 0

The 'order by' is not needed, since the query must have only one result row to work at all.

Re: The multi-part identifier "..." could not be bound.

Quecumber25612-Feb-07 9:02

Quecumber256

12-Feb-07 9:02

Thank you. I'm going to give it a try.

Quecumber256

gridview filtering using dropdownlist and textbox

bernie_01128-Feb-07 22:20

bernie_011

28-Feb-07 22:20

i have drpdownlist,textbox and a gridview and i have one table in my database named table1. It contains 2 columns the Lastname and FirstName, I will just add the items on my dropdown, The DropDOwn items are "Last name" and the value is lastName and the other one is the "First Name" and the value is firstName, If i select the "Last Name" on the dropdown the firstName will be entered in textbox or else the gridview will display nothing. If i select "Last Name" and for example i enter my last Name data will display.
Thanks

Bernie

Sql Server query needed

Saira Tanwir12-Feb-07 4:43

Saira Tanwir

12-Feb-07 4:43

hi
i'm trying to change the Null, Not Null constraint on a column.
do u know the query to do this??

also tell me, can the primary key of a table be changed??
i have a table in the DB with a primary key defined. i want to only remove the primary key constraint of the column and assign the primary key constraint to another column in the table.

How can i do this. plz do tell the query.

Saira

Re: Sql Server query needed

Parwej Ahamad12-Feb-07 6:07

Parwej Ahamad

12-Feb-07 6:07