Coding not working in adodc control : v.b 6.0

[hussainiyad]

Hi all,

Can anyone tell me how to get a record for particular field, in my sql i,v Pinvoice table following fields

InvNo int ItemNo int Descrption varchar(20) Price float Qty Gross float
1001 090 Fan 30 5 150
1001 080 Table 45 2 90
1002 012 Chair 90 3 270
1002 001 ToolBox 120 1 120

now t want to see the particular invNo details in datagrid when i type the particular invNo(for example InvNo =1001) in a textbox so what should i do to get the result.
i tried this ways but no result it showed me the error

1. in a Adodc control Recordsource command type AdcmdText i added the following code select ItemNo,Descrp,Rate,Qty,Gross from Pinvoice where InvNo=&

it showed me error msg Incorrect syntex near&" as well page loading & close

so i tried to give the datasource at run time, in a button click event i added this query

sqlstring
sqlstring = "select ItemNo,Descrp,Rate,Qty,Gross from Pinvoice where InvNo=& txtTest.Text"
Adodc1.RecordSource = sqlstring

it also showed me error msg

1. "Microsoft[ODBC Driver manager]datasource name not found" so i droped it.
2."Incorrect syntex near&"
3.Method 'Refresh' of object 1Adodc failed"

plz help me to solve this problem

[debasisdas]

Try to use this

sqlstring = "select ItemNo,Descrp,Rate,Qty,Gross from Pinvoice where InvNo=" & "'" & txtTest.Text & "'" 

[hussainiyad]

Thanx Debidas i used that query which u sent but still i,m facing the same error , is there any solution plzzzzzzzzzzzzzzzzzzzzzzzz

and also tell me how to set the total in datareport and datagrid for examples i,v some regards in datagrid and i need the grand total in a textbox in my form

i created a view create view[Total ]as select sum(Gross)as total from Pinvoice

but it showed me the all Gross sum, if i need a particular Supplier how can i do it

in sql we can give like that "select sum(Gross)as total from Pinvoice where InvNo=1001"
but in form how can we do it plz i need ur suggestion

[debasisdas]

in sql we can give like that "select sum(Gross)as total from Pinvoice where InvNo=1001"

what is the problem if you are using the SQL and pass the value at run time ?

[hussainiyad]

Dear Debasis

Actually i need the total for particular InvNo so i tried many ways, in sql we can simply use this query "select sum(Gross)as Total from Pinvoice where InvNo=1001" but in my form when i retrive the datas from sql how i make the total thats my question is ? , shall i use the same query in button event

string sql
sqlstring"select sum(Gross)as Total from Pinvoice where InvNo="&"'"&txtInvNo.Text&"'"
Adodc1.RecordSource = sqlstring
Adodc1.Refresh
but i doesn,t show me the Total

[debasisdas]

You can use two separate controls one for the record and the second for the total.

If using ADODB then can open 2 separate recordset for the purpose.

[er irfan]

You have to choose good driver provider. so it may make strong binding with all defined control.