|
I have a SQL query that is returning
modified 3-May-15 13:07pm.
|
|
|
|
|
You probably have an operator-precedence problem due to combining OR with AND -- I can never get it right myself, so I always use parentheses.
I also recommend not using sub-queries, especially with IN -- try a JOIN instead.
|
|
|
|
|
The only problem I see is how do I add the user and rights tables in to the query? There are no keys to access those tables.
|
|
|
|
|
I know there are a lot of posts but I finally understand and did what I was told to do. I broke the query up with parameters and I am still getting the security error. My code is below the with the parameters removed from the hard coded string, the calling code, and the implementing code:
The 3 classes with the SQL w/ with the parameters broken out, the calling code, and the implementing code:
Class with the parameters broken out:
public class MyParam
{
public string name { get; set; }
public string value { get; set; }
}
public class QueryContainer
{
string _query;
public List parameterList = new List();
public QueryContainer(string query) { _query = query; }
public string Query
{
get
{
return _query;
}
set { _query = value; }
}
}<pre>
The calling code:
<pre>
public int GetAccountSortByAccountCode(int account)
{
QueryContainer Instance = new QueryContainer("SELECT ac_sort_order FROM lkup_account_codes where ac_code = <a href="http:
MyParam myParam = new MyParam();
myParam.name = "@account";
myParam.value = account.ToString();
Instance.parameterList.Add(myParam);
return Convert.ToInt32(ExecuteScaler(Instance, 1));
}
<pre>
The implementing code:
<pre>
if (_connection == null || _connection.State == ConnectionState.Closed)
{
OpenConnection();
}
DbCommand command = _provider.CreateCommand();
command.Connection = _connection;
{
command.CommandText = Instance.Query;
command.CommandType = CommandType.Text;
foreach (var p in Instance.parameterList)
{
SqlParameter param = new SqlParameter(p.name, p.value);
command.Parameters.Add(param);
}
if (_useTransaction) { command.Transaction = _transaction; }
try
{
returnValue = command.ExecuteScalar();
}
catch (Exception ex)
{
if (ex is EntryPointNotFoundException)
throw ex;
RollBack();
LogBLL bll = new LogBLL();
bll.WriteErrorLog(ex);
_iserror = true;
}
<pre>
|
|
|
|
|
I know there are a lot of posts but I finally understand and did what I was told to do. I broke the query up with parameters and I am still getting the security error. My code is below the with the parameters removed from the hard coded string, the calling code, and the implementing code:
The 3 classes with the SQL w/ with the parameters broken out, the calling code, and the implementing code:
Class with the parameters broken out:
public class MyParam
{
public string name { get; set; }
public string value { get; set; }
}
public class QueryContainer
{
string _query;
public List<myparam> parameterList = new List<myparam>();
public QueryContainer(string query) { _query = query; }
public string Query
{
get
{
return _query;
}
set { _query = value; }
}
}
The calling code:
<pre>
public int GetAccountSortByAccountCode(int account)
{
QueryContainer Instance = new QueryContainer("SELECT ac_sort_order FROM lkup_account_codes where ac_code = <a href="http:
MyParam myParam = new MyParam();
myParam.name = "@account";
myParam.value = account.ToString();
Instance.parameterList.Add(myParam);
return Convert.ToInt32(ExecuteScaler(Instance, 1));
}
<pre>
The implementing code:
<pre>
if (_connection == null || _connection.State == ConnectionState.Closed)
{
OpenConnection();
}
DbCommand command = _provider.CreateCommand();
command.Connection = _connection;
{
command.CommandText = Instance.Query;
command.CommandType = CommandType.Text;
foreach (var p in Instance.parameterList)
{
SqlParameter param = new SqlParameter(p.name, p.value);
command.Parameters.Add(param);
}
if (_useTransaction) { command.Transaction = _transaction; }
try
{
returnValue = command.ExecuteScalar();
}
catch (Exception ex)
{
if (ex is EntryPointNotFoundException)
throw ex;
RollBack();
LogBLL bll = new LogBLL();
bll.WriteErrorLog(ex);
_iserror = true;
}
<pre>
|
|
|
|
|
Try something like this:
SELECT DISTINCT
dw.we_System_Key,
w.we_System
FROM
dod
INNER JOIN do_we_systems dw on d.dod = dw.dod
INNER JOIN lkup_we_systems w on dw.we_System_Key = w.we_System_Key
WHERE
dw.is_deleted = 0
AND
(
w.we_system != ''
OR
EXISTS
(
SELECT 1
FROM users_rights ur
INNER JOIN rights r ON r.rights_key = ur.rights_key
INNER JOIN users usr ON usr.username = ur.username
WHERE usr.username = 'RHale1'
AND r.rights_code = 'Non-Standard Test'
)
)
ORDER BY
w.we_system
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
holdorf wrote:
I have a SQL query that is returning
From where?
This looks weird like this; where did the rest of the question go?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi all, I have a function that receives a column name and it will return this column name when the function is called, if the column name is not null.
This function will be called in a stored proc inside of the Select statement. The function will return the name of a column entered as input and it will be used inside of the Select statement.
The following is my function
Create function GetName(@C nvarchar(50))
Returns nvarchar(50)
As
Begin
Declare @Col_Name nvarchar(50)<br />
IF (@Col IS NOT null)
Begin
Set @ColumName = @Col<br />
END
Return @Col_Name
End
I expect this function to return any input a user enters but it doesn't return anything and I'm not getting any errors. Also how do I call this function in my stored proc so that the column name is added to the select statement automatically. Any help is great, thanks.
modified 29-Apr-15 10:34am.
|
|
|
|
|
|
Hi, thank you all for responding. I decided to use a stored proc instead of a function and used Dynamic SQL to write my query.
Create Porocedure GetName
(
@column nvarchar(50)
@columnData nvarchar(50)
)
As
Begin
Declare @MyQuery nvarchar(300)
set @column= IF (@column IS NOT null AND @column ='TableA.SomeColumn')
Begin
Set @MyQuery = N'Select'+ TableA.[@column] + 'from dbo.TableA
(where TableA.Column1 LIKE' + @columnData + '+ ''%'' OR' + @columnData + 'IS null)'
END
End
EXEC sp_executesql;
I get no errors but it does not work. Any help will be greatly appreciated, thanks.
modified 29-Apr-15 19:53pm.
|
|
|
|
|
The statement "EXEC sp_executesql;" should include your variable (@MyQuery)
Mongo: Mongo only pawn... in game of life.
|
|
|
|
|
Try this:
CREATE PROC dbo.GetName
(
@column sysname,
@columnData nvarchar(50)
)
As
BEGIN
DECLARE @MyQuery nvarchar(max), @RealColumnName nvarchar(130);
SET NOCOUNT ON;
SELECT
@RealColumnName = QuoteName(name)
FROM
sys.columns
WHERE
object_id = OBJECT_ID('dbo.TableA')
And
name = @column
;
If @RealColumnName Is Null
BEGIN
RAISERROR('Invalid column name: "%s"', 16, 1, @column);
Return;
END;
SET @MyQuery = N'SELECT ' + @RealColumnName + N' FROM dbo.TableA WHERE (' + @RealColumnName + N' Like @columnData + N''%'' Or @columnData Is Null)';
EXEC sp_executesql @MyQuery, N'@columnData nvarchar(50)', @columnData = @columnData;
END
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Now that is a good explination
Mongo: Mongo only pawn... in game of life.
|
|
|
|
|
You made a typo in your explanation code, at least I hope it is not in your actual function.
@Col_Name is not defined. @ColumName is.
Mongo: Mongo only pawn... in game of life.
|
|
|
|
|
Hi, thanks for replying. I caught that error and my query now runs but it returns nothing
|
|
|
|
|
Hello,
Is it possible to start a SSIS 2012 DTSX-package via DTEXEC.exe (classic method in command shell) or Integration Services Catalogs (SQL Server 2012) without validating it?
I have several very big packages and the validations take several minutes causing performance problems because I have to run the packages every XX minutes and the validations take too much time.
I read the documentation of DTEXEC.exe and found a parameter that can be used in order to validate a package without executing it, but I need the opposite thing: just execute without validate it!
Thanks for any advices!
Best wishes,
Miroslav
|
|
|
|
|
|
Dim cn As String
con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\surendera\Documents\student.accdb"
cn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\surendera\Documents\student.accdb"
con = New OleDbConnection(cn)
cmd.Connection = con
con.Open()
cmd.CommandText = "INSERT into user_acnt(user_name,pas_word) values('" + login.TextBox1.Text + "' ," + login.TextBox2.Text.ToString + ")"
cmd.ExecuteNonQuery()
MsgBox("record successfully saved", vbInformation)
con.Close()
what is the problem in this code...when i run this code it says no value given for some parameter. this code is written within a vb clas named class1 and table name is useracnt
plz suggest me the solution
surendera singh
|
|
|
|
|
There seems to be some confusing code there; you have the connection string twice. You are also using string concatenation in your INSERT statement which leaves you open to SQL injection attacks, and the loss or destruction of your database. You are also displaying the message "record successfully saved" without checking that it actuallky has been, so leading to other errors that you will not know about. I suggest getting hold of some learning materials before going any further.
|
|
|
|
|
In addition to the SQL Injection[^] vulnerability, you're also storing passwords in plain text. You should only ever store a salted hash of the user's password.
You should also wrap the connection and command objects in Using blocks, to ensure that their resources are properly cleaned up.
You should also give your controls proper names, so that their meaning is obvious. Using the default names (TextBox1, TextBox2, etc.) will only confuse you when you come back to this code later.
To fix the immediate problem, use a parameterized query:
Using con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\surendera\Documents\student.accdb")
Using cmd As New OleDbCommand("INSERT into user_acnt (user_name, pas_word) values (?, ?)", con)
cmd.Parameters.AddWithValue("p0", login.UserNameTextBox.Text)
cmd.Parameters.AddWithValue("p1", login.PasswordTextBox.Text)
con.Open()
cmd.ExecuteNonQuery()
End Using
End Using
Then, go and read the following articles, and change your database design to store the passwords securely:
Secure Password Authentication Explained Simply[^]
Salted Password Hashing - Doing it Right[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Trying to install MySQL on Windows 7/64 Pro.
It was probably installed and removed a year or so ago. I do use the MySQL Query Browser and the Workbench for remote databases and do not want to remove them.
I do need to be able to test MySQL on my Windows machine so need the Server.
When I download and install any recent MySQL Windows installer the installer does come up but Server is not listed. It did update workbench.
The MySQL bin folder is NOT in program files or program files (x86).
MySQL is not in either Services or Control Panel Uninstall a Program.
How can I get MySQL Server installed and running on my WIn 7????
What am I doing wrong or how do I fix it?
|
|
|
|
|
Did you check your registry?
In Word you can only store 2 bytes. That is why I use Writer.
|
|
|
|
|
Hello all,
How to change the length of a column in a SQL Server table...
Thank you..
|
|
|
|
|
Balaji Naidu wrote: oralce table
Balaji Naidu wrote: SQL Server table
Which is it - Oracle, or SQL Server? 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
