|
I make a DataBase and create the table and procedure all in the the default schema and primery file group
Can any one tell me how to replace the tables and procedures in a new file group and schema
merwa
|
|
|
|
|
I am developing a solution in C# on top of ADO.NET. I am using MS Access as my data store. My requirement is that i have to insert a Unicode string containing Arabic characters into the database. So following is the INSERT command
INSERT INTO ClauseTranslations (Translation) VALUES('اردو کی ایک مثال')
So thats it. Now this query executes very well in the MS Access environment but simply fails when executed from the C# application using the ADO.NET classes. The error message is
"Syntax Error in INSERT Statement"
Can anyone guide me whats wrong and where is it wrong?
Regards,
Mohsin
Polite Programmer
More Object Oriented then C#
|
|
|
|
|
It is possible that your field name (Translations) is a keyword for the Jet Engine SQL implementation. Try enclosing it in square brackets [Translation] to avoid interpretation as a keyword.
If that fails, implement the insert using a parameterized insert stored procedure (querydef in Access Lingo) and use an SQLCommand to execute it from C#. This almost always works, as SQL syntax is interpreted at the database, and data values are cleanly encapsulated, avoiding any parsing problems.
Using stored procedures and commands also makes your application far less vulnerable to sql injection attacks.
|
|
|
|
|
Hey guys,
I am trying to write a C# program to import data from an Excel spreadsheet to a Sql table using a stored procedure.
So, I've created the connection objects:
OleDbConnection XLcon = new OleDbConnection(
@"Provider = Microsoft.Jet.OLEDB.4.0; Data Source = D:\Desktop\OA.xls;"
+ @"Extended Properties = Excel 8.0;");
SqlConnection SQLcon = new SqlConnection(
@"Data Source = (local); Integrated Security = SSPI; Initial Catalog = WebData");
And the Command Objects:
SqlCommand SQLcmd3 = new SqlCommand();
SQLcmd3.CommandText = "ImportOA";
SQLcmd3.CommandType = CommandType.StoredProcedure;
SQLcmd3.Connection = SQLcon;
OleDbCommand XLcmd = new OleDbCommand("SELECT [Date], [Order Application], "
+ "[Demand Orders], [Demand Units], [Demand Net], [Demand Gross], "
+ "[Demand Freight], [Demand Tax], [Demand Amount], [Demand Product Promo], "
+ "[Demand Ship Promo], [Fulfillment Units], [Fulfillment Net], "
+ "[Fulfillment Freight], [Fulfillment Tax], [Fulfillment Amount], "
+ "[Fulfillment Product Promo], [Fulfillment Ship Promo], [Return Orders], "
+ "[Return Units], [Return Net], [Month], [Quarter], [Year], [Week #], "
+ "[Return Amount] FROM [Excel$]", XLcon);
Set up the parameters:
SQLcmd3.Parameters.Add("@Date", SqlDbType.SmallDateTime);
SQLcmd3.Parameters.Add("@OrderApplications", SqlDbType.VarChar,10);
.
.
.
SQLcmd3.Parameters.Add("@ReturnAmount", SqlDbType.Money);
And then tried to import the data:
XLcon.Open();
OleDbDataReader XLread = XLcmd.ExecuteReader();
while (XLread.Read())
{
SQLcon.Open();
SQLcmd3.Parameters["@Date"].Value = XLread[0];
SQLcmd3.Parameters["@OrderApplications"].Value = XLread[1];
.
.
.
SQLcmd3.Parameters["@ReturnAmount"].Value = XLread[25];
SQLcon.Close();
}
XLcon.Close();
XLread.Close();
The code runs fine but it does not import anything into the table. The spreadsheet has data in it and I have verified that the Store procedure works so why can't I get the values in the spreadsheet into the table?
Thanks,
Bryan
|
|
|
|
|
I'm not sure what you are trying to do here, but it doesn't look correct.
you have created a command query containing a select statement that specifies a list of fields to be selected from the worksheet named Excel$. You read the results of that query into a command object that is linked to a stored proc in SQL server somewhere. You open a connection, set command parameters to row values read from excel and then close the connection (do you execute the command first?).
First, I would open and close the connection outside of the reader while loop, and just execute SQLcmd3 at the bottom of the loop. Close and dispose of the connection (and command) after the while loop.
Set a breakpoint at one of the lines that sets a parameter value and verify that you are really returning data from the sheet.
Good luck
|
|
|
|
|
Hi, I have to show a filesystem on a page. The information is stored in a table where I have ID(PK), Name, Parent, Order
001, Node1, NULL, 1
002, Node2, NULL, 2
003, Node3, 001, 1
004, Node4, 001, 2
005, Node5, 002, 2
I need to display the results as it should be seen:
+Node1
Node3
Node4
+Node2
Node5
The query I have is like this:
SELECT A.*
FROM TableFS A
LEFT OUTER JOIN TableFS B
ON A.ParentID = B.ItemID
ORDER BY A.ParentID, B.Order
The result is similar to what I should get, but similar is not enough... at least it's what they tell me... 
If anyone can help... 
Thanks...!
|
|
|
|
|
SELECT A.*
FROM TableFS A
INNER JOIN TableFS B ON A.ItemID = B.ParentID
ORDER BY A.ParentID, B.Order
Regards
KP
|
|
|
|
|
Sorry, but this is not even close. Items without parents have value in NULL, so your querry dosen't take those nodes in charge, and gives me duplicated values anyway.
Thanks for your time 
|
|
|
|
|
I have a database column that is variable character 1500, If I try to read a record with a value whose length is over 200 characters I get an empty string and the debugger returns the following error:
This expression causes side effects and will not be evaluated
|
|
|
|
|
Hi,
I've been using SQL Server 2005 for almost 2 years now. I want to know if there is anything to look at for the new SQL Server?
Regards
ma se
|
|
|
|
|
|
I haven't heard of SQL Server 2008
|
|
|
|
|
zhousz wrote: I haven't heard of SQL Server 2008
Codenamed Katmai - The CTP was released earlier this month.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
I have a central database in a server computer(In sql express 2005).
Two or more computers can connect to the central database.When a terminal computer is doing some operation on some data, how can i ensure that the other terminal
in the same network doesnot change the data at the very same moment?
X
|
|
|
|
|
Please don't crosspost. It's not very polite.
|
|
|
|
|
SQL Server automatically ensures that data modification statements (INSERT, UPDATE, DELETE) are atomic - a statement that affects multiple rows will either modify all of them, or none (if some error occurs, the partial changes will be rolled back).
If you need to execute multiple statements in the same logical operation, and have the results consistent, you can use a transaction. If submitting all the statements in a single batch, or in a stored procedure, you can use the BEGIN TRANSACTION/COMMIT TRANSACTION/ROLLBACK TRANSACTION statements. If you can't execute it all in one batch, or need to call multiple stored procedures, use a client-side transaction (e.g. SqlConnection.CreateTransaction in .NET languages)
There are a number of transaction isolation levels which control which updates from other users can appear between two SELECT statements in the same transaction. Basically, READ COMMITTED (the default) allows changes to be made between two SELECTs - the second statement may see results that another, completed, transaction has made. REPEATABLE READ prevents updates and deletes until your transaction is committed. SERIALIZABLE also prevents inserts that would change your resultset.
Transactions using the built-in transaction features should be kept short. Holding a transaction open for too long prevents other users getting work done. If you need your application to allow users to read a record, make changes to it over a period of time, then eventually commit the change, you need to either come up with a manual record-locking scheme, or somehow check to see if changes have been made since the data was read and either automatically merge the changes made by each user or simply alert the user that the database has been changed.
One of the simplest ways to detect that the data has changed since being read is to use a rowversion column, which changes every time the row is updated.
|
|
|
|
|
Hi
I want to insert records as CustID & Custname in Customer table.
custid Interger (autoincrement)
Custname varchar2
& want to display last added record .
Thnx
|
|
|
|
|
Care Career wrote: I want to insert records as CustID & Custname in Customer table
Insert into Customer (Custname) values ('Value')
Care Career wrote: & want to display last added record
select top 1 * from Customer order by CustID desc
I Love SQL
|
|
|
|
|
Blue_Boy wrote: select top 1 * from Customer order by CustID desc
if you use a simple select like this, you could get the wrong record when by many users at the same time, instead you can use:
select * from Customer where CustID = SCOPE_IDENTITY()
Alexei Rodriguez
|
|
|
|
|
@@IDENTITY returns last generated auto increment number
with which you can get the last custid inserted into the table.
Regards
KP
|
|
|
|
|
Krish - KP wrote: @@IDENTITY returns last generated auto increment number
Yes, but for what table? If you have a large multi user database handling many transactions at once there is no guarantee that it will return the identity of the last row inserted in the scope your running in. It might return the identity for some other code running on some other table that just happens to be running at the same time as your code.
You should use SCOPE_IDENTITY() to ensure that you get the identity of the last insert in your scope (and no one else's scope)
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
Hi,
I want to create an user in sql server.
My program create a database, then it create a user for that database .
The problem is that I can't see the tables from the database with the user.
please help.
thanks.
|
|
|
|
|
Sounds like you need to give database reader role to your new user.
Ben
|
|
|
|
|
SQL Server has security. In order to be able to SELECT from tables, the user account in the database has to be granted permissions. Use the GRANT statement to set the permissions.
I normally make ordinary users members of the public role and limit that role to only being allowed to do the minimal set of operations that the application requires. By default, public has no permissions to do anything.
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
