Jump to content
aesp

[VB.NET] Inserção de dados na BD

Recommended Posts

aesp

Boas pessoal, estou com um pequenito problema na inserção de dados na base de dados, o erro que me está a ocorrer é o seguinte, "String or binary data would be truncated. ", aqui fica o código e já agora se houver alguma maneira de o tornar mais pequeno agradecia se me pudessem dar umas dicas.  ;)

Imports System.Data.SqlClient
Imports System.Data
Imports System.Windows.Forms

Public Class AddRMA
    Dim CheckDiagGeral As String

#Region "// Intruções de SQL"
#Region "// Leitura de dados"
    Private Sub LoadNomes()
        Dim ConnectionString As New SqlConnection("Data Source=*;Initial Catalog=*;Integrated Security=SSPI;")
        Dim query As String = "SELECT Nome FROM TblClientes ORDER BY Nome;"
        Dim DataAdapter As New SqlDataAdapter(query, ConnectionString)
        Dim DataSet As New DataSet

        ConnectionString.Open()

        DataAdapter.Fill(DataSet, "TblClientes")

        With (TBox1Nome)
            .DataSource = DataSet.Tables("TblClientes")
            .DisplayMember = "Nome"
        End With

        ConnectionString.Close()

    End Sub
    Private Sub LoadNIF()
        Dim ConnectionString As New SqlConnection("Data Source=*;Initial Catalog=*;Integrated Security=SSPI;")
        Dim query As String = "SELECT NContribuinte FROM TblClientes WHERE TblClientes.Nome LIKE '" & TBox1Nome.Text & "';"
        Dim DataAdapter As New SqlDataAdapter(query, ConnectionString)
        Dim DataSet As New DataSet

        ConnectionString.Open()

        DataAdapter.Fill(DataSet, "TblClientes")

        With (TBox2Contribuinte)
            .DataSource = DataSet.Tables("TblClientes")
            .DisplayMember = "NContribuinte"
        End With

        ConnectionString.Close()
    End Sub
    Private Sub LoadMorada()
        Dim ConnectionString As New SqlConnection("Data Source=*;Initial Catalog=*;Integrated Security=SSPI;")
        Dim query As String = "SELECT Morada FROM TblClientes WHERE TblClientes.Nome LIKE '" & TBox1Nome.Text & "';"
        Dim DataAdapter As New SqlDataAdapter(query, ConnectionString)
        Dim DataSet As New DataSet

        ConnectionString.Open()

        DataAdapter.Fill(DataSet, "TblClientes")

        With (TBox3Morada)
            .DataSource = DataSet.Tables("TblClientes")
            .DisplayMember = "Morada"
        End With

        ConnectionString.Close()
    End Sub
    Private Sub LoadLocalidade()
        Dim ConnectionString As New SqlConnection("Data Source=*;Initial Catalog=*;Integrated Security=SSPI;")
        Dim query As String = "SELECT Localidade FROM TblClientes WHERE TblClientes.Nome LIKE '" & TBox1Nome.Text & "';"
        Dim DataAdapter As New SqlDataAdapter(query, ConnectionString)
        Dim DataSet As New DataSet

        ConnectionString.Open()

        DataAdapter.Fill(DataSet, "TblClientes")

        With (TBox4Localidade)
            .DataSource = DataSet.Tables("TblClientes")
            .DisplayMember = "Localidade"
        End With

        ConnectionString.Close()
    End Sub
    Private Sub LoadCodPostal()
        Dim ConnectionString As New SqlConnection("Data Source=*;Initial Catalog=*;Integrated Security=SSPI;")
        Dim query As String = "SELECT CodPostal FROM TblClientes WHERE TblClientes.Nome LIKE '" & TBox1Nome.Text & "';"
        Dim DataAdapter As New SqlDataAdapter(query, ConnectionString)
        Dim DataSet As New DataSet

        ConnectionString.Open()

        DataAdapter.Fill(DataSet, "TblClientes")

        With (TBox5CodPostal)
            .DataSource = DataSet.Tables("TblClientes")
            .DisplayMember = "CodPostal"
        End With

        ConnectionString.Close()
    End Sub
    Private Sub LoadTlm()
        Dim ConnectionString As New SqlConnection("Data Source=*;Initial Catalog=*;Integrated Security=SSPI;")
        Dim query As String = "SELECT Tlm FROM TblClientes WHERE TblClientes.Nome LIKE '" & TBox1Nome.Text & "';"
        Dim DataAdapter As New SqlDataAdapter(query, ConnectionString)
        Dim DataSet As New DataSet

        ConnectionString.Open()

        DataAdapter.Fill(DataSet, "TblClientes")

        With (TBox6Telemovel)
            .DataSource = DataSet.Tables("TblClientes")
            .DisplayMember = "Tlm"
        End With

        ConnectionString.Close()
    End Sub
    Private Sub LoadTlf()
        Dim ConnectionString As New SqlConnection("Data Source=*;Initial Catalog=*;Integrated Security=SSPI;")
        Dim query As String = "SELECT Tlf FROM TblClientes WHERE TblClientes.Nome LIKE '" & TBox1Nome.Text & "';"
        Dim DataAdapter As New SqlDataAdapter(query, ConnectionString)
        Dim DataSet As New DataSet

        ConnectionString.Open()

        DataAdapter.Fill(DataSet, "TblClientes")

        With (TBox7Telefone)
            .DataSource = DataSet.Tables("TblClientes")
            .DisplayMember = "Tlf"
        End With

        ConnectionString.Close()
    End Sub
    Private Sub LoadEmail()
        Dim ConnectionString As New SqlConnection("Data Source=*;Initial Catalog=*;Integrated Security=SSPI;")
        Dim query As String = "SELECT Email FROM TblClientes WHERE TblClientes.Nome LIKE '" & TBox1Nome.Text & "';"
        Dim DataAdapter As New SqlDataAdapter(query, ConnectionString)
        Dim DataSet As New DataSet

        ConnectionString.Open()

        DataAdapter.Fill(DataSet, "TblClientes")

        With (TBox8Email)
            .DataSource = DataSet.Tables("TblClientes")
            .DisplayMember = "Email"
        End With

        ConnectionString.Close()
    End Sub
    Private Sub LoadTipoAssistencia()
        Dim ConnectionString As New SqlConnection("Data Source=*;Initial Catalog=*;Integrated Security=SSPI;")
        Dim query As String = "SELECT * FROM TblAssistencia;"
        Dim DataAdapter As New SqlDataAdapter(query, ConnectionString)
        Dim DataSet As New DataSet

        ConnectionString.Open()

        DataAdapter.Fill(DataSet, "TblAssistencia")

        With (TBox14TipoAssistencia)
            .DataSource = DataSet.Tables("TblAssistencia")
            .DisplayMember = "TipoAssistencia"
        End With

        ConnectionString.Close()
    End Sub
    Private Sub LoadEstadoRMA()
        Dim ConnectionString As New SqlConnection("Data Source=*;Initial Catalog=*;Integrated Security=SSPI;")
        Dim query As String = "SELECT * FROM TblEstadoRMA;"
        Dim DataAdapter As New SqlDataAdapter(query, ConnectionString)
        Dim DataSet As New DataSet

        ConnectionString.Open()

        DataAdapter.Fill(DataSet, "TblEstadoRMA")

        With (TBox15EstadoRMA)
            .DataSource = DataSet.Tables("TblEstadoRMA")
            .DisplayMember = "EstadoRMA"
        End With

        ConnectionString.Close()
    End Sub
#End Region

#Region "// Inserção de dados"
    Private Sub addData()
        Dim ConnectionString As New SqlConnection("Data Source=*;Initial Catalog=*;Integrated Security=SSPI;")
        Dim AddSQL As String = "INSERT INTO TblGuiaRMA " & _
                               "([Nome], [Marca], [Modelo], [NSerie], [DescEquip], [DescAvaria], [TipoAssistencia], [DataEntrada], [DataSaida], [EstadoRMA], [DiagnosticoGeral])" & _
                               " VALUES " & _
                               "(@Nome, @Marca, @Modelo , @NSerie, @DescEquip, @DescAvaria, @TipoAssistencia, @DataEntrada, @DataSaida, @EstadoRMA, @DiagnosticoGeral);"

        UCase(TBox11Nserie.Text)

        Dim Command As New SqlCommand(AddSQL, ConnectionString)

        Command.Parameters.Add("@Nome", SqlDbType.VarChar).Value = TBox1Nome.Text
        Command.Parameters.Add("@Marca", SqlDbType.VarChar).Value = TBox9Marca.Text
        Command.Parameters.Add("@Modelo", SqlDbType.VarChar).Value = TBox10Modelo.Text
        Command.Parameters.Add("@NSerie", SqlDbType.VarChar).Value = TBox11Nserie.Text
        Command.Parameters.Add("@DescEquip", SqlDbType.VarChar).Value = TBox12DesEquip.Text
        Command.Parameters.Add("@DescAvaria", SqlDbType.VarChar).Value = TBox13DesAvaria.Text
        Command.Parameters.Add("@TipoAssistencia", SqlDbType.VarChar).Value = TBox14TipoAssistencia.Text
        Command.Parameters.Add("@DataEntrada", SqlDbType.VarChar).Value = DTPDataEntrada.Value
        Command.Parameters.Add("@DataSaida", SqlDbType.VarChar).Value = DTPDataSaida.Value
        Command.Parameters.Add("@EstadoRMA", SqlDbType.VarChar).Value = TBox15EstadoRMA.Text
        'Command.Parameters.Add("@ObsTecnicas", SqlDbType.VarChar).Value = TBox17ObsTec.Text
        'Command.Parameters.Add("@BackupDados", SqlDbType.VarChar).Value = CheckBox2Backup.Text
        'Command.Parameters.Add("@RecuperacaoDados", SqlDbType.VarChar).Value = CheckBox3RecoverDados.Text
        'Command.Parameters.Add("@ConfiguracaoRedes", SqlDbType.VarChar).Value = CheckBox4ConfigRedes.Text
        Command.Parameters.Add("@DiagnosticoGeral", SqlDbType.VarChar).Value = CheckDiagGeral
        'Command.Parameters.Add("@VirusSpywares", SqlDbType.VarChar).Value = CheckBox5Virus.Text
        'Command.Parameters.Add("@InstalacaoSO", SqlDbType.VarChar).Value = CheckBox6InstallSO.Text
        'Command.Parameters.Add("@ReinstalacaoSO", SqlDbType.VarChar).Value = CheckBox7ReInstallSO.Text
        'Command.Parameters.Add("@UpDowngradeSO", SqlDbType.VarChar).Value = CheckBox8UpDownSO.Text
        'Command.Parameters.Add("@InstalacaoSoftware", SqlDbType.VarChar).Value = CheckBox9InstllSoft.Text
        'Command.Parameters.Add("@LimpezaPC", SqlDbType.VarChar).Value = CheckBox10LimpzPC.Text
        'Command.Parameters.Add("@MontagemHardware", SqlDbType.VarChar).Value = CheckBox11MontHardware.Text
        'Command.Parameters.Add("@ReparacaoHardware", SqlDbType.VarChar).Value = CheckBox12RepHardware.Text
        ''Command.Parameters.Add("@TesteHardware", SqlDbType.VarChar).Value = CheckBox13TesteHardware.Text
        ''Command.Parameters.Add("@Disco", SqlDbType.VarChar).Value = CheckBox14Disco.Text
        ''Command.Parameters.Add("@Memoria", SqlDbType.VarChar).Value = CheckBox15Memoria.Text
        ''Command.Parameters.Add("@CPU", SqlDbType.VarChar).Value = CheckBox16CPU.Text

        Command.Connection.Open()
        Dim x As Integer = Command.ExecuteNonQuery()

        If x < 1 Then
            MessageBox.Show("Erro ao adicionar", My.Application.Info.Title, MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            MessageBox.Show("Adicionado com sucesso!", My.Application.Info.Title, MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
        Command.Connection.Close()
    End Sub
#End Region
#End Region

#Region "// Carregamentos do formulário"
    Private Sub AddRMA_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call LoadNomes()
        Call LoadNIF()
        Call LoadMorada()
        Call LoadLocalidade()
        Call LoadCodPostal()
        Call LoadTlm()
        Call LoadTlf()
        Call LoadEmail()
        Call LoadTipoAssistencia()
        Call LoadEstadoRMA()
    End Sub
#End Region

#Region "// Combinação de combobox's"
    Private Sub TBox1Nome_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TBox1Nome.SelectedIndexChanged
        Call LoadNIF()
        Call LoadMorada()
        Call LoadLocalidade()
        Call LoadCodPostal()
        Call LoadTlm()
        Call LoadTlf()
        Call LoadEmail()
    End Sub
#End Region

#Region "// Funcionalidades dos botões"
    Private Sub OK_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK_Button.Click
        Dim resp As String
        Me.DialogResult = System.Windows.Forms.DialogResult.OK
        resp = MsgBox("Pretende adicionar a seguinte RMA?", MsgBoxStyle.Information + vbYesNo, "Confirmação de inserção de RMA")
        If resp = vbYes Then
            Call addData()
            Me.Close()
        End If
    End Sub
    Private Sub Cancelar_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Cancelar_Button.Click
        Me.DialogResult = System.Windows.Forms.DialogResult.Cancel
        Me.Close()
    End Sub
    Private Sub Clean_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Clean_Button.Click
        TBox9Marca.Clear()
        TBox10Modelo.Clear()
        TBox11Nserie.Clear()
        TBox12DesEquip.Clear()
        TBox13DesAvaria.Clear()
        TBox17ObsTec.Clear()
    End Sub

#End Region

#Region "// Funcionalidades das checkbox's"
    Private Sub CheckBox1DiagGeral_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles CheckBox1DiagGeral.Click
        If CheckBox1DiagGeral.Checked = True Then
            CheckDiagGeral = "Sim"
            MsgBox(CheckDiagGeral)
        Else
            CheckDiagGeral = "Não"
            MsgBox(CheckDiagGeral)
        End If
    End Sub
#End Region
    
End Class

Share this post


Link to post
Share on other sites
bioshock

Para que é que carregas todos os dados para combobox's? Só alguns dados é que devem ser carregados, pois pode haver mais do que um registo semelhante. Dados como localidade, código postal, País..etc. ;)

Relativamente ao teu erro, os teus campos na base de dados, estão como obrigatórios? Se não tiverem, podes ir, apesar de cansativo, um a um verificar qual é que está a causar problemas..

Porque o erro está no tipo de dados de um campo. Podes eventualmente estar a inserir texto com um tamanho superior ao que o campo aceita.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site you accept our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.