vb and SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

vb and SQL

Author

Topic

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-08-26 : 10:54:56

Morning,

I have a VB front end that connects to SQL to present reports. My VB is marginal at best. Basically I cut and paste other reports and modify the SQL. I have a bit of a problem with one of the reports. There is one report but the filter can have multiple choices, program, vendor or all. The problem is I don't know how to "trick" it in to accepting the multiple choices within the query:


'	Retrieve Data
    sql = "select h.po, so.Ordered_Qty, so.Act_Qty totalrecieved, so.last_recv_date, " _
	& " case when so.Act_Qty < Ordered_Qty or so.last_recv_date > so.Due_Date+4 then 1 " _
	& " when so.Act_Qty < Ordered_Qty or so.last_recv_date IS null then 1 " _
	& " else 0 " _
	& " end as rating, " _
	& " so.Due_Date, " _
	& " d.Order_Quantity, " _
	& " isnull ((select sum(nc.qty_rejected) " _
	& " from K6Unipoint_Production.dbo.PT_NC nc " _
	& "      where nc.Origin = 'Vendor' " _
	& " 	  and nc.Origin_ref = v.vendor "_
	& " 	  AND dbo.fn_GetPrgName(nc.Material) = dbo.fn_GetPrgName(so.Material) " _
	
	'If SelProgram <> "" Then sql = sql & " AND dbo.fn_GetPrgName(nc.Material) = '" & SelProgram & "' " _
	
	& " 	  and nc.NCR_Date between '" & FromDate & "' " _
    & "       and '" & ToDate & "'),0) " _
	& "  qty_rejected, " _
	& "  dbo.fn_GetPrgName(so.Material)program, so.Material Vendor_Reference, v.Name,v.vendor, " _
	& "  dbo.fn_GetVendorCat(v.Vendor) category " _
	& "  from PO_detail d " _
	& "  	inner join Source so " _
	& " 			on so.PO_Detail = d.PO_Detail " _
	& "  	inner join PO_Header h " _
	& " 			on d.PO = h.PO " _
	& "  	inner join Vendor v " _
	& " 			on h.Vendor = v.Vendor " _
    & "  where (dbo.fn_GetVendorCat(v.Vendor) is not null " _
	& "  and d.PO_Type <> 1 " _
	& "  and dbo.fn_GetVendorCat(v.Vendor) <>  'Unknown') " _
	& "  and so.Due_Date between '" & FromDate & "' and '" & ToDate & "' " _
	
    If cboVend <> "" Then sql = sql & " AND v.Vendor = '" & cboVend & "' "
    If SelProgram <> "" Then sql = sql & " AND Production.dbo.fn_GetPrgName(so.Material) = '" & SelProgram & "' "

Its breaking on the first If SelProgram statement. The ones at the end it likes. I'm not sure if anyone here can help but I thought maybe someone can direct me to a resource that may.

Thanks

Laura

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-26 : 11:44:29

what does dbo.fn_GetPrgName do?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-08-26 : 13:18:26

Its a function that gets the program name based on the part number.

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-08-26 : 13:57:54

Got it that was a pain:


  sql = "select h.po, so.Ordered_Qty, so.Act_Qty totalrecieved, so.last_recv_date, " _
	& " case when so.Act_Qty < Ordered_Qty or so.last_recv_date > so.Due_Date+4 then 1 " _
	& " when so.Act_Qty < Ordered_Qty or so.last_recv_date IS null then 1 " _
	& " else 0 " _
	& " end as rating, " _
	& " so.Due_Date, " _
	& " d.Order_Quantity, " _
	& " isnull ((select sum(nc.qty_rejected) " _
	& " from K6Unipoint_Production.dbo.PT_NC nc " _
	& "      where nc.Origin = 'Vendor' " _

& " and nc.Origin_ref = v.vendor "
if SelProgram <> "" Then sql = sql & " AND dbo.fn_GetPrgName(nc.Material) = dbo.fn_GetPrgName(so.Material) "
sql = sql & " and nc.NCR_Date between '" & FromDate & "' " _


    & "       and '" & ToDate & "'),0) qty_rejected, " _
	& "  dbo.fn_GetPrgName(so.Material)program, so.Material Vendor_Reference, v.Name,v.vendor, " _
	& "  dbo.fn_GetVendorCat(v.Vendor) category " _
	& "  from PO_detail d " _
	& "  	inner join Source so " _
	& " 			on so.PO_Detail = d.PO_Detail " _
	& "  	inner join PO_Header h " _
	& " 			on d.PO = h.PO " _
	& "  	inner join Vendor v " _
	& " 			on h.Vendor = v.Vendor " _
    & "  where (dbo.fn_GetVendorCat(v.Vendor) is not null " _
	& "  and d.PO_Type <> 1 " _
	& "  and dbo.fn_GetVendorCat(v.Vendor) <>  'Unknown') " _
	& "  and so.Due_Date between '" & FromDate & "' and '" & ToDate & "' "

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources