Database

8-Sep-04 5:06

SQL Server: Books online - CREATE PROCEDURE[^]

.NET Framework Developer's Guide - Using Stored Procedures with a Command[^]

Does this help?

"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!

SQL server logs

Imtiaz Murtaza8-Sep-04 2:08

Imtiaz Murtaza

8-Sep-04 2:08

Friends,
I am running SQL server on my machine which is present in a LAN environment. Some one on the LAN, accessed my SQL server and deleted the data.
What i want to ask is that whether SQL server maintains any kind of laog that who tried to connect it and what query the client executed ? If yes, that how can i view this information. Is there any other alternative ? What i wnat to know is that who connected to my SQL server and what query request came from that machine ?

Imtiaz

Sql Query

rkyawal8-Sep-04 0:41

rkyawal

8-Sep-04 0:41

I have problem with this query..
it is taking too much time to fetch the records ..
can anybody tell me general solution on this.
is their any problem in any join ?
i have main problem in the following condition
( (b.cenid = c.cenidr and b.polyid = c.polyidr)or(b.cenid = c.cenidl and b.polyid = c.polyidl))
if only one condition is used then it gives result very fast
so
help me ..

SELECT a.laname, a.cfcc, b.cenid, b.polyid, c.tlid, d.fename, d.fetype, d.frlong as d_frlong, d.frlat as d_frlat, d.tolong as d_tolong, d.tolat as d_tolat, p.polylong, p.polylat, p.water ,e.rtsq, e.long1, e.lat1, e.long2, e.lat2, e.long3, e.lat3, e.long4, e.lat4, e.long5, e.lat5, e.long6, e.lat6, e.long7, e.lat7, e.long8, e.lat8, e.long9, e.lat9, e.long10, e.lat10 FROM ((((
( rt7 as a inner join rt8 as b on a.land = b.land )
inner join rti c on ( (b.cenid = c.cenidr and b.polyid = c.polyidr)or(b.cenid = c.cenidl and b.polyid = c.polyidl))
)
inner join rt11 as d on c.tlid = d.tlid)
inner join rtp as p on p.cenid = b.cenid and p.polyid = b.polyid ))
left join rt2 as e on d.tlid = e.tlid
WHERE a.laname like 'Mill Pond%' and
(( 3958.682 ) * ( acos( sin( (d.frlat) * 0.01745 ) * sin( ( $latc ) * 0.01745 ) + cos( (d.frlat) * 0.01745 ) * cos( ( $latc) * 0.01745 ) * cos( (d.frlong - ($longc) ) * 0.01745 ) ) )) <=$mile
ORDER BY a.laname

Re: Sql Query

Colin Angus Mackay8-Sep-04 5:13

Can this exception be taken care of?

8-Sep-04 5:13

Writing a GIS System?

From my experience trig functions are expensive so you should cut down the amount of data that has to be calculated. To do this, recreate your existing query (without the trig functions) as a subquery, replacing the trig functions with a very basic bounding box - this will cut out a vast quantity of data quickly. You are now left with just a subset of the original data, your outer query can now perform the trig operations on a much smaller set of data.

As for the joins, I'm assuming that these id fields are all primary keys and indexed by default, you may want to index the foreign key side of them to see if it speeds up the query also.

However, from experience, I'd say the trig functions are where your bottle neck is.

Re: Sql Query

EdbertP8-Sep-04 20:44

EdbertP

8-Sep-04 20:44

Try using UNION to solve the INNER JOIN condition.
Here's how it should be like:

SELECT ...
FROM ...
INNER JOIN rtiC ON b.cenid = c.cenidr AND b.polyid = c.polyidr
...
UNION
SELECT ...
FROM ...
INNER JOIN rtiC ON b.cenid = c.cenidl AND b.polyid = c.polyidl
...

mysorian7-Sep-04 18:15

mysorian

7-Sep-04 18:15

This line of code
myDataSet1.ReadXml(mySqlCommand.ExecuteXmlReader(), XmlReadMode.Fragment)
-------------------------
throws this exception,
Invalid Operation exception:The operation is not valid due to the current state of the object.
--------------------------
mySQlCommand is a Select statement with 'for XML auto'.

Re: Can this exception be taken care of?

Colin Angus Mackay8-Sep-04 5:17

Re: Can this exception be taken care of?

8-Sep-04 5:17

Just curious, why are you populating your dataset from SQL Server via XML? It is a most inefficient way of populating a dataset. First SQL Server has to convert its data to XML, send it to your application, which then has to parse the XML and place it in the dataset. It would be much better to use a DataAdapter to populate the DataSet.

mysorian8-Sep-04 6:49

mysorian

8-Sep-04 6:49

Mr. Mackay;
Many thanks for replying.
I know it is not the best way. The reason, I tried to do this was to test the proof of principle. In order to use the ReadXML method, the command has to bring XML Data and this is the reason for the 'for XML auto' statement.
From the trace, I feel this exception has to do with System.Exception and probably to some 'postback' related problem. I would whether you can point to trouble shoot this.

Thanks

Jay

Counting NULL columns across a row?

Mike Ellison7-Sep-04 9:38

Mike Ellison

7-Sep-04 9:38

Hi all. I've run into an interesting problem. I have a need, with a single selected row in TSql, to count the number of columns with a NULL value (or conversly, the number of columns with a non-null value entered). The table from which the row comes is likely to see changes in its structure over time; adding to the complexity of this need, for simplicity in maintenance, it would be useful not to have to hardcode the column names (I'm thinking of hardcoded "CASE" statements here...)

Does anyone have any suggestions? Ultimately, a user-defined function that, given the primary key value identifying a single row, would return the number of Null values (or non-null values) across the columns in that row would be ideal. I'm hurting my brain on how to get there.

textbox datasource retrieve text problem

Bart-Man7-Sep-04 8:29

Bart-Man

7-Sep-04 8:29

Greetings,

I have a textbox which I have bound manually (through code) to a database field, and it displays the text correctly. But when I call "myTextBox.Text;" it doesn't have any text found in it, but it shows up on the windows form textbox correctly, but when I call the ".Text" property it isn't there. Is there something I have to manually do to get the text from the bound control?

I am using C# from 2003 studio on a windows xp system, and
here is an example of my code which I called to bind the textbox.
"myTextBox.DataBindings.Add("Text", myDataset1, "TextboxTestField");"

Thanks in advance for any help.

Possible Solution

Bart-Man8-Sep-04 3:52

Bart-Man

8-Sep-04 3:52

The only solution I could find on my own was to catch the change text event for the Text box which I had bound to the database field. When the event happened it would just store the text in a member variable which I could then access the actual value.

DTS Dynamic Properties

ProffK6-Sep-04 23:20

ProffK

6-Sep-04 23:20

I have several DTS packages that each exports a table to a text file. The text files all have a common output folder which I would like to configure using a dynamic properties task.

However, all these packages are executed using another higher level package, and I would like to configure the output folder in this package. How do I communicate the setting to the lower level packages?

My blog.

how to bind the textbox value to sqlCommand?

DotNet6-Sep-04 19:49

DotNet

6-Sep-04 19:49

I have a textbox (txtPCID), button (btnSearch), and datagrid (DataGrid).

Right now, I want to enter "P0002" in the textbox and click on the search button, I will get all the records which belong to PCID= P0002.

Look at that 4th line.

I don't know why the value in the textbox cannot pass to the sqlCommand?
How to solve it?

Sub BindDataGrid()

Dim Tpcid As String

Tpcid = txtPCID.Text

cmdSql = New SqlCommand("Select * From Peripheral WHERE PCID=' & Tpcid & ' ", myConnection)

myConnection.Open()

DataGrid.DataSource = cmdSql.ExecuteReader()

DataGrid.DataBind()

myConnection.Close()

End Sub

Re: how to bind the textbox value to sqlCommand?

Sudee6-Sep-04 20:23

Sudee

6-Sep-04 20:23

cmdSql = New SqlCommand("Select * From Peripheral WHERE PCID='"+Tpcid+"' ", myConnection)

I am not sure.. just try once. Sorry if I am wrong. Smile | :)

- Sudeep

======
You need a head to program. Cool, fast and sharp.

Re: how to bind the textbox value to sqlCommand?

Colin Angus Mackay6-Sep-04 23:10

How to insert ' into database?

6-Sep-04 23:10

You should really parameterise your command. What you have here, if it worked, is suseptable to an injection attack.

cmdSql = New SqlCommand("Select * From Peripheral WHERE PCID=@PCID", myConnection)
cmdSql.Parameters.Add("@PCID",SqlDbType.VarChar, 10).Value = Tpcid

The above is much safer. (Remember to change the SqlDbType and field length values as appropriate)

ting6686-Sep-04 19:10

6-Sep-04 19:10

as title.

What is the sql statement?

thanks

Sudee6-Sep-04 20:17

Sudee

6-Sep-04 20:17

http://www.w3schools.com/sql/sql_select.asp

======
You need a head to program. Cool, fast and sharp.

ting6686-Sep-04 20:28

6-Sep-04 20:28

A good link

However, i would like to know how to insert char(') into database?

e.g
Insert into table1 values (''','hhhh')

it causes exception

THanks

Sudee6-Sep-04 22:09

Sudee

6-Sep-04 22:09

this.SqlConnection = new System.Data.SqlClient.SqlConnection();

cmd = new SqlCommand("INSERT INTO TableName (intColumnA, chrColumnB) VALUES (1,'XYX')",this.SqlConnection);

cmd.ExecuteNonQuery();

What exception did you get?

======
Yo need a brain to code.

ting6686-Sep-04 22:48

6-Sep-04 22:48

sorry

I would know to how to insert the character "'" (single quotation character) into the database varchar field.

Thanks!

Colin Angus Mackay6-Sep-04 23:01

6-Sep-04 23:01

Use 4 apostrophes, one for open string, two for the apostrophe and one for the close string.

If the apostrophe exists in the middle of a string do something like this: 'doesn''t'

Does this help?

iamalik7-Sep-04 7:53

iamalik

7-Sep-04 7:53

There is way that you can store '
Find and Replace ' by • (Alt+7) and Insert the string in the database. On Retrival Find and Replace • by '

If you using ASP.Net or VB.Net or C# then Finding and Replacing is no Problem
Just Try
String.Replace("'","•");P

EdbertP8-Sep-04 21:19

EdbertP

8-Sep-04 21:19

Just create a function that replaces all your ' (single apostrophe) with '' (TWO single apostrophe),
i.e. [Joe O'Connell] becomes [Joe O''Connell]

However, I do recommend using Data Adapters and SQLCommands to pass your parameters so you don't need to worry about this anymore.
It does make my life easier aside from being safer from SQL injection.

ting6688-Sep-04 22:07