|
In ASP.Net you can write SQL queries and use a data connection in code to connect to the database just by creating the connection in web.config then referencing the connection on the code behind page. The programmer has each page written with connections, if then statments that, based upon the query, print to a "select" page.
I spend hours trying to reference a collection that would export directly to excel to no avail.
|
|
|
|
|
Ok so you have the tools to do this
Connection can be accessed from the code behind, the select statement is in the gridview somewhere, take that out to the code behind. Use these two to get a datatable and set it as the datasource for the gridview and databind it. That should give you the same functionality as you currently have and you now have access to the datatable.
Note: I've never used the database => control binding so I'm not sure if it will work but I use the database => DAL => code behind => control style and that gives me complete control over my data, and therefore I do not have this issue.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
When I rewrite the code, I will be creating stored procedures for the various select statements because there are 13 pages of code that is written as javascript functions and vb code that display on a "select" page so the selection is not currently tied to the gridview.
The following code may appear to display in a gridview but this is not functioning in the actual program. Each query displays in another page "Select", which opens a separate window.
I will probable have to try programming in javascript using a remove element function of some type as opposed to html for now.
I was trying to do a temporary fix to buy more time while I re-design and re-write.
Sample code from one page...I cut out a lot.
Public Property DateSortDirection() As String
Get
Return ViewState("DateSortDirection")
End Get
Set(ByVal value As String)
ViewState("DateSortDirection") = value
End Set
End Property
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not Page.IsPostBack Then
Me.DateSortDirection = "ASC"
End If
ibtnVersion.Attributes.Add("onClick", "OpenUpdateDate();")
ibtnHelp.Attributes.Add("onClick", "OpenHelp();")
ibtnMap.Attributes.Add("onClick", "OpenJobWindow('Location');")
End Sub
Function PadQuotes(ByVal sTxt As Object)
Dim iX As Integer
iX = InStr(sTxt, "'")
Do While iX > 0
sTxt = Left$(sTxt, iX) & "'" & Right$(sTxt, Len(sTxt) - iX)
iX = iX + 2 ' get past inserted quotes
If Len(sTxt) < iX Then
iX = 0
Else
iX = InStr(iX, sTxt, "'")
End If
Loop
PadQuotes = sTxt
End Function
Function GetURL() As String
'CntrNoLst("[Contract].[Contract]")
Dim stringcntr As String = "2;3,5;7;"
Return "http://localhost/webapplication2/cntrprofilegen.aspx?page=cntrgen&id=" & stringcntr
End Function
Function CntrNoLst(ByVal sSortStr As String) As System.Data.SqlClient.SqlDataReader
'Dim connectionString As String = "server='PARSQL21'; user id='BusDev';password='psbd'; Database='hawk'"
'Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim queryString As String
Dim sFrom As String
Dim sWhere As String
Dim sOrder As String
' queryString = "SELECT DISTINCT [Contract].[Contract], [Contract].[ContractDesc], [JobKeys].[ClientName], [JobKeys].[ProjMgrName], " & _
'"[Contract].[ContractValue], [Contract].[BeginDate], [Contract].[EndDate], " & _
'"[Entity].[EntityName] "
queryString = "SELECT DISTINCT Contract.Contract, Contract.ContractDesc, JobKeys.ClientName, " & _
"JobKeys.ProjMgrName, Contract.ContractValue, Contract.BeginDate, Contract.EndDate, Entity.EntityName"
sFrom = "FROM Contract INNER JOIN " & _
"JobKeys ON Contract.Contract = JobKeys.Contract INNER JOIN " & _
"Entity ON JobKeys.Entity = Entity.Entity INNER JOIN " & _
"Office ON JobKeys.Office = Office.Office "
sWhere = "WHERE 1=1"
'=================================
'sFrom = "FROM [Contract], [JobKeys], [Entity], [Office] "
'sWhere = "WHERE [Contract].[Contract]=[JobKeys].[Contract] AND [JobKeys].[Entity] = [Entity].[Entity] AND [JobKeys].[Office] = [Office].[Office] "
Dim iWhereLen As String = Len(sWhere)
sOrder = " ORDER by " & sSortStr
If Len(txtJob.Text) > 1 Then
sWhere = sWhere & " AND [JobKeys].[Job] in (" & CreateInStatement(PadQuotes(txtJob.Text)) & ") "
End If
If Len(txtProjectMgr.Text) > 1 Then
sWhere = sWhere & " and [JobKeys].[ProjMgrNum] in (" & CreateInStatement(PadQuotes(txtProjectMgr.Text)) & ") "
End If
If Len(lstCntrTerm.SelectedItem.Value) > 1 Then
sWhere = sWhere & " AND [JobKeys].[ContractTerms] in (" & CreateInStatement(lstCntrTerm.SelectedItem.Value) & ") "
End If
If Len(txtClient.Text) > 1 Then
sWhere = sWhere & " AND [JobKeys].[Client] in (" & CreateInStatement(PadQuotes(txtClient.Text)) & ") "
End If
If Len(txtClientName.Text) > 1 Then
sWhere = sWhere & " AND [JobKeys].[ClientName] in (" & CreateInStatement(PadQuotes(txtClientName.Text)) & ") "
End If
'If lstAllEntity.Enabled = False Then
If Len(lstEntity.SelectedItem.Value) > 1 Then
sWhere = sWhere & " AND [JobKeys].[Entity] in (" & CreateInStatement(lstEntity.SelectedItem.Value) & ") "
End If
'End If
'If lstEntity.Enabled = False Then
' If Len(lstAllEntity.SelectedItem.Value) > 1 Then
' sWhere = sWhere & " AND [JobKeys].[Entity] in (" & CreateInStatement(lstAllEntity.SelectedItem.Value) & ") "
' End If
'End If
If Len(txtProposal.Text) > 1 Then
sWhere = sWhere & " and [JobKeys].[Proposal] in (" & CreateInStatement(PadQuotes(txtProposal.Text)) & ") "
End If
If Len(txtContract.Text) > 1 Then
sWhere = sWhere & " and [JobKeys].[Contract] in (" & CreateInStatement(txtContract.Text) & ") "
End If
If Len(lstStatus.SelectedItem.Value) > 1 Or Len(lstBusArrang.SelectedItem.Value) > 1 Or Len(lstStand.SelectedItem.Value) > 1 Then
'sFrom = sFrom & ", [Contract] "
sWhere = sWhere & " and [Contract].[Contract] = [JobKeys].[Contract] "
If Len(lstStatus.SelectedItem.Value) > 1 Then
sWhere = sWhere & " AND [Contract].[Status] in (" & CreateInStatement(lstStatus.SelectedItem.Value) & ") "
End If
If Len(lstBusArrang.SelectedItem.Value) > 1 Then
sWhere = sWhere & " AND [Contract].[BusArrangement] in (" & CreateInStatement(lstBusArrang.SelectedItem.Value) & ") "
End If
If Len(lstStand.SelectedItem.Value) > 1 Then
sWhere = sWhere & " AND [Contract].[StdIndustry] in (" & CreateInStatement(lstStand.SelectedItem.Value) & ") "
End If
End If
'If lstAllOffice.Enabled = False Then
If Len(lstOffice.SelectedItem.Value) > 0 Then
sWhere = sWhere & " AND [JobKeys].[Office] in (" & CreateInStatement(lstOffice.SelectedItem.Text) & ") "
End If
'End If
'If lstOffice.Enabled = False Then
' If Len(lstAllOffice.SelectedItem.Value) > 0 Then
' sWhere = sWhere & " AND [JobKeys].[Office] in (" & CreateInStatement(lstAllOffice.SelectedItem.Text) & ") "
' End If
'End If
If Len(txtProjectID.Text) > 0 Then
sWhere = sWhere & " AND [JobKeys].[Project] in (" & CreateInStatement(PadQuotes(txtProjectID.Text)) & ") "
End If
If Len(txtCntrValue.Text) > 0 Then
sWhere = sWhere & " AND [Contract].[ContractValue] >= " & ConvertToDollars(PadQuotes(txtCntrValue.Text)) & " "
End If
If Len(lstScope.SelectedItem.Value) > 0 Then
sFrom = sFrom & ", [JobBDCodes] "
sWhere = sWhere & " AND [JobKeys].[Job] = [JobBDCodes].[Job] "
sWhere = sWhere & " AND [JobBDCodes].[BDCodeType] = 'ScopeSrvcs' AND [JobBDCodes].[BDCode] in "
'sWhere = sWhere & " ('" & CreateInStatement(lstScope.SelectedItem.Value) & "') "
sWhere = sWhere & " (" & CreateInStatement(lstScope.SelectedItem.Value) & ") "
End If
If Len(lstComMkt.SelectedItem.Value) > 0 Then
sFrom = sFrom & ", [JobBDCodes] "
sWhere = sWhere & " AND [JobKeys].[Job] = [JobBDCodes].[Job] "
sWhere = sWhere & " AND [JobBDCodes].[BDCodeType] = 'CoMrktg' AND [JobBDCodes].[BDCode] in"
'sWhere = sWhere & " ('" & CreateInStatement(lstComMkt.SelectedItem.Value) & "') "
sWhere = sWhere & " (" & CreateInStatement(lstComMkt.SelectedItem.Value) & ") "
End If
If Len(lstCorpMkt.SelectedItem.Value) > 0 Then
sFrom = sFrom & ", [JobBDCodes] "
sWhere = sWhere & " AND [JobKeys].[Job] = [JobBDCodes].[Job] "
sWhere = sWhere & " AND [JobBDCodes].[BDCodeType] = 'CorpMrktg' AND [JobBDCodes].[BDCode] in"
'sWhere = sWhere & " ('" & CreateInStatement(lstCorpMkt.SelectedItem.Value) & "') "
sWhere = sWhere & " (" & CreateInStatement(lstCorpMkt.SelectedItem.Value) & ") "
End If
If Len(lstSF254.SelectedItem.Value) > 0 Then
sFrom = sFrom & ", [JobBDCodes] "
sWhere = sWhere & " AND [JobKeys].[Job] = [JobBDCodes].[Job] "
sWhere = sWhere & " AND [JobBDCodes].BDCodeType = 'SF254' AND [JobBDCodes].BDCode in"
'sWhere = sWhere & " ('" & CreateInStatement(lstSF254.SelectedItem.Value) & "') "
sWhere = sWhere & " (" & CreateInStatement(lstSF254.SelectedItem.Value) & ") "
End If
'-------- Nel
If Len(txtSubcntr.Text) > 0 Then
'txtSubcntr = Replace(txtSubcntr.Text, "'", "''")
sFrom = sFrom & ", [JobSubcontractors], [Subcontractor] "
sWhere = sWhere & " AND [JobKeys].[Job] = [JobSubcontractors].[Job] "
'sWhere = sWhere & " AND JS.SubcontractorName in (" & CreateInStatement(txtSubcntr.Text) & ") "
sWhere = sWhere & " AND [JobSubcontractors].[SubcontractorName]=[Subcontractor].[SubcontractorName]" & _
"and [Subcontractor].[Subcontractor] in (" & CreateInStatement(PadQuotes(txtSubcntr.Text)) & ") "
End If
'-----------
If IsDate(txtCloseDate.Text) Then
sWhere = sWhere & " AND ([JobKeys].[ClosedDate] IS NULL or [JobKeys].[ClosedDate] >= '"
sWhere = sWhere & txtCloseDate.Text & "')"
End If
If iWhereLen = Len(sWhere) Then ' no criteria entered!
lblMsg.Text = "Selection criteria must be entered before initiating a search"
Else
lblMsg.Text = ""
queryString = queryString & " " & sFrom & " " & sWhere & "" & sOrder
Dim queryString2 As String
Dim queryString3 As String
'queryString3 = "INSERT INTO ContractNo (Contract, ContractDesc, ClientName, ProjMgrName, ContractValue, BeginDate, EndDate, EntityName) VALUES(3022, 'John', 'Smith', 2101,3022, 'John', 'Smith', 2101)"
'Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString3, sqlConnection)
Dim sqlCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
sqlConnection.Open()
Dim dataReader As System.Data.SqlClient.SqlDataReader = sqlCommand1.ExecuteReader(System.Data.ConnectionState.Executing)
Dim stringcntr As String
While dataReader.Read()
stringcntr &= dataReader("Contract").ToString() & ";"
End While
'lbl3.Text = stringcntr
End If
End Function
Public Function CreateInStatement(ByVal sData As String)
' This routine will take in a vanilla text string,
' and create an SQL 'in' statement
Dim iCnt As Integer
Dim iX As Integer
Dim sText As String
iCnt = InStr(sData, ",")
If iCnt = 0 Then
CreateInStatement = "'" & sData & "'"
Exit Function
End If
iX = 0
Do While iCnt > 0
sText = sText & "'" & Trim$(Mid$(sData, iX + 1, iCnt - iX - 1)) & "', "
iX = iCnt
iCnt = InStr(iX + 1, sData, ",")
Loop
' now append last key
sText = sText & "'" & Trim$(Right$(sData, Len(sData) - iX)) & "'"
CreateInStatement = sText
End Function
Public Function ConvertToDollars(ByVal sAmt As String)
' This routine will strip off any dollars signs, comma, etc.
Dim sHoldAmt As String
Dim sHoldTxt As String
Dim iCnt As Integer
For iCnt = 1 To Len(sAmt)
sHoldTxt = Mid$(sAmt, iCnt, 1)
If Val(sHoldTxt) > 0 Or sHoldTxt = "0" Or sHoldTxt = "." Then
sHoldAmt = sHoldAmt & sHoldTxt
End If
Next
ConvertToDollars = sHoldAmt
End Function
Function Qrysf254() As System.Data.SqlClient.SqlDataReader
'Dim connectionString As String = "server='PARSQL21'; user id='BusDev';password='psbd'; Database='hawk'"
'Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim queryString As String = "Select distinct [BDCodeDesc], [BDCode] from [BDCodes] " & _
"where [BDCodeType] = 'SF254' ORDER BY [BDCodeDesc]"
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
sqlConnection.Open()
Dim dataReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Return dataReader
End Function
Function Qrystand() As System.Data.SqlClient.SqlDataReader
'Dim connectionString As String = "server='PARSQL21'; user id='BusDev';password='psbd'; Database='hawk'"
'Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim queryString As String = "Select distinct [BDCodeDesc], [BDCode] from [BDCodes] " & _
"where [BDCodeType] = 'StdIndustr' ORDER BY [BDCodeDesc]"
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
sqlConnection.Open()
Dim dataReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Return dataReader
End Function
Function Qryscope() As System.Data.SqlClient.SqlDataReader
'Dim connectionString As String = "server='PARSQL21'; user id='BusDev';password='psbd'; Database='hawk'"
'Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim queryString As String = "Select distinct [BDCodeDesc], [BDCode] from [BDCodes] " & _
"where [BDCodeType] = 'ScopeSrvcs' ORDER BY [BDCodeDesc]"
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
sqlConnection.Open()
Dim dataReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Return dataReader
End Function
Function Qrycommakt() As System.Data.SqlClient.SqlDataReader
'Dim connectionString As String = "server='PARSQL21'; user id='BusDev';password='psbd'; Database='hawk'"
'Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim queryString As String = "SELECT [BDCode], [BDCodeDesc] FROM [BDCodes] " & _
"WHERE [BDCodeType] = 'CoMrktg' ORDER BY [BDCodeDesc] "
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
sqlConnection.Open()
Dim dataReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Return dataReader
End Function
Function Qrycorpmkt() As System.Data.SqlClient.SqlDataReader
'Dim connectionString As String = "server='PARSQL21'; user id='BusDev';password='psbd'; Database='hawk'"
'Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim queryString As String = "SELECT [BDCode], [BDCodeDesc] FROM [BDCodes] " & _
"WHERE [BDCodeType] = 'CorpMrktg' ORDER BY [BDCodeDesc] "
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
sqlConnection.Open()
Dim dataReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Return dataReader
End Function
Function Qryoffice() As System.Data.SqlClient.SqlDataReader
'Dim connectionString As String = "server='PARSQL21'; user id='BusDev';password='psbd'; Database='hawk'"
'Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim queryString As String = "Select DISTINCT [Office], [OfficeName] from [Office] " & _
" ORDER BY [OfficeName]"
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
sqlConnection.Open()
Dim dataReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Return dataReader
End Function
Function Qrycntrterm() As System.Data.SqlClient.SqlDataReader
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim queryString As String = "Select distinct [ContractTermsText], [ContractTerms] " & _
"from [ContractTerms] ORDER BY [ContractTermsText]"
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
sqlConnection.Open()
Dim dataReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Return dataReader
End Function
Function Qrystatus() As System.Data.SqlClient.SqlDataReader
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim queryString As String = "Select Distinct [Status] from [Contract] order by [Status]"
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
sqlConnection.Open()
Dim dataReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Return dataReader
End Function
Function Qrybusarrang() As System.Data.SqlClient.SqlDataReader
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim queryString As String = "SELECT [BDCodeDesc],[BDCode] FROM [BDCodes] " & _
"WHERE [BDCodeType] = 'BusArrange' ORDER BY [BDCodeDesc] "
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
sqlConnection.Open()
Dim dataReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Return dataReader
End Function
Function Qryentity() As System.Data.SqlClient.SqlDataReader
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim queryString As String = "Select DISTINCT [Entity], [EntityName] from [Entity] " & _
" ORDER BY [EntityName]"
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
sqlConnection.Open()
Dim dataReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Return dataReader
End Function
Function Qryemp(ByVal sSortStr As String) As System.Data.SqlClient.SqlDataReader
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim queryString As String
Dim sFrom As String
Dim sWhere As String
Dim sOrder As String
queryString = "SELECT DISTINCT Contract.Contract, Contract.ContractDesc, JobKeys.ClientName, JobKeys.ProjMgrName, " & _
"Contract.ContractValue, Contract.BeginDate, " & _
"Contract.EndDate, Entity.EntityName "
sFrom = "FROM Contract INNER JOIN JobKeys ON Contract.Contract = JobKeys.Contract " & _
"INNER JOIN Entity ON JobKeys.Entity = Entity.Entity " & _
"CROSS JOIN Office "
sWhere = "WHERE 1=1"
Dim iWhereLen As String = Len(sWhere)
sOrder = " ORDER by " & sSortStr
If Len(txtJob.Text) > 1 Then
sWhere = sWhere & " AND [JobKeys].[Job] in (" & CreateInStatement(PadQuotes(txtJob.Text)) & ") "
End If
If Len(txtProjectMgr.Text) > 1 Then
sWhere = sWhere & " and [JobKeys].[ProjMgrNum] in (" & CreateInStatement(PadQuotes(txtProjectMgr.Text)) & ") "
End If
If Len(lstCntrTerm.SelectedItem.Value) > 1 Then
sWhere = sWhere & " AND [JobKeys].[ContractTerms] in (" & CreateInStatement(lstCntrTerm.SelectedItem.Value) & ") "
End If
If Len(txtClient.Text) > 1 Then
sWhere = sWhere & " AND [JobKeys].[Client] in (" & CreateInStatement(PadQuotes(txtClient.Text)) & ") "
End If
If Len(txtClientName.Text) > 1 Then
sWhere = sWhere & " AND [JobKeys].[ClientName] in (" & CreateInStatement(PadQuotes(txtClientName.Text)) & ") "
End If
If Len(lstStatus.SelectedItem.Value) > 1 Or Len(lstBusArrang.SelectedItem.Value) > 1 Or Len(lstStand.SelectedItem.Value) > 1 Then
'sFrom = sFrom & ", [Contract] "
sWhere = sWhere & " and [Contract].[Contract] = [JobKeys].[Contract] "
If Len(lstStatus.SelectedItem.Value) > 1 Then
sWhere = sWhere & " AND [Contract].[Status] in (" & CreateInStatement(lstStatus.SelectedItem.Value) & ") "
End If
If Len(lstBusArrang.SelectedItem.Value) > 1 Then
sWhere = sWhere & " AND [Contract].[BusArrangement] in (" & CreateInStatement(lstBusArrang.SelectedItem.Value) & ") "
End If
If Len(lstStand.SelectedItem.Value) > 1 Then
sWhere = sWhere & " AND [Contract].[StdIndustry] in (" & CreateInStatement(lstStand.SelectedItem.Value) & ") "
End If
End If
If Len(lstOffice.SelectedItem.Value) > 0 Then
sWhere = sWhere & " AND [JobKeys].[Office] in (" & CreateInStatement(lstOffice.SelectedItem.Value) & ") "
End If
If Len(txtProjectID.Text) > 0 Then
sWhere = sWhere & " AND [JobKeys].[Project] in (" & CreateInStatement(PadQuotes(txtProjectID.Text)) & ") "
End If
If Len(txtCntrValue.Text) > 0 Then
sWhere = sWhere & " AND [Contract].[ContractValue] >= " & ConvertToDollars(PadQuotes(txtCntrValue.Text)) & " "
End If
If Len(lstScope.SelectedItem.Value) > 0 Then
sFrom = sFrom & ", [JobBDCodes] "
sWhere = sWhere & " AND [JobKeys].[Job] = [JobBDCodes].[Job] "
sWhere = sWhere & " AND [JobBDCodes].[BDCodeType] = 'ScopeSrvcs' AND [JobBDCodes].[BDCode] in "
'sWhere = sWhere & " ('" & CreateInStatement(lstScope.SelectedItem.Value) & "') "
sWhere = sWhere & " (" & CreateInStatement(lstScope.SelectedItem.Value) & ") "
End If
If Len(lstComMkt.SelectedItem.Value) > 0 Then
sFrom = sFrom & ", [JobBDCodes] "
sWhere = sWhere & " AND [JobKeys].[Job] = [JobBDCodes].[Job] "
sWhere = sWhere & " AND [JobBDCodes].[BDCodeType] = 'CoMrktg' AND [JobBDCodes].[BDCode] in"
'sWhere = sWhere & " ('" & CreateInStatement(lstComMkt.SelectedItem.Value) & "') "
sWhere = sWhere & " (" & CreateInStatement(lstComMkt.SelectedItem.Value) & ") "
End If
If Len(lstCorpMkt.SelectedItem.Value) > 0 Then
sFrom = sFrom & ", [JobBDCodes] "
sWhere = sWhere & " AND [JobKeys].[Job] = [JobBDCodes].[Job] "
sWhere = sWhere & " AND [JobBDCodes].[BDCodeType] = 'CorpMrktg' AND [JobBDCodes].[BDCode] in"
'sWhere = sWhere & " ('" & CreateInStatement(lstCorpMkt.SelectedItem.Value) & "') "
sWhere = sWhere & " (" & CreateInStatement(lstCorpMkt.SelectedItem.Value) & ") "
End If
If Len(lstSF254.SelectedItem.Value) > 0 Then
sFrom = sFrom & ", [JobBDCodes] "
sWhere = sWhere & " AND [JobKeys].[Job] = [JobBDCodes].[Job] "
sWhere = sWhere & " AND [JobBDCodes].BDCodeType = 'SF254' AND [JobBDCodes].BDCode in"
'sWhere = sWhere & " ('" & CreateInStatement(lstSF254.SelectedItem.Value) & "') "
sWhere = sWhere & " (" & CreateInStatement(lstSF254.SelectedItem.Value) & ") "
End If
If IsDate(txtCloseDate.Text) Then
sWhere = sWhere & " AND ([JobKeys].[ClosedDate] IS NULL or [JobKeys].[ClosedDate] >= '"
sWhere = sWhere & txtCloseDate.Text & "')"
End If
If iWhereLen = Len(sWhere) Then ' no criteria entered!
lblMsg.Text = "Selection criteria must be entered before initiating a search"
Else
lblMsg.Text = ""
queryString = queryString & " " & sFrom & " " & sWhere & "" & sOrder
Dim queryString2 As String
Dim queryString3 As String
'queryString3 = "INSERT INTO ContractNo (Contract, ContractDesc, ClientName, ProjMgrName, ContractValue, BeginDate, EndDate, EntityName) VALUES(3022, 'John', 'Smith', 2101,3022, 'John', 'Smith', 2101)"
'Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString3, sqlConnection)
Dim sqlCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
sqlConnection.Open()
Dim dataReader As System.Data.SqlClient.SqlDataReader = sqlCommand1.ExecuteReader(System.Data.ConnectionState.Executing)
'While dataReader.Read()
'lbl3.Text &= dataReader("Contract").ToString() & ";"
'End While
Return dataReader
End If
End Function
Private Sub lstEntity_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstEntity.Init
lstEntity.DataSource = Qryentity()
lstEntity.DataBind()
lstEntity.Items.Insert(0, "")
lstEntity.SelectedIndex = 0
End Sub
'Private Sub lstAllEntity_Init(ByVal sender As System.Object, ByVal e As System.EventArgs)
' lstAllEntity.DataSource = QryAllentity()
' lstAllEntity.DataBind()
' lstAllEntity.Items.Insert(0, "")
' lstAllEntity.SelectedIndex = 0
'End Sub
Private Sub lstOffice_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstOffice.Init
lstOffice.DataSource = Qryoffice()
lstOffice.DataBind()
lstOffice.Items.Insert(0, "")
lstOffice.SelectedIndex = 0
End Sub
'Private Sub lstAllOffice_Init(ByVal sender As System.Object, ByVal e As System.EventArgs)
' lstAllOffice.DataSource = QryAlloffice()
' lstAllOffice.DataBind()
' lstOffice.Items.Insert(0, "")
' lstOffice.SelectedIndex = 0
'End Sub
Private Sub lstCntrTerm_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstCntrTerm.Init
lstCntrTerm.DataSource = Qrycntrterm()
lstCntrTerm.DataBind()
lstCntrTerm.Items.Insert(0, "")
lstCntrTerm.SelectedIndex = 0
End Sub
Private Sub lstStatus_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstStatus.Init
lstStatus.DataSource = Qrystatus()
lstStatus.DataBind()
lstStatus.Items.Insert(0, "")
lstStatus.SelectedIndex = 0
End Sub
Private Sub lstBusArrang_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstBusArrang.Init
lstBusArrang.DataSource = Qrybusarrang()
lstBusArrang.DataBind()
lstBusArrang.Items.Insert(0, "")
lstBusArrang.SelectedIndex = 0
End Sub
Private Sub lstStand_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstStand.Init
lstStand.DataSource = Qrystand()
lstStand.DataBind()
lstStand.Items.Insert(0, "")
lstStand.SelectedIndex = 0
End Sub
Private Sub lstScope_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstScope.Init
lstScope.DataSource = Qryscope()
lstScope.DataBind()
lstScope.Items.Insert(0, "")
lstScope.SelectedIndex = 0
End Sub
Private Sub lstComMkt_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstComMkt.Init
lstComMkt.DataSource = Qrycommakt()
lstComMkt.DataBind()
lstComMkt.Items.Insert(0, "")
lstComMkt.SelectedIndex = 0
End Sub
Private Sub lstCorpMkt_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstCorpMkt.Init
lstCorpMkt.DataSource = Qrycorpmkt()
lstCorpMkt.DataBind()
lstCorpMkt.Items.Insert(0, "")
lstCorpMkt.SelectedIndex = 0
End Sub
Private Sub lstSF254_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstSF254.Init
lstSF254.DataSource = Qrysf254()
lstSF254.DataBind()
lstSF254.Items.Insert(0, "")
lstSF254.SelectedIndex = 0
End Sub
Private Sub imgSearch_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles imgSearch.Click
gridContract.Visible = True
gridContract.DataSource = Qryemp("[Contract].[Contract]")
gridContract.DataBind()
CntrNoLst("[Contract].[Contract]")
lblrecordnum.Text = rsnum & " rows returned"
End Sub
Private Sub gridContract_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles gridContract.ItemDataBound
If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
arrlstCntr.Add(CType(e.Item.DataItem, System.Data.Common.DbDataRecord).Item("Contract"))
'((System.Data.Common.DbDataRecord)e.Item.DataItem)["solution"].ToString();
rsnum = arrlstCntr.Count
End If
End Sub
Private Sub gridContract_SortCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles gridContract.SortCommand
Dim NewSortExpr As String
Select Case e.SortExpression
Case "Contract"
If Me.DateSortDirection = "ASC" Then
NewSortExpr = "[Contract].[Contract] Desc"
Me.DateSortDirection = "DESC"
Else
NewSortExpr = "[Contract].[Contract] Asc"
Me.DateSortDirection = "ASC"
End If
Case "ContractDesc"
If Me.DateSortDirection = "ASC" Then
NewSortExpr = "[Contract].[ContractDesc] Desc"
Me.DateSortDirection = "DESC"
Else
NewSortExpr = "[Contract].[ContractDesc] Asc"
Me.DateSortDirection = "ASC"
End If
Case "ClientName"
If Me.DateSortDirection = "ASC" Then
NewSortExpr = "[JobKeys].[ClientName] Desc"
Me.DateSortDirection = "DESC"
Else
NewSortExpr = "[JobKeys].[ClientName] Asc"
Me.DateSortDirection = "ASC"
End If
Case "ProjMgrName"
If Me.DateSortDirection = "ASC" Then
NewSortExpr = "[JobKeys].[ProjMgrName] Desc"
Me.DateSortDirection = "DESC"
Else
NewSortExpr = "[JobKeys].[ProjMgrName] Asc"
Me.DateSortDirection = "ASC"
End If
Case "ContractValue"
If Me.DateSortDirection = "ASC" Then
NewSortExpr = "[Contract].[ContractValue] Desc"
Me.DateSortDirection = "DESC"
Else
NewSortExpr = "[Contract].[ContractValue] Asc"
Me.DateSortDirection = "ASC"
End If
Case "BeginDate"
If Me.DateSortDirection = "ASC" Then
NewSortExpr = "[Contract].[BeginDate] Desc"
Me.DateSortDirection = "DESC"
Else
NewSortExpr = "[Contract].[BeginDate] Asc"
Me.DateSortDirection = "ASC"
End If
Case "EndDate"
If Me.DateSortDirection = "ASC" Then
NewSortExpr = "[Contract].[EndDate] Desc"
Me.DateSortDirection = "DESC"
Else
NewSortExpr = "[Contract].[EndDate] Asc"
Me.DateSortDirection = "ASC"
End If
Case "EntityName"
If Me.DateSortDirection = "ASC" Then
NewSortExpr = "[Entity].[EntityName] Desc"
Me.DateSortDirection = "DESC"
Else
NewSortExpr = "[Entity].[EntityName] Asc"
Me.DateSortDirection = "ASC"
End If
End Select
gridContract.DataSource = Qryemp(NewSortExpr)
gridContract.DataBind()
End Sub
End Class
|
|
|
|
|
What I try to do is reading any existed row in the table 'Info' (normally only 1 row existed) then update that row immediately, but I have an error when execute at cmd2.ExecuteNonQuery()
- However, if I move the If room_N > 0 Then out of the While loop & place it under lrd.Close() then it works OK! Can some one know the reason?
Dim ra As Integer
Dim cmd As New SqlCommand
Dim cnt_del As Integer
Dim room_N As Integer
Dim str As String
con.ConnectionString = strOpen
con.Open()
cmd.Connection = con
cmd.CommandText = "SELECT * FROM Info"
Dim lrd As SqlDataReader = cmd.ExecuteReader()
If lrd.HasRows Then
Try
'Dim lwr As SqlDataReader
While lrd.Read()
ListBox1.Items.Clear()
str = lrd.GetValue(0).ToString() + ","
str = str + lrd.GetValue(1).ToString() + ","
str = str + lrd.GetValue(2).ToString() + ","
str = str + lrd.GetValue(3).ToString() + ","
str = str + lrd.GetValue(4).ToString() + ","
str = str + lrd.GetValue(5).ToString()+ ","
str = str + lrd.GetValue(6).ToString()
ListBox1.Items.Insert(cnt_del, str)
cnt_del = cnt_del + 1
ListBox_Available.Items.Clear()
room_N = SearchAvailableRooms(lrd.GetValue(2), lrd.GetValue(3), lrd.GetValue(4), lrd.GetValue(0), lrd.GetValue(1))
If room_N > 0 Then
cmd.CommandText = "UPDATE Reservations SET AvailRoomsRates = 'Trials'"
cmd.ExecuteNonQuery()
End If
End While
lrd.Close()
Catch ex As Exception
cnt_error = cnt_error + 1
TextBox_Error.Text = cnt_error.ToString()
End Try
End If
con.Close() 'Whether there is error or not. Close the connection.
|
|
|
|
|
What is the error you are getting? Just telling us you have an error won't help.
|
|
|
|
|
I forgot to display the error message, here it is:
Error while connecting SQL Server. There is already an open DataRedaer associate with this command which must be close first!
However, since I have to update that row before scan the next row (while loop), I can't close it ... may I need another cmd2.ExecuteNonQuery()? But then I need to connect database again?
Is there better command to avoid the abobe problems?
|
|
|
|
|
When you use a reader, the connection is not closed while the reading is going on. Therefore you cannot use a different command associated with that connection to do the update.
Try to create a different connection and command to do the update.
|
|
|
|
|
You are right, however my database is already open & connect ... now I have open & connect anothe one with the same database?
con.ConnectionString = strOpenSQLDatabase
con.Open()
con2.ConnectionString = strOpenSQLDatabase
con2.Open()
Is there other way around?
|
|
|
|
|
Dim ra As Integer
Dim cmd As New SqlCommand
Dim cnt_del As Integer
Dim room_N As Integer
Dim str As String
con.ConnectionString = strOpen
con.Open()
cmd.Connection = con
cmd.CommandText = "SELECT * FROM Info"
'con2 should be declared somewhere
con2.ConnectionString = strOpen
con2.Open()
'cmd2 should be declared somewhere
cmd2.Connection = con2
Dim lrd As SqlDataReader = cmd.ExecuteReader()
If lrd.HasRows Then
Try
'Dim lwr As SqlDataReader
While lrd.Read()
ListBox1.Items.Clear()
str = lrd.GetValue(0).ToString() + ","
str = str + lrd.GetValue(1).ToString() + ","
str = str + lrd.GetValue(2).ToString() + ","
str = str + lrd.GetValue(3).ToString() + ","
str = str + lrd.GetValue(4).ToString() + ","
str = str + lrd.GetValue(5).ToString()+ ","
str = str + lrd.GetValue(6).ToString()
ListBox1.Items.Insert(cnt_del, str)
cnt_del = cnt_del + 1
ListBox_Available.Items.Clear()
room_N = SearchAvailableRooms(lrd.GetValue(2), lrd.GetValue(3), lrd.GetValue(4), lrd.GetValue(0), lrd.GetValue(1))
If room_N > 0 Then
cmd.CommandText = "UPDATE Reservations SET AvailRoomsRates = 'Trials'"
cmd.ExecuteNonQuery()
cmd2.CommandText = "UPDATE Reservations SET AvailRoomsRates = 'Trials'"
cmd2.ExecuteNonQuery()
End If
End While
lrd.Close()
Catch ex As Exception
cnt_error = cnt_error + 1
TextBox_Error.Text = cnt_error.ToString()
End Try
End If
con.Close() 'Whether there is error or not. Close the connection.
con2.Close()
I really don't know if there is an alternative to this. Sorry!
|
|
|
|
|
I will use your update codes
There is an alternative, but I don't know which one is better regarding of process speed (considering there are many rows existed)
1. I move the update If room_N > 0 Then outside the while loop, in the loop I will put LOOP's str values into a string array, when Loop done ---> lrd.Close()
2. Then I use a While Loop to Update all rows from the array
*) This will avoid Open/Connect twice & Close twice ... but might it be a clumsy programming?
Thanks for help 
|
|
|
|
|
If you use transactions on the second connection, the performance will be improved. I don't know how much improvement you will get. On a different database engine, I once improved the performance of a bunch of updates by over 100 times just by putting them into transactions.
Your solution of saving the stuff into an array and updating after the loop is also good.
Good luck!
|
|
|
|
|
Then I will try both methods, the latter is just for curiously
Thanks & 
|
|
|
|
|
Hi experts,
I have read that using a datasource with ADODC1 you can use text boxes to display the database.
I read that all you have to do is on the text box you just change the properties, datafiled and data source
so it would like
Data field = ID
and
DataSource = Adodc1
however the textbox doesn't display anything? THe database works fine, can anybody help?
|
|
|
|
|
txtFirstName.DataField = "FirstName"
Set txtFirstName.DataSource = Adodc1
...
Set txtFirstName.DataSource = Nothing
Copied from an article[^] on databinding
I are Troll 
|
|
|
|
|
Hi,
Can Anyone guide me with scenario below:
I have two applications A and B with its respective database. One table is the same by both applications. For example Officer Profile table. In application A, Officer Profile table get updated very often.
So I have a Connection button in application B to get the updated records from Application A ..
Once connected, I want to show all available tables. and I want to import a selected table.
I want to have a restriction whereas if table is not resided on both databases it cannot be imported..
Steps are:
Connection->select tables(ex.Officer Profile table)->Import table(ex.Officer Profile table)
Now Officer Profile table for database in application B get updated.
Hope I'm clear with my explaination..
Thanks in advance
|
|
|
|
|
You want to synchronize tables that reside in different databases? What database are you using?
SQL Server[^] has good support for replication
I are Troll
|
|
|
|
|
Hi,
Thanks for replying..
Visual Studio 2005 and Sql Express
|
|
|
|
|
waner michaud wrote: Visual Studio 2005 and Sql Express
SQL Express cannot serve as a Publisher or Distributer, only as a Subscriber[^]. There's also the Microsoft Sync Framework to consider, but I couldn't find much examples on Sql Express[^].
The alternative would be to synchronize the data manually. There's a CodeProject article here[^] that explains how this could be done.
I are Troll
|
|
|
|
|
Hi,
thanks again for the information you provided me..
Ok.for my purpose I only need to work with one table..
one table from the source and one table to destination..
destination table has no foreign key constraint..
OfficerTable on the destination database is solely there for searching..
I can do basecally anything on it such(del, alter, modify and so forth) since no constraint..
I think using a stored procedure will be ideal situation in my case..
The article is bit convoluted, with my very litle experience in sp..
can you lead me a bit so I don't get lost in a carbweb.. I do not like to waste valuable in something I am not good at..
Briefly, my cenario is as such, Open a connection, select OfficerTable from the source, send it to destination database which was the original point of initiating the connection to modify OfficerTable in destination.
Once again, I thank you..
|
|
|
|
|
waner michaud wrote: The article is bit convoluted, with my very litle experience in sp..
No worries, experience comes from doing
waner michaud wrote: I do not like to waste valuable in something I am not good at..
If the client wants it, then somebody has to build it. Building yields experience and valuable knowledge.
waner michaud wrote: Briefly, my cenario is as such, Open a connection, select OfficerTable from the source, send it to destination database which was the original point of initiating the connection to modify OfficerTable in destination.
Let's make three scenario's out of that;
0) Records to be deleted from Destination database table
1) Records to be inserted into Destination database table
2) Records to be updated in the Destination database table
The link to the CodeProject[^]-article contains example sourcecode on performing those three steps using stored procedures.
I are Troll
|
|
|
|
|
Hi,
Must I create a sp for delete, insert, and update that reside in the database for the destination table and as well in the source database?
Thanks
|
|
|
|
|
waner michaud wrote: Must I create a sp for delete, insert, and update that reside in the database for the destination table and as well in the source database?
According to the article, you'd only have to create stored procedures for the destination database
I are Troll
|
|
|
|
|
Hi,
Ok. since I need the destination table for search only. Can I just simply alter the destiantion table?
thanks..
|
|
|
|
|
waner michaud wrote: Ok. since I need the destination table for search only. Can I just simply alter the destiantion table?
Would it be OK to overwrite the destination-table with the source-data? If that's the case, then DROP the destination table and do a SELECT INTO. That would create a new table, based on the data that the query returns.
If you want readonly remote access to a table in a different database from the same server, then a view could be another alternative;
CREATE VIEW [destinationDb].[dbo].[RemoteOrders]
AS
SELECT *
FROM [sourceDb].[dbo].[Orders]
I are Troll
|
|
|
|
|
hi,
I am getting the concept of stored procedure a bit..I am going to create a sp to do DROP AND INSERT INTO.
which the following Should I do?
I am using dataset.
I will have a Connect_to_HNP_button. Once I establish a connection, I want to show all the tables. Select OfficerProfile_table, and then call the sp to import table.
Or on the Connect_to_HNP_button_click event call the stored procedure. Inside the sp, establish the connection, and then do DROP DESTINATION OfficerProfile_table, and INSERT INTO DESTINATION OfficerProfile_table FROM THE SOURCE..
Which way to go and how?
Thanks
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
