|
Saira Tanwir wrote: i encounter many problems.
Can you explain in details, what problems you are facing and the query you are using ?
Saira Tanwir wrote: please reply soon.
If you specify the complete details in your question itself, I am sure you will get lot of replies and you wont waste your time.
Next time please specify complete details in your postings.
"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder
coolestCoder
|
|
|
|
|
ok!
the query i'm using top retrieve the primary keys of all tables in the database is
sql="SELECT name FROM sysobjects WHERE xtype='PK'";
when i execute this query through
DataTable pkey=new DataTable();<br />
sql="SELECT name FROM sysobjects WHERE xtype='PK'";<br />
cmd=new SqlCommand(sql, conn);<br />
reader=cmd.ExecuteReader();<br />
pkey=reader.GetSchemaTable();<br />
reader.Close();<br />
if(pkey!=null)<br />
{<br />
dataGrid2.DataSource=pkey;<br />
}
the output i get is always the same i.e. always only one column name is returned with its attributes and the column name is always 'name'.
i've also tried the following queries:
sql="SELECT name FROM sysobjects WHERE xtype='PK' AND name='name'";
the result is still the same
Saira
|
|
|
|
|
Hi coolestcoder
i've just got the desired result from the query, by changing me logic from SQLDataReader to SQLDataAdapter.
now i have all the primary keys in the entire DB.
now i want a query which gives me the primary key with respect to its table name. can u please guide me through the query that gives me the desired results.
Saira
|
|
|
|
|
|
Saira Tanwir wrote: i've just got the desired result from the query, by changing me logic from SQLDataReader to SQLDataAdapter.
That was not the reason. The reason was that you were not actually reading the DATA from the data reader, you were reading the schema of the result set. Your SQL Query specified that your result set contained just one column called "name".
|
|
|
|
|
U can try this code--
first u feel the dataset by adapter pass datatable to this function-
Private Sub GetPrimaryKeys(table As DataTable)
' Create the array for the columns.
Dim columns() As DataColumn
columns = table.PrimaryKey
' Get the number of elements in the array.
msgbox("Column Count: " & columns.Length.ToString())
Dim i As Integer
For i = 0 To columns.GetUpperBound(0)
msgbox(columns(i).ColumnName & columns(i).DataType.ToString())
Next i
End Sub
Keep Smiling !!!
Parwej Ahamad
g_parwez@rediffmail.com
|
|
|
|
|
i want read mouse data from ps2 port with my application
i know mouse data format that send to ps2 port but i don't know
how read from ps2 port
|
|
|
|
|
The PS2 port is a serial device ( i.e. com port ). If you already have a driver installed reading the mouse you may be able to "hook" in to that or else you can write your driver. You need to know some low level code to read of the com port.
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Using SQL ?
Christian Graus - C++ MVP
|
|
|
|
|
The boy likes a challenage
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
i need to swap variable.how to swap in sql .
i need syntax about swapping with 'if' and 'forloop' conditions
Smitha
|
|
|
|
|
prameelapydi wrote: i need to swap variable.how to swap in sql .
What do you mean by "swap variable"?
|
|
|
|
|
Some thing like this
<br />
@Declare @MyVar1 int <br />
@Declare @MyVar2 int<br />
@Declare @MyVar3 int<br />
<br />
SET @MyVar1 = 1<br />
SET @MyVar2 = 2<br />
<br />
------- Swap @MyVar1 @MyVar2 -------------------<br />
<br />
SET @MyVar3 = @MyVar1<br />
<br />
SET @MyVar1 = @Myvar2<br />
<br />
SET @MyVar2 = @MyVar3<br />
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
how to write in sql storeprocedure
ForExample:3 rows like
flag1 flag2 flag3
1 malini salini
2 devi rani
3 vani vijaya
here flags are fields i need to swap complete 1 row to other row
like this,
flag1 flag2 flag3
1 malini salini
3 vani vijaya
2 devi rani
Priyanka
|
|
|
|
|
i wanna to make data visualisation for selected numerical fields in star coordinates(1 star/row)
 please help me as soon as possible THANKS
-- modified at 11:57 Tuesday 28th November, 2006
|
|
|
|
|
Is this homework ? as we can't see figure below ?
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Hello,
I want to update a database. the code i have below gets all the values from a table called roles, and populates a dataset.
I then have a datatable that i want to add some new rows too. I then add this rows to the data table and add the data table to the dataset.
I then want to update the database by calling the dataAdapter update command. The code below as a problem with the update.
I have been told that the insertcommand would be better, but not sure, as i have never used that below. Can anyone give any advice on this.
Many thanks in advance,
Steve
using VB 2005 and SQL Server 2005
<br />
Dim i As Integer<br />
Dim dt As New DataTable()<br />
Dim dr As DataRow<br />
Dim userID As Integer<br />
Dim ds As New DataSet()<br />
Dim da As New SqlDataAdapter()<br />
Dim cmd As New SqlCommand()<br />
Dim cnn As New SqlConnection<br />
Dim cb As New SqlCommandBuilder()<br />
<br />
dt.Columns.Add("UserID")<br />
dt.Columns.Add("GroupID")<br />
Try<br />
cnn.ConnectionString = "server=.; database=serviceMaster; integrated security = true"<br />
cnn.Open()<br />
cmd.Connection = cnn<br />
cb.DataAdapter = da<br />
cmd.CommandType = CommandType.Text<br />
cmd.CommandText = "SELECT * FROM Roles"<br />
<br />
da.SelectCommand = cmd<br />
da.Fill(ds)<br />
dt = ds.Tables(0)<br />
<br />
Catch ex As Exception<br />
<br />
End Try<br />
<br />
userID = 24<br />
Dim chklstIndexes As CheckedListBox.CheckedIndexCollection = Me.chkLstSecurityRoles.CheckedIndices<br />
<br />
For i = 0 To Me.chkLstSecurityRoles.CheckedItems.Count - 1<br />
dr = dt.NewRow()<br />
dr("userID") = userID<br />
dr("GroupID") = chklstIndexes.Item(i)<br />
dt.Rows.Add(dr)<br />
Next<br />
<br />
'Add to the dataset and use the dataAdapter to update the database<br />
Try<br />
ds.Tables.Add(dt)<br />
cb.GetUpdateCommand()<br />
da.Update(ds)<br />
Catch ex As Exception<br />
<br />
End Try<br />
|
|
|
|
|
You have a few choices
1) Set your datadapter to write back to the database using update, insert commands configured for your dataAdapter
2) Use SQL commands or storedprocedures to update your data
3) Create an object class with a save method to save data back to the database.
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
How do i do a funtion o procedure to add or Sum two values from differents columns.
La Light
|
|
|
|
|
Select (tbl1.value + tbl2.value) as ColSum from tbl1,tbl2
Keep Smiling !!! Tomorrow Never Die
Parwej Ahamad
g_parwez@rediffmail.com
|
|
|
|
|
I need to write a stored procedure that meets the following specification
Tables Involved:
TblCustomer - CustNo,CustName
TblInvoice - InvoiceNo,IssuedDate,CustNo
TblProduct - ProductCode,ProdDesc,ProductFamily,GroupKeyID,FRB,SourceID,MarketSegmtID,ConvFactor
TblInvoiceDetailProduct - InvoiceNo,ProductCode,Quantity,UnitPrice
TblInvoiceDetailGL - InvoiceNo,GLCode,Quantity,UnitPrice
TblInvoiceDetailNonProduct - InvoiceNo,
TblInvoiceDetailProductNonInventory - InvoiceNo,ProductCode,Quantity,UnitPrice
Fields to be selected - CustName,ProductDescription,ProductCode,SourceID,FRB,MktSegmtID,Quantity( Sum up for the Invoices' of the particular period),Value - Summation of all the Invoices' quantity * convfactor in ProductTable
Using the CustNo from TblInvoice and TblCustomer,CustName should be selected
If the ProductCode of ProductTable matches with the following tables
InVoiceDetailProduct
InVoiceDetailProductNonInventory
InVoiceDetailGL
InVoiceDetailNonProduct,
We have to retrieve the corresponding fields : ProductDesc,SourceId,FRB, MktSegmentID From ProductTable
Quantity - For the particular period,summing up of all the Invoices' Quantity
Can anyone let me know how I can write a stored procedure that meets the specification?That would be a great help!
Thank you!
Sincerely,
Deepa
|
|
|
|
|
Hi,
I dont think that asking others to write stored procedure for you will be good for you. You can search for syntax and understanding of stored procedures on google.com. Then if you have any specific problem, everybody here is sitting for solving each others problems. I am sure someone will help you.
As far as the syntax of stored procedures you can visit - http://www.microsoft.com/sql/techinfo/books.mspx[^]
"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder
coolestCoder
|
|
|
|
|
Hi,
I came up with the following stored procedure.But I couldn't any data.Can anyone let me know the issue behind?
Create Procedure dbo.procMARRPTMonthlySalesByCustomer_Final
@FromDate smalldatetime='',
@ToDate smalldatetime=''
As
Set NoCount On
if @FromDate =''
Begin
Select @FromDate=cast(cast(year(getdate())as varchar)+ '-01-01' as smalldatetime)
End
Begin
Select @FromDate=cast(cast(year(getdate()) as varchar)+'-01-01' as smalldatetime)
End
if @ToDate =''
Begin
Select @ToDate=cast(cast(year(getdate())as varchar)+ '-01-01' as smalldatetime)
End
Begin
Select @ToDate=cast(cast(year(getdate()) as varchar)+'-01-01' as smalldatetime)
End
Select Cust.CustName,
P.ProductDesc,
P.ProductCode,
P.SourceID,
P.FRB,
P.MarketSegmtID,
Sum(InvDp.Quantity) As "Billing Quantity",
Sum(InvDp.Quantity) * P.ConvFactor As "Reporting Quantity",
Sum(InvDp.Quantity) * InvDp.UnitPrice As "Value"
From
dbo.MARTblInvoiceDetailProduct InvDp
inner join
dbo.MARTblInvoice Inv
on InvDp.InvoiceNo = Inv.InvoiceNo
inner join
dbo.MARTblCustomer Cust
on Inv.CustNo = Cust.CustNo
inner join
dbo.MARTblProduct P
on InvDp.ProductCode = P.ProductCode and InvDp.SourceID = P.SourceID and InvDp.MarketSegID = P.MarketSegmtID
Where Inv.IssuedDate between @FromDate and @ToDate
Group by
Cust.CustNo,
Cust.CustName,
P.ProductDesc,
P.ProductCode,
P.sourceID,
P.FRB,
P.MarketSegmtID,
P.ConvFactor,
InvDp.UnitPrice,
InvDp.Quantity
union
Select Cust.CustName,
P.ProductDesc,
P.ProductCode,
P.SourceID,
P.FRB,
P.MarketSegmtID,
Sum(InvDpNonInv.Quantity) As "Billing Quantity",
Sum(InvDpNonInv.Quantity) * P.ConvFactor As "Reporting Quantity",
Sum(InvDpNonInv.Quantity) * InvDpNonInv.UnitPrice As "Value"
From
dbo.MARTblInvoiceDetailProductNonInventory InvDpNonInv
inner join
dbo.MARTblInvoice Inv
on InvDpNonInv.InvoiceNo = Inv.InvoiceNo
inner join
dbo.MARTblCustomer Cust
on Inv.CustNo = Cust.CustNo
inner join
dbo.MARTblProduct P
on InvDpNonInv.ProductCode = P.ProductCode and InvDpNonInv.SourceID = P.SourceID and InvDpNonInv.MarketSegID = P.MarketSegmtID
Where Inv.IssuedDate between @FromDate and @ToDate
Group by
Cust.CustNo,
Cust.CustName,
P.ProductDesc,
P.ProductCode,
P.sourceID,
P.FRB,
P.MarketSegmtID,
P.ConvFactor,
InvDpNonInv.Quantity,
InvDpNonInv.UnitPrice
Select Cust.CustName,
GLC.GLDesc As "ProductDesc",
InvDetGL.GLCode As "ProductCode",
'' As SourceID,
'' As FRB,
'' As MarketSegmtID,
Sum(InvDetGL.Quantity) As "Billing Quantity",
0 As "Reporting Quantity",
Sum(InvDetGL.Quantity) * InvDetGL.UnitPrice As "Value"
From
dbo.MARTblInvoiceDetailGL InvDetGL
inner join
dbo.MARTblInvoice Inv
on InvDetGL.InvoiceNo = Inv.InvoiceNo
inner join
dbo.MARTblCustomer Cust
on Inv.CustNo = Cust.CustNo
inner join
dbo.MARTblGLCode GLC
on InvDetGL.GLCode = GLC.GLCode
Where Inv.IssuedDate between @FromDate and @ToDate
Group by
Cust.CustNo,
Cust.CustName,
InvDetGL.Quantity,
InvDetGL.UnitPrice,
GLC.GLDesc,
InvDetGL.GLCode
union
Select Cust.CustName,
InvDNonP.ProductName As "ProductDesc",
'' As "ProductCode",
'' As "SourceID",
'' As "FRB",
'' As "MarketSegmtID",
0 As "Billing Quantity",
0 As "Reporting Quantity",
Sum(InvDNonP.Amount) As "Value"
From
dbo.MARTblInvoiceDetailNonProduct InvDNonP
inner join
dbo.MARTblInvoice Inv
on InvDNonP.InvoiceNo = Inv.InvoiceNo
inner join
dbo.MARTblCustomer Cust
on Inv.CustNo = Cust.CustNo
Where Inv.IssuedDate between @FromDate and @ToDate
Group by
Cust.CustNo,
Cust.CustName,
InvDNonP.ProductName,
InvDNonP.Amount
Thank you,
Sincerely,
Deepa
|
|
|
|
|
Hi there,
I am passing a LocationCode to a Stored Proc in my SQL report, and then the report is build around that parameter I passed.
The problem is, now I need to display the Location Name in the report footer for each page. I can currently display the LocationCode using the following code:
<br />
"Parameters!LocationCode.Value"
How can I get the LocationName for that LocationCode, without using "ReportingItems!txtLocationName.Value" - if I use this, I can only display the Name on the first page in a PDF doc.
|
|
|
|
|
Are you using Reporting Services ?
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
