Database Discussion Boards

Joe Stansfield15-Oct-12 10:48

15-Oct-12 10:48

Yea, from reading what you have said, I think my error may be in allowing a user to input a value into the query that is not a valid record (ie one that doesn't exist).

So would the best approach be to run a query to test if input value is a record, then if true, proceed to the query which returns the record?

The loop error is because of my attempting to sort a datagrid with linked data in it - if a null value is returned (ie no record), it gets caught in a loop and error because it can't sort a null record (along those lines).

Thanks for all your help, even though I haven't fixed the problem I am picking up some valuable tips!

Re: SQL Select Where query

fjdiewornncalwe15-Oct-12 11:06

fjdiewornncalwe

15-Oct-12 11:06

You shouldn't need to run 2 queries. You should just need to check the resulting dataset to see if you get any results. (ie. rowcount > 0)

I wasn't, now I am, then I won't be anymore.

Re: SQL Select Where query

Joe Stansfield16-Oct-12 0:08

Joe Stansfield

16-Oct-12 0:08

Thanks for all the help guys.

After reading what you have all posted and doing some more research I managed to solve the problem I was having.

The basic query I was running was sufficient, it turned out the problem was in an automatically created linked query. By fixing that I have got ride of the errors I was getting.

Re: SQL Select Where query

Eddy Vluggen16-Oct-12 0:12

Eddy Vluggen

16-Oct-12 0:12

Joe Stansfield wrote:
So would the best approach be to run a query to test if input value is a record, then if true, proceed to the query which returns the record?

The best approach is one without loops. Are you displaying the "linked record" in a separate (child) gridview?

Usually, we combine the tables in Sql, like this;

SQL

SELECT a.Field1
      ,a.Field2
      ,b.Field1 AS Field3
  FROM Employee AS a
  JOIN Department AS b ON a.DepartmentId = b.Id

This will generate a single resultset, and depending on the type of join, the database will fetch the employees with, those without a linked record, or both Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: SQL Select Where query

Joe Stansfield16-Oct-12 23:04

Joe Stansfield

16-Oct-12 23:04

Yea I have a linked record showing in a child view.

So from what I can gather the first query executes to fill the main record table, and then a second query populates the correct record in the grid view (and a few other text boxes on the form).

It is working now - but is it worth the time to write it as it should be? It won't affect any of my datasource bindings by combing it into a single query will it?

Re: SQL Select Where query

Eddy Vluggen16-Oct-12 23:37

Eddy Vluggen

16-Oct-12 23:37

Joe Stansfield wrote:
It is working now - but is it worth the time to write it as it should be?

It's already as it should be; if you have a separate grid for the children, you'll need a second query. Also explains why the second grid might get an empty resultset.

Joe Stansfield wrote:
It won't affect any of my datasource bindings by combing it into a single query will it?

It would! The net effect is that you only pull the data over the network-line once. You're using a different concept here - if you'd fetch all tables, you'd probably have fetched a lot of child-records that the user isn't going to use. That means that your current approach might be the more efficient one, depending on what the user does Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: SQL Select Where query

Member 952116716-Oct-12 22:41

Member 9521167

16-Oct-12 22:41

ITS BETTER U YO USE THE IMMEDIATE CONSTRAINT DURING THE CODING

Re: SQL Select Where query

Simon_Whale16-Oct-12 22:52

Simon_Whale

16-Oct-12 22:52

Please don't shout (capital letters are considered shouting!).

I would also refrain from using text speak as it upsets a lot of people on these forums.

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

Re: SQL Select Where query

Rohit Shrivastava18-Oct-12 7:56

Rohit Shrivastava

18-Oct-12 7:56

Hi Joe,

You can use following query:

SQL

SELECT ISNULL( (SELECT File_Name
  FROM Files
 WHERE File_Name = @FileName1),1)

This would solve your problem, in case there is no result, it will return you 1 otherwise the matching result.

Re: SQL Select Where query

Joe Stansfield15-Oct-12 1:20

Joe Stansfield

15-Oct-12 1:20

There are also a number of other columns for each row, I assume that I just populate the query with those separated by columns?

Re: SQL Select Where query

Eddy Vluggen15-Oct-12 1:25

Eddy Vluggen

15-Oct-12 1:25

Yup

SQL

SELECT Col1
      ,ISNULL(Col2, '1')
      ,Col3
      ,Col4
  FROM SomeTable
 WHERE SomeCondition

..or even nest another select, like this;

SQL

SELECT Col1
      ,ISNULL(Col2, '1')
      ,(SELECT 1) AS Col3
      ,Col4
  FROM SomeTable
 WHERE SomeCondition

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: SQL Select Where query

Nidhisha Biju15-Oct-12 1:05

Nidhisha Biju

15-Oct-12 1:05

SQL

SELECT isnull(File_Name,1) as FileName from tblname
WHERE (File_Name = @File_Name1)

Re: SQL Select Where query

Eddy Vluggen15-Oct-12 1:26

Eddy Vluggen

15-Oct-12 1:26

Darn, you're right Big Grin | :-D

I was returning the value as a VARCHAR, not an INTEGER - well spotted.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: SQL Select Where query

Aadhar Joshi16-Oct-12 20:47

Aadhar Joshi

16-Oct-12 20:47

Hi,
U can use following statment..

SQL

SELECT ISNULL(File_Name, '')
  FROM Files
 WHERE File_Name = @FileName1

because is is string so it should return blank

Re: SQL Select Where query

Hitesh R23-Oct-12 1:06

Hitesh R

23-Oct-12 1:06

Try the following query.

SQL

SELECT CASE WHEN COUNT(File_Name)=0 THEN 1 ELSE COUNT(File_Name) END as FileName from tblname
WHERE (File_Name = @File_Name1)

MS SQL didn't allow to store apostrophe( ' ) in name

Azher.Cybertech15-Oct-12 0:04

Azher.Cybertech

15-Oct-12 0:04

if v want to store name like Mohammed's then sql didn't allow to save data and give an error

Re: MS SQL didn't allow to store apostrophe( ' ) in name

Eddy Vluggen15-Oct-12 0:55

Eddy Vluggen

15-Oct-12 0:55

Only goes wrong if you concatenate strings, which shouldn't be done in the first place. Use a 'parameterized query';

using (cmd = new MySqlCommand("SELECT EmployeeName FROM Employees WHERE EmployeeId = @P1;"))
{
    cmd.Parameters.AddWithValue("@P1", Parameter1);
    m.ExecuteNonQuery();
}

Research the AddWithValue member.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: MS SQL didn't allow to store apostrophe( ' ) in name

Hitesh R23-Oct-12 1:08

Hitesh R

23-Oct-12 1:08

to store name like Mohammed's pass Mohammed''s then it will allow to save data.

Re: MS SQL didn't allow to store apostrophe( ' ) in name

Azher.Cybertech27-Oct-12 23:34

Azher.Cybertech

27-Oct-12 23:34

thenks for ur reply

Error Running SP [SOLVED]

jojoba201113-Oct-12 21:53

jojoba2011

13-Oct-12 21:53

Hi ,
When i run this Stored Procedure I get this error :

Msg 102, Level 15, State 1, Line 1<br />
Incorrect syntax near 'Pages'.

AND my SP is :

SQL

declare @ModuleSettings NVarChar(max) = N'<Article type=''Pages''><Ucl name=''~/UserControls/ModuleSettings/ArticleModuleSettings.ascx''></Ucl><Election>0</Election><IsPaging>False</IsPaging><PageSize>5</PageSize><PagingLocation>Top</PagingLocation><Title>توليد کنندگان مواد اوليه / Suppliers of raw materials</Title><ThumbNail location=''3''>True</ThumbNail><ContentTitle>True</ContentTitle><ContentAbbr>True</ContentAbbr><Icon address=''''>False</Icon><Category>429</Category><All><item align="right" visible="true" order="1" block="true" navigateUrl="~/Management/Modules/Details/">Title</item><item align="right" visible="true" order="2" block="true">Abbr</item><item align="left" visible="true" order="4" block="true">CreateDate</item><item align="right" visible="false" order="5" block="false">Content</item><item align="right" visible="false" order="6" block="false">Keyword</item><item align="right" visible="false" order="7" block="false">Category</item><item align="right" visible="false" order="8" block="false">File</item><item align="right" visible="false" order="9" block="false">Icon</item><item align="right" visible="false" order="10" block="false">writer</item><item align="right" visible="false" order="11" block="false">Source</item></All></Article>' 

declare @param nvarchar(max)= N'update [Management].[PageModules] set '
if 100 is not NULL
BEGIN
set @param = @param + '[AreaId]=''' + cast(100 as nvarchar(3)) + ''',' 
End
if @ModuleSettings is not NULL
BEGIN
set @param = @param + '[ModuleSettings]=''' + @ModuleSettings + ''',' 
END
if 0 is not NULL
BEGIN
set @param = @param + '[Election]=''' + cast(0 as nvarchar(3)) + ''',' 
End
if 'sdfg' is not NULL
BEGIN
set @param = @param + '[Title]=''' + 'sdfg' + ''',' 
End
if 418 is not NULL
BEGIN
set @param = @param + '[CategoryId]=''' + cast(418 as nvarchar(17)) + ''',' 
End
if NULL is not NULL
BEGIN
set @param = @param + '[DepartmentId]=''' + cast(NULL as nvarchar(9)) + ''',' 
End
if 38 is not NULL
BEGIN
set @param = @param + '[CultureId]=''' + cast(38 as nvarchar(5)) + ''',' 
End
if 15 is not NULL
BEGIN
set @param = @param + '[ModuleId]=''' + cast(15 as nvarchar(5)) + ''',' 
End
if 1 is not NULL
BEGIN
set @param = @param + '[PageId]=''' + cast(1 as nvarchar(9)) + ''',' 
End
set @param= substring(@param,0,len(@param))
set @param = @param + ' Where Id=''' + cast( 68 as nvarchar(17)) + ''' ' 

exec sp_executesql @param

modified 6-Nov-12 4:38am.

Re: Error Running SP

Eddy Vluggen14-Oct-12 3:06

Eddy Vluggen

14-Oct-12 3:06

Change this;

SQL

N'<Article type=''Pages''>

to this;

SQL

N'<Article type="Pages">

..and do the same for the "ucl name".

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: Error Running SP

Richard Deeming15-Oct-12 3:38

Richard Deeming

15-Oct-12 3:38

Building a dynamic query in SQL, even inside a stored procedure, will not prevent SQL injection[^].

Your example could easily be rewritten as a simple UPDATE statement. (NB: Constant values like 100 are never null!) If any of the new values are passed in as parameters, then your query is open to SQL injection.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Error Running SP

jojoba201115-Oct-12 4:52

jojoba2011

15-Oct-12 4:52

Hi
Please help ..
Solutions are not usefull!
but its SP and for example i fill info in that!

Re: Error Running SP [Solved]

jojoba20115-Nov-12 22:37

jojoba2011

5-Nov-12 22:37

Hi,
I my Self solved the problem via this strategy

Change the bellow code :

SQL

if @ModuleSettings is not NULL
BEGIN
set @param = @param + '[ModuleSettings]=''' + <code>@ModuleSettings</code> + ''',' 
END

With :

SQL

if @ModuleSettings is not NULL
BEGIN
set @param = @param + '[ModuleSettings]=''' + <code>replace (@ModuleSettings,CHAR(39),char(39)+CHAR(39))</code> + ''',' 
End

conclusion :
you should replace :
@ModuleSettings
with :
replace (@ModuleSettings,CHAR(39),char(39)+CHAR(39))

Bug in SQLBulkCopy? Truncates decimal digits after 26 records

Allan Watkins12-Oct-12 14:25

Allan Watkins

12-Oct-12 14:25

I believe I've found a very unexpected bug in SQLBulkCopy that took me a while to track down. My test program reads in records from a CSV file with exactly 1 column which is defined as a "numeric(19,5)" for statistical uploads.

Condition 1:
If the input file has 25 rows with the only value being the digit zero, followed by a number with a decimal (like '1.23456'), the data is imported perfectly.
Condition 2:
If the input file has 26 rows with the only value being the digit zero (or any non-decimal number), followed by a number with a decimal (like '1.23456'), the data is imported and the last (and any subsequent) rows have the field imported with truncated decimal digits (like '1.00000').
Condition 3:
If the input file has 26 rows and the first row is '0.0', all of the rows are imported perfectly.

I've checked the table/field definition for Condition 2 and it's the same as 1 and 3 so there's no manipulation of the field type (as far as I can tell).

Is this a bug or am I missing something?

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