|
I'm having a problem connecting to the Sql Server database setup on my local machine. I can connect to my company's network database with no problem so it has to be something wrong with my connection string.
I'm using Sql Server 2005.
Here's the current string I'm using in my web.config file:
----------------------------------------------------------------
<add name="DBConn"
connectionString="Server=(local);Initial Catalog = <my database name>;User Id =<the username I log in with>;Password=<I don't login with a password so I left this property blank>"
providerName="System.Data.SqlClient"/>
-----------------------------------------------------------------
Where can I locate the necessary data for each of these properties? I'm pretty sure the "Server" property is correct however the rest is a pure guess.
Please help. I can't progress until I'm able to connect to my database.
Thanks in advance.
-Goalie35
|
|
|
|
|
I think you need to do some reading on connection strings
Goalie35 wrote:
User Id =<the username I log in with>
It may not be the name you log in with
Goalie35 wrote:
Password=<I don't login with a password so I left this property blank>"
You need to specify a password
only two letters away from being an asset
|
|
|
|
|
How about removing the user/password and use Trusted_Connection=true;
see http://www.connectionstrings.com for more info.
|
|
|
|
|
Thanks for the help guys.
Smallguy78, the Trusted_Connection=True worked.
Thanks.
-Goalie35
|
|
|
|
|
The trusted_connection=true relates to your windows userid and passward. The user_id and password relate to SQL Server login information.
Ian
|
|
|
|
|
i am using microsoft access to view SQL.I need help on how to set a column to currency [this column result after multiplying the number of units bought(UNIT_BOUGHT) and the unit price(UNIT_PRICE)(unit price is in currency)].So the results are named SUBTOTAL,i want them to contain currency.
LudereMP
|
|
|
|
|
Are you suing MS Access as a front-end to a SQL back-end database?
If so, you can set the column type to 'Currency' in the table designer.
Steve
|
|
|
|
|
I have the following tables
Person
---------------
Id
Name
Locations
---------------
Id
Name
PersonLocation
---------------
Id
PersonId
LocationId
So each person can be in multiple locations.
The problem with the 3rd table is with a lots of Locations, this table can grow huge (2million+ rows). Even with indexes on PersonId and LocationId, I'm thinking this will make inserts take a lot longer once the table is big. Is this the optimum way of storing the relationship between the two tables? Or there a clevered way of doing the storage?
|
|
|
|
|
Welcome to the wonderfull world of relational databases!
This is the most efficient, and the most sensible, way to store this information. Whilst 2 million records may seem a lot, it really isn't. Inserting and deleting will not be noticably slower when the table has many rows than when the table has few rows in it.
The alternative to this structure would be to use a denormalised table that has the person_name and location_name stored. Not only does this arrangement require more storage space, but what happens if:
1. You need to change the name of a location or person?
2. Two or more people have the same name?
BTW, you only need a single non-clustered index on the personid and locationid fields as I assume that the combination would be unique.
Ian
|
|
|
|
|
Yes the combination will be unique so a clustered index would work.
|
|
|
|
|
A clustered index in this case may not be useful unless your searches will always be on one column such as Person_id. A clustered index over the two columns will be no advantage in this case as the combinations are unique. Having a clustered index on a table may mave an impact on performance as it involves a physical re-ordering of data every time that a change is made. In this case you will still need a non-clustered unique index covering both person_id and location_id to ensure uniqueness of combinations.
Ian
|
|
|
|
|
Hi everyone,
I don't have any previous knowledge of SQL but need to be able to retrieve data from an SQL database here at work. From what I've managed to gather from some sample code for running SQL commands on an Excel file but the code doesn't work against an sql server. I've searched high and low on the net and best I have managed is to be able to connect to the database successfully...
Try<br />
CN.ConnectionString = _<br />
"Network Library=DBMSSOCN;" & _<br />
"Data Source=xxx.xxx.xxx.xxx,xxxx;" & _<br />
"Initial Catalog=" & dbName & ";" & _<br />
"User ID=" & dbUser & ";" & _<br />
"Password=" & dbPass<br />
CN.Open()<br />
Catch ex As Exception<br />
Console.WriteLine(ex.Message)<br />
End Try
But I don't know what to do next as far as retrieving the actual data 
Is anyone able to help?
|
|
|
|
|
Hi,
create a data adapter or data reader or command object as
Dim dap as new sqldataadapter()
and then write an Sql Statement in the braces to retrieve the data.Like
Select * from (Table_name) where empno= eno or anything according to the query.ok
srushti08
|
|
|
|
|
|
I'm doing a program to try to learn some ADO.NET programming and I'm running into the following problem:
One of the fields in my database is a VarBinary(MAX) that I want to store and image in.
I created my own SqlDataAdapter to talk to the DataSet and such, now on the SqlDataAdapter for the InsertCommand and UpdateCommand I have the following parameter to correspond to the image field in the database:
param_arr2[7] = new SqlParameter ("@jacket", SqlDbType.Image, 15000, "jacket");the problem is that I don't know how big the image is going to be so I don't want to have to specify the size in the parameter, and if I change the parameter to
param_arr2[7] = new SqlParameter ("@jacket", SqlDbType.Image);when I try to update the data I get and exception with the message "Parameterized Query ... expects parameter @jacket, which was not supplied." Any help on this problem would be most appreciated.
Thanks!
- Aaron
|
|
|
|
|
Image and VarBinary are two different column types. You should use SqlDbType.VarBinary if that is what is defined in the DB structure.
Image: Stored out of band (not on the same page as the other table data) and can be up to 2 gigs in size.
VarBinary: Stored with the other table data and limited by page size ~8000 bytes give or take your other columns.
Not sure if these descriptions changed with 2005.
|
|
|
|
|
According to the SQL Server 2005 BOL the image datatype is going to be removed in future versions of SQL Server, which is why I in the database I set the field type to varbinary(max), which indicates maximum storage is 2^31-1 bytes. The same problem still occurs in the program if I run it using
param_arr2[7] = new SqlParameter ("@jacket", SqlDbType.VarBinary);I still get the same exception "Parameterized Query ... expects parameter @jacket, which was not supplied."
Is there any way to set this up to have to parameter use as much space as is necessary to store the image?
Thanks for the help.
- Aaron
|
|
|
|
|
Quoting from MSDN:
"For variable-length data types, Size describes the maximum amount of data to transmit to the server. For example, for a Unicode string value, Size could be used to limit the amount of data sent to the server to the first one hundred characters."
So, you should set the size to the largest expected size you can handle.
For updates, set the size later (just before using it in the sqlCommand):
((SqlParameter)param_array[7]).size = mydata.length;
|
|
|
|
|
SELF JOIN is a type of OUTER JOIN .The difference is that of SELF JOIN,The same table is joined?
srushti08
|
|
|
|
|
Correct. The table is joined upon itself. Typically you will have a field in the table that is used to store a PK value for another record in the table for a row that you are currently either storing or updating. For example, I have a table that is called SECURITY table. It contains a list of all securities that are traded and need to be maintained within the system. One of the columns on the table is called 'underlying_security'. When an option type security is being stored, the underlying_security field will contain the PK value for the underlying equity of the option. A query might be as follows
select ...
...
from security opts
,security unds
...
where opts.underlying_security = unds.security_code
...
In this case the column security_code is the primary key on the security table.
Chris Meech
I am Canadian. [heard in a local bar]
Nobody likes jerks. [espeir]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
When you delete a table,its strututral definition,data,indexes,views,constraints are permanently deleted from a database?
srushti08
|
|
|
|
|
Yes, usually...
In SQL Server, you can setup a 'View' in such a way that it prevents a participating table from being deleted...I think!
Unless you store the schema of a table in some other way, the structural definition will be lost too.
Steve
|
|
|
|
|
If you use "drop table", yes. If what you really want to do is just remove all the data, you should use "truncate table..."
|
|
|
|
|
Sounds like a school question. All data and schemas are deleted, however views won't be removed, you may get a warning however and the delete won't work if you have SCHEMABINDING in your views. Constraints will stop the delete working too, so they'll need to be removed from other questions.
Try deleting a table, that's the easiest way to find out.
|
|
|
|
|
Hi,
I have a couple of VBA modules I need to convert to a SQL 2005 udf (or a C# class if that is abetter place for it!).
The VBA code is below:
Function RMSBetaDist(x, alpha, beta, rate)
'Main function where the cumulative Beta distribution is assembled
datasize = Range("Datsize")
Dim bt() As Double
Dim RMSBeta() As Double
Dim i As Integer
ReDim bt(datasize)
ReDim RMSBeta(datasize)
For i = 1 To datasize
If x(i, 1) < 0 Or x(i, 1) > 1 Then
'Error = MsgBox("There is a problem with the x value of the beta function", vbOKOnly)
'GoTo Abend
x(i, 1) = 1
End If
If x(i, 1) = 0 Then
bt(i) = 0
RMSBeta(i) = 0
GoTo Nextarray
ElseIf x(i, 1) = 1 Then
bt(i) = 0
RMSBeta(i) = 1
GoTo Nextarray
Else
bt(i) = Exp(RMSGammaln(alpha(i, 1) + beta(i, 1)) - RMSGammaln(alpha(i, 1)) - RMSGammaln(beta(i, 1)) + alpha(i, 1) * Log(x(i, 1)) + beta(i, 1) * Log(1 - x(i, 1)))
End If
If x(i, 1) < (alpha(i, 1) + 1) / (alpha(i, 1) + beta(i, 1) + 2) Then
RMSBeta(i) = bt(i) * RMSBetaCF(x(i, 1), alpha(i, 1), beta(i, 1)) / alpha(i, 1)
Else 'symmetry relation I(a,b)=1-I(b,a) where I() is the incomplete beta function
RMSBeta(i) = 1 - bt(i) * RMSBetaCF(1 - x(i, 1), beta(i, 1), alpha(i, 1)) / beta(i, 1)
End If
Nextarray:
RMSBetaDist = RMSBetaDist + rate(i) * (1 - RMSBeta(i))
Next i
Abend:
End Function
Function RMSGammaln(xg) 'Lanczos gamma approximation
'Used in the Beta distribution approximation
Dim y, temp, serial, PI, small As Double
Dim coeff(6) As Double
Dim j As Integer
PI = 3.14159265358979
coeff(0) = 76.18009173
coeff(1) = -86.50532033
coeff(2) = 24.01409822
coeff(3) = -1.231739516
coeff(4) = 0.00120858003
coeff(5) = -0.00000536382
small = 0
If xg < 1 Then
small = xg
y = xg - 1
Else
y = xg - 1
End If
temp = y + 5.5
temp = temp - (y + 0.5) * Log(temp)
serial = 1
For j = 0 To 5
y = y + 1
serial = serial + coeff(j) / y
Next j
If small <> 0 Then 'formula for values of x < 1
RMSGammaln = -temp + Log(Sqr(2 * PI) * serial)
Else
RMSGammaln = -temp + Log(Sqr(2 * PI) * serial)
End If
End Function
Function RMSBetaCF(x2, alpha2, beta2)
'Used to approximate the integral portion of the cumulative Beta distribution
Dim errorf As Double
Dim qap, qam, qab, em, temp, d As Double
Dim bz, bm, bp, bpp As Double
Dim az, am, ap, app, aold As Double
Dim m, MaxIterations As Integer
errorf = 0.0000001
MaxIterations = 1000
bm = 1
az = 1
am = 1
qab = alpha2 + beta2
qap = alpha2 + 1
qam = alpha2 - 1
bz = 1 - qab * x2 / qap
For m = 1 To MaxIterations
em = m
temp = em + em
d = em * (beta2 - em) * x2 / ((qam + temp) * (alpha2 + temp))
ap = az + d * am
bp = bz + d * bm
d = -(alpha2 + em) * (qab + em) * x2 / ((qap + temp) * (alpha2 + temp))
app = ap + d * az
bpp = bp + d * bz
aold = az
am = ap / bpp
bm = bp / bpp
az = app / bpp
bz = 1
If Abs(az - aold) < errorf * Abs(az) Then
GoTo FinalStep
End If
Next m
ErrorStep:
'Error = MsgBox("Either alpha or beta is too big or the maximum iterations are too small for convergence", vbOKOnly, "Problem in RMSBetaCF function")
RMSBetaCF = 1
GoTo Abend
FinalStep:
RMSBetaCF = az
Abend:
'Msg = MsgBox(m & " iterations ", vbOKOnly)
End Function
Any help with this would be greatly appreciated!
Thanks,
Guytz72
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
