[RESOLVED] Grab data from MS Access and use it again in SQL!

[sg552]

Hi folks,


I need help using the data I retrieve from Access in SQL syntax. This is my code btw:

Imports System.Data.OleDb
Public Class Form1
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
        Dim con As New OleDb.OleDbConnection
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        Dim nilai As String
 
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Users\hadi\Desktop\SISTEM PENGUNDIAN ELEKTRONIK\SISTEM PENGUNDIAN ELEKTRONIK\Mini Projek_database.mdb"
        con.Open()
 
        sql = "SELECT StudentID FROM Calon"
 
 
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "Calon")
 
        nilai = ds.Tables("Calon").Rows(0).Item("StudentID")
        RadioButton1.Text = nilai
 
        '   Dim con As New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=C:\Users\hadi\Desktop\SISTEM PENGUNDIAN ELEKTRONIK\SISTEM PENGUNDIAN ELEKTRONIK\Mini Projek_database.mdb")
        Dim cmd As OleDbCommand
 
 
 
 
 
        If RadioButton1.Checked = True Then
 
            cmd = New OleDbCommand("UPDATE Calon SET Jumlah_Undi=Jumlah_Undi+1 WHERE StudentID = 'RadioButton1.text'", con)
 
 
 
 
            Dim sdr As OleDbDataReader = cmd.ExecuteReader()
 
 
        ElseIf RadioButton2.Checked = True Then
 
            cmd = New OleDbCommand("UPDATE Calon SET Jumlah_Undi=Jumlah_Undi+1 WHERE StudentID = '01DIP09F107'", con)
 
 
            Dim sdr As OleDbDataReader = cmd.ExecuteReader()
 
        End If

Using RadioButton1.text in the SQL syntax don't work even the RadioButton1.text hold the value of StudentID. but using StudentID value like eg: 01DIP09F107 is working good! Note that 01DIP09F107 is in my StudentID column.

Now where did I do wrong? Thanks for any help. I'm looking at this thread http://www.vbforums.com/showthread.php?t=356711 right bow and comparing my code...

[si_the_geek]

Welcome to VBForums

The FAQ article you linked to contains the answer, but I would recommend also reading the other article that it links to (Why should I use Parameters instead of putting values into my SQL string? ), because it not only helps with this kind of thing, but also solves lots of other issues.

[sg552]

thanks for your reply

I look at the given link and I realize I don't need this declaration

RadioButton1.Text = nilai

this is my new code:

    nilai = ds.Tables("Calon").Rows(0).Item("StudentID")

        Dim cmd As OleDbCommand


        If RadioButton1.Checked = True Then

            cmd = New OleDbCommand("UPDATE Calon SET Jumlah_Undi=Jumlah_Undi+1 WHERE StudentID = ' & nilai & '", con)

I know nilai is holding the value of 01DIP09F101 because I can see it clearly when I test it: TextBox1.Text = nilai

now I'm lost. why my Access is not updating but when I replace the & nilai & in the SQL syntax with the value 01DIP09F101 my database is updating thanks in advance.

[si_the_geek]

Take another look at the code in the article, and compare it to what you have got - there is something very important missing (twice) in ' & nilai & '

[sg552]

hey! thank you very much for your quick reply!
my database is updating!!!

my working syntax:

cmd = New OleDbCommand("UPDATE Calon SET Jumlah_Undi=Jumlah_Undi+1 WHERE StudentID = '" & nilai & " '", con)

I thought that ' and " works the same.

thanks again

[si_the_geek]

Not quite... they both indicate String values, but ' is only within an SQL statement, and " is only within VB code.

As far as VB is concerned, an SQL statement is just a String - so you need to build it in the same way as you would build any other string... or better still, don't build it at all (see the link I posted earlier for why it is better, and how to do it).