Change local connection string on remote server

Hello,

I have searched every post and tried every connection string there but can't figure out how to connect to my database on my remote server. I am using Visual Developer 2005 Express Edition in C# and uploading it to a Windows server running asp.net 2.0. The domain has not resolved yet.

I am so new to this and have followed many tutorials step by step but none of them address this issue. They just show how to upload it to the server.

Do I need to use the SQL server provided by my host or can the database stay in the App_Data folder?

My local connection string works locally:

<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\add_newSQL.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />

When I uploaded to my server I changed \SQLEXPRESS to (local) as advised in the forum.

<add name="ConnectionString" connectionString="Data Source=(local);AttachDbFilename=|DataDirectory|\add_newSQL.mdf;Integrated Security=True;User Instance=True"

providerName="System.Data.SqlClient" />

When I debug a page I get the <customErrors mode="Off"/>
error message even thought I have already set it in my remote web.config file which may be causing problems itself:

<configuration>

<appSettings/>

<connectionStrings>

<add name="ConnectionString" connectionString="Data Source=(local);AttachDbFilename=|DataDirectory|\add_newSQL.mdf;Integrated Security=True;User Instance=True"

providerName="System.Data.SqlClient" />

</connectionStrings>

<system.web>

<customErrors mode="Off" />

<compilation debug="false" />

<authentication mode="Windows" />

</system.web>

</configuration>

Thanks for any help you can offer. I wish I could find someone to hire to do this part for me or teach me how over the phone. Any suggestions?

You are running SQL Express on your development system (your local computer). Are you running SQL Express on the web server?

Thanks,
Bryan

|||Having this same, problem!!!!!|||So the same question to you, as the question wasn′t answered by the original poster, are you running SQL Server Express on the WebServer ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Chalenge returning the first available number in a sequence

Hi all,

Ive been trying to figure this out and has proven to be quite difficult for me. Lets say i do a select on all client numbers from a clients table i would want the first available number returned.

Given the client table below my query would return 4 because its the lowest number availeble.

Thanks.

client table

clnum

1

2

3

6

7

Writing a single sql query for this task is challenging. Though I suggest that you could write a stored procedure that loops through a cursor pointing to clnum and check which number isn't present in the column:

psuedo code

open a cusor on clnum

loop i =1 to max int

loop thru the cursor and check if i is present ; if not then return i

end of loop

close the cursor

For help on using loops and cursors,seehttp://www.databasejournal.com/features/mssql/article.php/3111031

Hope this helps.

QI.

|||

Create a temp table or table variable, insert your clientid's and query from it. Something like this:

Declare @.ttable( Idint identity, clientidint)insert into @.tselect 1unionallselect 2unionallselect 3unionallselect 6unionallselect 7select top 1 IDfrom @.tWhere Idnot in (Select clientidfrom @.t)order by id

|||

This would not work if we already had 4 and 5 in the column as well. For example:

Declare @.ttable( Idintidentity, clientidint)

insertinto @.t

select 1unionall

select 2unionall

select 3unionall

select 4unionall

select 5unionall

select 6unionall

select 7

selecttop 1 ID

from @.tWhere Idnotin(Select clientidfrom @.t)

orderby id

The above code wouldn't return anything. Even though it should return 8, which is the lowest available number. Also, you want to be able to select from a column which may have an unknown number of rows so I don't think you can keep on doing unions like this.

QI.

|||

(1) If the ID returned is NULL, you can always query back with MAX(Clientid) and increment by 1. If you write a function to do this, it should be very simple.

(2) There is no need to manually write the INSERTs. That was just for a sample.

INSERT INTO @.T

SELECT Clientid

FROM Clients

would insert all the clients into the table.

Casting Help

Can someone please help me with this? I'm losing all my hair trying to figure it out. I've tried all that I can think of. I am getting the Error converting data type varchar to numeric.

Thanks
Sam "O"

cmdInsert = New SqlCommand( "INSERT INTO tmp_blank (sessionid, ssn,job,sun,mon,tue,wed,thu,fri,sat,totcol)
SELECT '" & Session.SessionId & "', ssn,job,sun,mon,tue,wed,thu,fri,sat,
(cast(sun as numeric(4,2)) + cast(mon as numeric(4,2)) +
cast(tue as numeric(4,2)) + cast(wed as numeric(4,2)) +
cast(thu as numeric(4,2)) + cast(fri as numeric(4,2)) +
cast(sat as numeric(4,2))) as totcol from
" & strTableName, conTimeCard)
intUpdateCount = cmdInsert.ExecuteNonQuery()

What are the values for sun, etc.? Could they be null? Could they be non-numeric, like 'fred'? If they are numeric, why are you using a varchar field?|||Thank you for your quick reply. Here are the answers to your questions.

yes values could be null
They will never be non-numeric
I am using an exisiting table and I am not able to modify the datatypes (yet)

Thanks
Sam "O"|||I am not certain the NULL is the issue, but I would try using IsNull(), like so:

CAST(IsNull(@.v,'0') as Numeric(4,2))

Even if it does not throw the exception, if the variable is null, null+anything will return null.

I would also check and see that all values are null rather than a single space. For instance, this will give you the casting error:

DECLARE @.v as varchar(20)
SET @.v=''
SELECT CAST(IsNull(@.v,'0') as Numeric(4,2))

If the values that do not appear obviously to be numbers are not NULL, but are rather empty strings or ' ', then perhaps you will want to run a query to change the "missing" values to null, or create a view with case statements to fix up the values. This is why using one type when you want another is a bad idea.|||I did try the IsNull Prior to posting and still was thrown the exception so it makes sense to me now what you are saying about the empty string possibilities.

Thank you for your input!