[RESOLVED] database connection question

[Crash893]

Okay

the db admin has created a stored procedure called

GetContent_MapPathByName

which can be used like so

exec GetContent_MapPathByName 'put-the-complete-story-name-here’



I wanted to make an app that would allow the users to connected and do this from a winform



what i have so far is

dt.Clear();
            string adapter = string.Format("exec GetContent_MapPathByName ‘{0}’", TB_SearchString.Text);
            string conn = "Data Source = dcsql.usatoday.us.ad.gannett.com; Initial Catalog = [CMS-Site2]; User Id = xxxxx; Password = xxxxx;";
            SqlConnection sqlconn = new SqlConnection(conn);
            SqlDataAdapter daSQL = new SqlDataAdapter(adapter, sqlconn);
            daSQL.Fill(dt);

where xxxxx has been replaced over the name and password ( not windows authentication)


the problem i'm running into is that when i execute code it errors out on the daSQL.fill(dt) line

and it gives the error "Cannot open database "[CMS-Site2]" requested by the login. The login failed.
Login failed for user 'xxxxx'."


But i know that the username and password are correct. becuase i can use thouse same credintals to log in to that server using MS sql managment studio ( and the credintals are under security for that pirticular database too)


Does anyone see any other errors in what im doing?

[techgnome]

a few things:
1) check with http://www.connectionstrings.com to make sure the connection string is correct. Try the db name w/o the brackets.
2) While not related to your problem, I suggest NOT running your sproc as string like that... create a command object, set the commandtext to the NAME of the sproc, and add a parameter to it.... then run it.
3) I had another point, but forgot what it was by the time I got here.

-tg

[Crash893]

Thanks for the reply

1)

there were more connection strings for sql server 2005 than stars in the sky. How do i know which one to pick?


2)

why would you avoid doing that

2a) do you have a generic example of how you would access the sproc?


Thanks for your help

[techgnome]

1) The one that fits your circumstances... VB2005/2008 ... using ADO.NET ... SQLClient... etc...

2) because you're losing the ability to run it like an sproc... running it the way you are, it runs as a text command. PLUS, what if some one searches for O'Brien ???? Now your SQL looks like this: EXEC usp_SomePRoc 'O'Brien' ... which will cause an error.

2a) -- check out the FAQ section in the Database Development. There's a tutorial on how to run parameterized queries (which is what you really want to do).

But in a nut shell... something along the lines of this:

SqlConnection sqlconn = new SqlConnection(conn);
SqlCommand mySqlComm = new SqlCommand;
mySqlComm.CommandText = ('GetContent_MapPathByName'); 
mySqlComm.Parameters.AddWithValue('@ParameterName', TB_SearchString.Text);
mySqlComm.Connection = sqlconn;
dt.Merge(mySqlConn.ExecuteReader);

-tg

[mendhak]

This one: http://www.connectionstrings.com/sql-server-2005#1

You may need to specify that it's not a Trusted Connection as it might be attempting to use mixed authentication or your windows credentials.

[Crash893]

I took the [] of my db name
and the perens on the queriy were the wrong type after that

but it works


as for updateing to the proper sproc execution im going to give that a wirll

[techgnome]

Funny thing is that I remember what that third item was supposed to be now... "take the [] off of the database name" .. hehehehehe.... looks like you beat me to it.

-tg

[mendhak]

Didn't even think twice about that