Pasar un parametro a una sentencia Select

28/07/2004 - 15:47 por Roberto Londono | Informe spam
Hola amigos del grupo. Tengo un inquietud de como pasar
parametros a una sentencia select.
1. Yo creo SQLConnection, SQLDataAdapter arrastrandolo
despues de haber seleccinado la tabla. Este me genera un
codigo Select, Insert, .
2. Creo un data set con el ayudante.
3. Creo el codigo para mostralo en datagrind.
Perfecto.
pero ahora mi inquietud es que yo creo una caja de texto,
para introducir un parametro en la busqueda y hay
comienza el problema.

Yo revise el ejeplo del tutorial
Sub GetAuthors_Click(Sender As Object, E As EventArgs)

Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyCommand As SqlDataAdapter

Dim SelectCommand As String = "select * from
Authors where state = @State"

MyConnection = New SqlConnection("server=(local)
\NetSDK;database=pubs;Trusted_Connection=yes")
MyCommand = New SqlDataAdapter(SelectCommand,
MyConnection)

MyCommand.SelectCommand.Parameters.Add(New
SqlParameter("@State", SqlDbType.NVarChar, 2))
MyCommand.SelectCommand.Parameters
("@State").Value = MySelect.Value

DS = new DataSet()
MyCommand.Fill(DS, "Authors")

MyDataGrid.DataSource=DS.Tables
("Authors").DefaultView
MyDataGrid.DataBind()
End Sub
Pero este me esta en un sub y para mi caso no me sirve
hacerlo en un sub o funcion a parte por que me sale un
error en el datagrid.

Specified argument was out of the range of valid values.
Parameter name: index
Description: An unhandled exception occurred during the
execution of the current web request. Please review the
stack trace for more information about the error and
where it originated in the code.

Exception Details: System.ArgumentOutOfRangeException:
Specified argument was out of the range of valid values.
Parameter name: index

Source Error:


Line 287:
Line 288:
Line 289: CodCard = e.Item.Cells(1).Text
Line 290: TBCardNo.Text = CodCard
Line 291:

Todo el codigo del webpag esta a continuacion.

Imports System.Data
Imports System.Data.SqlClient



Public Class FmrRepUserMove
Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private
Sub InitializeComponent()
Me.SqlSelectCommand1 = New
System.Data.SqlClient.SqlCommand
Me.SqlInsertCommand1 = New
System.Data.SqlClient.SqlCommand
Me.SqlConnection1 = New
System.Data.SqlClient.SqlConnection
Me.SqlDataAdapter1 = New
System.Data.SqlClient.SqlDataAdapter
Me.MeS_VistCtrl_DS_ViewInVisitor1 = New
MES.MES_VistCtrl_DS_ViewInVisitor




CType(Me.MeS_VistCtrl_DS_ViewInVisitor1,
System.ComponentModel.ISupportInitialize).BeginInit()
'
'SqlSelectCommand1

'
Me.SqlSelectCommand1.CommandText = "SELECT [Full
Name Visitor], [Name Employee], InTime, TimeShedule,
OutTime, Identi" & _
"fication, VisitorIdentification_ID,
ControlVisitId, DateVisit, Reason, Comment, " & _
"State FROM MES_PWNT_VIEW_INVisitor WHERE (convert
(char(10),DateVisit,104)>=convert(char
(10),@Date_Need1,104)and convert(char(10),DateVisit,104)
<=convert(char(10),@Date_Need2,104))ORDER BY DateVisit"
Me.SqlSelectCommand1.Connection =
Me.SqlConnection1
'
'SqlInsertCommand1
'
Me.SqlInsertCommand1.CommandText = "INSERT INTO
MES_PWNT_VIEW_INVisitor([Full Name Visitor], [Name
Employee], InTime," & _
" TimeShedule, OutTime, Identification,
DateVisit, Reason, Comment, State) VALUES" & _
" (@Param1, @Param2, @InTime, @TimeShedule,
@OutTime, @Identification, @DateVisit" & _
", @Reason, @Comment, @State); SELECT [Full Name
Visitor], [Name Employee], InTim" & _
"e, TimeShedule, OutTime, Identification,
VisitorIdentification_ID, ControlVisitI" & _
"d, DateVisit, Reason, Comment, State FROM
MES_PWNT_VIEW_INVisitor"
Me.SqlInsertCommand1.Connection =
Me.SqlConnection1
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Param1",
System.Data.SqlDbType.VarChar, 240, "Full Name Visitor"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Param2",
System.Data.SqlDbType.VarChar, 60, "Name Employee"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@InTime",
System.Data.SqlDbType.DateTime, 8, "InTime"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@TimeShedule",
System.Data.SqlDbType.VarChar, 50, "TimeShedule"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@OutTime",
System.Data.SqlDbType.DateTime, 8, "OutTime"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Identification",
System.Data.SqlDbType.VarChar, 50, "Identification"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@DateVisit",
System.Data.SqlDbType.DateTime, 8, "DateVisit"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Reason",
System.Data.SqlDbType.NVarChar, 300, "Reason"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Comment",
System.Data.SqlDbType.VarChar, 200, "Comment"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@State",
System.Data.SqlDbType.SmallInt, 2, "State"))
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "workstation
idýRMQ01;packet size@96;integrated security=SSPI;data
source¾NT" & _
"ON;persist security info=False;initial
catalog=PWNT"
'
'SqlDataAdapter1
'
Me.SqlDataAdapter1.InsertCommand =
Me.SqlInsertCommand1
Me.SqlDataAdapter1.SelectCommand =
Me.SqlSelectCommand1
Me.SqlDataAdapter1.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping
("Table", "MES_PWNT_VIEW_INVisitor", New
System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("Full Name
Visitor", "Full Name Visitor"), New
System.Data.Common.DataColumnMapping("Name
Employee", "Name Employee"), New
System.Data.Common.DataColumnMapping("InTime", "InTime"),
New System.Data.Common.DataColumnMapping
("TimeShedule", "TimeShedule"), New
System.Data.Common.DataColumnMapping
("OutTime", "OutTime"), New
System.Data.Common.DataColumnMapping
("Identification", "Identification"), New
System.Data.Common.DataColumnMapping
("VisitorIdentification_ID", "VisitorIdentification_ID"),
New System.Data.Common.DataColumnMapping
("ControlVisitId", "ControlVisitId"), New
System.Data.Common.DataColumnMapping
("DateVisit", "DateVisit"), New
System.Data.Common.DataColumnMapping("Reason", "Reason"),
New System.Data.Common.DataColumnMapping
("Comment", "Comment"), New
System.Data.Common.DataColumnMapping("State", "State")})})
'
'MeS_VistCtrl_DS_ViewInVisitor1
'
Me.MeS_VistCtrl_DS_ViewInVisitor1.DataSetName
= "MES_VistCtrl_DS_ViewInVisitor"
Me.MeS_VistCtrl_DS_ViewInVisitor1.Locale = New
System.Globalization.CultureInfo("en-US")
CType(Me.MeS_VistCtrl_DS_ViewInVisitor1,
System.ComponentModel.ISupportInitialize).EndInit()



End Sub
Protected WithEvents Mensaje As
System.Web.UI.WebControls.Label
Protected WithEvents BtnGoBack As
System.Web.UI.WebControls.Button
Protected WithEvents BtnContinue As
System.Web.UI.WebControls.Button
Protected WithEvents TBDate2 As
System.Web.UI.WebControls.TextBox
Protected WithEvents TBDate1 As
System.Web.UI.WebControls.TextBox
Protected WithEvents Label1 As
System.Web.UI.WebControls.Label
Protected WithEvents Date1 As
System.Web.UI.WebControls.Label
Protected WithEvents Image1 As
System.Web.UI.WebControls.Image
Protected WithEvents TBCardNo As
System.Web.UI.WebControls.TextBox

Protected WithEvents DGDetail As
System.Web.UI.WebControls.DataGrid
Protected WithEvents MasterGrid As
System.Web.UI.WebControls.DataGrid
Protected WithEvents Calendar1 As
System.Web.UI.WebControls.Calendar
Protected WithEvents Calendar2 As
System.Web.UI.WebControls.Calendar
Protected WithEvents Label28 As
System.Web.UI.WebControls.Label
Protected WithEvents SqlSelectCommand1 As
System.Data.SqlClient.SqlCommand
Protected WithEvents SqlInsertCommand1 As
System.Data.SqlClient.SqlCommand
Protected WithEvents SqlConnection1 As
System.Data.SqlClient.SqlConnection
Protected WithEvents SqlDataAdapter1 As
System.Data.SqlClient.SqlDataAdapter
Protected WithEvents MeS_VistCtrl_DS_ViewInVisitor1
As MES.MES_VistCtrl_DS_ViewInVisitor

'NOTE: The following placeholder declaration is
required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As
System.Object

Private Sub Page_Init(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web
Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region
Dim CartView As DataView
Private Sub Page_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load

With MasterGrid
' Enable paging.
.AllowPaging = True
' Display 5 page numbers at a time.
.PagerStyle.Mode = PagerMode.NumericPages
.PagerStyle.PageButtonCount = 10
.PageSize = 3
End With










'Put user code to initialize the page here


MasterGrid.Enabled = False


End Sub




Private Sub BtnContinue_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
BtnContinue.Click
If TBDate1.Text = "" Or TBDate2.Text = "" Then
Mensaje.Text = " Need date"

Else
Mensaje.Text = ""


MasterGrid.DataSource = GetLogVisitor
(TBDate1.Text, TBDate2.Text)

MasterGrid.DataBind()


MasterGrid.Enabled = True
'MasterGrid.Enabled = False
End If











End Sub

Private Sub BtnGoBack_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
BtnGoBack.Click
MasterGrid.Enabled = False
Response.Redirect
("/MES/MES/Security/Report/MRS.aspx")
End Sub

Function GetLogVisitor(ByVal date_Need1 As Date,
ByVal date_Need2 As Date) As System.Data.DataSet

Dim connectionString As String = Application
("Conexion1")
Dim dbConnection As System.Data.IDbConnection =
New System.Data.SqlClient.SqlConnection(connectionString)


Dim queryString As String = "SELECT * FROM
MES_Sec_View_Identication_Visitor_Log WHERE
(MES_Sec_View_Identication_Visitor_Log.DateVisit >=
@Date_Need1) AND
(MES_Sec_View_Identication_Visitor_Log.DateVisit <=
@Date_Need2) ORDER BY DateVisit "
'convert(char(10),ReceptionDate,104)>=convert(char
(10),@Date_Need1,104)and convert(char
(10),ReceptionDate,104)<=convert(char(10),@Date_Need2,104)


Dim dbCommand As System.Data.IDbCommand = New
System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection


Dim dbParam_date_Need1 As
System.Data.IDataParameter = New
System.Data.SqlClient.SqlParameter
dbParam_date_Need1.ParameterName = "@Date_Need1"
dbParam_date_Need1.Value = date_Need1
dbParam_date_Need1.DbType =
System.Data.DbType.DateTime
dbCommand.Parameters.Add(dbParam_date_Need1)

Dim dbParam_date_Need2 As
System.Data.IDataParameter = New
System.Data.SqlClient.SqlParameter
dbParam_date_Need2.ParameterName = "@Date_Need2"
dbParam_date_Need2.Value = date_Need2
dbParam_date_Need2.DbType =
System.Data.DbType.DateTime
dbCommand.Parameters.Add(dbParam_date_Need2)



Dim dataAdapter As System.Data.IDbDataAdapter =
New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New
System.Data.DataSet
dataAdapter.Fill(dataSet)
'Hola 1
Return dataSet

End Function





Function GetVisitorDetail(ByVal date_Need1 As Date,
ByVal date_Need2 As Date, ByVal cardcod As String) As
System.Data.DataSet

Dim connectionString As String = Application
("Conexion1")
Dim dbConnection As System.Data.IDbConnection =
New System.Data.SqlClient.SqlConnection(connectionString)


Dim queryString As String = "SELECT LogUserId,
Logdevdescrp, Logdevadescrp, Logdevtypedesc, Evnt_" & _
"addr, Rec_Dat, Evnt_Dat, Evnt_Descrp,
Descrp, Location, Loop_descrp, Panel_Descr" & _
"p, Card_No, LName, Fname, MI, Stat_Cod,
Comp_Name, Last_Acc, Expire_Dat, Issue_D" & _
"at, PinCode FROM MES_HRD_LogUser WHERE
(MES_HRD_LogUser.ReceptionDate >= @Date_Need1) AND
(MES_HRD_LogUser.ReceptionDate <= @Date_Need2) AND
(MES_HRD_LogUser.Card_No = @cardcod)ORDER BY
ReceptionDate "



Dim dbCommand As System.Data.IDbCommand = New
System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection


Dim dbParam_date_Need1 As
System.Data.IDataParameter = New
System.Data.SqlClient.SqlParameter
dbParam_date_Need1.ParameterName = "@Date_Need1"
dbParam_date_Need1.Value = date_Need1
dbParam_date_Need1.DbType =
System.Data.DbType.DateTime
dbCommand.Parameters.Add(dbParam_date_Need1)

Dim dbParam_date_Need2 As
System.Data.IDataParameter = New
System.Data.SqlClient.SqlParameter
dbParam_date_Need2.ParameterName = "@Date_Need2"
dbParam_date_Need2.Value = date_Need2
dbParam_date_Need2.DbType =
System.Data.DbType.DateTime
dbCommand.Parameters.Add(dbParam_date_Need2)

Dim dbParam_cardcod As System.Data.IDataParameter
= New System.Data.SqlClient.SqlParameter
dbParam_cardcod.ParameterName = "@cardcod"
dbParam_cardcod.Value = cardcod
dbParam_cardcod.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_cardcod)

Dim dataAdapter As System.Data.IDbDataAdapter =
New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New
System.Data.DataSet
dataAdapter.Fill(dataSet)
'Hola 1
Return dataSet

End Function









Private Sub MasterGrid_PageIndexChanged1(ByVal source
As Object, ByVal e As
System.Web.UI.WebControls.DataGridPageChangedEventArgs)
Handles MasterGrid.PageIndexChanged
MasterGrid.CurrentPageIndex = e.NewPageIndex
MasterGrid.Enabled = True
MasterGrid.DataSource = GetLogVisitor
(TBDate1.Text, TBDate2.Text)
MasterGrid.DataBind()

End Sub

Private Sub MasterGrid_ItemCommand1(ByVal source As
Object, ByVal e As
System.Web.UI.WebControls.DataGridCommandEventArgs)
Handles MasterGrid.ItemCommand
'Dim Itm As Integer

' Itm = e.Item.ItemIndex()
'Dim keys As DataKeyCollection
'keys = MasterGrid.DataKeys





Dim CodCard As String
'CodCard = Me.MasterGrid.SelectedItem.Cells
(0).Text



CodCard = e.Item.Cells(1).Text
TBCardNo.Text = CodCard


MasterGrid.Enabled = True
DGDetail.DataSource = GetVisitorDetail
(TBDate1.Text, TBDate2.Text, TBCardNo.Text)
' SqlDataAdapter1.Fill
(MeS_HR_DS_ViewIdenticationVisitorlog1)
DGDetail.DataBind()
DGDetail.Enabled = True
End Sub
Private Sub Calendar2_SelectionChanged(ByVal sender
As System.Object, ByVal e As System.EventArgs) Handles
Calendar2.SelectionChanged
Mensaje.Text = ""
TBDate2.Text = Calendar2.SelectedDate
End Sub

Private Sub Calendar1_SelectionChanged(ByVal sender
As System.Object, ByVal e As System.EventArgs) Handles
Calendar1.SelectionChanged
Mensaje.Text = ""
TBDate1.Text = Calendar1.SelectedDate
End Sub
End Class
 

Leer las respuestas

#1 Franco Figún
28/07/2004 - 16:12 | Informe spam
Proba este ejemplo:

<%@ Page Language="VB" debug="true"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script runat="server">
Dim Conn As New OleDbConnection("Provider=" & _
"Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Inetpub\wwwroot\Agendaewagenda.mdb")

Sub ObtenerDatos(obj As Object, e As EventArgs)
Dim objCmd As OleDbCommand = New OleDbCommand _
("SELECT * FROM newagenda WHERE id = @ID", Conn)
Dim objLector As OleDbDataReader
Dim objParam As OleDbParameter

objParam = objCmd.Parameters.Add("@ID", _
OleDbType.Integer)
objParam.Direction = ParameterDirection.Input
objParam.Value = tbID.Text

Try
objCmd.Connection.Open()
objLector = objCmd.ExecuteReader
Catch ex As OleDbException
Label1.Text = "Error al obtener datos de la base de datos."
End Try

DataGrid1.DataSource = objLector
DataGrid1.DataBind()

objLector.Close
objCmd.Connection.Close()
End Sub
</script>

<html><body>
<form runat="server">
<asp:Label id="Label1" runat="server" /><br>
Teclee un identificador: <asp:TextBox id="tbID" runat="server"
AutoPostBack=True
OnTextChanged=ObtenerDatos /><p>
<asp:DataGrid id="DataGrid1" runat="server"
BorderColor="black" GridLines="Vertical"
cellpadding="4" cellspacing="0" width="100%"
Font-Name="Arial" Font-Size="8pt"
HeaderStyle-BackColor="#cccc99"
ItemStyle-BackColor="#ffffff"
AlternatingItemStyle-Backcolor="#cccccc"
AutoGenerateColumns="true" />
</form>
</body></html>

FF
www.francofigun.com.ar
www.microsofties.com.ar
Yahoo MSN:
"Roberto Londono" wrote in message
news:5ac401c474a9$7494cb60$
Hola amigos del grupo. Tengo un inquietud de como pasar
parametros a una sentencia select.
1. Yo creo SQLConnection, SQLDataAdapter arrastrandolo
despues de haber seleccinado la tabla. Este me genera un
codigo Select, Insert, .
2. Creo un data set con el ayudante.
3. Creo el codigo para mostralo en datagrind.
Perfecto.
pero ahora mi inquietud es que yo creo una caja de texto,
para introducir un parametro en la busqueda y hay
comienza el problema.

Yo revise el ejeplo del tutorial
Sub GetAuthors_Click(Sender As Object, E As EventArgs)

Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyCommand As SqlDataAdapter

Dim SelectCommand As String = "select * from
Authors where state = @State"

MyConnection = New SqlConnection("server=(local)
\NetSDK;database=pubs;Trusted_Connection=yes")
MyCommand = New SqlDataAdapter(SelectCommand,
MyConnection)

MyCommand.SelectCommand.Parameters.Add(New
SqlParameter("@State", SqlDbType.NVarChar, 2))
MyCommand.SelectCommand.Parameters
("@State").Value = MySelect.Value

DS = new DataSet()
MyCommand.Fill(DS, "Authors")

MyDataGrid.DataSource=DS.Tables
("Authors").DefaultView
MyDataGrid.DataBind()
End Sub
Pero este me esta en un sub y para mi caso no me sirve
hacerlo en un sub o funcion a parte por que me sale un
error en el datagrid.

Specified argument was out of the range of valid values.
Parameter name: index
Description: An unhandled exception occurred during the
execution of the current web request. Please review the
stack trace for more information about the error and
where it originated in the code.

Exception Details: System.ArgumentOutOfRangeException:
Specified argument was out of the range of valid values.
Parameter name: index

Source Error:


Line 287:
Line 288:
Line 289: CodCard = e.Item.Cells(1).Text
Line 290: TBCardNo.Text = CodCard
Line 291:

Todo el codigo del webpag esta a continuacion.

Imports System.Data
Imports System.Data.SqlClient



Public Class FmrRepUserMove
Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private
Sub InitializeComponent()
Me.SqlSelectCommand1 = New
System.Data.SqlClient.SqlCommand
Me.SqlInsertCommand1 = New
System.Data.SqlClient.SqlCommand
Me.SqlConnection1 = New
System.Data.SqlClient.SqlConnection
Me.SqlDataAdapter1 = New
System.Data.SqlClient.SqlDataAdapter
Me.MeS_VistCtrl_DS_ViewInVisitor1 = New
MES.MES_VistCtrl_DS_ViewInVisitor




CType(Me.MeS_VistCtrl_DS_ViewInVisitor1,
System.ComponentModel.ISupportInitialize).BeginInit()
'
'SqlSelectCommand1

'
Me.SqlSelectCommand1.CommandText = "SELECT [Full
Name Visitor], [Name Employee], InTime, TimeShedule,
OutTime, Identi" & _
"fication, VisitorIdentification_ID,
ControlVisitId, DateVisit, Reason, Comment, " & _
"State FROM MES_PWNT_VIEW_INVisitor WHERE (convert
(char(10),DateVisit,104)>=convert(char
(10),@Date_Need1,104)and convert(char(10),DateVisit,104)
<=convert(char(10),@Date_Need2,104))ORDER BY DateVisit"
Me.SqlSelectCommand1.Connection > Me.SqlConnection1
'
'SqlInsertCommand1
'
Me.SqlInsertCommand1.CommandText = "INSERT INTO
MES_PWNT_VIEW_INVisitor([Full Name Visitor], [Name
Employee], InTime," & _
" TimeShedule, OutTime, Identification,
DateVisit, Reason, Comment, State) VALUES" & _
" (@Param1, @Param2, @InTime, @TimeShedule,
@OutTime, @Identification, @DateVisit" & _
", @Reason, @Comment, @State); SELECT [Full Name
Visitor], [Name Employee], InTim" & _
"e, TimeShedule, OutTime, Identification,
VisitorIdentification_ID, ControlVisitI" & _
"d, DateVisit, Reason, Comment, State FROM
MES_PWNT_VIEW_INVisitor"
Me.SqlInsertCommand1.Connection > Me.SqlConnection1
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Param1",
System.Data.SqlDbType.VarChar, 240, "Full Name Visitor"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Param2",
System.Data.SqlDbType.VarChar, 60, "Name Employee"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@InTime",
System.Data.SqlDbType.DateTime, 8, "InTime"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@TimeShedule",
System.Data.SqlDbType.VarChar, 50, "TimeShedule"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@OutTime",
System.Data.SqlDbType.DateTime, 8, "OutTime"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Identification",
System.Data.SqlDbType.VarChar, 50, "Identification"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@DateVisit",
System.Data.SqlDbType.DateTime, 8, "DateVisit"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Reason",
System.Data.SqlDbType.NVarChar, 300, "Reason"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Comment",
System.Data.SqlDbType.VarChar, 200, "Comment"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@State",
System.Data.SqlDbType.SmallInt, 2, "State"))
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "workstation
idýRMQ01;packet ;integrated security=SSPI;data
source¾NT" & _
"ON;persist security info=False;initial
catalog=PWNT"
'
'SqlDataAdapter1
'
Me.SqlDataAdapter1.InsertCommand > Me.SqlInsertCommand1
Me.SqlDataAdapter1.SelectCommand > Me.SqlSelectCommand1
Me.SqlDataAdapter1.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping
("Table", "MES_PWNT_VIEW_INVisitor", New
System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("Full Name
Visitor", "Full Name Visitor"), New
System.Data.Common.DataColumnMapping("Name
Employee", "Name Employee"), New
System.Data.Common.DataColumnMapping("InTime", "InTime"),
New System.Data.Common.DataColumnMapping
("TimeShedule", "TimeShedule"), New
System.Data.Common.DataColumnMapping
("OutTime", "OutTime"), New
System.Data.Common.DataColumnMapping
("Identification", "Identification"), New
System.Data.Common.DataColumnMapping
("VisitorIdentification_ID", "VisitorIdentification_ID"),
New System.Data.Common.DataColumnMapping
("ControlVisitId", "ControlVisitId"), New
System.Data.Common.DataColumnMapping
("DateVisit", "DateVisit"), New
System.Data.Common.DataColumnMapping("Reason", "Reason"),
New System.Data.Common.DataColumnMapping
("Comment", "Comment"), New
System.Data.Common.DataColumnMapping("State", "State")})})
'
'MeS_VistCtrl_DS_ViewInVisitor1
'
Me.MeS_VistCtrl_DS_ViewInVisitor1.DataSetName
= "MES_VistCtrl_DS_ViewInVisitor"
Me.MeS_VistCtrl_DS_ViewInVisitor1.Locale = New
System.Globalization.CultureInfo("en-US")
CType(Me.MeS_VistCtrl_DS_ViewInVisitor1,
System.ComponentModel.ISupportInitialize).EndInit()



End Sub
Protected WithEvents Mensaje As
System.Web.UI.WebControls.Label
Protected WithEvents BtnGoBack As
System.Web.UI.WebControls.Button
Protected WithEvents BtnContinue As
System.Web.UI.WebControls.Button
Protected WithEvents TBDate2 As
System.Web.UI.WebControls.TextBox
Protected WithEvents TBDate1 As
System.Web.UI.WebControls.TextBox
Protected WithEvents Label1 As
System.Web.UI.WebControls.Label
Protected WithEvents Date1 As
System.Web.UI.WebControls.Label
Protected WithEvents Image1 As
System.Web.UI.WebControls.Image
Protected WithEvents TBCardNo As
System.Web.UI.WebControls.TextBox

Protected WithEvents DGDetail As
System.Web.UI.WebControls.DataGrid
Protected WithEvents MasterGrid As
System.Web.UI.WebControls.DataGrid
Protected WithEvents Calendar1 As
System.Web.UI.WebControls.Calendar
Protected WithEvents Calendar2 As
System.Web.UI.WebControls.Calendar
Protected WithEvents Label28 As
System.Web.UI.WebControls.Label
Protected WithEvents SqlSelectCommand1 As
System.Data.SqlClient.SqlCommand
Protected WithEvents SqlInsertCommand1 As
System.Data.SqlClient.SqlCommand
Protected WithEvents SqlConnection1 As
System.Data.SqlClient.SqlConnection
Protected WithEvents SqlDataAdapter1 As
System.Data.SqlClient.SqlDataAdapter
Protected WithEvents MeS_VistCtrl_DS_ViewInVisitor1
As MES.MES_VistCtrl_DS_ViewInVisitor

'NOTE: The following placeholder declaration is
required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As
System.Object

Private Sub Page_Init(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web
Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region
Dim CartView As DataView
Private Sub Page_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load

With MasterGrid
' Enable paging.
.AllowPaging = True
' Display 5 page numbers at a time.
.PagerStyle.Mode = PagerMode.NumericPages
.PagerStyle.PageButtonCount = 10
.PageSize = 3
End With










'Put user code to initialize the page here


MasterGrid.Enabled = False


End Sub




Private Sub BtnContinue_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
BtnContinue.Click
If TBDate1.Text = "" Or TBDate2.Text = "" Then
Mensaje.Text = " Need date"

Else
Mensaje.Text = ""


MasterGrid.DataSource = GetLogVisitor
(TBDate1.Text, TBDate2.Text)

MasterGrid.DataBind()


MasterGrid.Enabled = True
'MasterGrid.Enabled = False
End If











End Sub

Private Sub BtnGoBack_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
BtnGoBack.Click
MasterGrid.Enabled = False
Response.Redirect
("/MES/MES/Security/Report/MRS.aspx")
End Sub

Function GetLogVisitor(ByVal date_Need1 As Date,
ByVal date_Need2 As Date) As System.Data.DataSet

Dim connectionString As String = Application
("Conexion1")
Dim dbConnection As System.Data.IDbConnection > New System.Data.SqlClient.SqlConnection(connectionString)


Dim queryString As String = "SELECT * FROM
MES_Sec_View_Identication_Visitor_Log WHERE
(MES_Sec_View_Identication_Visitor_Log.DateVisit >> @Date_Need1) AND
(MES_Sec_View_Identication_Visitor_Log.DateVisit <> @Date_Need2) ORDER BY DateVisit "
'convert(char(10),ReceptionDate,104)>=convert(char
(10),@Date_Need1,104)and convert(char
(10),ReceptionDate,104)<=convert(char(10),@Date_Need2,104)


Dim dbCommand As System.Data.IDbCommand = New
System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection


Dim dbParam_date_Need1 As
System.Data.IDataParameter = New
System.Data.SqlClient.SqlParameter
dbParam_date_Need1.ParameterName = "@Date_Need1"
dbParam_date_Need1.Value = date_Need1
dbParam_date_Need1.DbType > System.Data.DbType.DateTime
dbCommand.Parameters.Add(dbParam_date_Need1)

Dim dbParam_date_Need2 As
System.Data.IDataParameter = New
System.Data.SqlClient.SqlParameter
dbParam_date_Need2.ParameterName = "@Date_Need2"
dbParam_date_Need2.Value = date_Need2
dbParam_date_Need2.DbType > System.Data.DbType.DateTime
dbCommand.Parameters.Add(dbParam_date_Need2)



Dim dataAdapter As System.Data.IDbDataAdapter > New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New
System.Data.DataSet
dataAdapter.Fill(dataSet)
'Hola 1
Return dataSet

End Function





Function GetVisitorDetail(ByVal date_Need1 As Date,
ByVal date_Need2 As Date, ByVal cardcod As String) As
System.Data.DataSet

Dim connectionString As String = Application
("Conexion1")
Dim dbConnection As System.Data.IDbConnection > New System.Data.SqlClient.SqlConnection(connectionString)


Dim queryString As String = "SELECT LogUserId,
Logdevdescrp, Logdevadescrp, Logdevtypedesc, Evnt_" & _
"addr, Rec_Dat, Evnt_Dat, Evnt_Descrp,
Descrp, Location, Loop_descrp, Panel_Descr" & _
"p, Card_No, LName, Fname, MI, Stat_Cod,
Comp_Name, Last_Acc, Expire_Dat, Issue_D" & _
"at, PinCode FROM MES_HRD_LogUser WHERE
(MES_HRD_LogUser.ReceptionDate >= @Date_Need1) AND
(MES_HRD_LogUser.ReceptionDate <= @Date_Need2) AND
(MES_HRD_LogUser.Card_No = @cardcod)ORDER BY
ReceptionDate "



Dim dbCommand As System.Data.IDbCommand = New
System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection


Dim dbParam_date_Need1 As
System.Data.IDataParameter = New
System.Data.SqlClient.SqlParameter
dbParam_date_Need1.ParameterName = "@Date_Need1"
dbParam_date_Need1.Value = date_Need1
dbParam_date_Need1.DbType > System.Data.DbType.DateTime
dbCommand.Parameters.Add(dbParam_date_Need1)

Dim dbParam_date_Need2 As
System.Data.IDataParameter = New
System.Data.SqlClient.SqlParameter
dbParam_date_Need2.ParameterName = "@Date_Need2"
dbParam_date_Need2.Value = date_Need2
dbParam_date_Need2.DbType > System.Data.DbType.DateTime
dbCommand.Parameters.Add(dbParam_date_Need2)

Dim dbParam_cardcod As System.Data.IDataParameter
= New System.Data.SqlClient.SqlParameter
dbParam_cardcod.ParameterName = "@cardcod"
dbParam_cardcod.Value = cardcod
dbParam_cardcod.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_cardcod)

Dim dataAdapter As System.Data.IDbDataAdapter > New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New
System.Data.DataSet
dataAdapter.Fill(dataSet)
'Hola 1
Return dataSet

End Function









Private Sub MasterGrid_PageIndexChanged1(ByVal source
As Object, ByVal e As
System.Web.UI.WebControls.DataGridPageChangedEventArgs)
Handles MasterGrid.PageIndexChanged
MasterGrid.CurrentPageIndex = e.NewPageIndex
MasterGrid.Enabled = True
MasterGrid.DataSource = GetLogVisitor
(TBDate1.Text, TBDate2.Text)
MasterGrid.DataBind()

End Sub

Private Sub MasterGrid_ItemCommand1(ByVal source As
Object, ByVal e As
System.Web.UI.WebControls.DataGridCommandEventArgs)
Handles MasterGrid.ItemCommand
'Dim Itm As Integer

' Itm = e.Item.ItemIndex()
'Dim keys As DataKeyCollection
'keys = MasterGrid.DataKeys





Dim CodCard As String
'CodCard = Me.MasterGrid.SelectedItem.Cells
(0).Text



CodCard = e.Item.Cells(1).Text
TBCardNo.Text = CodCard


MasterGrid.Enabled = True
DGDetail.DataSource = GetVisitorDetail
(TBDate1.Text, TBDate2.Text, TBCardNo.Text)
' SqlDataAdapter1.Fill
(MeS_HR_DS_ViewIdenticationVisitorlog1)
DGDetail.DataBind()
DGDetail.Enabled = True
End Sub
Private Sub Calendar2_SelectionChanged(ByVal sender
As System.Object, ByVal e As System.EventArgs) Handles
Calendar2.SelectionChanged
Mensaje.Text = ""
TBDate2.Text = Calendar2.SelectedDate
End Sub

Private Sub Calendar1_SelectionChanged(ByVal sender
As System.Object, ByVal e As System.EventArgs) Handles
Calendar1.SelectionChanged
Mensaje.Text = ""
TBDate1.Text = Calendar1.SelectedDate
End Sub
End Class

Preguntas similares