|
You should not create SQL Statements like this if at all possible because it is vulnerable to a SQL Injection Attack.
SQL Injection Attacks and Some Tips on How to Prevent Them[^]
Some Microsoft documentation that might help:
SqlCeCommand.Parameters[^]
I've left the explanantion for your error until the end of this message as I wanted to get across the point about security your applications against attack.
The reason for your error is that SqlMoney is not a string and the & operator is used for concatenating strings together. This is an inefficient way to concatenate strings, you should consider using String.Concat() or String.Format(), the latter being more likly to understand how to insert a SqlMoney object into the string without you having to write any code to convert it yourself.
Does this help?
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
i know that SqlMoney is not of a string type, but then again i'm not trying to insert a string into the query. The data type i specified in my database is of type Money, which is why i'm using SqlMoney in my code. Right now i'm thinking of ways on how to insert the value in, but its giving me errors so far.......thanks for the reply anyway.
|
|
|
|
|
gundamhamtaro wrote:
i know that SqlMoney is not of a string type, but then again i'm not trying to insert a string into the query
You are trying to concatenate the SqlString object into a string. The SQL query is a string, therefore anything in the query must be converted to a string so it can be submitted to the database.
gundamhamtaro wrote:
Right now i'm thinking of ways on how to insert the value in, but its giving me errors so far
Did you read my post? Have you tried parameterised queries like I suggested? It isn't just for the security considerations. It makes converting data types to SQL much easier also.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
i tried String.format and String.Concat, and when i use String.format, i get this error: System.ArgumentNullException; and if i use Concat i get an error when trying to insert the data: the specified data type is not valid. maybe i'm doing it wrongly, so i'll just post my code:
dim price as string
price = String.Concat(newprice)
|
|
|
|
|
Okay - I give up. You obviously don't want to take my advice and use parameterised queries.
String.Concat() is used with 2 or more strings. You cannot put anything in there that is not a string. SqlMoney is not a string. That is why you get an error message saying "data type is not valid".
String.Format() is used with a format string and some parameters. For example String.Format("{0}", newPrice) The value newPrice is formatted into a string. You can also do a lot more powerful things with format, but you can read that in the documentation.
Once again, I would suggest you use parameterise queries (the links I gave in past posts show examples of how to use them) because then you do not have to worry about converting data into a string in order to format them to fit in a SQL statement.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
huh??? i didnt say that i dont want to use parameterised queries did i? there was a misunderstanding, i thought that parameterised queries was something thats similar to what i had typed out. now that i've checked it, i know i was wrong and now i that i've changed it, everything works fine. thanks for everything =)
|
|
|
|
|
|
Hopefully this is very simple - using a vb.net front end & access database for college assignment and need to write a SQL query as follows (and never having used SQL before its proving beyond me!!)
I need to apply a filter criteria within a SQL query, using a value stored in a variable.
A value is applied in a combo box, which is then stored as the variable, and another combo box should then show results filtered on the first combo box selection.
Cheers.
|
|
|
|
|
So, which bit are you stuck on? Getting the value from the conbo box into the SQL? or, the SQL itself?
The SQL for Access uses a ? to denote a parameter position, which you can add in your VB.NET program using the Parameters collection on the OleDbCommand object. I hope I've supplied enough hints. I won't fully answer a homework question (for obvious reasons) and I hope you'll understand why I don't give a complete solution. But I am willing to nudge you in the right direction if necessary.  Feel free to elaborate on what you have done and where you are stuck.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
The bit I am stuck on is getting the SQL query to recognise that the value I want to search from is the value in the variable, rather than looking for the name of the variable as a value,
e.g. (its a very basic query..)
SELECT names
FROM Reservations
Where names = "name" (I don't want it to look for "name" in the query, but the value contained within "name"
Theres probably something more I need to to in the vb.net code as well but not sure what yet
Yes, its something want to/ got to learn, but have only been learning vb.net for a month and SQl for about two weeks, so hints greatly apprecaited so can move forward!
|
|
|
|
|
Okay. Like I said in my previous post you can replace your search criteria with a ? which is a parameter placeholder. So, your WHERE clause will look like this:
WHERE names = ? You can then add the parameter value to your command object in VB.NET. For example:
MyCommand.Parameters.Add("search value", OleDbType.VarChar)Remember that if you have more than one parameter then the order of the parameters in VB.NET must match the order of the ?s in the SQL Statement because Access does not support named parameters (in other DBs you can use a name prefixed with an @ instead of a ?)
Here are some links to some documentation you might find useful. They also contain examples of how to create a Command with parameters and run it:
OleDbCommand.Parameters[^]
OleDbParametersCollection.Add()[^]
OleDbType[^] enumerations
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Many thanks, you have been most helpful, I have more of an idea how to approach this now, will give it a try.
|
|
|
|
|
|
Firstly, i'm very new to Sql Server 2000.
I'm just wondering why there isn't an option in the Enterprise Manager for selecting a database and executing SQL from a file on it.
I've been using the osql command in the command prompt to perform my sql functions (create table, drop, alter, etc..) but i need to modify each one by specifying the (USE myDB) and then GO.
Do i need to go through DTS and all that package stuff or is there an easier way? I havn't touched the Query Analyzer yet.
|
|
|
|
|
I open up query analyzer and click the drop down up top to choose my db and then copy and paste the sql file into it and click the run button...
|
|
|
|
|
I have the following problem. I have two columns. One is a number, the other is a name. I want to make a calculation that sums ONLY where the name is the same. For example:
Name Value CALCULATEDVALUE
Bob 3 9
Bob 2 9
Alice 1 1
Bob 4 9
Carol 2 5
Carol 3 5
How do I go about doing this? I don't have the faintest clue.
Thanks a lot!
Did I post well? Rate it! Did I post badly? Rate that too!
|
|
|
|
|
Use the GROUP BY clause.
SELECT Name, SUM(Value)
FROM MyTable
GROUP BY Name If you need the output to be like the table you have above, you will need to create a subquery and join to it, like this:
SELECT Name, Value, GroupValue
FROM MyTable
INNER JOIN (SELECT Name, SUM(Value) AS GroupValue
FROM MyTable
GROUP BY Name) AS Group ON Group.Name = MyTable.Name
Does this help?
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Hello Friends
Someone know some article about "Database SQL Syntax Comparison" among:
- SQL Server 2000
- SQL Server 2005
- Firebird
- Interbase
- MySQL
- PostgreSql
|
|
|
|
|
Oops... I forgot:
- Oracle
Thanks for all!
|
|
|
|
|
Does anyone have an example of code required to connect to an AS400. I am new to .Net and looking for the best way.
Thanks
|
|
|
|
|
|
Does anyone know how to transfer an entire database out of mssql server 2005 (express) into a .sql file so I can recreate it? I have looked though the TSQL commands on msdn and tried bcp (unless I'm missing somthing that will only do tables and I need the stored procedures as well) The reason for this is I want to export the data and put it into a ms sql 2000 database but dts won't work because the database is so different (neither will the sp_attach_db stored procedure (gives an error about indexes)
|
|
|
|
|
I've not had a chance to play around with SQL Server 2005, but in SQL Server 2000 the Enterprise Manager has a feature where you can script database objects (tables, views, stored procedures, user defined functions, etc.) by right clicking on them and selecting the Script item. You can also click on the database to script the whole DB. I'm guessing that SQL Server 2005 will have something similar.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
that's the problem  the express version of 2005 doesn't have enterprise manager, it has a community built one that doesn't have much funtionality right now. The higher versions of 2005 are suppose to have this but not express (express is the msde of 2005)
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
