Home Page
Articoli
Tips & Tricks
News
Forum
Archivio Forum
Blogs
Sondaggi
Rss
Video
Utenti
Chi Siamo
Contattaci
Username:
Password:
Login
Registrati ora!
Recupera Password
Home Page
Stanze Forum
App. WinForms / WPF .NET
Relazione tra tabelle visual studio 2005
lunedì 18 agosto 2008 - 16.57
Elenco Threads
Stanze Forum
Aggiungi ai Preferiti
Cerca nel forum
Mau67
Profilo
| Expert
917
messaggi | Data Invio:
lun 18 ago 2008 - 16:57
Salve ho costruito un form per l'immissione, modifica e cancellazione di record e ho la necessita di fare una relazione tra due o più tabelle vi posto tutto il codice che ho usato in questo form mi serve relazionare le due tabelle
Nella select sono già relazionate però ad ogni inserimento il programma mi fà vedere tanti amministrati quanti sono i recor inseriti in base alla presenza
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports System.Text
Imports System.IO
Public Class Presenza
' Declare objects...
Private Const FILE_NAME As String = "C:\myConnectionString.txt"
Dim objDataSet As DataSet
Dim objDataView As DataView
Dim objCurrencyManager As CurrencyManager
Private Sub FillDataSetAndView()
If Not File.Exists(FILE_NAME) Then
MsgBox(FILE_NAME & " does not exist.")
Return
End If
Dim sr As StreamReader = File.OpenText(FILE_NAME)
Dim sqlString As String
sqlString = sr.ReadLine()
Me.ToolStripStatusLabel2.Text = (sqlString) 'Questo è solo per verificare!
sr.Close()
Dim objConnection As New SqlConnection(sqlString)
Dim objDataAdapter As New SqlDataAdapter( _
" SELECT Anagrafica.IDUtente, Anagrafica.CodiceFiscale, Anagrafica.ForzaArmata," & _
"Anagrafica.Categoria, Anagrafica.Grado, Anagrafica.LivParCod," & _
"Anagrafica.Cognome, Anagrafica.Nome, Presenze.IDPresenza, Presenze.Dal, Presenze.Al, Presenze.Giorni," & _
"Presenze.Mese, Presenze.Anno, Presenze.Motivo" & _
" FROM Anagrafica JOIN Presenze ON Anagrafica.IDUtente = Presenze.IDUtente WHERE (Anagrafica.CodMissione >= 510)", objConnection)
' Initialize a new instance of the DataSet object...
objDataSet = New DataSet()
' Fill the DataSet object with data...
objDataAdapter.Fill(objDataSet, "Anagrafica")
' Set the DataView object to the DataSet object...
objDataView = New DataView(objDataSet.Tables("Anagrafica"))
' Set our CurrencyManager object to the DataView object...
objCurrencyManager = CType(Me.BindingContext(objDataView), CurrencyManager)
End Sub
Private Sub Presenza_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not File.Exists(FILE_NAME) Then
MsgBox(FILE_NAME & " does not exist.")
Return
End If
Dim sr As StreamReader = File.OpenText(FILE_NAME)
Dim sqlString As String
sqlString = sr.ReadLine()
Me.ToolStripStatusLabel2.Text = (sqlString) 'Questo è solo per verificare!
sr.Close()
' Fill the dataset and bind the fields...
FillDataSetAndView()
BindFields()
' Show the current record position...
ShowPosition()
btnAdd.Enabled = False
End Sub
Private Sub BindFields()
' Clear any previous bindings...
txtIDUtente.DataBindings.Clear()
txtCodiceFiscale.DataBindings.Clear()
cboForzaArmata.DataBindings.Clear()
cboCategoria.DataBindings.Clear()
txtGrado.DataBindings.Clear()
txtLivCodPar.DataBindings.Clear()
txtCognome.DataBindings.Clear()
txtNome.DataBindings.Clear()
txtDal.DataBindings.Clear()
txtAl.DataBindings.Clear()
txtMese.DataBindings.Clear()
txtAnno.DataBindings.Clear()
txtGiorni.DataBindings.Clear()
txtMotivo.DataBindings.Clear()
txtIDPresenza.DataBindings.Clear()
' Add new bindings to the DataView object...
txtIDUtente.DataBindings.Add("Text", objDataView, "IDUtente")
txtCodiceFiscale.DataBindings.Add("Text", objDataView, "CodiceFiscale")
cboForzaArmata.DataBindings.Add("Text", objDataView, "ForzaArmata")
cboCategoria.DataBindings.Add("Text", objDataView, "Categoria")
txtGrado.DataBindings.Add("Text", objDataView, "Grado")
txtLivCodPar.DataBindings.Add("Text", objDataView, "LivParCod")
txtCognome.DataBindings.Add("Text", objDataView, "Cognome")
txtNome.DataBindings.Add("Text", objDataView, "Nome")
txtDal.DataBindings.Add("Text", objDataView, "Dal")
txtAl.DataBindings.Add("Text", objDataView, "Al")
txtMese.DataBindings.Add("Text", objDataView, "Mese")
txtAnno.DataBindings.Add("Text", objDataView, "Anno")
txtGiorni.DataBindings.Add("Text", objDataView, "Giorni")
txtMotivo.DataBindings.Add("Text", objDataView, "Motivo")
txtIDPresenza.DataBindings.Add("Text", objDataView, "IDPresenza")
' Display a ready status...
Label35.Text = "Pronto"
End Sub
Private Sub ShowPosition()
'Always format the number in the txtPrice field to include cents
' Display the current position and the number of records
txtPosizione.Text = objCurrencyManager.Position + 1 & _
" of " & objCurrencyManager.Count()
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
' Add items to the combo box...
cboField.Items.Add("Grado")
cboField.Items.Add("Cognome")
cboField.Items.Add("Codice Fiscale")
' Make the first item selected...
cboField.SelectedIndex = 0
' Fill the DataSet and bind the fields...
FillDataSetAndView()
BindFields()
' Show the current record position...
ShowPosition()
End Sub
Private Sub btnPerformSort_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPerformSort.Click
' Determine the appropriate item selected and set the
' Sort property of the DataView object...
Select Case cboField.SelectedIndex
Case 0 'Last Name
objDataView.Sort = "Grado"
Case 1 'First Name
objDataView.Sort = "Cognome"
Case 2 'Book Title
objDataView.Sort = "CodiceFiscale"
End Select
' Call the click event for the MoveFirst button...
btnFirst_Click(Nothing, Nothing)
' Display a message that the records have been sorted...
Label35.Text = "Records Ordinato"
End Sub
Private Sub btnPerformSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPerformSearch.Click
' Declare local variables...
Dim intPosition As Integer
' Determine the appropriate item selected and set the
' Sort property of the DataView object...
Select Case cboField.SelectedIndex
Case 0 'Last Name
objDataView.Sort = "Grado"
Case 1 'First Name
objDataView.Sort = "Cognome"
Case 2 'Book Title
objDataView.Sort = "CodiceFiscale"
End Select
' If the search field is not price then...
If cboField.SelectedIndex < 3 Then
' Find the last name, first name, or title...
intPosition = objDataView.Find(txtCerca.Text)
Else
' otherwise find the price...
intPosition = objDataView.Find(CType(txtCerca.Text, Decimal))
End If
If intPosition = -1 Then
' Display a message that the record was not found...
Label35.Text = "Record Non Trovato"
Else
' Otherwise display a message that the record was
' found and reposition the CurrencyManager to that
' record...
Label35.Text = "Record Trovato"
objCurrencyManager.Position = intPosition
End If
' Show the current record position...
ShowPosition()
End Sub
Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
If MessageBox.Show("Stai per inserire una presenza al " & " " & txtGrado.Text & " " & Me.txtCognome.Text & " " & Me.txtNome.Text & " " & "Vuoi continuare?", "Inserisci presenza", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
' Clear the book title and price fields...
txtDal.Text = ""
txtAl.Text = ""
txtMese.Text = ""
txtAnno.Text = ""
txtGiorni.Text = ""
txtMotivo.Text = ""
btnAdd.Enabled = True
btnUpdate.Enabled = False
' Display a message that the record was added...
Label35.Text = "Inserimento nuovo record"
End If
End Sub
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
Me.txtGiorni.Text = DateDiff("d", Me.txtDal.Text, Me.txtAl.Text) + 1
If Not File.Exists(FILE_NAME) Then
MsgBox(FILE_NAME & " does not exist.")
Return
End If
Dim sr As StreamReader = File.OpenText(FILE_NAME)
Dim sqlString As String
sqlString = sr.ReadLine()
Me.ToolStripStatusLabel2.Text = (sqlString) 'Questo è solo per verificare!
sr.Close()
Dim objConnection As New SqlConnection(sqlString)
' Declare local variables and objects...
Dim intPosition As Integer, intMaxID As Integer
Dim strID As String
Dim objCommand As SqlCommand = New SqlCommand()
' Save the current record position...
intPosition = objCurrencyManager.Position
' Create a new SqlCommand object...
Dim maxIdCommand As SqlCommand = New SqlCommand _
("SELECT MAX(IDPresenza) AS MaxID " & _
"FROM Presenze WHERE IDPresenza LIKE 'PR%'", objConnection)
' Open the connection, execute the command
objConnection.Open()
Dim maxId As Object = maxIdCommand.ExecuteScalar()
' If the MaxID column is null...
If maxId Is DBNull.Value Then
' Set a default value of 1000...
intMaxID = 1000
Else
' otherwise set the strID variable to the value in MaxID...
strID = CType(maxId, String)
' Get the integer part of the string...
intMaxID = CType(strID.Remove(0, 2), Integer)
' Increment the value...
intMaxID += 1
End If
' Finally, set the new ID...
strID = "PR" & intMaxID.ToString
' Set the SqlCommand object properties...
objCommand.Connection = objConnection
objCommand.CommandText = "INSERT INTO Presenze (IDPresenza,IDUtente,CodiceFiscale, Dal, Al, Giorni, Mese, Anno, Motivo) VALUES (@IDPresenza, @IDUtente, @CodiceFiscale, @Dal, @Al, @Giorni, @Mese, @Anno, @Motivo)"
' Add parameters for the placeholders in the SQL in the
' CommandText property...
' Parameter for the title column...
objCommand.Parameters.AddWithValue("@IDPresenza", strID)
' Parameter for the type column
objCommand.Parameters.AddWithValue("@IDUtente", txtIDUtente.Text)
' Parameter for the title column...
objCommand.Parameters.AddWithValue("@CodiceFiscale", txtCodiceFiscale.Text)
' Parameter for the price column...
objCommand.Parameters.AddWithValue("@Dal", txtDal.Text).DbType _
= DbType.DateTime
' Parameter for the price column...
objCommand.Parameters.AddWithValue("@Al", txtAl.Text).DbType _
= DbType.DateTime
' Parameter for the price column...
objCommand.Parameters.AddWithValue("@Giorni", txtGiorni.Text)
' Parameter for the price column...
objCommand.Parameters.AddWithValue("@Mese", txtMese.Text)
' Parameter for the pubdate column
objCommand.Parameters.AddWithValue("@Anno", txtAnno.Text)
' Parameter for the title column...
objCommand.Parameters.AddWithValue("@Motivo", txtMotivo.Text)
' Execute the SqlCommand object to insert the new data...
Try
objCommand.ExecuteNonQuery()
Catch SqlExceptionErr As SqlException
MessageBox.Show(SqlExceptionErr.Message)
End Try
' Close the connection...
objConnection.Close()
' Fill the dataset and bind the fields...
FillDataSetAndView()
BindFields()
' Set the record position to the one that you saved...
objCurrencyManager.Position = intPosition
' Show the current record position...
ShowPosition()
' Display a message that the record was added...
Label35.Text = "Record Inserito"
btnUpdate.Enabled = True
btnAdd.Enabled = False
End Sub
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Me.txtGiorni.Text = DateDiff("d", Me.txtDal.Text, Me.txtAl.Text) + 1
If Not File.Exists(FILE_NAME) Then
MsgBox(FILE_NAME & " does not exist.")
Return
End If
Dim sr As StreamReader = File.OpenText(FILE_NAME)
Dim sqlString As String
sqlString = sr.ReadLine()
Me.ToolStripStatusLabel2.Text = (sqlString) 'Questo è solo per verificare!
sr.Close()
Dim objConnection As New SqlConnection(sqlString)
' Declare local variables and objects...
Dim intPosition As Integer
Dim objCommand As SqlCommand = New SqlCommand()
' Save the current record position...
intPosition = objCurrencyManager.Position
' Set the SqlCommand object properties...
objCommand.Connection = objConnection
objCommand.CommandText = "UPDATE Presenze" & _
" SET CodiceFiscale=@CodiceFiscale, Dal=@Dal, Al=@Al," & _
"Giorni=@Giorni, Mese=@Mese, Anno=@Anno,Motivo=@Motivo WHERE IDPresenza=@IDPresenza"
objCommand.CommandType = CommandType.Text
' Add parameters for the placeholders in the SQL in the
' CommandText property...
' Parameter for the title column...
objCommand.Parameters.AddWithValue("@CodiceFiscale", txtCodiceFiscale.Text)
' Parameter for the type column
objCommand.Parameters.AddWithValue("@Dal", txtDal.Text).DbType _
= DbType.DateTime
' Parameter for the price column...
objCommand.Parameters.AddWithValue("@Al", txtAl.Text).DbType _
= DbType.DateTime
' Parameter for the price column...
objCommand.Parameters.AddWithValue("@Giorni", txtGiorni.Text)
' Parameter for the price column...
objCommand.Parameters.AddWithValue("@Mese", txtMese.Text)
' Parameter for the price column...
objCommand.Parameters.AddWithValue("@Anno", txtAnno.Text)
' Parameter for the pubdate column
objCommand.Parameters.AddWithValue("@Motivo", txtMotivo.Text)
' Parameter for the title_id field...
objCommand.Parameters.AddWithValue _
("@IDPresenza", BindingContext(objDataView).Current("IDPresenza"))
' Open the connection...
objConnection.Open()
' Execute the SqlCommand object to update the data...
objCommand.ExecuteNonQuery()
' Close the connection...
objConnection.Close()
' Fill the DataSet and bind the fields...
FillDataSetAndView()
BindFields()
' Set the record position to the one that you saved...
objCurrencyManager.Position = intPosition
' Show the current record position...
ShowPosition()
' Display a message that the record was updated...
Label35.Text = "Record Modificato"
End Sub
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
If MessageBox.Show("Vuoi eliminare questo record?", "Elimina", MessageBoxButtons.YesNo, MessageBoxIcon.Hand) = Windows.Forms.DialogResult.Yes Then
If Not File.Exists(FILE_NAME) Then
MsgBox(FILE_NAME & " does not exist.")
Return
End If
Dim sr As StreamReader = File.OpenText(FILE_NAME)
Dim sqlString As String
sqlString = sr.ReadLine()
Me.ToolStripStatusLabel2.Text = (sqlString) 'Questo è solo per verificare!
sr.Close()
Dim objConnection As New SqlConnection(sqlString)
' Declare local variables and objects...
Dim intPosition As Integer
Dim objCommand As SqlCommand = New SqlCommand()
' Save the current record position - 1 for the one to be
' deleted...
intPosition = Me.BindingContext(objDataView).Position - 1
' If the position is less than 0 set it to 0...
If intPosition < 0 Then
intPosition = 0
End If
' Set the Command object properties...
objCommand.Connection = objConnection
objCommand.CommandText = "DELETE FROM Presenze " & _
"WHERE IDPresenza = @IDPresenza;" & _
"DELETE FROM Presenze WHERE IDPresenza = @IDPresenza"
' Parameter for the title_id field...
objCommand.Parameters.AddWithValue _
("@IDPresenza", BindingContext(objDataView).Current("IDPresenza"))
' Open the database connection...
objConnection.Open()
' Execute the SqlCommand object to update the data...
objCommand.ExecuteNonQuery()
' Close the connection...
objConnection.Close()
' Fill the DataSet and bind the fields...
FillDataSetAndView()
BindFields()
' Set the record position to the one that you saved...
Me.BindingContext(objDataView).Position = intPosition
' Show the current record position...
ShowPosition()
' Display a message that the record was deleted...
Label35.Text = "Record Eliminato"
End If
End Sub
Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
' Set the record position to the first record...
objCurrencyManager.Position = 0
' Show the current record position...
ShowPosition()
End Sub
Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
' Move to the previous record...
objCurrencyManager.Position -= 1
' Show the current record position...
ShowPosition()
End Sub
Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
' Move to the next record...
objCurrencyManager.Position += 1
' Show the current record position...
ShowPosition()
End Sub
Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
' Set the record position to the last record...
objCurrencyManager.Position = objCurrencyManager.Count - 1
' Show the current record position...
ShowPosition()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.Close()
End Sub
Private Sub btnAnnulla_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAnnulla.Click
If Not File.Exists(FILE_NAME) Then
MsgBox(FILE_NAME & " does not exist.")
Return
End If
Dim sr As StreamReader = File.OpenText(FILE_NAME)
Dim sqlString As String
sqlString = sr.ReadLine()
Me.ToolStripStatusLabel2.Text = (sqlString) 'Questo è solo per verificare!
sr.Close()
' Fill the dataset and bind the fields...
FillDataSetAndView()
BindFields()
' Show the current record position...
ShowPosition()
btnAdd.Enabled = False
btnUpdate.Enabled = True
End Sub
Private Sub txtDal_ValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtDal.ValueChanged
Me.txtMese.Text = Me.txtDal.Value.ToString("MMMM")
Dim Anno As Date = Me.txtDal.Text
Dim subString As String
subString = Microsoft.VisualBasic.Right(Anno, 4)
Me.txtAnno.Text = subString
End Sub
End Class
Grazie
Mau67
Mau67
Profilo
| Expert
917
messaggi | Data Invio:
mer 20 ago 2008 - 08:56
Scusate nessuno sa dirmi come relazionare le tabelle in base al codice che vi ho postato, e da parecchio che ci provo ma non riesco.
Grazie in anticipo
Mau67
Torna su
Stanze Forum
Elenco Threads
Partecipa anche tu! Registrati!
Hai bisogno di aiuto ?
Perchè non ti registri subito?
Dopo esserti registrato potrai chiedere
aiuto sul nostro
Forum
oppure aiutare gli altri
Consulta le
Stanze
disponibili.
Registrati ora !