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 !