|
Is it possible to use multiple Like statments within a SQL statement? I have a page where users can query data based on two fields. The user then has the option to export the results to excel, via a link. If the user does not select any criteria then the fields default to %. When I trie to run the SQL statement it doesnt seem to like the %. Please help!!
DocAttach_Summary_Excel.asp?HMO=%&Doc_Attach=%
<%
Response.ContentType = "application/vnd.ms-excel"
Dim THMO
Dim TDoc_Attach
THMO = request.querystring("HMO")
TDoc_Attach = request.querystring("Doc_Attach")
%>
<% set objconn=server.createobject("adodb.connection")
connpath="DBQ=" & server.mappath("/fpdb/ClaimMart_Access.mdb")
objconn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & connpath
set objrs=objconn.execute("SELECT * FROM Tbl_DocAttached_Summary WHERE (HMO LIKE '" & THMO & "' AND DOC_ATTACHED_FLG LIKE '" & TDoc_Attach & "')")
%>
|
|
|
|
|
You have a hugh security risk in your code. You are passing though a query string value directly to the SQL statement without it being sanitised. If the query string should contain, say, an apostrophe then that will change the meaning of the SQL Statement. If after the apostrophe it contains other SQL text then it could completely change the SQL statement's meaning. I advise you to read about SQL Injection Attacks and tips on how to prevent them[^]
|
|
|
|
|
This is an internal website located on the companies intranet. I just created the website to make the data more available. With that being said, am I still at risk?
|
|
|
|
|
admoore77 wrote: With that being said, am I still at risk?
Statistically, most breaches of security are insider jobs.
|
|
|
|
|
hello
i was creating relationship betwween 2 tables with different data types for the primarykeys of the two tables.
One primary key had data type int and the other primary key had data typevarchar. when i tried to create relationship between the two table, Sql Server gave the error that both the primary key data types are different and hence it didn't create the relationship.
so what i want to know is that does the data types of the primary keys have to be the same to create relationship between the tables???
|
|
|
|
|
Saira Tanwir wrote: what i want to know is that does the data types of the primary keys have to be the same to create relationship between the tables???
If you want to create a relationship between two tables the type of the primary key on one table must match the foreign key on the other. The only time that two primary keys must match is when you are creating a one-to-one (or zero-to-one) join because the primary keys are also the foreign keys.
|
|
|
|
|
Yes.It should be same datatypes while creating a relationship between two tables.
Priya
|
|
|
|
|
Priya_2007 wrote: It should be same datatypes while creating a relationship between two tables
The OP was asking about two PRIMARY KEYS. The primary keys do NOT have to be the same, and often they are not the same in two related tables. The important part is the PRIMARY KEY on the parent table matches the type of the FOREIGN KEY on the child table. You only need to match both primary keys when you are creating a one-to-one or zero-to-one relationship.
|
|
|
|
|
hi folks
can anyoe guide me about how to generate script file programmatically??
Saira
|
|
|
|
|
Create a FileStream and write SQL statements to it.
|
|
|
|
|
i'll work on it and then be back if any problem occurrs.
till then
Goodbye
|
|
|
|
|
hi
i'm using the following query to create a One to One relatonship between two tables 'boys' and 'sports'
sql="ALTER TABLE boys ADD CONSTRAINT UK_sports_type UNIQUE(sports_type)";
i get the following error
column sports_type does not exist in the target table. Couldnot create constraint see previous errors
can u plz explain the meaning of this, too???
Saira
|
|
|
|
|
Saira Tanwir wrote: a One to One relatonship between two tables 'boys' and 'sports'
Are you sure that is what you want to do? It doesn't sound like a very useful relationship to me. What if two (or perhaps 11) boys play football?
Adding a unique constraint does not make a 1-to-1 relationship.
If you want to create a one-to-one relationship then generally both tables will share the same primary key (which also happens to be a foreign key to the other table too)
Saira Tanwir wrote: column sports_type does not exist in the target table. Couldnot create constraint see previous errors
can u plz explain the meaning of this, too???
Well, it means that the table boys does not contain a column called sports_type.
|
|
|
|
|
thanks for explaining the error.
Regards![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
Saira
|
|
|
|
|
May be It Doesnot Contain a Column SPorts_type for the Table Boys
Priya
|
|
|
|
|
Hi G. to the U.R.U.S.!
I have a table with the following fields
MessageID
MessageTitle
MessageText
I want to create an SP which allows me to:
1. add new message if there's no similar message on it
2. edit the message is theres existing message already.
Please advice
regards
Dom 
|
|
|
|
|
It's hard to know which of the fields you are intending to define as the control. If you just want to base the check on the message title, then the following will suffice:
IF Exists (SELECT MessageID FROM Message
WHERE MessageTitle = @Title)
BEGIN
UPDATE Message SET MessageText = @MessageText WHERE MessageTitle = @Title
END
ELSE
BEGIN
INSERT INTO Message (MessageTitle, MessageText) VALUES
(@Title, @MessageText)
END
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.
|
|
|
|
|
Hi John
Thanks for the help,
I tried the suggestion you've made but some weird thing comes up. I don't know where @message_id came from, I don't remember having it on the SP. any ideas?
Msg 201, Level 16, State 4, Procedure sp_altermessage, Line 0<br />
Procedure or Function 'sp_altermessage' expects parameter '@message_id', which was not supplied.<br />
|
|
|
|
|
OK - my names Pete not John. John's the name of somebody I've quoted. (Note to self, change sig).
Secondly, you need to declare @message_id.
CREATE PROCEDURE dbo.PostMessage
@Title NVARCHAR(50),
@MessageText NVARCHAR(200),
@message_id INT OUT
AS
SET NOCOUNT ON
IF Exists (SELECT @message_id = MessageID FROM Message WHERE MessageTitle = @Title)
BEGIN
UPDATE Message SET MessageText = @MessageText
WHERE MessageTitle = @Title
END
ELSE
BEGIN
INSERT INTO Message (MessageTitle, MessageText) VALUES (@Title, @MessageText)
SET @message_id = SCOPE_IDENTITY()
END
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.
|
|
|
|
|
woot woot! :->
Thanks Pete
Dom
|
|
|
|
|
Hi,
Add when that does not exist or edit if exists, but what are you checking with message title column or message text column???
Gautham
|
|
|
|
|
Hi Gautham,
I'm checking the message title, picture this as a message board, if there's existing message title this means that that user is updating / posting on the message text, but if theres no existing title, this means the user intends to add another set of message.
btw im using messageID as an output (scope_identity) to reference the lates message that was added.
Thanks
regards
Dom
|
|
|
|
|
Hi Guru's
Im somehow stuck with the procedure I made, can you help me out with this, its a simple select but I put it on a declared variable.
Here's the code
SET ANSI_NULLS ON<br />
SET QUOTED_IDENTIFIER ON<br />
GO<br />
<br />
alter PROCEDURE trc_spVisitorPath<br />
(<br />
<br />
@visitorID int = 0<br />
<br />
)<br />
<br />
as<br />
begin<br />
declare @Sel_Pageview varchar(255)<br />
<br />
set @Sel_Pageview = <br />
'select <br />
convert(char(11),DateEntered) as ''Date Entered'', <br />
browser,<br />
platform,<br />
MajorVersion,<br />
MinorVersion<br />
from<br />
visitors <br />
where <br />
visitorID = ' + @visitorID<br />
<br />
end<br />
<br />
begin<br />
declare @Sel_Visitor varchar(255)<br />
<br />
set @Sel_Visitor = <br />
'select <br />
convert(char(13),DateEntered) as ''Date Entered'',<br />
PageName as ''Page Name''<br />
where<br />
visitorID = ' + @visitorID<br />
end<br />
<br />
exec(@Sel_Pageview)<br />
exec(@Sel_Visitor)
I'm getting this error after I run it
Msg 245, Level 16, State 1, Procedure trc_spVisitorPath, Line 13<br />
Conversion failed when converting the varchar value 'select <br />
convert(char(11),DateEntered) as 'Date Entered', <br />
browser,<br />
platform,<br />
MajorVersion,<br />
MinorVersion<br />
from<br />
visitors <br />
where <br />
visitorID = ' to data type int.
thanks
Dom
|
|
|
|
|
Why are you setting these up as strings and then executing them? You know that only the results from the final select will be returned?
The error comes from the fact that you have a string that you are trying to add (+) to an integer value (@VisitorId).
Why are you not just doing:
select
convert(char(11),DateEntered) as 'Date Entered',
browser,
platform,
MajorVersion,
MinorVersion
from
visitors
where
visitorID = @visitorID
etc...
|
|
|
|
|
I'm just trying to shoot the moon.
Thanks for the advice Paddy!
regards
Dom;)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
