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



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()
        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)

        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


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

        ' Me.EmployeesTableAdapter.FillByFirstName(Me.EmployeesDataSet.Employees, Me.txtSearch.Text)
        'Dim da As New LoanSampleDBDataSetTableAdapters.loan1TableAdapter
        '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


what code should I insert after "Where ID="

Member Avatar


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

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

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

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

    "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.