I try to access a MSSQL Database from Windows by using the following statement:
Dim rs As RecordSet = WaWi_DB.SQLSelect("SELECT DEB.CRM_AdressenID, CRM.* " + _
"FROM [SL_MARCHE].[dbo].[DEBITOREN] AS DEB, [SL_MARCHE].[dbo].[CRM_ADRESSEN] AS CRM " + _
"WHERE DEB.Nummer='" + Kunde + "' AND DEB.CRM_AdressenID=CRM.ID")
“Kunde” is a string like “12345”
It works fine within an app called “Database explorer” on the same computer. In this “Database explorer” the statement is:
SELECT DEB.CRM_AdressenID, CRM.*
FROM [SL_MARCHE].[dbo].[DEBITOREN] AS DEB, [SL_MARCHE].[dbo].[CRM_ADRESSEN] AS CRM
WHERE DEB.Nummer='12345' AND DEB.CRM_AdressenID=CRM.ID
If i access only one of both tables in a statement, it seems to work fine. It looks like the MSSQL Plugin is very unstable.
Initial thoughts are you should be using JOINS as they are the ANSI standard whereas the method you’ve used is older. Assuming DEB.CRM_AdressenID and CRM.ID are the foriegn key columns I’d rewrite your query
SELECT
DEB.CRM_AdressenID,
CRM.*
FROM
[SL_MARCHE].[dbo].[DEBITOREN] AS DEB
INNER JOIN
[SL_MARCHE].[dbo].[CRM_ADRESSEN] AS CRM ON
DEB.CRM_AdressenID=CRM.ID
WHERE
DEB.Nummer=‘12345’
Not sure if this is the problem with the plugin but worth a go…
damned syntax indenting - I had it nicely laid out
Let’s see if this improves it!
SELECT
DEB.CRM_AdressenID,
CRM.*
FROM
[SL_MARCHE].[dbo].[DEBITOREN] AS DEB
INNER JOIN
[SL_MARCHE].[dbo].[CRM_ADRESSEN] AS CRM ON
DEB.CRM_AdressenID=CRM.ID
WHERE
DEB.Nummer='12345'
Thank you Patrick, but it still crashes. It also crashes on the 2nd statement if i split them…
[code] Dim rs As RecordSet = WaWi_DB.SQLSelect(“SELECT CRM_AdressenID FROM [SL_MARCHE].[dbo].[DEBITOREN] WHERE Nummer=’” + Kunde + “’”)
If rs<>Nil And Not rs.EOF Then
Dim AdressID As String = rs.IdxField(1).StringValue
rs = WaWi_DB.SQLSelect("SELECT * FROM [SL_MARCHE].[dbo].[CRM_ADRESSEN] WHERE ID='" + AdressID + "'")
If rs<>Nil And Not rs.EOF Then
Break // Missing code
End If
End If
Ya. For last years Training Day I discovered 3 or 4 major bugs like that. They might be fixed for 2014 R1 but since I don’t use SQL Server I’ve not checked them. SQL Server just isn’t used much in Xojo apps.
I can send you (off list) a test app I put together for a presentation I gave to the London User Group in 2012! You can reverse engineer that to see how it hangs together.
It connected to SQL Azure (Microsoft Cloud MS SQL). The native plugin, at that stage, didn’t support Azure - don’t know if that’s still the case.
The database itself is offline so the app won’t run but may prove useful.