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

Hi folks,


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

f Code:

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? :confused: Thanks for any help. I'm looking at this thread http://www.vbforums.com/showthread.php?t=356711 right bow and comparing my code...

Welcome to VBForums :wave:

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.

thanks for your reply:wave:

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

t Code:

RadioButton1.Text = nilai

this is my new code:

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:confused: thanks in advance.

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 & '

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

my working syntax:

Code:

---

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

---

I thought that ' and " works the same.:blush:

thanks again :thumb:

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