|
wetrivrrat wrote:
I need to find a way (is it possible?) to have a variable query written in vb.net that will change based on a text box input.
Yes, of course.
myCommand.CommandText = "SELECT someColumns From someTable WHERE machineName = @machineName";
myCommand.Parameters.Add("@machineName", someMachineName);
Assumes that your command object is called myCommand.
The variable someMachine contains the name of the machine that you retrieved out of your user interface.
Does this help?
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
in your code i'm assuming you're writing it in a command function? right, if
so then i'm only limited to 'GetType' unless i add the 'as string' or
someother class. so I guess my first Q would be, what class are you
expecting this to go under? if it helps any I was just using a
OleDbDataAdapter and DataSet to connect to sql(so if i had to use access later its already ready for it!).
Second Q was, is my interpretation of your code correct?
Function LoadLastInvoice()
Dim VAMachineName As String
VAMachineName = TextBox3.Text
LoadLastInvoice.CommandText = "SELECT last_invoice From Location_table WHERE
Location_ID = @machineName"
LoadLastInvoice.Parameters.Add("@machineName", VAMachineName)
End Function
Because I'm getting the following crash-related error...
'Object variable or With block variable not set'
and I'm not sure how to set the @machineName variable as I tried several ways and nothing seemed to work...
Thanks
Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."
Why is common sense such an un-common comodity?
|
|
|
|
|
hello my dear,
want to register the sql database from my local computer using enteprize manager but this error apeared :
sql server does not exist or access is denied.
i am sure about the correction of the user name and password and server name.my local windows is xp2000 but the windows of the server is 20003pro.the version of the sql in my local is personal no enteprize.
thanks in advanced
|
|
|
|
|
Anonymous wrote:
i am sure about the ... server name.
OK on your local intranet, otherwise you will need to use an IP address. Could the problem be as simple as that?
cheers
Phil
|
|
|
|
|
thank you,
I used the ip but did'nt get advantage.
|
|
|
|
|
can i bind a parameter to a column of a datagrid, or it can only be binded in table's columns?
I have the aggregated values of total sales for each year(from sh schema) in a datagrid, and i want to retrieve all the detail tuples from a year which user will 'click'.
i use the following query to do this:
select decode(prod_subcategory,null,'ALL',prod_subcategory) as subcateg,decode(prod_category,null,'ALL',prod_category) as categ,sum(amount_sold) as dolars
from sh.products,sh.sales,sh.times
where products.prod_id=sales.prod_id and times.time_id=sales.time_id and end_of_cal_year=:param1
group by rollup(prod_category,prod_subcategory)
where param1 it's an OracleParameter with parameter->set_SourceColumn("END_OF_CAL_YEAR") and i get an
ORA-01008 error( not all variables binded )
any help?
thanks in advance
|
|
|
|
|
A big problem is a Datetime object in the CommandText property. I always get an error. Is .NET unable to convert Datetime object into the right SQL Server system format? When it's possible ... how to solve it?
|
|
|
|
|
Seraphin wrote:
A big problem is a Datetime object in the CommandText property.
Why are you putting a DateTime in the CommandText property?
Seraphin wrote:
Is .NET unable to convert Datetime object into the right SQL Server system format?
.NET is perfectly capbable of converting a DateTime object to what ever format you wish in multiple calendars (should you so wish it).
Seraphin wrote:
how to solve it?
If you need to use a DataTime (or anything else for that matter) in a query you should use parameters. This reduces the possibility of security holes.
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM MyTable WHERE SomeDate > @theDate";
cmd.Parameters.Add("@theDate", myDateTimeObject);
Does this help?
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Thank you. That's what I want.
|
|
|
|
|
I want my DateTime value type fields just store date values (month/day/year) or time values (hour:minute). How can i do that?
I have datetime fields in tables with full information (month/day/year hour:minute:second:milisecond). But i just want to take month/day/year or hour:minute in my select query string. How can i do that?
Thanks!
|
|
|
|
|
|
Is there an easier way than
SELECT ltrim(str(DATEPART(month, GETDATE()))) + '/' + ltrim(str(DATEPART(day, GETDATE()))) + '/' + ltrim(str(DATEPART(year, GETDATE()))) ?
<italic>Work hard, Work effectively.
|
|
|
|
|
|
I haven't got the solution Colin. I have tried
select convert(datetime,getDate(),101) .  I only want the day, month, and year. Thank you for the reply.
<italic>Work hard, Work effectively.
|
|
|
|
|
Yeah, i often use ltrim to take the day, month, year string too. But it's too long. I wonder why there isn't just a date data type? And i can't find out in which situation we need to store millisecond.
The way using the convert function to get the day, month, year that Colin helps works Yulianto! You must convert it into varchar type, not datetime type :
select convert(varchar(100),getdate(),101)
Thanks Colin! 
But sometimes i what i want to take is hour and minute  I think the convert function doesn't work on this case. Maybe i should use ltrim again. 
|
|
|
|
|
Blue_Skye wrote:
And i can't find out in which situation we need to store millisecond.
There are a number of reasons for storing milliseconds. For instance, Security audit information often uses milliseconds.
Blue_Skye wrote:
But sometimes i what i want to take is hour and minute I think the convert function doesn't work on this case.
You're right, the function is quite limited and you will need to roll your own conversion if you want anything other than what the CONVERT function supplies.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
|
Can I use a Parameter ( either in MSAccess / SQL Server ) to set the name of a table which I want to create or get data from .
To make it simple, can I execute an SQL this way,
(Assuming the DB to be MSAccess)
Parameters TableName Text;
SELECT * FROM TableName;
Or is there a workaround for this?
|
|
|
|
|
You for something like that you will need to create dynamic SQL in either .NET or in a Stored Procedure. In other words:
string tableName = "Account";
string sqlQuery = string.Format("SELECT * FROM {0}", tableName);
Here's how you do this in a stored procedure
CREATE PROCEDURE dbo.ExecQuery
(
@tableName varchar(50)
)
AS
declare @sql varchar(100)
set @sql = 'SELECT * FROM ' + @tableName
exec @sql
return 0
The main question is, is this there a different approach all together?
~Javier Lozano
|
|
|
|
|
Be careful with both those approaches as they are weak points from which a SQL Injection attack could be launched. If you are selecting an existing table, then it is worthwhile checking that the table exists already with a proper parameterised query so you can be sure there isn't anything nasty injected into the tableName variable you are using.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
You are very much correct, and why I specifically asked for a parameterised query is for the same reason, to avoid Injection Attack. So can you explain how I can use a parameter to substitite for table name? I couldn't get it to work !!
|
|
|
|
|
csylesh wrote:
So can you explain how I can use a parameter to substitite for table name? I couldn't get it to work !!
You would have to use something like Javier suggested. You cannot have a table name as a parameter. However, table names are unlikely to have strange characters in them* so you can easily check it for non-alphanumeric characters and reject the name if it contains such things.
Also, if you are just checking for a tables existance, you can create a query such as
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @MyTableNameParameter
Does this help?
* Having said that, a table name, if I remember correctly, can be any unicode string upto 128 characters so it is still possible.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Thanks, I just wanted to hear from somebody authoritatively that a parameter substitution is not possible for a table name.
Let me raise a new question for curiosity, how does parameter substitution actually prevents injection attack evenif it is a string datatype that is being substituted. Is it some magic with the quotes substitution or the like? Any ideas?? Thanks in advance.
|
|
|
|
|
Hello friends!!
In Execution plan please somebody help me to understand I am new to it.
If I run one select statement having Execution plan...
1.first of all what we will watch closely so that it will understand easily i.e related to costing....
2.Here I am seeing factors like 'SELECT' ,' TOP' , Hash Match/Left Anti Semi Join.
3.Index Scan : Physical Operation and Logical Operation
Here only I can see some factores like
Physical Operation And Logical Operation
a)Row Count =========== 26,795,748
b)Estimated Row Size ==== 132 (What this exactly means??)
c)I/O Cost ============ 209
d)CPU Cost ============ 29.5
e)Number Of Executes ==== 1
f)Cost ============== 238.884090(57%)
h)Subtree Cost ======== 417
i)Estimated Row Count==== 26,795,748
4.Computer Scalar:
5.Stram Aggregate/Aggregate
6.Table Scan
Like That........
Please somebody guide me in that I will be thankful.. (Please Give me detail description where i can judge all factors of execution plan so that i can reduce the cost of query if possible)
T.I.A
Shashank
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
