Definition And Explaination:-
ALTER TABLE allows you to change the structure of an existing table. For example, you can add or delete columns, create indexes, change the type of existing columns, or rename columns or the table itself.You can use ALTER TABLE to modify the structure of a table that has not been added to a database.Alter table command allows
* To add the field;
* To delete the field out;
* To change the DEFAULT value for any of the field;
* To add or delete the table PRIMARY KEY;
* To add or delete the table FOREIGN KEY;
* To add or delete UNIQUE constraint;
* To add or delete CHECK constraint;
* To rename fields or table itself;
* To change field type.

Syntax And Structure:-

ADD [ COLUMN ]   column_definition
|    ADD [ COLUMN ] ( column_definition [,   column_definition ... ] )

|    ADD   table_constraint_definition
|    ADD ( table_constraint_definition [, table_constraint_definition ... ] )

|    ALTER [ COLUMN ] column_name  < SET DEFAULT default_option | DROP DEFAULT>
|    DROP  [ COLUMN ] column_name [ drop_behavior ]
|    DROP PRIMARY KEY
|    DROP INDEX index_name
|    DROP CONSTRAINT IDENT

|    CHANGE [ COLUMN ] old_col_name column_definition
|    MODIFY [ COLUMN ] column_definition
|    RENAME [ AS ] table_name

Examples:-

ALTER TABLE customers ADD CONTACT_PHONE VARCHAR(30)

ALTER TABLE customers ADD COLUMN CONTACT_PHONE VARCHAR(30)

ALTER TABLE customers DROP contact_name

ALTER TABLE Person CHANGE LastName Surnname String ( 40 )

ALTER TABLE Person MODIFY  Surnname String ( 30 )

ALTER TABLE offices ADD CONSTRAINT inregion  FOREIGN KEY ( region ) REFERENCES regions

ALTER TABLE salesreps DROP CONSTRAINT worksin

ALTER TABLE Customer ADD COLUMN Fax2 c(20) NOT NULL

Tags: Mysql, RDBMS/DBMS SQL, SQL Server by admin
No Comments »

Many times in ASP or dot net we may need to connect to a mysql database.For making a connection to mysql database we need to install mysql connector for windows which you can download from:-

http://dev.mysql.com/get/Downloads/Connector-ODBC/5.1/mysql-connector-odbc-5.1.5-win32.msi/from/pick?file=Downloads/Connector-ODBC/5.1/mysql-connector-odbc-5.1.5-win32.msi&mirror=pick&file=Downloads/Connector-ODBC/5.1/mysql-connector-odbc-5.1.5-win32.msi&mirror=pick

After downloading this connector just install it.and follow the instructions at mysql site:-
http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-dsn-windows.html

So all is done…

enjoy mysql databases in ASP code file now

Tags: ASP Dot Net, CMS(Content management System), Dot Net Nuke(DNN), Drupal, Mysql, PHP, Programming, Programming Technologies, SQL Server by admin
No Comments »

First of all you should know  how you can add users in mysql databases:-

• Using CREATE USER and/or GRANT commands
• Inserting a new record into the mysql.user table

First let’s see how to use the CREATE USER command:-

     CREATE USER user [IDENTIFIED BY [PASSWORD] ‘password‘]

e.g

mysql>CREATE USER ‘myuser’@‘localhost’ IDENTIFIED BY ‘mypassword’;

Once user added in databases you need to provide privileges to that user.You can use like:-

mysql>GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO ‘myuser‘@‘localhost’;
or mysql>GRANT ALL ON *.* TO ‘myuser’@‘localhost’;

Another way for creating users in mysql is just enter directoly in user table of mysql and make few entries:-

mysql> INSERT INTO user VALUES('localhost','monty',PASSWORD('some_pass'),
     'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
or
mysql> INSERT INTO user (Host,User,Password) VALUES('localhost','usename','pass');
mysql> FLUSH PRIVILEGES;

Tags: Dot Net Nuke(DNN), Drupal, Linux, Mysql, SQL Server by admin
1 Comment »

In Dotnetnuke many time you can get a blank page on Import module definition.For one of my dnn(dot net nuke) site i did Host=>Module definition=>Import module definition and got a blank page.I did a google and found solution applied that and now making that code available here.For making this thing to work you ned to get few SQL commands run from host sql window.

So, the solution is to run SQL script with Run as Script option, in Host > SQL :

So, the solution is to run SQL script with Run as Script option, in Host > SQL :

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}GetUserRoles]‘) AND OBJECTPROPERTY(id, N’IsPROCEDURE’) = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}GetUserRoles
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}GetUserRoles
@PortalId int,
@UserId int
AS
SELECT
R.*,
UR.UserRoleID,
U.UserID,
U.DisplayName,
U.Email,
UR.EffectiveDate,
UR.ExpiryDate,
UR.IsTrialUsed
FROM {databaseOwner}{objectQualifier}UserRoles UR
INNER JOIN {databaseOwner}{objectQualifier}Users U ON UR.UserID = U.UserID
INNER JOIN {databaseOwner}{objectQualifier}Roles R ON UR.RoleID = R.RoleID
WHERE
U.UserID = @UserId AND R.PortalID = @PortalId

select @ModuleDefID = ModuleDefID
from {databaseOwner}{objectQualifier}ModuleDefinitions
where FriendlyName = 'Module Definitions'
IF NOT EXISTS (SELECT ModuleControlID FROM {databaseOwner}{objectQualifier}ModuleControls WHERE ModuleDefID = @ModuleDefID AND ControlKey = N'Import')
BEGIN
insert into {databaseOwner}{objectQualifier}ModuleControls ( ModuleDefID, ControlKey, ControlTitle, ControlSrc, IconFile, ControlType, ViewOrder, HelpUrl, SupportsPartialRendering )
values ( @ModuleDefID, 'Import', 'Import Module Definition', 'Admin/ModuleDefinitions/ImportModuleDefinition.ascx', NULL, 3, NULL, NULL, 0 )
END


Tags: ASP Dot Net, Dot Net Nuke(DNN), Programming Technologies, SQL Server by admin
No Comments »

There are manby things which are considered as basics in dot net nuke.When you are using host sql window from dnn then most definitely you will need to have ideas of basic sql statements to fetch info from db.Few of them are:-

Listing all tables from DataBase:-

SELECT * FROM information_schema.Tables

Get Stored procedure description and Structure:-

select * from information_schema.routines where routine_name = ‘Stored Procedure Name’

Querry to fetch all tables in a database:

select * from sysobjects where type=’u’

Querry to fetch all procedures in a database:

select * from sysobjects where type=’p’

Display Table Creation And Owner info

sp_help table_name

Display Table structure and columns:-

sp_columns @table_name = ‘table_name‘

this will show all fields from table table_name including data type and length information.

Return a list of objects that can be queried in the current environment

EXEC sp_tables

Return information about the syscolumns table in the Company database

EXEC sp_tables syscolumns, dbo, Company, "'SYSTEM TABLE'"

Altering/modifying cloumn Width :-
alter table table_name ALTER COLUMN column_name varchar(30)

Tags: ASP Dot Net, Dot Net Nuke(DNN), SQL Server by admin
No Comments »