|
Either escape the ' with another ' ('') or use an SqlCommand object with parameters. You can set the value of the parameter to a string containing 'special characters' and it will insert without difficulty.
Power corrupts and PowerPoint corrupts absolutely. - Vint Cerf
|
|
|
|
|
Anyone have any hands on with Rapid SQL, we are considering buying an enterprise license.
What are your thoughts on this tool good, bad etc;
Thanks
Tony
|
|
|
|
|
|
I have followign two tables, and want to update TB1 such that IDs col gets updated
PARAMETER tables
TB1
Dt desk IDs
----------- ---------- ----------
1 PT
1 AB
2 PT
2 AB
TB2
Dt desk book IDs
----------- ---------- ---------- -----------
1 PT PT1 100
1 PT PT2 200
1 PT PT3 300
1 AB AB1 400
2 PT PT1 500
2 PT PT2 600
2 PT PT3 700
2 AB AB1 800
Final RESULT
TB1
Dt desk IDs
----------- ---------- ----------
1 PT 100, 200, 300
1 AB 400
2 PT 500, 600, 700
2 AB 800
Any pointers
Thanks
Ruchi
|
|
|
|
|
Is this on sql server? If so, your best bet might be to write a cursor to build each of your id strings ('100,200,300') and then update. I've been trying to come up with a clever way to do it in one SQL statement, doing self joins of tb2 to itself...but I am not getting anywhere...
Typically, when I need to denormalize data like this, I do the denormalization in the application (typically c#...but that's details). SQL just does not lend itself to this kind of thing nearly as readily as ADO.NET / more procedural programming approaches do.
Hope this helps
Bill
|
|
|
|
|
Thanks very much for your response.
I myself am doing the same thing you suggested - Cursor & Updates. But it is kind of expensive operation, when temp table is huge (depending on param passed to SP)
I need this result set for C# application. Could you please give me pointers on how you do it in ADO.NET
Thanks
Ruchi
|
|
|
|
|
Ruchi Gupta wrote:
But it is kind of expensive operation, when temp table is huge
Agreed. Which is why I was trying to be clever...the update...from is often handy for complex updates, but I can't come up with a single query that will generate your desired result strings ('100,200,300','400', etc). Besides, that approach required that you know the max number of strings (in your example, 3) before hand.
You mention passing parameters for the first time above. Are you updating only a few records in TB1? That might make this easier. I was assuming you were updating the whole table.
Anyway in ADO, the idea is to get your data from TB2 in order, loop through the results building the new IDs string for each entry in TB1 and then do the update. C# doesn't suffer from the horrible performance penalties of SQL for looping.
Something like:
-Load TB1 into a DataTable using a dataadapter (if you set the PK, you can use a SqlCommandBuilder to automatically generate your update command)
-Load TB2, sorted by DT,Desk into a second datatable
-create a string variable to accumulate the id string;
-Cycle through every row of this second table.
-if the current row has the same DT and Desk as the previous row, just add the ID to the id string with the comma
-other wise, set the IDs column of the Row in the first DataTable with the matching DT and Desk to the accumulated ID string. Use DataTable.Find() to find the row. Clear the ID string.
-At the end, use the original dataadapter to update the database.
This way, you can do all the itteration in C#, which does it well, rather than in SQL which is terrible at it.
Hope this helps
Bill
|
|
|
|
|
this.oleDbConnection1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=c:\Technomedia.mdb;Mode=ReadWrite|Share Deny None;"
for the source, how do I make it so it accesses the database that is in the same folder as the program? Because when I load the program on other computer it give me this exception error:
"Process id = 0x4f4(1268), Thead id = 0x5dc(1500)"
I think it gives me this because it can't find the database because it is hardcoded into the program as being in a different location. So I want to know how to make it look for the database in the program location. i tried Data Source=technomedia.mdb but it gives me an error.
Thanks
|
|
|
|
|
Does this work? What exception is thrown, if not?
oleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Password=\"\";User ID=Admin;Data Source=" + Environment.CurrentDirectory + "\\Technomedia.mdb;Mode=ReadWrite|Share Deny None;"
|
|
|
|
|
now it gets stuck on my dataadapter, give me this:
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll
on this line: oleDbDataAdapter3.Fill(dataSet1, "Region");
|
|
|
|
|
Please post the full stack trace. (You should always do this when you post a question; it'll speed things up a bit.)
|
|
|
|
|
I am just started with c# and do it as a hoby. So I have no idea what a full stack trace is.
Thanks
|
|
|
|
|
Okay, I didn't notice that it was an unhandled exception. The first thing you need to do is wrap that section of code in a try-catch block so you can catch the exception! When an exception is "unhandled", that means that it's bubbled up to the top, causing your program to crash. You will do something like this:
try {
throw new ApplicationException("I'm a little teapot");
} catch (Exception e) {
Console.WriteLine(e.StackTrace);
}
You should read up on exception handling right away-- you're already past the "Hello, world!" stage, and into database applications, where you really need to be able to clean up your resources in case of a problem. Good luck.
|
|
|
|
|
this is what I get
at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr)
at System.Data.OleDb.OleDbConnection.InitializeProvider()
at System.Data.OleDb.OleDbConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState)
at System.Data.Common.DbDataAdapter.Fill(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
at WindowsApplication5.Form1..ctor() in c:\documents and settings\roman\my documents\visual studio projects\windowsapplication5\form1.cs:line 77
|
|
|
|
|
Try creating an ODBC data source, and then use the data source name in your connection string.
|
|
|
|
|
|
Hi, EveryBody,
I want to display pictures stored in a database. First I use the following code(VB6) to save the picture to the database:
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stream As New ADODB.stream
Set cnn = New Connection
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\test.mdb;"
cnn.CursorLocation = adUseClient
cnn.Open
Set rst = New Recordset
rst.ActiveConnection = cnn
rst.Open "t1", cnn, adOpenKeyset, adLockOptimistic
rst.AddNew
rst!p_name = "dd"
stream.Type = adTypeBinary
stream.Open
stream.LoadFromFile "H:\pp.jpg"
rst!pic = stream.Read
Set stream = Nothing
rst.Update
cnn.Close
------------------------------
and then I use the following code(ASP) to display the image:
<%
Dim cnn
Dim rs
' Clear existing HTTP header info
Response.Expires = 0
Response.Buffer = TRUE
Response.Clear
' Set the HTTP header to an image type.
Response.ContentType = "image/JPEG"
cnnPubs=getdbconn() ' the function return the db connection
set rs=server.CreateObject("adodb.recordset")
rs.Open "SELECT FROM t1where id=1",cnn
Response.BinaryWrite rst("pic")
Response.End
%>
and I use to show the image.
the first(vb) code works well.
but I alway can not display the image, I just got a image like red X.
please help me to solve the problem.
Thanks a lot!!!
Best!
Alan
|
|
|
|
|
MS Access stores an OLE header before the image data.
remove that header before sending the Response.
the size of the header is about 19 bytes (as i reacall)
search MSDN , KB for info about this:
Retrieving Bitmap from Access and Displaying In Web Page.
|
|
|
|
|
I'm trying to build a cross-tab query that is dynamic in mssql 2000. Since it does use the transform syntax I am stuck at doh
The columnheader will always change. So it's more of a dynamic columnheader. Depending on the Day of the week. There could be 5 or 3 or 10 teacher who have classes to teacher at different time of the day.
The Teacher's names will be the columnheader
Teaher's names across the top
The time is in the left column down
and student name
____ Teacher_Name Teacher_Name Teacher_Name Teacher_Name
Time Student_Name Student_Name Student_Name Student_Name
Time Student_Name Student_Name Student_Name Student_Name
Time Student_Name Student_Name Student_Name Student_Name
C2 and R2 = Student_Name
C3 and R2 = Student_Name
C2 and R3 = Student_Name
C1 and R3 = Student_Name and so on
The only way i can build a cross-tab query is in crystal report.
Any help would be gladly appreciated
I want to display the data in a listview or datagrid.
I'm lossing hair here and i have a bit left.
I'll look like homer in now time with 3 strings of hair. 
|
|
|
|
|
I initially created a cursor to create the a SQL statements as follows. Example:
SELECT tp.PropertyTestID, tp.PropertyTestGroupID, tp.PropertyTestName,
SUM(CASE WHEN S.SampleID = 122 THEN ResultDecimal ELSE NULL END) AS [N/A122 05/02/2004],
SUM(CASE WHEN S.SampleID = 109 THEN ResultDecimal ELSE NULL END) AS [N/A109 03/31/2004],
SUM(CASE WHEN S.SampleID = 108 THEN ResultDecimal ELSE NULL END) AS [N/A108 03/30/2004],
SUM(CASE WHEN S.SampleID = 107 THEN ResultDecimal ELSE NULL END) AS [N/A107 03/17/2004],
SUM(CASE WHEN S.SampleID = 106 THEN ResultDecimal ELSE NULL END) AS [N/A106 03/01/2004],
SUM(CASE WHEN S.SampleID = 105 THEN ResultDecimal ELSE NULL END) AS [N/A105 01/05/2004],
SUM(CASE WHEN S.SampleID = 104 THEN ResultDecimal ELSE NULL END) AS [N/A104 11/17/2003]
FROM tblSamplesPropertyTest tp,
tblSamplesSample s,
tblSamplesSampleResult sr,
tblSamplesCustomerProductReportPropertyTest prpt,
tblSamplesProductPropertyTest ppt
WHERE tp.PropertyTestID = ppt.PropertyTestID
AND ppt.PropertyTestID = prpt.PropertyTestID
AND sr.CustomerProductReportPropertyTestID = prpt.CustomerProductReportPropertyTestID
AND s.SampleID =* sr.SampleID
AND s.SampleID in (122,109,108,107,106,105,104)
AND tp.ResultTypeID = 1
AND (tp.Deleted = 0 or tp.Deleted Is Null)
GROUP BY tp.PropertyTestID, tp.PropertyTestGroupID, tp.PropertyTestNameAfter creating the statement I executed exec(@SQL) in the procedure.
However, I found this site that was quite helpful. Once I figured out what the crosstab stored procedure was doing in the article. Now any new crosstab/pivot tables are created using this method.
http://www.sqlteam.com/item.asp?itemID=2955
The easiest route, but not the best, may be to create an ODBC link to SQL server. Create an access database that links the necessary tables for the reports. Then simply create the reports in Access.
Good Luck,
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Thanks I'll give it a try and see how it goes.
|
|
|
|
|
We have a UDF (defined below that we are rtying to call from a column in the databse table. We are using the formua field in SQL enterprise manager to enter the call to the UDF.
Both columns are defined as datetime as well as the column we are trying to get the UDF to work in.
I have tried to use the following:
database.dbo.RuntimeSort(database.dbo.table.columnname, database.dbo.table.columnname)
RuntimeSort(columnname,columnname)
database.dbo.RuntimeSort(columnname, columnname)
RuntimeSort(database.dbo.table.columnname, database.dbo.table.columnname)
UDF:
CREATE FUNCTION dbo.RuntimeSort (@DateProduction datetime, @TimeStart datetime)
RETURNS datetime
BEGIN
DECLARE @Date datetime
DECLARE @DateReturn datetime
SET @Date = dateadd(day, 1, @DateProduction)
SET @DateReturn = datepart(mm, @Date) + '/' + datepart(dd, @Date) + '/' + datepart(yyyy, @Date) + ' ' + datepart(hour, @TimeStart) + ':' + datepart(minute, @TimeStart) + ':' + datepart(second, @TimeStart)
RETURN @DateReturn
END
I am at a loss here  and any help would be appreciated.
Regards
Eric C. Tomlinson
No comment, Mr. Senator<pre>
|
|
|
|
|
If that function is a direct cut-and-paste from Enterprise manager, there's a bug in it:
"Server: Msg 245, Level 16, State 1, Procedure RuntimeSort, Line 10
Syntax error converting the varchar value '/' to a column of data type int."
**Edit**
I see the problem. You have DATEPART() + '/'
DATEPART returns an int, which you are NOT casting to a varchar. So, when you try to add '/' to an int, it tries to auto-cast the slash into an int, and the function bombs.
To answer your original question, however, the proper method of defining the formula is dbo.RuntimeSort(date1, date2). You MUST provide the object owner, as well as the object name, and ALL parameters. UDF's on SQL Server 2000 don't support default parameter values like stored procs do.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
|
|
|
|
|
In this question I have Form1, myDataAccess class, Customer class.
In Form1 I have ListBox1 and a ButtonFill. Can you show me how do I get the details of customer(Firstname and Lastname) to fill in the ListBox1 by click on the ButtonFill. The 2 classes are coded below. The code in Customer class has some error. I am appreciate if you give me an example code. Thanks
'--------myDataAccess class---------------
Imports System.Data.SqlClient
Public Class myDataAccess
Public Function GetDataSet() As DataSet
Dim connection As SqlConnection
Dim command As New SqlCommand
If command.Connection Is Nothing Then
connection = New SqlConnection("workstation id=WHITE;packet size=4096;integrated security=SSPI;data source=WHITE;persist security info=False;initial catalog=ABC")
connection.Open()
command.Connection = connection
End If
Dim dataset As New DataSet
Dim adapter As New SqlDataAdapter(command)
adapter.Fill(dataset)
adapter.Dispose()
If Not connection Is Nothing Then
connection.Close()
End If
Return dataset
End Function
End Class
-----------------------------------
Imports System.Data.SqlClient
Public Class CustomerClass
Inherits myDataAccess
Public Function GetCustomerByname(ByVal Firstname As String, ByVal Lastname As String) As DataSet
Dim myQuery As String = "select * from CustomerTable"
GetDataSet()
Dim dataset As New DataSet
Dim adapter As New SqlDataAdapter
adapter.SelectCommand = New SqlCommand(myQuery)
adapter.Fill(dataset, "CustomerTable")
Return dataset
End Function
End Class
-----------------------------------
|
|
|
|
|
Hi,
i m getting problem in a query ,
==> Order_Recieved_Detail
ordID | ordQuantity | ordCost | productID | orID
=================================================
1_____|___10________|_2500____|____1______|___1
2_____|___10________|_3000____|____2______|___1
3_____|__ 10 _______|_3000____|____3______|___1
4_____|___10 _______|_2500____|____1______|___2
5_____|___10 _______|_3000____|____2______|___2
now i m getting problem in calculation of getting sum of ordQuantity of productID 1 of orID 1 and 2 to get 20
i want to get the following result
ordQuantity | productID
==========================
___20_______|____1______
___20_______|____2______
__ 10 ______|____3______
the query which i am using just sum all the itmes in the orderquantity column and not giving the sum of productID 1 and 2
waiting for any one to help me out....
"Winner's don't do different things , they do things differently "
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
