dotNetHell.it - Forum - Poblema con codice in Visual Studio 2005

Mau67 Profilo | Expert

917 messaggi | Data Invio: sab 9 ago 2008 - 12:09

Buongiorno a tutti, premetto sono un principiante ma con la volontà di imparare,
stò costruendo un applicazione e ho trovato su un libro un codice completo che fà al caso mio, ma aimè non funziona forse perchè sono inesperto, vi posto tutto il codice con l'errore e con una variate che vorrei apportare sulla stringa di connessione ma anche li mi genera l'errore:ù

' Import Data and SqlClient namespaces...
Imports System.Data
Imports System.Data.SqlClient

Public Class Form1
' Declare objects...
Dim objConnection As New SqlConnection _
("server=myserver;database=pubs;user id=sa;password=mypassword")
Dim objDataAdapter As New SqlDataAdapter( _
"SELECT authors.au_id, au_lname, au_fname, " & _
"titles.title_id, title, price " & _
"FROM authors " & _
"JOIN titleauthor ON authors.au_id = titleauthor.au_id " & _
"JOIN titles ON titleauthor.title_id = titles.title_id " & _
"ORDER BY au_lname, au_fname", objConnection)
Dim objDataSet As DataSet
Dim objDataView As DataView
Dim objCurrencyManager As CurrencyManager

Private Sub FillDataSetAndView()
' Initialize a new instance of the DataSet object...
objDataSet = New DataSet()

' Fill the DataSet object with data...
objDataAdapter.Fill(objDataSet, "authors")

' Set the DataView object to the DataSet object...
objDataView = New DataView(objDataSet.Tables("authors"))

' Set our CurrencyManager object to the DataView object...
objCurrencyManager = CType(Me.BindingContext(objDataView), CurrencyManager)
End Sub

Private Sub BindFields()
' Clear any previous bindings...
txtLastName.DataBindings.Clear()
txtFirstName.DataBindings.Clear()
txtBookTitle.DataBindings.Clear()
txtPrice.DataBindings.Clear()

' Add new bindings to the DataView object...
txtLastName.DataBindings.Add("Text", objDataView, "au_lname")
txtFirstName.DataBindings.Add("Text", objDataView, "au_fname")
txtBookTitle.DataBindings.Add("Text", objDataView, "title")
txtPrice.DataBindings.Add("Text", objDataView, "price")

' Display a ready status...
ToolStripStatusLabel1.Text = "Ready"
End Sub
Private Sub ShowPosition()
'Always format the number in the txtPrice field to include cents
Try
txtPrice.Text = Format(CType(txtPrice.Text, Decimal), "##0.00")
Catch e As System.Exception
txtPrice.Text = "0"
txtPrice.Text = Format(CType(txtPrice.Text, Decimal), "##0.00")
End Try
' Display the current position and the number of records
txtRecordPosition.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("Last Name")
cboField.Items.Add("First Name")
cboField.Items.Add("Book Title")
cboField.Items.Add("Price")

' 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 btnMoveFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveFirst.Click
' Set the record position to the first record...
objCurrencyManager.Position = 0

' Show the current record position...
ShowPosition()

End Sub

Private Sub btnMovePrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMovePrevious.Click
' Move to the previous record...
objCurrencyManager.Position -= 1

' Show the current record position...
ShowPosition()

End Sub

Private Sub btnMoveNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveNext.Click
' Move to the next record...
objCurrencyManager.Position += 1

' Show the current record position...
ShowPosition()

End Sub

Private Sub btnMoveLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveLast.Click
' Set the record position to the last record...
objCurrencyManager.Position = objCurrencyManager.Count - 1

' 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 = "au_lname"
Case 1 'First Name
objDataView.Sort = "au_fname"
Case 2 'Book Title
objDataView.Sort = "title"
Case 3 'Price
objDataView.Sort = "price"
End Select

' Call the click event for the MoveFirst button...
btnMoveFirst_Click(Nothing, Nothing)

' Display a message that the records have been sorted...
ToolStripStatusLabel1.Text = "Records Sorted"

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 = "au_lname"
Case 1 'First Name
objDataView.Sort = "au_fname"
Case 2 'Book Title
objDataView.Sort = "title"
Case 3 'Price
objDataView.Sort = "price"
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(txtSearchCriteria.Text)
Else
' otherwise find the price...
intPosition = objDataView.Find(CType(txtSearchCriteria.Text, Decimal))
End If
If intPosition = -1 Then
' Display a message that the record was not found...
ToolStripStatusLabel1.Text = "Record Not Found"
Else
' Otherwise display a message that the record was
' found and reposition the CurrencyManager to that
' record...
ToolStripStatusLabel1.Text = "Record Found"
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
' Clear the book title and price fields...
txtBookTitle.Text = ""
txtPrice.Text = ""

End Sub

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
' 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(title_id) AS MaxID " & _
"FROM titles WHERE title_id LIKE 'DM%'", 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 = "DM" & intMaxID.ToString

' Set the SqlCommand object properties...
objCommand.Connection = objConnection
objCommand.CommandText = "INSERT INTO titles " & _
"(title_id, title, type, price, pubdate) " & _
"VALUES(@title_id,@title,@type,@price,@pubdate);" & _
"INSERT INTO titleauthor (au_id, title_id) VALUES(@au_id,@title_id)"

' Add parameters for the placeholders in the SQL in the
' CommandText property...

' Parameter for the title_id column...
objCommand.Parameters.AddWithValue("@title_id", strID)

' Parameter for the title column...
objCommand.Parameters.AddWithValue("@title", txtBookTitle.Text)

' Parameter for the type column
objCommand.Parameters.AddWithValue("@type", "Demo")
' Parameter for the price column...
objCommand.Parameters.AddWithValue("@price", txtPrice.Text).DbType _
= DbType.Currency

' Parameter for the pubdate column
objCommand.Parameters.AddWithValue("@pubdate", Date.Now)

' Parameter for the au_id column...
objCommand.Parameters.AddWithValue _
("@au_id", BindingContext(objDataView).Current("au_id"))

' 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...
ToolStripStatusLabel1.Text = "Record Added"

End Sub

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
' 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 titles " & _
"SET title = @title, price = @price WHERE title_id = @title_id"
objCommand.CommandType = CommandType.Text

' Add parameters for the placeholders in the SQL in the
' CommandText property...

' Parameter for the title field...
objCommand.Parameters.AddWithValue("@title", txtBookTitle.Text)

' Parameter for the price field...
objCommand.Parameters.AddWithValue("@price", txtPrice.Text).DbType _
= DbType.Currency

' Parameter for the title_id field...
objCommand.Parameters.AddWithValue _
("@title_id", BindingContext(objDataView).Current("title_id"))

' 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...
ToolStripStatusLabel1.Text = "Record Updated"

End Sub

Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
' 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 titleauthor " & _
"WHERE title_id = @title_id;" & _
"DELETE FROM titles WHERE title_id = @title_id"

' Parameter for the title_id field...
objCommand.Parameters.AddWithValue _
("@title_id", BindingContext(objDataView).Current("title_id"))

' 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...
ToolStripStatusLabel1.Text = "Record Deleted"

End Sub
End Class

ALL'AVVIO MI GENERA IL SEGUENTE ERRORE

Si è verificato un errore mentre si cercava di stabilire una connessione con il server.
Quando ci si connette a SQL Server 2005, questo errore potrebbe essere provocato dal fatto
che l'SQL Server non ammette connessioni remote sotto le impostazioni predefinite.
(provider: Provider Named Pipes, error: 40 - Impossibile aprire una connessione a SQL Server)

QUESTA E' LA VARIANTE CHE VORREI APPORTARE ALLA CONNESSIONE DOVE
ToolStripStatusLabel1.Text e il percorso del mio database,

' Declare objects...
Dim objConnection As New SqlConnection _
("Data Source=.\SQLEXPRESS;AttachDbFilename=" & ToolStripStatusLabel1.Text & ";Integrated Security=True;Connect Timeout=30;User Instance=True;" )

Dim objDataAdapter As New SqlDataAdapter( _
"SELECT authors.au_id, au_lname, au_fname, " & _
"titles.title_id, title, price " & _
"FROM authors " & _
"JOIN titleauthor ON authors.au_id = titleauthor.au_id " & _
"JOIN titles ON titleauthor.title_id = titles.title_id " & _
"ORDER BY au_lname, au_fname", objConnection)
Dim objDataSet As DataSet
Dim objDataView As DataView
Dim objCurrencyManager As CurrencyManager

e mi genera il seguente errore
Errore: Riferimento a un oggetto non impostato su un'istanza di oggetto.

AIUTATEMI è importante per me o datemi qualche consiglio per costruire la mia applicazione utilizzando lo stesso criterio.

Grazie in anticipo
Mau67

Cteniza Profilo | Guru

1.509 messaggi | Data Invio: sab 9 ago 2008 - 12:19

E' ovvio che prima devi risolvere il problema della connessione.
Vai su programmi, sql server, strumenti di configuraizone, configurazione superficie d'attacco, servizi e connessioni, connessioni remote
Imposta lì dentro tcp ip e named pipe

Mau67 Profilo | Expert

917 messaggi | Data Invio: sab 9 ago 2008 - 12:58

Scusa la mia ignoranza ma nel tcp ip e named pipe cosa devo scrivere?
Grazie

Mau67

Mau67 Profilo | Expert

917 messaggi | Data Invio: lun 11 ago 2008 - 09:09

610_errore.zip

Ciao ho impostato le connessioni remote sul server ma mi genera lo stesso errore non so cosa fare ti allego
un file dove vedi visivamente l'interruzione

Ciao e grazie
Mau67

Cteniza Profilo | Guru

1.509 messaggi | Data Invio: lun 11 ago 2008 - 09:20

Non ostinarti a provare solo il tuo programma, può contenere anche altri errori.
Apri un nuovo progetto, usa il wizard di visual studio, praticamente devi solo fare tutto con l'ide.
Seleziona database e connessione, seleziona le tables (fai una sola tabella), imposta il dataset, compila, aggiungi un bindingsource, un datagridview, un bindingnavigator.
Se funziona allora verifica quello che non va sul tuo progetto.
Se non funziona, leggi il messaggio di errore, correggi e ricomincia da capo (butta il progetto e fanne un'altro)

Mau67 Profilo | Expert

917 messaggi | Data Invio: lun 11 ago 2008 - 11:32

740_Errore1.zip

Ho seguito il tuo consiglio e o cambiato la stringa di connessione adesso in fase di debug si avvia ma quando arriva alla lettura del Dataset mi genera il seguente errore (vedi file allegato).

Da cosa può dipendere?
Ciao Grazie
Mau67

Cteniza Profilo | Guru

1.509 messaggi | Data Invio: lun 11 ago 2008 - 11:38

E il nome della tabella dove l'hai messo ? (select campo, campo, from mytable)