|
|
Try it this way:
insert_transaction = New SqlCommand("insert into [Transaction] (Transaction_Id,Account_Id,Type,Amount,Date,Time) values ('30','" & account & "','Account Transfer','" & money.Text & "','" & Today & "','" & time & "')", myConnection)
The word Transaction has a special meaning to most databases.
Having said that, it would be a great deal better if you used parameters in your query, as your current query leaves your database open to SQL injection.
|
|
|
|
|
Hi All,
Is there any Query to Export Table to Xml File Using Sql Server?
Plz Suggest Me ...
Thx in Advance
Nagaraju
|
|
|
|
|
In MS SQL Server 2000 there is a "FOR" clause which you can use to retrive the select query result in xml. Basic syntax is as follows,
<br />
FOR XML mode [, XMLDATA] [, ELEMENTS][, BINARY BASE64]<br />
<br />
Arguments<br />
XML mode<br />
<br />
Specifies the XML mode. XML mode determines the shape of the resulting XML. <br />
mode can be RAW, AUTO, or EXPLICIT.<br />
<br />
XMLDATA<br />
<br />
Specifies that an XML-Data schema should be returned. The schema is prepended to the document as an inline schema.<br />
<br />
ELEMENTS<br />
<br />
If the ELEMENTS option is specified, the columns are returned as subelements. Otherwise, they are mapped to XML attributes. This option is supported in AUTO mode only.<br />
<br />
BINARY BASE64<br />
<br />
If the BINARY Base64 option is specified, any binary data returned by the query is represented in base64-encoded format. To retrieve binary data using RAW and EXPLICIT mode, this option must be specified. In AUTO mode, binary data is returned as a reference by default.<br />
Example : Select * from TableName for xml auto
You can search the SQL transact help for further details.
Nothing is Impossible. Even impossible spells "i m possible"
|
|
|
|
|
Thanks Maharishi,
But I need to Save the whole table Data into a xml file which is to be created with a sql query.
means
Select * from TableName to "c:\test.xml"
I need a query to send whole sql table to cml file
is there any way to do like that?
thx..........
Nagaraju
|
|
|
|
|
I don't know wether you can achieve this with a sql statement, but if your're using a .NET-application , to send the SQL Statement, you could do the following:
- Fill a DataSet via SqlDataAdapter with the output of your Select-statement
- Write the data to a xml-file using the DataSet.WriteXML()-method.
This would solve your problem in a very easy way, but if you really need to do that in one sql-statement I haven't got the faintest idea.
Tobias
|
|
|
|
|
Hi,
For that you will need to write a PL/SQL for this kind of stuff. In MS SQL 2000/2005 there is an extended stored proc named xp_cmdshell which can be used to access the windows command line which can be used to save file. I have got an example which you can use for this...
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)
SET @FileName = 'c:\data.txt'
SET @bcpCommand = 'bcp "SELECT * FROM TableName for xml auto" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U sa -P pwd -c'
EXEC master..xp_cmdshell @bcpCommand
But for this kind of extended proc access the procedure has to run in 'sa' context... or you need administrator rights to do that...
Hope this helps
Maharishi
Nothing is Impossible. Even impossible spells "i m possible"
|
|
|
|
|
I want to connect to a database and retrieve and insert data which is in another server.which means the database is not in our server.
it may be in another server globally remote.
this is about an asp web application.
please reply me as soon as possible
dayya <gmherath@gmail.com>
|
|
|
|
|
Either set your remote server up as a linked server in SQL Server or write a web service to access it.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
dayya wrote: please reply me as soon as possible
What kind of reply are you looking for? Suggestions or free code?
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
How can we get the newly inserted row's size from a SQL Server table?
Soumya Mathew
|
|
|
|
|
Soumya Mathew wrote: How can we get the newly inserted row's size from a SQL Server table?
I really have no clue what you mean by "Row Size"...
Are you trying to say you want the sum of individual field sizes?
|
|
|
|
|
Don't you mean the row count ???
<br />
select count(*) from tablename<br />
|
|
|
|
|
hi all,
i have developed an application in vb.net using Ms-Sql as back end.
the application is a multiuser on net/Lan.
But i have no idea if more than one user fire same querry at the same time.
is this hang the system/application.
if so solution please
thankx in advance.
-- modified at 4:43 Thursday 29th November, 2007
rmshah
Developer
|
|
|
|
|
r_mohd wrote: But i have no idea if more than one user fire same querry at the same time.
is this hang the system or crash the application.
SQL Server is designed as a scalable back-end database for multi-user applications. As long as you write your data access code and queries in an efficient manner, you should have no problems.
r_mohd wrote: pl remdy also
What does this mean? Please try and use English - textspeak is really annoying.
BTW, you claim to be a Senior Developer but you don't have basic knowledge of data access? I find this very worrying  .
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
pmarfleet wrote: BTW, you claim to be a Senior Developer but you don't have basic knowledge of data access? I find this very worrying
The "Senior" is probably not an adjective for the "Developer"
|
|
|
|
|
He's now modified his post and changed his job title to 'Developer'. I wonder whether he got demoted after I posted my comments?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
pmarfleet wrote: He's now modified his post and changed his job title to 'Developer'. I wonder whether he got demoted after I posted my comments?
|
|
|
|
|
Hi all,
I need to be able to read in a CSV and then write all data back to a specified table within Oracle.
At the moment, I have put the following Oracle connection together, but need to read in c:\test.csv for example.
OracleConnection importOraConn = new OracleConnection();
importOraConn.ConnectionString = "User Id=USERNAME,Password=PASSWORD;Data Source=ORACLEDB";
importOraConn.Open();
OracleCommand importOraCommand = importOraConn.CreateCommand();
OracleDataAdapter oda = new OracleDataAdapter(importOraCommand);
Any help with this would be greatly appreciated.
Thanks
|
|
|
|
|
Oracle should have an import wizard you can use and load the data into a temp table then sort it from there.
If you must use code then you need to:
1) open the file as text via code.
2) Get each row from the text file
3) Copy row into an array as the ; being the delimiter of the array
4) transfer the array into an object or fields and sanitise it.
or boadge a sql insert string using the array values.
5) insert this into oracle using as a sql command.
There are a few ways of doing what you want and this is just a suggestion.
|
|
|
|
|
Thanks, that's made things a little clearer of how to do things.
Do you have a snippet of how I get from step one to using insert statements?
Thanks
|
|
|
|
|
public DataSet Get_Friends_Birthday(DateTime d)
{
User=0;
DataSet ds = new DataSet();
SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=" + '"' + @"C:\Users\LadyLara\Documents\Visual Studio 2008\Projects\Private Notebook WebService\Private Notebook WebService\App_Data\Private Notebook Database.mdf" + '"' + ";Integrated Security=True;User Instance=True");
string scom = "SELECT FriendID, FFirstName, FLastName, FBirthday, FE_mail1, FE_mail2, FE_mail3, FE_mail4, FPhone_Number1, FPhone_Number2, FPhone_Number3, FPhone_Number4, UserID FROM Friends WHERE (UserID = "+"@IDUS)"+" AND (FBirthday = "+"@Date)";
SqlCommand sqlcom = new SqlCommand(scom,con);
sqlcom.Parameters.Add("IDUS", SqlDbType.Int).Value=User.ToString();
sqlcom.Parameters.Add("DATE", SqlDbType.SmallDateTime).Value=d.ToString();
SqlDataAdapter adapt = new SqlDataAdapter(scom, con);
con.Open();
adapt.Fill(ds);
return ds;
}
when it reaches adapt.fill(ds) i get the folowing error. "System.Data.SqlClient.SqlException: Must declare the scalar variable "@IDUS"."
Please Help!
rzvme
|
|
|
|
|
rzvme wrote: sqlcom.Parameters.Add("IDUS", SqlDbType.Int).Value=User.ToString();
sqlcom.Parameters.Add("DATE", SqlDbType.SmallDateTime).Value=d.ToString();
Prefix your parameter names with @ symbols.
BTW, you should consider not using inline SQL statements. Use stored procedures instead.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
i thought about using stored procedures only i don't know exactly how to do this.(oracle fan) and by the way....even with the @ symbol in front of the parametername i still get the exact same error.
rzvme
|
|
|
|
|
string scom = "SELECT FriendID, FFirstName, FLastName, FBirthday, FE_mail1, FE_mail2, FE_mail3, FE_mail4, FPhone_Number1, FPhone_Number2, FPhone_Number3, FPhone_Number4, UserID FROM Friends WHERE (UserID = "+"@IDUS)"+" AND (FBirthday = "+"@Date)";
You don't need to do any string concaternation here.
You should still really consider using stored procedures instead - you wouldn't have these problems if you did.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
