SQL MULTIPLE TABLE QUERY WITH CONDITION

SQL MULTIPLE TABLE QUERY WITH CONDITION

If Not Page.IsPostBack Then
Dim conn As New SqlConnection("Data Source=\\10.182.186.100\pipe\sql\query;Initial Catalog=pay-roll;")
Dim adp As New SqlDataAdapter("select DEPT_NAME from DEPTT" , conn)
Dim dset As New Data.DataSet
adp.Fill(dset, "DEPTT")
datagrid1.datasource=dset
datagrid1.databind()
Dropdownlist1.DataSource = dset
Dropdownlist1.DataTextField = "DEPT_NAME"
Dropdownlist1.DataValueField = "DEPT_NAME"
Dropdownlist1.DataBind()
End If

End Sub
Sub DropDownList1_SelectedIndexChanged(sender As Object, e As System.EventArgs)
Dim conn As New SqlConnection("Data Source=\\10.182.186.100\pipe\sql\query;Initial Catalog=pay-roll;")
Dim adp1 As New SqlDataAdapter("select SECT_NAME from sectmast where ( dept_code = (select dept_code from deptt where

dept_name=" & "'" & Dropdownlist1.TEXT & "'" & "))" , conn)
Dim dset1 As New Data.DataSet
adp1.Fill(dset1, "sectmast")
Dropdownlist2.DataSource = dset1
Dropdownlist2.DataTextField = "SECT_NAME"
Dropdownlist2.DataValueField = "SECT_NAME"
Dropdownlist2.DataBind()


End Sub

Sub DropDownList3_SelectedIndexChanged(sender As Object, e As System.EventArgs)
Dim conn As New SqlConnection("Data Source=\\10.182.186.100\pipe\sql\query;Initial Catalog=pay-roll;")
Dim adp2 As New SqlDataAdapter("SELECT innder.EMP_CODE, innder.F_NAME, innder.L_NAME, DESGNMAS.DESGN_DTL,innder.PBASIC,

innder.SALARY_HD FROM EMPMAST innder INNER JOIN DESGNMAS ON innder.DESGN_CD = DESGNMAS.DESGN_CD where ( dept_code IN (select

dept_code from deptt where dept_name=" & "'" & Dropdownlist1.TEXT & "'" & ") AND SECT_CODE IN (select SECT_CODE from

sectmast where sect_name=" & "'" & Dropdownlist2.TEXT & "'" & ")) AND salary_hd=" & "'" & Dropdownlist3.TEXT & "'", conn)
Dim dset2 As New Data.DataSet
adp2.Fill(dset2, "innder")
datagrid1.datasource=dset2
datagrid1.databind()

End Sub
" HERE IN MULTIPLE QUERY WE HAVE TO USE ALIAS NAME OF OUR FIRST TABLE AS WE HAVE TAKEN "indder" in place of our table "EMPMAST" in query as well as we use "IN" in place of '=' condition and we have to use "INNER JOIN" in place of From EMPMAST, DESGNMAS as well as to link between both table we have to use :
ON innder.Desgn_cd=Desgnmas.Desgn_cd but If we want to unite two table result in one DATASET then we may use this like:
Select EMP_CODE FROM EMPMAST UNION SELECT DESGN_CD FROM DESGNMAS...

IN BODY :


OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged" AutoPostBack="true">

DEPARTMENT

       

Width="104px">
   


SECTION

       

Width="104px">
   

OnSelectedIndexChanged="DropDownList3_SelectedIndexChanged" AutoPostBack="true">

N
P


       
       

DropDownList1_SelectedIndexChanged AND SQL SUB QUERY

DropDownList1_SelectedIndexChanged AND SQL SUB QUERY

Let Suppose you need to refine Dropdownlist2 contents according to list item of Dropdownlist1 which are connected with data base then first you have to use :

OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged" AutoPostBack="true">

autopostback should true and on indexchanged event you have to fire :
Sub DropDownList1_SelectedIndexChanged(sender As Object, e As System.EventArgs)
Dim conn As New SqlConnection("Data Source=\\10.182.186.100\pipe\sql\query;Initial Catalog=pay-roll;")
Dim adp1 As New SqlDataAdapter("select SECT_NAME from sectmast where ( dept_code = (select dept_code from deptt where

dept_name=" & "'" & Dropdownlist1.TEXT & "'" & "))" , conn)
Dim dset1 As New Data.DataSet
adp1.Fill(dset1, "sectmast")
Dropdownlist2.DataSource = dset1
Dropdownlist2.DataTextField = "SECT_NAME"
Dropdownlist2.DataValueField = "SECT_NAME"
Dropdownlist2.DataBind()


End Sub

where at load event already you have loaded dropdownlist1 with data like :

If Not Page.IsPostBack Then
Dim conn As New SqlConnection("Data Source=\\10.182.186.100\pipe\sql\query;Initial Catalog=pay-roll;")
Dim adp As New SqlDataAdapter("select DEPT_NAME from DEPTT" , conn)
Dim dset As New Data.DataSet
adp.Fill(dset, "DEPTT")
datagrid1.datasource=dset
datagrid1.databind()
Dropdownlist1.DataSource = dset
Dropdownlist1.DataTextField = "DEPT_NAME"
Dropdownlist1.DataValueField = "DEPT_NAME"
Dropdownlist1.DataBind()
End If

End Sub

and your header should be :
<%@ Page Language="VB" %>
<%@ import Namespace="System.Data.Sqlclient" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Web.UI.WebControls" %>

dropdownlist should be like :

OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged" AutoPostBack="true">

MEMO_NO

but here since you are initiating your dropdownlist at run tine loading with datatable then here listitem should not be added.

SQL DATABASE CONNECTION IN ASP.NET

SQL DATABASE CONNECTION IN ASP.NET

<%@ Page Language="VB" %>
<%@ import Namespace="System.Data.Sqlclient" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Web.UI.WebControls" %>


Sub Page_Load

If Not Page.IsPostBack Then
Dim conn As New SqlConnection("Data Source=\\10.182.186.100\pipe\sql\query;Initial Catalog=pay-roll;")
Dim adp As New SqlDataAdapter("select * from ALLCDMAS", conn)
Dim dset As New Data.DataSet
adp.Fill(dset, "ALLCDMAS")
datagrid1.datasource=dset
datagrid1.databind()
End If

End Sub






BEFORE DOING THIS, YOU MUST HAVE TO GRANT ACCESSING PERMISSION FOR ASP.NET CLIENT USER TO YOUR SQL SERVER USING SQL SERVER > ENTERPRISE MANAGER > CONSOLE ROOT > MICROSOFT SERVER > SERVER GROUP > LOCAL SERVER > SECURITY > LOGINS > RITGH CLICK AND SELECT NEW LOGIN OPTION THEN > CLICK ... BUTTON AT NAME TEXTBOX > THEN SELECT ASP.NET USER TYPE > ADD > OK > THEN SPECIFY DEFAULT DATA BASE TO YOUR DATABASE NAME THEN ASSIGN SERVER ROLE > SYSTEM ADMIN > DATABASE ACCESS > CHECK YOUR DATABASE NAME THEN OK. NOW ENABLE NAMED PIPE FOR YOUR SERVER REMOTE ACCESS AS \\YOUR SERVER IP\pipe\sql\query , NOW GO TO YOUR CODE PAGE AND INSERT AS BELOW :

let suppose our server is 10.182.186.100 and database is pay-roll and i need ALLCDMAS table accessing then i have to insert like this _________


<%@ Page Language="VB" %>
<%@ import Namespace="System.Data.Sqlclient" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Web.UI.WebControls" %>

Sub Page_Load

If Not Page.IsPostBack Then
Dim conn As New SqlConnection("Data Source=\\10.182.186.100\pipe\sql\query;Initial Catalog=pay-roll;")
Dim adp As New SqlDataAdapter("select * from ALLCDMAS", conn)
Dim dset As New Data.DataSet
adp.Fill(dset, "ALLCDMAS")
datagrid1.datasource=dset
datagrid1.databind()
End If

Different Connecting Strings for ASP.Net

Different Connecting Strings for ASP.Net

Standard security
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False;

with DSN:
ODBC DSNType: ODBC Driver
Usage: DSN=myDSN or FILEDSN=c:\myDsnFile.dsnManufacturer: Microsoft
More info about this driver »Customize string
example values »System DSN
DSN=myDsn;Uid=myUsername;Pwd=; File DSN
FILEDSN=c:\myDsnFile.dsn;Uid=myUsername;Pwd=; .NET Framework Data Provider for ODBCType: .NET Framework Wrapper Class Library
Usage: System.Data.Odbc.OdbcConnectionManufacturer: Microsoft
More info about this wrapper class library »Customize string
example values »Bridging to ODBC DSN
This is just one connection string sample for the wrapping OdbcConnection class that calls the underlying ODBC Driver. See respective ODBC driver for more connection strings to use with this class.DSN=myDsn;Uid=myUsername;Pwd=;

Connectingstring="DSN="mydsnname"

With database password
This is the connection string to use when you have an Access 2007 database protected with a password using the "Set Database Password" function in Access.Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Jet OLEDB:Database Password=MyDbPassword;Some reports of problems with password longer than 14 characters. Also that some characters might cause trouble. If you are having problems, try change password to a short one with normal characters. DataDirectory functionality
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\myAccess2007file.accdb;Persist Security Info=False; .NET Framework Data Provider for OLE DBType: .NET Framework Wrapper Class Library
Usage: System.Data.OleDb.OleDbConnectionManufacturer: Microsoft
More info about this wrapper class library »Customize string example values »Bridging to ACE OLEDB 12.0
This is just one connection string sample for the wrapping OleDbConnection class that calls the underlying OLEDB provider. See respective OLE DB provider for more connection strings to use with this class.Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False; Microsoft Access accdb ODBC DriverType: ODBC Driver
Usage: Driver={Microsoft Access Driver (*.mdb, *.accdb}Manufacturer: Microsoft
Customize string example values »Standard Security
Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\mydatabase.accdb;Uid=Admin;Pwd=; Workgroup
Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\mydatabase.accdb;SystemDB=C:\mydatabase.mdw;No changes were made to the .mdw file format for Office Access 2007. The Office Access 2007 Workgroup Manager creates .mdw files that are identical to those that are created in Access 2000 through Access 2003. The .mdw files that are created in those earlier versions can be used by databases in Office Access 2007.

ASP.Net on XP XML error solution

ASP.Net on XP XML error solution

You get this error when running your application:

The XML page cannot be displayed.

Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.

A name was started with an invalid character. Error processing resource 'http://localhost/MyApp1/...

<%@ Register Tagprefix="MyApp1" TagName="Header" Src="../Header & Footer/Header.ascx" %>

This error occurs when your Microsoft .NET Framework is not properly installed, not properly configured or not registered with the Microsoft IIS web server.
Just go to command prompt then type :
" c:\windows\Microsoft.net\framework\v2.0.50727\aspnet_regiis.exe i "
after this if you had installed asp.net 2.0.50727 in form of .net framework 2.0.50727 from internet into your computer then this will initialised .net framework to run asp.net page on your XP web server.