|
wasife wrote: I have used ExecuteNonQuery of SqlCommand class. But that does not work.
Too vague. How does it "not work"?
Since you can run just about any SQL through a SqlCommand (including CREATE INDEX) there must be some explanation and saying "that does not work" gives us no idea of where to start helping you.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Colin Angus Mackay wrote: saying "that does not work" gives us no idea of where to start helping you
Please give more detail as to what is not working 
Paul
|
|
|
|
|
Deat CPians,
I have a heavy duty database on SQL Server 2000.
Every now and then I monitor the transactions applied against the database using SQL Profiler in order to spot the queries and SPs that use alot of CPU resources.
Here are a few of many transactions that my question will be based on:
APPLICATION TEXTDATA DURATION STARTTIME READS WRITES CPU
---------------------------------------------------------------------------------------------------------------------------------------------
.Net SqlClient Data Provider 1229250 1/3/2006 10:53:53 am 2080346 366 60811
Internet Information Services 618203 1/3/2006 10:54:38 am 61550 1 4016
.Net SqlClient Data Provider exec lc_getLatestIpById @mem_id = 267980 483 1/3/2006 11:26:44 am 4594 0 484 As you can see the second column displays the actual querry.
I noticed the there are many requests that have no TEXTDATA at all like the two firsts ones. Not only I do not know what these transactiosn are, but they are very heavy, they cost alot!
What are these transactions? What triggers them?
Thanking you in advance.
theJazzyBrain
Excellence is not an act, but a habit! Aristotle
|
|
|
|
|
Hello All
I have looked into this on the web and found many articals explaining what causes the problem. Some I understand and some I don't but I am no closer to fixing it so some help would be great.
Problem:
I have a table I AM NOT allowed to alter, I can only run select queries against it. The table has a column called docsize (Document Size) in btyes, I run the following query on it.
Select Sum (DocSize) AS myDocSize From Document
Being bytes these are generally "1001374" bytes in size with around 100 000 rows on the table. The resulting number of the SUM is to big I guess so how to I work around this.
P.S. When I get this final figure I bring it in with this Sub
<br />
Private Sub TotalDocumentSize()<br />
Dim Command1 As SqlCommand = New SqlCommand("Select Sum (DocSize) AS myDocSize From Document")<br />
<br />
Command1.CommandType = CommandType.Text<br />
Command1.Connection = SQLConnection1<br />
<br />
Dim myDocSizeVarTmp As Integer = CType(Command1.ExecuteScalar(), Integer)<br />
BytesToMegabytes(myDocSizeVarTmp)<br />
<br />
End Sub<br />
And then run this Function
<br />
Public Function BytesToMegabytes(ByVal Bytes As Double) As Double<br />
'This function gives an estimate to two decimal<br />
'places. For a more precise answer, format to<br />
'more decimal places or just return dblAns<br />
<br />
Dim dblAns As Double<br />
dblAns = (Bytes / 1024) / 1024<br />
BytesToMegabytes = FormatNumber(dblAns, 2, , , TriState.False)<br />
<br />
Dim MyItem As ListViewItem<br />
MyItem = New ListViewItem("Total Document Size (MB)")<br />
MyItem.SubItems.Add(BytesToMegabytes)<br />
listStat.Items.Add(MyItem)<br />
End Function<br />
Any help would be great...
When people make you see red, be thankful your not colour blind.
|
|
|
|
|
You can try:
Select Sum (cast(DocSize as bigint)) AS myDocSize From Document
Wout Louwers
|
|
|
|
|
Also, in your code, try to store it in a UInt64, much bigger.
--------
"I say no to drugs, but they don't listen."
- Marilyn Manson
|
|
|
|
|
Thanks both your ideas worked the UInt64 gave an error but prompted me to use long for now.
Thanks you Both
"Hummmm what should we call an something bigger than an int 'BIGINT'"
Two SQL developers when home very happy with their decision.
When people make you see red, be thankful your not colour blind.
|
|
|
|
|
Hello,
I'm developing a simple website with a SQL server 2005 back-end. I've designed my database schema and I'm ready to create the database and add tables. So, here's my question:
When building a database backend for a web application, do you generally build the database on a local development machine and then somehow move it to the server? Or do you build it in place on the server somehow (by running server-side sql queries or something)?
I just need a little high-level help here.
Thanks!
-Ian
Ian
|
|
|
|
|
You can generate scripts while creating your schema objects on your local dev machine, modify as needed and then run the scripts against your production machine later. As you make changes you keep this same practice and then you can rebuild the schema whenever you want when you get new development or testing boxes...
hth
Al
|
|
|
|
|
I create scripts. When I'm ready to deploy I run the scripts on the live server.
Once the system is in production and there is an upgrade to do I take a backup of the live (production) system and restore it on the development platform and test the migration scripts. Once they work, I run them on the live server when deploying the changes.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Colin Angus Mackay wrote: I create scripts. When I'm ready to deploy I run the scripts on the live server.
Yeah, no sense in risking live production data if something isn't right in the script.
Colin Angus Mackay wrote: Once the system is in production and there is an upgrade to do I take a backup of the live (production) system and restore it on the development platform and test the migration scripts. Once they work, I run them on the live server when deploying the changes.
Good to have your bases covered
PJC
|
|
|
|
|
B"H
Please post links to examples of creating databound multirecord controls in winforms for .net ver 2.
I am looking for something like adding data binding to the listview or creating any control that can display (and even better if it can edit) multiple records at a time and use the standard databinding.
ASP.net seams to be getting all the attention in this area. to the extent that you can now acomplish easily in HTML that which is nearly imposible in a rich windows client.
Thanks
Moshe Plotkin
|
|
|
|
|
I have a table that has a set of financial transactions (simplified version)
Transactions
(
Account varchar(200),
TransactionDate datetime,
TransactionNo int,
TransactionType int,
Value money
)
Transactions can be payments or withdrawals. When a withdrawal takes place this is applied across the payments where the payment number is less than the withdrawal trans and the value of that transaction is reduced.
e.g. if I have transactions:
1, 2005-01-01 , Payment, $100
2, 2005-06-01 , Payment, $150
3, 2005-06-08 , Withdrawal, $180
Then after the third transaction the remaining value in the two payments is
1, 2005-01-01, Payment, $0
2, 2005-06-01, Payment, $70
So now I need to produce a report of the net value of the transactions account at a given date i.e. if the date passed in is less that 2005-06-08 then it would be $100 and $150 otherwise $0 and $70. Is there any clever view / query to do this or will i have to do a cursor?
Thanks in advance
'--8<------------------------
Ex Datis:
Duncan Jones
Merrion Computing Ltd
|
|
|
|
|
I do something similar and use a database procedure that implements two cursors and then calculates and stores in a separate table the results for each payment transaction. Essentially you provide the date to the procedure and the proc opens two cursors, one for all the payment transactions, and another for all the withdrawls. You then loop through each payment transaction one at a time and loop through the withdrawls inside that loop.
Chris Meech
I am Canadian. [heard in a local bar]
When I want privacy, I'll close the bathroom door. [Stan Shannon]
BAD DAY FOR: Friendly competition, as Ford Motor Co. declared the employee parking lot at its truck plant in Dearborn, Mich., off limits to vehicles built by rival companies. Workers have to drive a Ford to work, or park across the street. [CNNMoney.com]
Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
|
|
|
|
|
I am using VB.Net and have recently started working with databases. I hae managed to connect to and manipulate databases done in access that hae already been built, but I am currently trying to use a database I have built but get errors when I try to generate a dataset. The error reads "Retreiving the schema for OleDbDataAdapter1 failed. IErrorInfo.GetDescription failed with E_FAIL(0x80004005)" I think it has something to do with how I am building the database, all I hae done is create a new database and put a table in it with the headings that I require. I wold be greatfull if anyone cold tell me what else I need to do to the database to make it work.
Health Warning! Childlessness is hereditary (If your perents didn't have kids, neither will you!!)
|
|
|
|
|
I'm using SQL Express and I have a table that has a column of type image. My question is how to I go about inserting and/or updating an image in a row using C#? I'm aware that the image type in SQL is simply a BLOB, but I don't know how to insert an acutal picture (.jpg) using C#. Any and all help on this is very appreciated.
- Aaron
|
|
|
|
|
just check this article it helped me. www.codeproject.com/cs/database/ImageSaveInDataBase.asp
I hope it will help u too.
|
|
|
|
|
Hi,
I have put togther a windows 2003 server. I have SQL 2005 running and operating. I am having trouble connecting to my database from outside my network...I have port forwarding on tcp:1433. I have a public ip and domain services routing fine.
any suggestions?
Thanks,
Charles Pence
Student/DOTNET DEVELOPER
|
|
|
|
|
Do you have any firewalls enabled? Windows Server 2003 SP1 includes Windows Firewall which may need an exception added, if it's enabled (I'd recommend that it's turned on).
Also note that by default, TCP connections to some editions of SQL Server 2005 are not enabled. Go to Start, Programs, Microsoft SQL Server 2005, Configuration Tools, SQL Server Configuration Manager. Expand SQL Server 2005 Network Configuration, go to Protocols for instancename. Right-click TCP/IP and choose Enabled.
It's not generally considered a good idea to expose a SQL Server to the Internet. It would be better to implement some service to format the data more appropriately, for example, a web service.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thank you kindly for the advice.
Thanks,
Charles Pence
|
|
|
|
|
Hello Dear
How to insert records into two tables using single Query in SQL Server
N.Rajakumar B.E.,
Application Developer,
www.newdreamz.tk
|
|
|
|
|
If you seperate the 2 SQL statements with a semicolon ; they will both be executed.
cmd.CommandText = "INSERT INTO Table1 VALUES(1,2,3); INSERT INTO Table2 VALUES(3,2,1);";
The inserts won't be transactional unless you run the statement in a transaction or add SQL to the statement to create one.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
I am trying to edit an exisiting row in an existing table. I know I have the row as I can output to debug window values and column (field) names. When I then try and loop thru my rows and update a specific column, the code runs fine but the edits never get into my table/dataset.
Row.State returns "2".
The table is simply a SQLServre table I created with Enterprise Manager.
I am just trying to update some rows (ints and strings) from a sub in vb.Net.
either I am accessing the table in a read only manner or I am missing the function to make the "comit" happen. Any help would be appreciated.
If you need to look at the code, it is below...
Private Sub playwithSql()
Dim sqlConn As New SqlConnection(sConnection) <----This is cool...
Dim mysqlCommand As New SqlCommand
mysqlCommand.Connection = sqlConn
mysqlCommand.CommandType = CommandType.Text
mysqlCommand.CommandText = "SELECT * FROM TestTable"
Dim dsTxA As New DataSet
Dim dataAdapter As New SqlDataAdapter
dataAdapter.SelectCommand = mysqlCommand
dataAdapter.Fill(dsTxA)
Dim table As DataTable
table = dsTxA.Tables(0)
Dim col As DataColumn
For Each col In table.Columns
Debug.WriteLine(col.ColumnName)
Next col
Dim myrow As DataRow
For Each myrow In table.Rows
myrow.BeginEdit()
'' myrow.Item(2) = Date.Now
myrow.Item(1) = "data"
MessageBox.Show(myrow.RowState)
myrow.EndEdit()
'' myrow.AcceptChanges()
Next
''dataAdapter.Update(dsTVA)
''sqlConn.Close()
Debug.WriteLine("-----------------------------------------------------------")
End Sub
***DETAILS
sqlSrv2000
DotNet 1.1 (VB.NET)
|
|
|
|
|
Hi,
When u call myrow.AcceptChanges() method on datarow it will reset the RowState to unchanged and hence when you call dataAdapter.Update(dsTVA) it does nit find any record to be updated in database.
Also before you call dataAdapter.Update(dsTVA) you have to assign appropriate Insert/Update/Delete command of DataAdapter object.
Regards,
Ritesh
|
|
|
|
|
I got it figured out. Since I was using the DataAdapter,it seems that in order to "post back" to the dataset you have created, the table in the database needs to have a Primary Key. I guess because since when you re using the dataAapter, the resultant dataset (in memory) needs to somehow map back to the original table and a primary key is needed. I didn't need to use a DataAdapter but I was trying to do it using the new cool stuff in DotNet.
Here is a complete sample. (I palgerized some of this fro the help...)
Notice you will need to return a connectin. I did this with my sconneciton function...
**********UPDATING AN EXISTIN ROW WITH THE DATA ADAPTER - SQLSERVER**********
(I needed a primary key.)
''This works!!!!
Public Sub UpdateExistingRow()
Dim myConnection As New SqlConnection(sConnection)
Dim mySqlDataAdapter As SqlDataAdapter
mySqlDataAdapter = New SqlDataAdapter("SELECT * FROM TestTable1", sConnection)
Dim myDataSet As DataSet = New DataSet
Dim myDataRowsCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(mySqlDataAdapter)
mySqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
mySqlDataAdapter.Fill(myDataSet, "TestTable1")
Dim table As DataTable
table = myDataSet.Tables(0)
Dim myDatarow As DataRow
myDatarow = table.Rows(0)
myDatarow.Item(1) = "Wednesday"
mySqlDataAdapter.Update(myDataSet, "TestTable1")
End Sub
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
