hi there.
I have a problem in making my amortization.
I just only want to know how to change data grid view's command text with where conditions.

this what i've got:

LoanSampleDBDataSetTableAdapters.loan1TableAdapter.commandText = "Select * from Employees where ID =" & n1

Then error occurs.

Member Avatar

LastMitch

@gujinni

Then error occurs.

It's nice that you post that single line code but it's more important to post the errors when you ran your whole code. Without understanding the error, it will be very hard to resolve the issue you are having.

This doesn't look right:

"Select * from Employees where ID =" & n1

It should look like this:

"SELECT FROM Employees WHERE ID = " + ID;

Imports System.Data.Sql
Imports System.Data.SqlClient

Public Class Form1
    Dim cmd As SqlCommand
    Dim con As SqlConnection
    Dim str As String
    Dim n1, n2 As Integer
    Dim d1, d2, d3 As Decimal
    Dim t1, t2 As Decimal
    Dim p1 As Decimal
    Dim curDate As Date
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        n1 = Convert.ToInt64(TextBox1.Text)
        d1 = Decimal.Parse(TextBox2.Text) 'loan amount
        d2 = Decimal.Parse(TextBox3.Text) 'interest rate
        n2 = Integer.Parse(TextBox4.Text)
        d3 = d1 / 100
        t1 = d1 * d3 'intereset
        p1 = d1 / n2 'monthly principal
        t2 = p1 + t1
        TextBox5.Text = t2
        con = getconn()
        con.Open()
        Dim loop1 As Integer
        For loop1 = 1 To n2

            str = "insert into loan1(ID,PrincipalAMount,interest,Date) VALUES(" & n1 & "," & p1 & "," & t1 & ",'" & curDate & "')"
            cmd = New SqlCommand(str, con)
            cmd.ExecuteNonQuery()

        Next
        cmd.Dispose()
        con.Close()
        Call Me.Loan1TableAdapter.Fill(Me.LoanSampleDBDataSet.loan1)
        'Me.Loan1TableAdapter.Fill(Me.LoanSampleDBDataSet.loan1, Me.TextBox1.Text)
    End Sub



    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'Me.Loan1TableAdapter.Fill(Me.LoanSampleDBDataSet1.loan1)


        'LoanSampleDBDataSetTableAdapters.loan1TableAdapter.commandText = "Select * from Employees where ID =" & n1

        ' Me.EmployeesTableAdapter.FillByFirstName(Me.EmployeesDataSet.Employees, Me.txtSearch.Text)
        'Dim da As New LoanSampleDBDataSetTableAdapters.loan1TableAdapter
        Me.Loan1TableAdapter.Fill(Me.LoanSampleDBDataSet.loan1)
        'Dim cmd As New SqlCommand
        ''Dim conn As New SqlConnection("Connection string here")
        'Dim MySQLAdapter As New SqlDataAdapter
        'Dim MyTable As New DataTable

        'With MySQLAdapter
        '    ' .SelectCommand.Connection = conn
        '    .SelectCommand.CommandText = "SELECT * FROM loan1 WHERE ID=" & TextBox1.Text
        '    .Fill(MyTable)
        '    .Dispose()
        'End With
        'DataGridView1.DataSource = MyTable

I'm stack here!!! 
    End Sub


End Class

n1 is associated with the textbox1.text
I just want to display the datagrid view where ID=n1

image24

what code should I insert after "Where ID="

Member Avatar

LastMitch

I don't have a query builder. Try this:

DeleteCommand=
    "DELETE FROM [loan1] WHERE [ID] = @ID"

InsertCommand=
    "INSERT INTO [loan1] ([ID], [PrincipalAMount], [interest], [Date])
     VALUES (@n1, @p1, @t1, @curDate)"

SelectCommand=
    "SELECT [ID], [PrincipalAMount], [interest], [Date]
     FROM [loan1]"

UpdateCommand=
    "UPDATE [loan1] SET [ID] = @n1,
     [PrincipalAMount] = @p1, [interest] = @t1, [Date] = @curDate
     WHERE [ID] = @ID"

Can you please post the error you are getting?
From your code I see that you have declared a connection, but didn't give it a connection sting.