I am new, just bought the NudeDK package this afternoon.

I am trying to start on my local machine, but I cant get a custom connection string to work to my local database on my development laptop.

I am using the Quick Build screen and the test query button. I can run a query against the default DNN database, But no luck against my database (SelfASP).

Here are some I have tried:

Provider=sqloledb;Data Source=laphome;Initial Catalog=SelfASP;User Id=Ray;password=;

Driver={SQL Native Client};Server=(local);Database=SelfASP;Uid=Ray;Pwd=;

Provider=sqloledb;Data Source=Test;Initial Catalog=Test;user Id=sa;password=test;

Provider=sqloledb;Server=(local);Database=SelfASP;uid=Ray;


I always get:

Either no records were returned, or your query was invalid. Please check your query and try again.

* My Environment is as follows: My local DNN is the version 4.4.1 (installed by the automatic DNN installer). DNN and SelfASP reside on the same local server. When I open them in SQLServer mgt studio, this is what it says at the top: laphome (SQL Server 9.0.3054 - LAPHOME\Ray). So the name of my server/computer is laphome and my userID is Ray. There is no password.


* This is the string to my database inside a localhost ASP website's web.config, which works fine:

<connectionStrings>
<add name="SelfControllerString" connectionString="Data Source=laphome;Initial Catalog=SelfASP;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>


* These are the localhost DNN connections string (which work):

<add key="SiteSqlServer" value="Server=(local);Database=DotNetNuke;Trusted_Connection=True;" />
<!-- Connection String for SQL Server 2005 Express -->
<add name="SiteSqlServer" connectionString="Server=(local);Database=DotNetNuke;Trusted_Connection=True;" providerName="System.Data.SqlClient" />

<img src='desktopmodules/ntforums/images/emoticons/sick.gif' height='20' width='20' border='0' title='Sick' align='absmiddle'> Uh - ok, me again, dont know what that third connection string is doing there, that was one of the ones I found on the forum before I changed it.. sorry.

Me again...
Unable to get the connection string to work, I decided to try my online site. The same problem. The site is at PowerDNN.

Here is the connection string for the db on that site, which works (Ive changed only the password to DBPassword):

<add name="HypnoConnectionString"
connectionString="Server=(local);Database=SelfASP;uid=SelfASP;pwd=DBPassword"
providerName="System.Data.SqlClient" />

Here are some of the strings I have tried unsuccessfully in ListX:

Provider=sqloledb;Data Source=(local);Initial Catalog=SelfASP;User Id=SelfASP;Password=DBPassword;

Server=(local);Database=SelfASP;uid=SelfASP;pwd=DBPassword;providerName=System.Data.SqlClient

Driver={SQL Native Client};Server=(local);Database=SelfASP;Uid=SelfASP;Pwd=DBPassword;

Provider=sqloledb;Data Source=(local);Initial Catalog=SelfASP;User Id=SelfASP;Password=DBPassword;

Is there some secret to all this that I am missing?

Hello,
I am assuming that the system you need the connection string to is not for a database on the same server or network right? Otherwise you could use the named pipe and specify the connection in your sql query. Otherwise the syntax you have above is not correct. You will need to lookup the connection strings for SQL Server 2005 from Microsoft to get your exact syntax.

Otherwise I would not use Driver, I would stick to Provider as SQL Native Client.

Thanks,

Huh?
In reference to my original post:
On both my local machine the DNN database and the custom database are on the same server .

In reference to my 3rd post:
I think they are on the same server on PowerDNN too, but I am not sure. I will put in a request to their tech support to find out.

I am a programmer of sorts, but I am not a network person (or maybe I wouldnt be having this problem) Ive never heard the term "named pipe" before.

But Im a bit perplexed. Are you saying that none of the samples in the instructions and on this forum apply to SQL server 2005 ??.. and that I should go to Microsoft to find out what type of connection string to use to connect your Microsoft based product to an SQL server 2005 database?

Im not trying to be contrary. I know you guys are busy, and I am probably just tired and will regret my grumpiness tomorrow, but is there something unusual about what I am doing?

Most of the time the External Connection String in the Listx module points to a dbms that is either

#1 on a different network
#2 not SQL server. (oracle)
#3 of course not your own database

Otherwise you can refer to the database as the exposed name such as

myserver.mydatabase.myuser.mytable

In this instance myserver is the named pipe in the sql query. This is just a convenience for an All Microsoft network.

If you are on the same server you only need to provide the pipe name for the database, so you can use mydatabase.myuser.mytable etc...

Otherwise, if you are trying to connect to another dbms, like I believe you may be if you are running the website locally and want to connect to another server's database, then you can use an external connection string to another server/dbms. The connection string should be very similar to what you have in your web.config except specifying the ip address and login credentials. Otherwise, you do not need to provide any connection string if you want to query against your local database.
Thanks and hope this helps,

Im sorry, but I put about 4 more hours in, but still cant get it working on my local server (Ill worry about production later).

I have an absolutely basic ONE SERVER, SQL2005 Developer edition, out of the box installation on a laptop. I have a simple laptop with 3 databases on the same out-of-the box "server".
One is Northwind, the other is DNN the other is SelfASP (my custom DB).

After reading your post and doing some research on named pipes, I discovered I needed to turn named pipes on in SQL server (using the server config mgr), so I did that. But I still cant find a way to get it to work. In the couple books I have there is nothing on named pipes, what I find online seems to be about connection strings with slashes (not use in a query with dots).

I have tried various versions of the following:

In the table_name box:
User_Table

In the primary key box:
UserID

In the Connection box:
nothing

In the Query box:

use SelfASP.Ray.user_table
GO
select * from user_table

Sorry for your frustration. Rob should be able to assist you further in the AM. In the mean time if you install the Doctor Management ListX package it has examples of working with a SQL DB, connecting to tables for various purposes (ie. filtering, grouping, listing, detail, etc.). that may be a good workng example.

Chris Chodnicki
R2integrated

If the database SelfASP is on the same instance of SQL Server 2005, there is no need for a connection string. For example, my DNN database is DNN0408. I have another database called IGI. In my query, I write:

SELECT *
FROM igi.dbo.product_master
WHERE type_id = 1 and is_Available = 'True'
ORDER BY title

Notice in the FROM clause I reference the database name, IGI, then the owner, dbo, and then the table name.

Is this scenario similar to yours?

I attached a screeenshot from the QuickBuilder. It shows the fields filled in, no connection string, and the first few rows retrieved by the query.

Hello,
Is everything passing when you test the query in the quick builder? If not, was there an error that you were getting?
Thanks,

Still not working.
If I query the DNN database it works, but not for SelfASP.
There is never an error message. Even if I enter garbage in the fields I always just get the following in red:

Either no records were returned, or your query was invalid. Please check your query and try again.

This is what I have tried most recently in the quickbuilder:

Table Name:
SelfASP.dbo.User_Table

Primary Key:
UserID

Connection:
blank

Query:
select * from SelfASP.dbo.User_Table

<b> </b>

FYI:

If I look in the server in SQL Server Mgt Studio

This is the Custom DB:
LAPHOME\Databases\SelfASP\Tables\dbo.User_Table

This is the DNN DB:
LAPHOME\Databases\DotNetNuke\Tables\dbo.Users

Just to make sure there isnt some other DNN db somewhere that is being used, I checked the modules table and fount listx.

You mentioned that no matter what you types in for fields that you received the same message. Did you try just selecting the userID field, instead of "SELECT *"?

No luck. I tried:

select UserID from User_Table

and

select UserID from dbo.User_Table

and

select UserID from SelfASP.dbo.User_Table

.... all with matching entries in the table name

Miamiray,

Have you checked the results of what the QuickBuilder has done?

If not, click the arrow in the uper left-hand corner of the module to expose the menu.

Click on View Options.

Click on Query.

Verify the query is as you thought you had typed. If you change it, click Save Configuration at the bottom of the screen.

Are any records now displayed?

Hmm...
Thanks for your help, this does suggest something (I think).

If I enter something like select UserID from SelfASP.dbo.User_Table in the quick builder,

then I go to the main query window, This is what it shows:

Select * FROM (
select UserID from SelfASP.dbo.User_Table
) x ORDER BY [SORTTAG,UserID]

I dont even know what to make of that.

Ok, so now I tried ditching the Quick Builder and using the query window for the first time. But here, even the simple queries against the DNN db in the quick builder dont give any output when I use them in the normal query window.

Of course, the problem here is probably that I dont know what I am doing. Im still trying to figure out how to get output to show without using the quick builder, in order to see if it is working.

Without using the QuickBuilder, you need to access the menu like in previous response. Then choose View Options, Format.

Click the pencil to the left of List Item Detail; then enter HTML code for the display of a single record in the List Item box. For example,

<tr><td>[userID]</td><td>[UserName]</td></tr>

Click Save and Continue at the bottom of the screen.

Click Add List Group link just below the pencil and above the List Item box. Since you are not grouping, enter a name for the group such as Users in the Group Statement box; then enter HTML code for display of the group info, which will actually be the beginning and ending table tags for the user record display. For example, in the Header box enter,

<table>

In the Footer box, enter

</table>

Click Save and Continue

Click General

Type the # of records to display per page

Click Save Configuration

You should now see data assuming you are doing this in the module where the query is already defined.

Miamiray - I'm not sure exactly why you are having so many issues getting up and running, but I'd like to get involved in finding the solution to the problem rather than prolonging the cycle which you are experiencing. I'm certain that the problem you are having is specifically based on the logistics of your configuration and permissions, rather than the connection string itself. So let's go through some basic information before I get too deep.

First - the default SQL Server environment is capable of sharing users between database. So for example - your Dotnetnuke instance is using the database named "DotNetNuke". In your initial post, you stated that both connections are using Integrated Security. Which is fine. However the user "Ray" which you are using throughout the SelfASP database will only work properly if in fact "Ray" is the actually owner of that table, and not just the preferred username for the connection itself.

ListX can use the default connection and simply connect to the other database via that connection because they reside on the same database server. This is true as long as the credentials for the user are provided.

Basically - you can say "Select * from Tabs" against the primary database and "Select * from SelfASP..User_Table" for the second. Notice that I left off the owner of the table because I'm not confident at this point that Ray is in fact your table owner.

Also make sure that whatever you are doing, you are verifying that this will work within the permissions you have defined for the user you are using. For example, the connection string for a custom connection of OleDb - you would use the following:

Provider=sqloledb;Data Source=laphome;Initial Catalog=SelfASP;User Id=Ray;password=;

This is the correct connection string with a user named Ray and no password. However - you MUST test this with your SQL database to make sure that Ray has access to this database.

Assume now that Ray has proper access to the database tables. In your initial post you were using the Integrated Security. So if Ray is NOT the owner of the table, you would select from your primary tables as:

Select * from SelfASP.dbo.User_Table

OR

Select * from SelfASP..User_Table

If Ray is the owner (and you connect as Ray) you can do the following:

Select * from User_Table

Finally - this does work in the quick builder, however if you are still having problems you can get the physical error very quickly.

1. Open View Options, under General check the Show Debug for Super Users
2. In the Query Panel, set your Query and Custom Connection String
3. Save the Configuration. If you get a Yellow debug marked "Tables" which contains the info about your table result - you are fine.
if not, and you get the Red Error box - thats the issue - expand it and it will tell the exact nature of the problem.

Please let me know if this gets you moving in the right direction.

THanks!

Lets try a different approach.

When I click on iether the DNN or the SelfASP database it tells me that the owner is LAPHOME/Ray. But I dont know enough to know if some other issue with my development machine is stopping this from working ( Im a server admin virgin)... So

Let me just try to get it working at PowerDNN. Then if I can do that, I will have the confidence to take my laptop into the office (this is why I use a laptop for this), and ask someone with a lot more server admin expertise than myself to make my local environment do what is obviously possible, because it is happening in the production environment.... and Ill be good to go.

So:
Question 1:

My database owner for the DNN database is called selfcontroller.
My database owner for the SelfASP database is called SelfASP.
If I log in remotely I can see that they are on the same server.
They TELL me that selfcontroller is a valid user for database SelfASP.

If I go into the SQL window on my site at PowerDNN and type:

Select * from selfcontroller.users
I get a nice display.

But if I type:
Select * from SelfASP.dbo.User_Table

I get a red message that says: There is an error in your query!

What should I ask them?