Click here to Skip to main content
15,896,207 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
Hello..
I want ask about how to insert a record in database by looping function?
Here my code :
VB
Imports System.Data
Imports System.Data.SqlClient
Public Class LoopingDB
    Dim con As SqlConnection
    Dim cmd As SqlCommand
    Dim reader As SqlDataReader
    Dim constring As String
    Dim SqlString As String
    Dim myConnection As SqlConnection
    Dim myCommand As SqlCommand
    Dim Da As SqlDataAdapter
    Dim Ds As DataSet
    Dim ra As Integer
    Dim myDA As New SqlDataAdapter
    Dim MyDS As New _FKIP_UNILAKDataSet()
    Dim numAffected As Integer
    Private Sub LagiiBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LagiiBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.coordinateBindingSource.EndEdit()
        Me.coordinateTableAdapter.Update(Me._FKIP_UNILAKDataSet.coordinate)

    End Sub

    Private Sub LoopingDB_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the '_FKIP_UNILAKDataSet.lagii' table. You can move, or remove it, as needed.
        Me.LagiiTableAdapter.Fill(Me._FKIP_UNILAKDataSet.lagii)
        constring = "Data Source=RAGAZ-PC\SQLEXPRESS;Initial Catalog=FKIP-UNILAK;Integrated Security=True"
        con = New SqlConnection(constring)
        con.Open()
        Da.Fill(Ds, "coordinate")
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim i, j As Integer
        For i = 1 To 3
            For j = 1 To 2
                
                SqlString = "Insert coordinate  values ('" (i) " ','" (j) "')"  ' Here the problem
                cmd = New SqlCommand(SqlString, con)
                numAffected = cmd.ExecuteNonQuery
            Next
        Next
        MessageBox.Show("Data coordinate saved", "SUKSES", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Me.coordinateTableAdapter.Fill(Me._FKIP_UNILAKDataSet.lagii)
    End Sub
End Class

I want the gridview shown 6 record, the record is i and j value :

X_Value                 Y_Value<br />
   1                       1<br />
   1                       2<br />
   2                       1<br />
   2                       2<br />
   3                       1 <br />
   3                       2
Posted
Updated 19-Apr-11 0:02am
v2
Comments
OriginalGriff 19-Apr-11 6:05am    
And your problem is?

First of all you should use insert into when inserting this way. Second, you should convert the integers to strings. You can look into String.Format(...) for that.

Also, this is not actually the best way of executing sql. You could look into SqlParameter or use stored procedures. For now I understand you just want a working example but be aware that the method used now is possible but not advised.

Good luck!
 
Share this answer
 
v2
Comments
Toniyo Jackson 19-Apr-11 6:10am    
Well Catch. My 5
[no name] 19-Apr-11 8:17am    
This is a very good suggestion, cause if the coordinate has NULL value, it's thro an exception.
E.F. Nijboer 19-Apr-11 9:15am    
Normally it will then use default values but there is also an option to keep nulls. (http://msdn.microsoft.com/en-us/library/ms188365.aspx)
There is also another very simple way by importing into a temporary table first (without constraints) and then insert only the correct rows into the actual table. Simply an extra step so you can avoid any problems.
I got the solution.
Here my code:

SqlString = "Insert coordinate values ('" (i) " ','" (j) "')" ' Here the problem


Change to :

SqlString = "Insert coordinate values ('" & i & " ','" & j & "')" ' Here the solution
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900