|
Hi,
I hv a query which retrives the data frm data base and also calculates sm value for few colums i get the value as infinity.so how can i hv it in my query so that it should display zero and find the sum of that column.
Here is my query...Pls sm one help...
OleDbCommand cmdMtdDays = new OleDbCommand("with member COSt as sum({[Dimension Time].[Day Text].[" + FromFilter + "]},[Measures].[Out Standing Amt]) member AV_MTD as sum(" + QueryFilterMTD + ",[Measures].[Inv Amt]) member COSMTD as (AV_MTD/" + day + ") member MTDDAYS as (iif(COSt=0,null,(COSt/COSMTD))) select {MTDDAYS} on columns, nonempty([DLOMAS].[OMVALU].[OMVALU]) on rows from COS", con);
Regards,
Annu
|
|
|
|
|
Already answered in another forum
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
HI
I have a table (ContactDetails) with 2000 records. I have an application receiving an XML file from an external web service. Most time the data received from the web service is similar to the data in my table. Occasionally the data returned from the web service will have few differences. I want to compare the data received from the web service with the data in my local database and generate a xml file (based on the same schema) to send to another application . So how do i go about doing that?
Thanks
modified on Thursday, August 28, 2008 12:13 PM
|
|
|
|
|
I would approach this problem by breaking it down into phases.
1) Read the XML file
2) Fetch data from the database and compare to XML data
3) Write a resulting XML file
Below should get you some idea how to loop through an XML file.
<br />
Imports System.Xml<br />
<br />
Dim xdTemplate As New XmlDocument<br />
Dim xnRoot As XmlNode<br />
<br />
xdTemplate.Load("c:\temp\FP_TAK.xml")<br />
<br />
xnRoot = xdTemplate.SelectSingleNode("/myPath/mySub/etc")<br />
<br />
' This code show some simple code to loop through an XML file<br />
' you will need to modify this based on your layout, but at least it shows some<br />
' functionality.<br />
<br />
While (Not (IsNothing(xnRoot))<br />
If (xnRoot.Name = "FSFORMULATPALL") Then<br />
If (xnRoot.Item("PARAM_CODE").InnerText = "R.B.D_SOY_ALLERGEN") Then<br />
Debug.Print(CStr(i) + ") " + xnRoot.Item("PARAM_CODE").InnerText)<br />
xnRoot.Item("PVALUE_LABEL").InnerText = "No"<br />
End If<br />
End If<br />
<br />
xnRoot = xnRoot.NextSibling<br />
End While<br />
<br />
xdTemplate.Save("c:\temp\dave.xml")<br />
<br />
|
|
|
|
|
Hello every one
I have created small application which can save and delete images from sql database it's works fine but only problem is when ever I add or delete image from sql database it dosent refresh I mean If I delete on image so in picture or database count it should show that database is empty or if there is two images in database then it should shows that one image in database
but if i close application and restart it then it shows all the changes so could you please help me with this
this is the code for save,delete and load image
Private Sub LoadImages()
Try
conImage = GetDBConnection()
dsImage.Clear()
Me.ImagesTableAdapter.ClearBeforeFill = True
Me.ImagesTableAdapter.Fill(Me.dsImage.Images)
daImage.Fill(dsImage, "Images")
Catch ex As Exception
MsgBox(ex.Message)
End Try
conImage.Close()
conImage.Dispose()
End Sub
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
'get sql connection
Try
conImage = GetDBConnection()
Dim sSQL As String = "INSERT INTO Images (Pic,Title, IType, Height, Width) VALUES(" & _
"@pic, @title, @itype, @iheight, @iwidth)"
commImage = New Data.SqlClient.SqlCommand(sSQL, conImage)
Call GetImage()
commImage.ExecuteNonQuery()
MessageBox.Show("Image successfuly saved in database", "Image Load")
Catch ex As Exception
MsgBox(ex.Message)
End Try
commImage.Dispose()
' commImage = Nothing
conImage.Close()
conImage.Dispose()
Call LoadImages()
End Sub
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
SelectedImage = ("DELETE FROM Images WHERE ImageID = " & txtImageFile.Text)
conImage = GetDBConnection()
Try
commImage = New Data.SqlClient.SqlCommand(SelectedImage, conImage)
commImage.ExecuteNonQuery()
MsgBox("Image successfuly deleted from database", MsgBoxStyle.Information)
Catch ex As Exception
MsgBox(ex.Message)
End Try
commImage.Dispose()
commImage = Nothing
conImage.Close()
conImage.Dispose()
Call LoadImages()
End Sub
please help me with this issue 
|
|
|
|
|
Hi,
Is the row really being deleted: Modify the following to see that one record is really being deleted
SelectedImage = ("DELETE FROM Images WHERE ImageID = " & txtImageFile.Text)
conImage = GetDBConnection()
Try
commImage = New Data.SqlClient.SqlCommand(SelectedImage, conImage)
recordsAffected = commImage.ExecuteNonQuery()
MsgBox("Deleted count: " & recordsAffected)
MsgBox("Image successfuly deleted from database", MsgBoxStyle.Information)
If recordsAffected is 0 then your condition isn't correct
Mika
|
|
|
|
|
Hello Mika
first of all thanks for your rep.
yes it's shows affected records so i have dont this
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
Dim RecordAffected As Integer
SelectedImage = ("DELETE FROM Images WHERE ImageID = " & txtImageFile.Text)
conImage = GetDBConnection()
Try
commImage = New Data.SqlClient.SqlCommand(SelectedImage, conImage)
RecordAffected = commImage.ExecuteNonQuery()
MsgBox(CStr(RecordAffected) & " Image successfuly deleted from database", MsgBoxStyle.Information)
Catch ex As Exception
MsgBox(ex.Message)
End Try
commImage.Dispose()
commImage = Nothing
conImage.Close()
conImage.Dispose()
Call LoadImages()
End Sub
but still same after deleting record from sql database I have open "Show Data Table" and check that record has been deleted so i dont know what to do but if i close application and rerun it it shows the changes on screen
waiting for your kind rep.
have a nice day
|
|
|
|
|
Okay, was the RecordAffected 1 or 0?
Another thing, if I understood correctly the record is deleted when you close and rerun the application. What does your connection string look like? Could you post the GetDBConnection method? What I'm thinking of is that you have a pending transaction if the record is deleted, but you cannot see thet the deletion has happened.
|
|
|
|
|
Hello again
thanks for your rep.
Private Function GetDBConnection()
' Compose the database file name.
' Modify this if the database is somewhere else.
Dim DBname As String = Application.StartupPath()
DBname = DBname.Substring(0, DBname.LastIndexOf("\bin"))
DBname = DBname & "\Images.mdf"
' Compose the connect string.
Dim connect_string As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & _
DBname & ";Integrated Security=True;User Instance=True"
' Open a database connection.
Dim SqlConn As New Data.SqlClient.SqlConnection(connect_string)
SqlConn.Open()
' Return the connection.
Return SqlConn
End Function
well in the message box it says record 1 as bellow
RecordAffected = commImage.ExecuteNonQuery()
MsgBox(CStr(RecordAffected) & "Image successfuly deleted from database", MsgBoxStyle.Information)
i set a brakpoint and stepin so variable RecordAddected was 1
waiting for your kind rep.
thanks again
|
|
|
|
|
When you run the application and delete the image, open Task Manager and go to processes tab. How many sqlservr processes you see?
|
|
|
|
|
Hello again
Thanks for your promt rep.
yes I have delete one image and i can see as bellow
1- sqlbrowser.exe -- 652k
2- sqlservr.exe --- 25540k
3- sqlservr.exe -- 5900k
4- msftesql.exe --1724k
5- sqlservr.exe ---54084k
this is what i can see in task manager
waiting for your kind rep.
thanks
|
|
|
|
|
You have two SQL Server instances running at the same time. I don't know if they are separate databases, but in worst case they are separate user instances of the same database (you had User Instance=true in your connection string).
Instead of getting a new connection to the db in every sub, try to change the logic so that you get the connection only once. For example create the connection when the window loads and use that connection in every sub. Does anything change?
|
|
|
|
|
hello again
thanks for promt rep.
well to be very honest I have just started sql so i am not expert but as you said I will try to change logic and let you know
what I understand according to you that I need to open connection when form load and but do I need to close it or not but upto now what i have learnt i have to close the connection but i will see how it goes
is it possible to help me with this logic that where to start i dont want answer i will try it
waiting for your kind rep.
have a nice time
thanks
|
|
|
|
|
No problem,
Perhaps the easiest way is to make the connection static so that every time you connect to the database, you get the same connection. Something like (may contain errors):
Private Function GetDBConnection()
Static SqlConn = Nothing
If SqlConn Is Nothing Then
' Compose the database file name.
' Modify this if the database is somewhere else.
Dim DBname As String = Application.StartupPath()
DBname = DBname.Substring(0, DBname.LastIndexOf("\bin"))
DBname = DBname & "\Images.mdf"
' Compose the connect string.
Dim connect_string As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & _
DBname & ";Integrated Security=True;User Instance=True"
' Open a database connection.
SqlConn = New Data.SqlClient.SqlConnection(connect_string)
SqlConn.Open()
End If
' Return the connection.
Return SqlConn
End Function
And when using this, don't close and don't dispose the connection, so comment the lines conImage.Close() and conImage.Dispose() in btnDelete_Click and LoadImages and btnSave_Click
The idea is to test that you are using the same database all the time. If this doesn't help then we change back to the original logic and try something else.
|
|
|
|
|
Hello sir
thanks for promt rep.
yes i have change the logic the way you said but still it's same problem i mean i can save or delete image from sql database but i have to close application to see the changes
and one more thing i would like to tell you that i have created another application without image but it works fine if i add or delete first name and last name it shows the changes same time and i have used same method with this application but i am not sure but i sow one difference in it
that project has one table with three columns ID, FirstName, LastName
and this is button event to change enable = false
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Button2.Enabled = False
Button3.Enabled = False : Button4.Enabled = False : Button5.Enabled = False
Button1.Enabled = True
TextBox1.Enabled = True : TextBox2.Enabled = True
TextBox1.BackColor = Color.Aquamarine : TextBox2.BackColor = Color.Aquamarine
Try
Me.StudentBindingSource.EndEdit()
Me.StudentBindingSource.AddNew()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
but with this there is only one difference
Me.StudentBindingSource.EndEdit()
Me.StudentBindingSource.AddNew()
so do you think this is the issue
waiting for your kind rep.
have a nice time
thanks again
|
|
|
|
|
If you have corresponding BeginInit somewhere, you should call EndInit. Trying that is a good idea.
Also one possibility is that even if you delete the image from database, it isn't correctly refreshed in the dataset (dsImage). However you do call Clear-method so it should work.
If calling EndInit doesn't help, can you re-create the dataset (set dsImage to Nothing and create a new dsImage) and fill it after that.
|
|
|
|
|
hello again
as you said that to refresh dsimage so for example if there is 4 images in database and if i delete image no 3 so after deleting image it's refreshing dataset because in picture box it shows first image but still you can go forward or backword up to image 4 but when i close application and rerun it then it shows 3 images in database so that means it refreshing dataset properly
and there is no corresponding in begininit at all so i think i dont need to endinit
so we have only last option so i think i will try tomorrow
but any way i learn lot from you i realy appriciate that
thanks a lot
if i need any help i will contact you tomorrow
good night
|
|
|
|
|
You're welcome.
I'll go and have some sleep also... 
One thing you can try tomorrow is to add AcceptChanges()- call to dataset after deletion or fill. In case that the record is deleted but changes are still pending
MIka
|
|
|
|
|
Hello sir
good morning
how are you
I have tried every thing but still it's same problem so now I don't know what to do
I have also tried AcceptChanges() but still it's same
waiting for your kind rep.
|
|
|
|
|
I couldn't be bothered to read all the posts, but if the data is actually being deleted in the database (check from Management Studio) then you need to refresh your dataset and rebind it to your control.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hello sir
could you please guide me how to do this because i have just started sql so i dont know much about it i mean i dont want code but just want to know where to start
waiging for your kind rep.
have a nice day
|
|
|
|
|
Its not in sql, its in your code.
I assume yuo load data from the database into a dataset and then bind the datatable(s) within the dataset to controls. When you add or delete data you need to probably (depending on how you update your database) refresh your dataset and/or rebind the datatable(s) to the controls.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hello again
this is the code for add image
btnSave.Enabled = False
'get sql connection
Dim ImageAdd As Integer
Try
conImage = GetDBConnection()
dsImage.BeginInit()
Dim sSQL As String = "INSERT INTO Images (Pic,Title, IType, Height, Width) VALUES(" & _
"@pic, @title, @itype, @iheight, @iwidth)"
commImage = New Data.SqlClient.SqlCommand(sSQL, conImage)
Call GetImage()
ImageAdd = commImage.ExecuteNonQuery()
Me.daImage.Update(Me.dsImage, "Images")
dsImage.AcceptChanges()
MessageBox.Show(ImageAdd.ToString & " Image successfuly saved in database", "Image Load")
dsImage.EndInit()
Catch ex As Exception
MsgBox(ex.Message)
End Try
commImage.Dispose()
' commImage = Nothing
conImage.Close()
conImage.Dispose()
Call LoadImages()
End Sub
and when ever i add or delete image it refresh the the dataset i mean if ther is 3 images in database and if i want to delete image no. 3 after deleting image 3 it shows image no. 1 in picture that means it's reloading image that is what i am thinking
|
|
|
|
|
Hi again,
Few additional questions
- about the binding. Have you bound the dsImage dataset to a BindingSource and again a picture box to that BindingSource?
- in LoadImages sub, what is the difference between ImagesTableAdapter and daImage?
- in INSERT statement, you don't specify a value for ImageID-column, but that column is used in DELETE statement. How the ImageID get's it's value
|
|
|
|
|
hello again
how are you
thanks for your rep.
yes i have dound picturebox to binding source , and i am trying so many things so i add imagestable adapter but daImage is i have drag and drop on form and went through adapter wizard
and when i created Image table i have set primary key and identity specification set to yes so every time when i add new image to database it increment 1 by it self
and in delete statement i have find record to delete it so it gets the ImageID from textbox
all this is works fine but you know the problem
so now i have no idea what to do
waiting for your kind rep.
and thanks again
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
