Problems On Fill(Kind of Urgent)

  • Thread starter Thread starter scorpion53061
  • Start date Start date
S

scorpion53061

With an access database in vb.net.

This works in access:
SELECT JOBTRTID, JOBTRDATE, JOBTRUSER, JOBTRACTION, JOBTRCUSTNUMBER,
JOBTRCUSTSHIPTO, JOBTRCUSTNAME, JOBTRCUSTCOMPANY, JOBTRITEM, JOBTRITEMDESC,
JOBTRITEMQTY, JOBTRJOBNUMBER, JOBTRJOBSHIPTO, JOBTRJOBNAME, JOBTRJOBTYPE
FROM JOBTRANSACTIONS where (JOBTRITEM = 'TESTING3');

This is not working in vb.net where chitem is the item variable:
sql2 = "SELECT JOBTRTID, JOBTRDATE, JOBTRUSER, JOBTRACTION, JOBTRCUSTNUMBER,
JOBTRCUSTSHIPTO, JOBTRCUSTNAME, JOBTRCUSTCOMPANY, JOBTRITEM, JOBTRITEMDESC,
JOBTRITEMQTY, JOBTRJOBNUMBER, JOBTRJOBSHIPTO, JOBTRJOBNAME, JOBTRJOBTYPE
FROM JOBTRANSACTIONS where (JOBTRITEM = " & chitem & ")"

I tried plus signs and no parenthesis without success.

The rest of the code is here. The error is on fill and it says that the
required value of a parameter has not been given.
I confirmed that chitem does have a value.

Dstran1.Clear()
Dstran1.AcceptChanges()
sql2 = "SELECT JOBTRTID, JOBTRDATE, JOBTRUSER, JOBTRACTION,
JOBTRCUSTNUMBER, JOBTRCUSTSHIPTO, JOBTRCUSTNAME, JOBTRCUSTCOMPANY,
JOBTRITEM, JOBTRITEMDESC, JOBTRITEMQTY, JOBTRJOBNUMBER, JOBTRJOBSHIPTO,
JOBTRJOBNAME, JOBTRJOBTYPE FROM JOBTRANSACTIONS where (JOBTRITEM = " &
chitem & ")"
OleDbDataAdapter2 = New OleDb.OleDbDataAdapter(sql2, sConn45)
'Try
OleDbDataAdapter2.Fill(Dstran1.JOBTRANSACTIONS)
'Catch ex As Exception
'MsgBox(ex.ToString)
'Exit Sub
'End Try
Dstran1.AcceptChanges()
dsreport = Dstran1.Copy
reportdataset = "dstran"
If Dstran1.Tables(0).Rows.Count - 1 < 0 Then
MsgBox("No results found.", MsgBoxStyle.Information)
DataGrid1.Visible = False
sConn45.Close()
Button2.Enabled = False
Exit Sub
Else
Button2.Enabled = True
dv = Dstran1.Tables(0).DefaultView
DataGrid1.DataSource = dv
AddSizedColoredColumnsTran()
DataGrid1.Visible = True
DataGrid1.Refresh()
sConn45.Close()
End If
 
Hi Scorpion! :O)
This is not working in vb.net where chitem is the item variable:
Click to expand...

you didn't tell us what happens. Are you getting an error?

FROM JOBTRANSACTIONS where (JOBTRITEM = " & chitem & ")"
Click to expand...

here you seem to have forget the quotes, it think it should be.. I can't be
sure since I don't know what chitem contains...
 
Hi Scorpion,

Try this (assuming JOBTRITEM is a stringtype field I suggest you add a
single quote before first double quote and another between second double
quote and the bracket) ......(JOBTRITEM = '" & chitem & "')"

Regards,
Jan
 
Thank you all you were exactly right.

Jan said:
Hi Scorpion,

Try this (assuming JOBTRITEM is a stringtype field I suggest you add a
single quote before first double quote and another between second double
quote and the bracket) ......(JOBTRITEM = '" & chitem & "')"

Regards,
Jan
Click to expand...
 
Consider situations where the user indicates that they want
to search for a job title of "Sorcerer's Apprentice". If you
parse the query by hand, you'd need to replace the apostrophes in
the query with two apostrophes. The query you would want to
execute looks like:

"SELECT ... WHERE (JOBTRITEM = 'Sorcerer''s Apprentice')"

Rather than parsing the user's input for special characters,
you may want to look at using a parameterized query. Your code
would look like the following:

strSQL = "SELECT ... WHERE (JOBTRITEM = ?)"
da = New OleDbDataAdapter(strSQL, strConn)
Dim p As OleDbParameter
p = da.SelectCommand.Parameters.Add("@JOBTRITEM",
OleDbType.VarChar, 255)
p.Value = chitem

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.
 
Back
Top