|
|
Hi All
I have been stuck on this problem for a week and read every example on the web
I have 2 tables and I have generated a SQL Join Statement that fills a dataset
I display this in a datagrid and all is ok
However when I attempt to push this to the crystal reports object
I get a cross join which does not have any of the criteria applied
The code I execute to generate the dataset is as follows.
Two tables concerned are CustomerInvoices and CustomerAccounts
PK of CustomerInvoices is: UniqueInvoiceID
PK of CustomerAccounts is: UniqueInvoiceID and PaymentNumber
Its like a parent- weak entity relationship
1 invoice many accounts
sqlstr = " SELECT CustomerInvoices.InvoiceID , CustomerInvoices.CustomerID , " +" CustomerInvoices.CustomerName , CustomerAccounts.DateOfPayment , CustomerAccounts.Payment , " + _" CustomerAccounts.PaymentMethod " + _" FROM CustomerAccounts INNER JOIN " + _" CustomerInvoices ON CustomerAccounts.UniqueInvoiceID = CustomerInvoices.UniqueInvoiceID "
Dim Sqladapter As SqlDataAdapter
connection = createDatabaseConnection()
dim receivablesDataSet = New DataSet("DATASETNAME")
Sqladapter = New SqlDataAdapter(sqlstr, connection)
Sqladapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
Sqladapter.Fill(receivablesDataSet )
Dim newForm As New GenericCrystalViewerForm
Dim rpt As New CrystalReport2
rpt.SetDataSource(myDataSet)
GenericCrystalReportViewer.ReportSource = rpt
I dont want to use XML as it gives me an error saying I have a Invalid Key Node
and its driving me crazy
All I want to use is the simple setDataSource method.
By the way I have used all combinations such as
rpt.Database.Tables(1).SetDataSource(myDataSet.Tables(0))
with no luck at all
In summary:
Instead of getting 95 rowsfor example I am getting 65000 rows
which looks like a crossjoin
I would appreciate any help please
Thanks
Touraj
|
|
|
|
|
How to Create DSN by APIs in VB6?
Shoaib Nawaz
|
|
|
|
|
You need to call methods on ODBCCP32.dll
SQLConfigDataSource
Option Explicit
Private Const ODBC_ADD_DSN = 1 ' Add data source
Private Const ODBC_CONFIG_DSN = 2 ' Configure (edit) data source
Private Const ODBC_REMOVE_DSN = 3 ' Remove data source
Private Const ODBC_ADD_SYS_DSN = 4 ' Add a new system data source.
'ODBC_CONFIG_SYS_DSN: Modify an existing system data source.
'ODBC_REMOVE_SYS_DSN: Remove an existing system data source
Private Const vbAPINull As Long = 0& ' NULL Pointer
Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal hwndParent As Long, ByVal fRequest As Long, ByVal lpszDriver As String, ByVal lpszAttributes As String) As Long
Public Function AddODBCDSN()
Dim strDriver As String
Dim strAttributes As String
Dim intRet As Long
'since i do not have SQL Server installed....
strDriver = "Microsoft Access Driver (*.mdb)" 'Set the attributes delimited by null.
'See driver documentation for a complete
'list of supported attributes.
'You should use this
'strDriver = "SQL Server"
strAttributes = "SERVER=SomeServer" & Chr$(0)
strAttributes = strAttributes & "DESCRIPTION=Test DSN" & Chr$(0)
strAttributes = strAttributes & "DSN=DSN_TEMP" & Chr$(0)
strAttributes = strAttributes & "DATABASE=c:\test.mdb" & Chr$(0)
'strAttributes = strAttributes & "UID=" & Chr$(0)
'strAttributes = strAttributes & "PWD=" & Chr$(0)
'To show dialog, use Form1.Hwnd instead of vbAPINull.
intRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_SYS_DSN, strDriver, strAttributes)
If intRet Then
MsgBox "DSN Created"
Else
MsgBox "Create Failed"
End If
End Function
|
|
|
|
|
Does Max(NumberFieldName) works in SQL Server? If no then why??
Shoaib Nawaz
|
|
|
|
|
Yes, it works.
SELECT MAX(Count) FROM MyTable WHERE Date < #1/1/2005# -- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005
|
|
|
|
|
convert interbase database file into sql server database
My question is that through interbase database i have text file and again i want to convert that file in sql server database or any ms-access database is it possible so what r the steps.....
plz reply soon..
brijesh
convert interbase database file into sql server database
My question is that through interbase database i have text file and again i want to convert that file in sql server database or any ms-access database is it possible so what r the steps.....
plz reply soon..
brijesh
|
|
|
|
|
I am sending emails from SQL Server database. I used sp_send_cdosysmail stored procedure to send emails. I have to pass SMTP Server for this procedure.It works sometime for same SMTP Server name, it does not work sometime. Plz suggest solution for this.
Regards
Venkat M
|
|
|
|
|
would appreciate any assistance re creating a table in a MS Access database and filling it with data stored in a dataset
thanks
|
|
|
|
|
sorry I'm coding in vb.net 2003
|
|
|
|
|
Re creating, could you be more specific? There's a lot about this in the web. You just need to look.
<italic>Work hard, Work effectively.
|
|
|
|
|
I would like my program to read data from an excel spreadsheet into a data set then manipulate the data and then create a new table in a MS Access database and populate the table with data from the dataset without using ADOX.
I cant find examples of a table being filled with a dataset that has data from a different source.
any urls (that may assist) would be appreciated
Thanks
|
|
|
|
|
i have a table with just only 1 column(its type is int) and when i make a query on it i need to have the RECORD NUMBER for every record in resault of query. on the other hands, the query should have two culomns: record number column, the Sumation of table field. i cant have identity column for that table. the table should have only 1 column.
sample:
table data:
1000
3000
5000
query data:
1 1000
2 3000
3 5000
my DBMS is SQL Server 2000
please help me with this problem.
Thanks
Rastegar
|
|
|
|
|
There is no concept of RECORD NUMBER in SQL.
If you need a query row number, you can build it using a temporary table with an identity column and dumping the table when you are done. I don't know why you would want to do this since the index in the result set would be equivalent on the client side.
|
|
|
|
|
Hi all.
i have following problem:
there is a DataSet with two tables. A relation between them.
First table (parent) is a DataSource for a DataGid. What I need is:
when I'm navigating in the grid the ComboBox shows appropriate rows (one column ofcource) from child-table.
can anybody help me?
|
|
|
|
|
Hi all,
I wanna Format my database server due to some reason...
so how i can Backup of SQL Server Databases....
i have around 25 Databases in Sql Server.. and i wanna take backup of all..
So how i can perform this task..
plz guide Step by step....
i m new in DataBase Backup ......
Thanx
Sajjad
|
|
|
|
|
just simply copy all data & log files of all databases
then, restore all
<< >>
|
|
|
|
|
I want to enable TCP\IP protocol for sqlserver, for my client server application, at server side. Becoz i m using MSDE as database. I can enable it through by using SvrNetcn.exe, but want to enable it by vb.net code or by command line parameter or any other way with out using any User Interface. How i can do that?
SEC
|
|
|
|
|
Hi
I have a stored procedure (sp_getscores) that gets a parameter with type int( name it ID )
and returns one row with fields A int, B int, C int.
I need to use this stored procedure as :
I have a range of IDs say 100 to 150.
I want to use sp_getscore foreach ID in range of 100 to 150 and get the tabular result as
ID |A|B|C
___|_|_|____
100|.|.|.
101|.|.|.
...
150|.|.|
which . is an int.
It is obvious that i can achieve this by call the sp in .net application and use a for structure as
for( int i = 100 ; i<= 150 ;i++ )<br />
{<br />
}
But i need to have a sp that does this job for me.
Can anyone write me a t-sql to does this?
Thanks!
|
|
|
|
|
select table.A, table.B, table.C from table where ID in ( 100,101.....,150)
|
|
|
|
|
Please read my problem exactly
I never want what you say!!!
|
|
|
|
|
|
hi,
ı have a report on sql 2005 reporting services. and ı have a matrix on it. ı add a subtotal coloumn for this matrix, but ı want to add an extra coloumn.
for example, ı take some integer numbers totals in subtotal coloumn, and ı want to multiply this values with 50 and ı want to show it in an extra coloumn near subtotal coloumn. but ı didn't do it.
how can ı fix this problem?
please answer me on alisapanci@gmail.com
thanx
|
|
|
|
|
hi guys,
i really need your help
i want to import excel file to sql server table.
the problem is the recordset from excel connection, just insert first value of this recordset to sql server table, but record count is the same as recordcount from this recordset.
before the recordset inserted to sql server table, i display in MSFlexGrid, in MSFlexGrid the values is match with original excel file.
by the way, in excel connection and sql server connection i use aduseclient in cursorlocation, so the recordset support bookmark.
thank you
|
|
|
|
|
I don't understand your problem. Maybe in Indonesian, I'll understand it.
<italic>Work hard, Work effectively.
|
|
|
|