SQL MS Access

[Compass1]

Hi every body

I am trying to retrieve data from MS Access table but the SQL statement I hav written dos not work, I am bigginer can you please correct it form please.

I use Access 2003 and VB.net 2005

strPatientD = "Select * From tblPatientRegister Where Surname = " & (txtSurname.Text) And DoB = (dtpDoB.Text) And Gender = (cboGender.Text)

Many Thanks

 

[giadich]

strPatientD = "Select * From tblPatientRegister Where Surname = '" & Replace(txtSurname.Text, "'", "''") & "' And DoB = #" & dtpDoB.Text & "# And Gender = '" & Replace(cboGender.Text, "'", "''") & "'"

 

[JohnH]

Wish I'd never see such string mess again This is an example how to add parameters to queries:

PatientD = "Select * From tblPatientRegister Where Surname=@surname And DoB=@dob And Gender=@gender"
command.Parameters.AddWithValue("@surname", txtSurname.Text)
command.Parameters.AddWithValue("@dob", dtpDoB.Value.Date)
command.Parameters.AddWithValue("@gender", cboGender.Text)

 

[Compass1]

Hello sir

Thanks alot for your help,

my senario is:

I have a database called dbbhc.mdb and a table called tblPatientRegister
I add patient details in tblPatientRegister but before adding patient's details
I want the following code to search for patient details with criterias I will provide. If patient is already in the tblPatientRegister it should retrive the patientID which is COLUM 1 of my table and put it in variable called intPatientPresence .

I am bigginer in MS Access and VB.net 2005

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        'Check if the patient is already in table
        Dim strPatientD As String
        Dim intPatientPresence As integer

        strPatientD = "Select * From tblPatientRegister Where Surname=@surname And DoB=@dob And Gender=@gender"
        Command.Parameters.AddWithValue("@surname", txtSurname.Text)
        Command.Parameters.AddWithValue("@dob", dtpDoB.Value.Date)
        Command.Parameters.AddWithValue("@gender", cboGender.Text)

        Dim ds As DataSet = New DataSet
        Dim da As New OleDbDataAdapter
        Dim dv As New DataView

        'call class method
        da = dbUtils.ConList(strPatientD)
        da.Fill(ds, "tblPatientRegister")

        If strPatientPresence = Nothing Then ' If patient is not in table then display message
            MsgBox("Patian not found")
        End If

    End Sub

Thanks for your help

 

[cjard]

Wish I'd never see such string mess again This is an example how to add parameters to queries:

PatientD = "Select * From tblPatientRegister Where Surname=@surname And DoB=@dob And Gender=@gender"
command.Parameters.AddWithValue("@surname", txtSurname.Text)
command.Parameters.AddWithValue("@dob", dtpDoB.Value.Date)
command.Parameters.AddWithValue("@gender", cboGender.Text)

note that though VB.NET Access "driver" does seem to support @namedParameters in some contexts, it is still critically important that the parameters are added in the order that they appear in the statement..

Access parameterized queries usually take the form:

SELECT * FROM TABLE WHERE col = ? AND col2 = ?


With the ? marks..

Additionally I dont know what happens if you want to repeat a parameter. This:

SELECT * FROM person WHERE firstName = @nameParam OR lastName = @nameParam

in SQL server only needs one parameter. In access, because the driver is weird and still uses the "order of addition" concept, you might hav eto add two parameters, same name and same value.. I havent checked, but I was always under the impression that access doesnt properly do named parameters and hence the benefits of e.g. re-use dont apply

 

[cjard]

Thanks for your help

Youll do yourself a bigger favour reading the DW2 article in my signature, start with the section "Creating a Simple Data App" - this will teach you th enew, queicker, easier, better way of doing data access.. RIght now, youre making your life very hard, needlessly

 

[JohnH]

Access don't use named parameters, but you can still use @name instead of ?, makes more readable. Also in the case you need to run same query with different parameter values you can retrieve from the Parameters collection by name key instead of by integer index, again for readability. The order the parameters are added to the Parameters collection is significant, since Access don't use the name. I don't think you can reuse same Parameter different places with Access for the same reason as name and order.