|
You can put the database into a mode where only a single connection is allowed at a time by using ALTER DATABASE db SET SINGLE_USER. Use the WITH ROLLBACK option to tell SQL Server when to rollback/abort any other existing connections. The connection you run ALTER DATABASE from remains connected.
You may also be able to restore the database if you take it OFFLINE.
|
|
|
|
|
I am trying to insert Data in SQL Database using C#, it's not giving any error but at the same time no rows are getting updated in my database.
private void Button1_Click(object sender, System.EventArgs e)
{
SqlConnection myConnection = new SqlConnection("server=sbs;database=bansal;Trusted_Connection=Yes");
SqlCommand myCommand = new SqlCommand("InsertCategory", myConnection);
myCommand.CommandText = "InsertCategory";
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add(new SqlParameter("@CategoryName",SqlDbType.NChar, 15));
myCommand.Parameters["@CategoryName"].Value = "Ambarish";
myCommand.Connection.Open();
try
{
myCommand.ExecuteNonQuery();
}
catch(SqlException e){}
myCommand.Connection.Close();
}
Following is the Stored Procedure:
CREATE PROCEDURE InsertCategory
(
@CategoryName nchar(15)
)
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
Table Name is Categories and has two Columns only:
1. CategoryName NChar 15
2. Identity Int 4 (Identity Seed is 1 starting from 1)
Sachin Jain
-- modified at 4:36 Saturday 4th August, 2007
|
|
|
|
|
You are adding a parameter to the command every single time you press the button. You now have several parameters - which one does the database use? It will use the first one with the correct name and ignore the others. You most likely have a parameters set up by the wizard that created your connection with the database.
This is the big problem with using these wizards - they hide what is really happening. Don't use them. They should never be used. Write your own database code, and write it in a DAL (Data Access Layer) class.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
Dear Sir,
Thank you very much for your quick reply. I would like to mention that these lines have been coded by seeing Microsoft Help and I have not used the Wizard for creating this.
The problem that this code doesn't give any error, but at the same time data is not inserting in table.
Please help me in this regard.
Thanks,
Sachin Jain
|
|
|
|
|
sacheesach wrote: I would like to mention that these lines have been coded by seeing Microsoft Help and I have not used the Wizard for creating this.
Okay - it looked a bit like you were using wizard created code, but on looking again it doesn't seem to be the case anymore.
sacheesach wrote: The problem that this code doesn't give any error
It probably does but you are using the anti-pattern known at try-catch-ignore which is summed up by this line in your code:
catch(SqlException e){}
Comment out the try and catch statements to run the code inside on its own. Does the debugger point out an error?
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
Sir,
Thanks for your suggestions.
It was a silly mistake committed by my new programmer. In fact the Permissions (Select, Insert, Delete, Update etc.) on the Categories Table were unchecked and this was the reason the error was given.
Thank you again for your kind support.
Sachin Jain
|
|
|
|
|
Hi!
Hope every1 is doing fine. I am having an issue with database, i am using SQL Server 2000 and Asp.Net (VS.NET 2003) to develop my application. There is a scenario in my application where i need to compare rows of a table with each other and if they match then store its one copy in another table and delete both copies from first table.
Problem is that the table contains more than 50 fields (columns) and if i try to compare each column through query it will take hell of a time...i was wondering if there is any builtin funtion or some technique through which i can compare two rows without writing lengthy query.
Also i want to keep track of those fields of table which does not match and stotre their information in another table.
Any kind of help is appriciated...thanks in advance!
Regards,
Affan Ahmad Toor
..................
QUAIDIAN FOR ONCE, QUAIDIAN FOR EVER!
|
|
|
|
|
Affan Toor wrote: There is a scenario in my application where i need to compare rows of a table with each other and if they match then store its one copy in another table
How it should match ? All columns should match or only some columns. I presume that it should match all cloumns. Writing a single query that does all theses things seems impossible to me. You can do this using cursors.
Declare YourCursor Cursor For
Select Coulumn1,Column2,Column3..... from your table
Open YourCursor
FETCH NEXT FROM YourCursor into @Columnvar1,@Columnvar2...
WHILE @@FETCH_STATUS <> -1
BEGIN
--Select rows from table and insert to new table
Insert into newtable
Select columns fom table where column1=@Columnvar1, column2 = @Columnvar1
FETCH NEXT FROM YourCursor into @Columnvar1,@Columnvar2...
END
Close YourCursor
deallocate YourCursor
select * from newtable
This may insert duplicate rows into newtable. You need to refine the code to make it work for you.
|
|
|
|
|
HI!
thanks for reply, i will tryn this query and return to you if further help is required.
Regards,
Affan Ahmad Toor
..................
QUAIDIAN FOR ONCE, QUAIDIAN FOR EVER!
|
|
|
|
|
 HI,
Do we have a tool to capture requests made to a Syabse Server, same like SQL Profiler.
Bala
|
|
|
|
|
Have you looked around at Sybase? I don't know much about Sybase, and I cannot really help you other than recommending looking at Sybase's resources.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
hi, what is the difference between SQL 2005 32bit and 64 bit and which one should i install
|
|
|
|
|
The 32-bit edition can only use up to 2GB of virtual address space on 32-bit editions of Windows (3GB if the OS is booted with the /3GB switch but it's not recommended), or 4GB on 64-bit editions of Windows. SQL Server supports the 'address windowing extensions' feature which allows more physical memory to be used, but it's not particularly efficient.
The 64-bit editions (there are separate editions for AMD/Intel 'x64' processors - all new Athlon 64, Opteron, Core 2 and Xeon processors are - and Intel Itanium processors) can use a huge virtual address space - 48 bits' worth.
In general, if you've got a 64-bit computer with the 64-bit OS, you should install the appropriate 64-bit version of SQL Server. If you have an Itanium system, you'd know about it and you wouldn't be asking the question!
|
|
|
|
|
Hi, after saving scripts for creating a mysql database table as a .SQL file, how do I execute it in mysql? Also in what folder must I put it? Thank you in advance for your help.
|
|
|
|
|
Executing SQL Statements from a Text File[^].
Please don't ask me questions on MySQL directly as I have no experience with the product. Post them here if you want, but we're generally more experienced in Microsoft SQL Server, so a MySQL forum (e.g. here[^]) might be able to help you better.
|
|
|
|
|
ASPnoob wrote: after saving scripts for creating a mysql database table as a .SQL file, how do I execute it in mysql?
You exectue it from the mysql command line or from a gui.
ASPnoob wrote: Also in what folder must I put it?
It doesn't matter, you spcify the location of the file.
Google is great for this sort of thing you know.[^]
|
|
|
|
|
Hi this is my probs...,
Its working in query analyzer...,
But its not working, when the values passing from asp.net to stored procedure...,
I am getting error like "Incorrect syntax near '/'...,
Sorry before msg i really confused lot...,
declare @appdate as smalldatetime
declare @noofdays as varchar(50)
declare @month as varchar(50)
declare @year as varchar(50)
set @appdate='8/1/2007'
Set @noofdays=Day(DateAdd(Month, 1, @appdate) - Day(DateAdd(Month, 1,@appdate)))
set @month =datepart(month,@appdate)
set @year=datepart(year,@appdate)
begin
SELECT EM.empId, TC.carddate,SD.Whrs,sd.edate,MHD.Appwhours as AppHours,MHD.AppOtHours as AppOtHours
FROM dbo.tblEmployeeMaster EM INNER JOIN dbo.tblTimeCardmain TC ON EM.empId = TC.empid
INNER JOIN dbo.tblSalaryDetails SD ON EM.empId = SD.EmpID
left outer join dbo.tblMonthHourdetails MHD ON TC.empid=MHD.empid and TC.Carddate=MHD.Sdate
inner join (select empid,max(edate) as edate from tblsalarydetails
where edate <= @month + '/' + @noofdays + '/' + @year group by empid) z on SD.empid=z.empid and SD.edate=z.edate
WHERE TC.carddate = @appdate AND TC.app1 = 1 AND TC.projectid = 104 order by EM.Empid
end
GO
Regards,
Magi
|
|
|
|
|
Why aren't you using a DATETIME type?
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
Hey there,
I've never liked databases and will probably never do so rather than trying to build my own database connection class I copied one written in VB.Net.
Dont know why but it does not work :-?
Here is the code:
[code]
Public Sub FindAndSelect(ByVal TheTableName As String, ByVal oFindValue As String, ByVal ColName As String, ByVal xColName As String, ByRef xValue As String)
Dim i As Integer
For i = 0 To MyDts.Tables(TheTableName).Rows.Count - 1
If MyDts.Tables(TheTableName).Rows(i).Item(ColName) = oFindValue Then
xValue = MyDts.Tables(TheTableName).Rows(i).Item(xColName)
Exit For
End If
Next
End Sub
[/code]
That looks this way after converting it (at least imho  )
[code]
public static void FindAndSelect(string tableName, string findValue, string colName, string xcolName, string xValue)
{
for(int i = 0; i < MyDts.Tables.Count; i++)
{
if(MyDts.Tables[tableName].Rows[i].Item[colName] = findValue)
{
DataRow row = new DataRow();
xValue = MyDts.Tables[tableName].Rows[i].Item[colName];
DataRowCollection col = new DataRowCollection();
break;
}
}
}
[/code]
But that causes a compiler error in the lines:
if(MyDts.Tables[tableName].Rows[i].Item[colName] = findValue)
&
xValue = MyDts.Tables[tableName].Rows[i].Item[colName];
saying that "System.Data.DataRow does not contain a definition for 'Item'".
I dont understand that since it works in VB.Net and according to the MSDN the DataRow class has a 'Item' member.
http://msdn2.microsoft.com/en-us/library/system.data.datarow_members(VS.71).aspx
Any ideas?
|
|
|
|
|
Drop the .Item and just refer to Rows[i][colName]. This is an indexer property so it doesn't need the Items.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
That works, thank you.
Still I dont understand why the .Item doesn't work.
|
|
|
|
|
The collection is called .Items and not .Item. In order to do this, you would have to do .Items[...].
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
c0rvus wrote: But that causes a compiler error in the lines:
if(MyDts.Tables[tableName].Rows[i].Item[colName] = findValue)
&
xValue = MyDts.Tables[tableName].Rows[i].Item[colName];
bolded code replace with Rows[i][colName]
all what to do is to remove .Item property
I Love SQL
|
|
|
|
|
Hi all,
How to communicate an AJAX and SQL. I want to pass values directly from AJAX to SQL, with out any server side script(like asp,php). Is it possible to do so? If there is an option plz send me the link or plz guide me.
Thanks in advance
Know is Drop, Unknown is Ocean
Know is Drop, Unknown is Ocean
|
|
|
|
|
You would presumably have to use the Classic ADO interface, but:
* There have been security issues in some ADO components (ADODB.Stream) which have led to them being blocked from loading in Internet Explorer;
* It is very unlikely, and very bad practice, for the SQL Server to be exposed to the internet.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
