Hi!
Can someone tell me how or where I can find information on editind an SQL query through code?
I want to be able to run a user-defined lookup, where the user can choose what the query will look for.
Thanks in advance.
Guy
I have SQL Server 2005 Developer Edition. I have a script component in one dataflow which I have added code to today. I saved using the save Icon and exited. I noticed a warning on the item, so I went back into the script and my changes had gone. I though I could not of saved the code and retyped, saving every 5 minutes. I closed visual studio, and noticed the script component had the warning on again! I went back to the code and all my changes were gone. I did not see any errors or warnings in Visual Studio. What am I missing, retyping code becomes tedious!
View 2 Replies View RelatedWhen I am run this query the bonus is wrong. how to edit it and make it work ?
SELECT salesman_code,user_group,user_name,trx_acc_amt,trx_date,trx_no,sh_code,salesman_code4,salesman_code5,salesman_code6,
Sum(case when salesman_code4 is null then 0 else 1 end +
case when salesman_code5 is null then 0 else 1 end +
case when salesman_code6 is null then 0 else 1 end) as total_counter,
[code]....
When using the 2005 SQL Server Management Studio Express to create a new query, most of the necesary keyboard functions do not work (Return/Enter, Backspace, Delete, etc). The only way to edit in the New Query tab is to select the undesired text and re-type it. The 'Enter' key is non-functioning so all text appears on one line.
A second issue arises when trying to execute a query. I always get an error message stating that the designated database cannot be found in the System Databases group. I have right-clicked on the database (Northwnd) I am querying to create the new query and I have also used the 'USE' statement but I still get the same error message.
anyone know how to delete diagram in database that show relationship between tables?or anyone know how to open the existing relation diagram and existing query in database?wonder why my sqlexpress which come wit visual basic web developer 2005 only hav new query button and new diagram button~dont have edit exisiting diagram or edit existing query~==.=~~hope someone help me~
View 3 Replies View RelatedI am currently working on a website that deals with sales of products. For one of my pages for the website I need it to be able to change the current sales information for a specific product.
The top part of the following code selects the specific product however I cannot get the update query to work.
Code:
$describeQuery = "SELECT p.ID, p.NAME, dt.[Year], dt.[Month], dt.SalesVolume FROM Products p
join
(select ProductCode, sum(SalesVolume) as SalesVolume, [Year], [Month] from MonthlySales
group by ProductCode, [Year], [Month])dt
on dt.ProductCode = p.ID WHERE [NAME] = '$desiredProduct' AND [Year] = '$desiredYear' AND [Month] = '$desiredMonth'";
$editQuery = "UPDATE MonthlySales SET SalesVolume = '$NewSales' WHERE ID = '$desiredProduct' AND Year = '$desiredYear' AND Month = '$desiredMonth'";
$query = sqlsrv_query($link, $describeQuery);
Dear All
I am a beginner and looking for some help. I have a database with just one column (some names). That is the primary key aswell. Because I want the names to be unique.I used a grid view control to display the data and included the insert functionality in the grid view by using some code and the part of the code that does the insert is 1 public static void Insert(Categories category)
2 {
3 string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
4 using (IDbConnection cn = new SqlConnection(connectionString))
5 {
6 cn.Open();
7
8 IDbCommand cmd = new SqlCommand();
9
10 cmd.CommandText = "INSERT INTO Categories (CategoryName) VALUES " +
11 "('" + category.CategoryName + "')";
12
13 cmd.Connection = cn;
14 cmd.ExecuteNonQuery();
15 }
16 }
Question: when someone tried to enter a new name which already exists in the database it throws an error page which is what I want but is there a way to be able to display the user a message sayin g that he/she has entered a name that already exists and hence they need to try a different name? instead of the ugly error page? Thank you in advance,Prasad.
Hi,
I need to bind to dropdown list, the conference names from the last one to the first one.
I'm using following query, the problem is it returns the conference names the number of dates created, since it's one table with all data I can't change it now.
Is there a way to get the names one time for each name without repeating?
string Sql = "SELECT DISTINCT conference_name,creation_date FROM Conference ORDER BY creation_date DESC";
SqlDataAdapter da = new SqlDataAdapter(Sql, myConn);
dataSet = new DataSet();
da.Fill(dataSet);
ddlConfName.DataTextField = "conference_name";
ddlConfName.DataSource = dataSet.Tables[0];
ddlConfName.DataBind();
Forgive me if i'm being really stupid but... we have an accounts package (Exchequer) using sql. I would like to use the tables in sql to interrogate the db further. However my numeric fields e.g. Credit Limit are split into 2 fields. One is a smallint type and one is an integer. How do I combine these fields to get an accurate value?
Thanks
I keep getting the error "Invalid attempt to read when no data is present" when trying to query a table in my SQL DB. I have checked and rechecked and the table name and column names within it are spelled correctly. There are only three records in the database, they all have data in them, and the code in Country.Text precisely matches the data in the Country field in one of the records.
It's worth mentioning that when I use Visual Studio 2005's little direct SQL query tool to build and run the following SQL statement that it works properly:
SELECT Country, WordForStateFROM data_CountriesWHERE (Country = N'Jordan')
I am perplexed. Any ideas anybody...here is the code...?
Dim SelectSQL_Countries As String
SelectSQL_Countries = "SELECT * FROM data_Countries "
SelectSQL_Countries &= "WHERE Country='" & Country.Text & "'"Dim con_Countries As New SqlConnection(ConfigurationManager.ConnectionStrings("MySiteMainDB").ConnectionString)
Dim cmd_Countries As New SqlCommand(SelectSQL_Countries, con_Countries)Dim reader_Countries As SqlDataReader
Try
con_Countries.Open()
reader_Countries = cmd_Countries.ExecuteReader()StateID.Text = reader_Countries("WordForState")
reader_Countries.Close()Catch err As Exception
lblResults.Text = err.Message
Finally
con_Countries.Close()
End Try
I have a database and would like to retrieve specific data via queries. This database is also connected to an ASP .Net 2.0 application to be the front end. Ive created the query in the database. Would you recommend i use parameter names to retrieve the data via code or should i have the query within my code to retrieve the data?
Thanks
HI all, I got a tsql that needs to be simplified.
Select * from Table1 where condition1 and id not in
( Select id from table1 where condition2 and id in
( Select id from Table1 where condition1 )
)
basicly all records thats in condition1 but that doesnt have condition2 but limited to condition1. I'm probably maken this to complicated. but im tired and im losing time just on one stupid query. Thanks for the help.
Hey everyone,
I know this is a very simple problem however I've literally just begun learning about SQL and this is the first ever code i've created using the language.
The error is:
Msg 102, Level 15, State 1, Line 30
Incorrect syntax near ')'. Located on line containing "create table Trailer_Type"
create table Tralier
(load_types varchar(20),
Serial_no varchar(5),
constraint trailerkey primary key(Serial_no));
create table Tractor
(Reg_no varchar(8),
Descript varchar(20),
constraint tractorkey primary key(Reg_no));
create table Unit
(Unit_no varchar(4),
Total_length int(3),
Max_load int(2),
constraint unitkey primary key(Unit_no));
create table Maintenance
(Reg_no varchar(8),
Date_in date(8),
Date_out date(8),
Descript varchar(20),
Job_no varchar(6),
Mech_name varchar(20),
constraint maintenancekey primary key(Job_no));
create table Load_type
(load_type varchar(20),
constraint typekey primary key(load_type));
create table Trailer_Type
(constraint holds foreign key (serial_number) references
Trailer(serial_no),
Constraint holds foreign key (load_type)
references Load_type(load_type));
I'd be very greatful if someone could help me.
Thanks
David
Ok, so im pretty much finished writing my forum web page. However to display things like how many replies each thread has and who replied last, i need to perform a query in the code file. Im guessing its simple enough but i cant get the syntax for actually performing any query. I already know the sql syntax like select * from all that stuff but how do i get do something like:
Dim x as integer = sqlQuery("Select count(*) FROM ...")
Currently i have it all working by creating a table and making it invisible and just pulling data from the table but thats sloppy and pretty ineffecient if i databind a table for every single topic name.
When I want to user variable in the name of the database, I have an error. What's wrong with my code ?
DECLARE @BASE_SOURCE varchar (30),@BASE_DESTI varchar(30),@TEST varchar(30)
set @BASE_SOURCE='BASE1'
set @BASE_DESTI='BASE2'
select * from @BASE_SOURCE.dbo.FOURNISS
Msg*170, Niveau*15, État*1, Ligne*4
Ligne 4 : syntaxe incorrecte vers '.'.
I am writing a query now where I only want to get participants in the 112 service code only but this query I have is giving me other service codes as well when I have it set to 112. How can I fix this just so i can get this 112 service code only.
declare @bgDte smalldatetime
declare @enDte smalldatetime
declare @rgn char(2)
set @bgDte = '2013-01-01'
set @enDte = '2013-04-30'
set @rgn='05'
[Code] .....
Hi,
Is it possible to connect to a database and fetch data from it from within custom code inside a report?
Appreciate any help.
Regards,
Asim.
Hi,
I've created a stored procedure which inserts values into a table and upon successful execution the RC column gets returned along with the Identity value (I'm using SELECT SCOPE_IDENTITY()), but I don't want to RC column, I only want to get the Id number of the current row. I'm doing this using the Query Analyzer.
Is there a way to suppress the RC column? I have run the same query on different servers and the RC col doesn't show up. I only want the ID value to put into an ASP.NET page.
While testing different query methods from ASP.NET (Output parameter, Return_Value, etc) did I set a flag within SQL Server?
Sincerely,
Fred
i have a number of business programs. Each program is started anew at the beginning of each fiscal year. each program has a number of goals and customers subscribe to the goals.
i have to pull all this info out of the database.
i have a cursor that gets the first program, inside this program i have a cursor that gets the first period, and inside that i have a cursor that gets the info on each goal.
program cursor
{
period cursor
{
goal cursor
}
}
}
}
this takes ages ( hours and hours ) to run. is there any way i could have designed this using joins and simple selects to make it more efficient??
Hi experts,
I have a custom code that successfully query my database in SSRS 2005 report.
However, I am hard coding its connection string (in the custom code) and I want it to be able to use the same connection string as the ones that the report use. This information is available in my registry setting, but if it is possible, I want to avoid that, since I aim for a quite simple solution. If I am able to get the connection string details at the report level or from within the custom code, it would be great.
Anybody have a suggestion? Anything would be really appreciated.
Thanks
Hi All,First of all its very very urgent. Secondly I am not an expert. Now here is my problem.I want to loop through my html files and want to save the data in my table column. here is the Code which need correction.CODE:USE [AdventureWorks]DECLARE @cmd varchar(1000);DECLARE @FileName varchar(100);DECLARE @FilePath varchar(100);SET @FileName = 'C: est'+@FilePath+'html';SET @cmd = 'INSERT INTO myTable(FileName, FileType, Document)' + 'SELECT ' + @FilePath + ', FileType, * FROM OPENROWSET(BULK @FileName, SINGLE_CLOB) AS Document'EXEC (@cmd);Select * from myTableJust for practice I am using Adventureworks db. I am using SQL Express 2005.My html files are named something like this:AC0234.htmlDB9803.htmlCG4571.htmlI cannot change my file names.Thanks in advance.Regards;
View 1 Replies View RelatedHello,
I'm trying to select the maximim value for certain zip codes, for example my table looks like this:
ZIP Category Value
90210 S1 5
90210 S2 10
90210 S3 2
90210 S4 3
10221 S1 24
10221 S2 3
And I want the end result to be each unique zip code with the category having the maximum value, for example 90210 S2, 10221 S1, etc
If I have a simple query like:
select * from bigtable
where product_code in ('abc','def','hij','klm')
I know that each of those product_codes will return a large data set. What I'd like to do is to run the same query but constrain the result set to a max rownum of 200.
Is this possible?
I have some custom code in my report <Code> element that I use to query a database. I have hardcoded the connectionstring in this code but I would like to use the same datasource that the report uses. Is there some report property that allows me to access this datasource?
View 11 Replies View RelatedI want to check if a table is empty using query code?
How to do this?
Does anyone know if there is a limit on characters or lines of code that can be used to create your dataset?
I cut and pasted some sql code of a report in there and it's cutting off the last 100 lines or so.
hi
have a problem ...
I tried to test my statement in QA as it returns no dataset ,it gives me empty columns in return,I though it will return all the columns and some records of (lmeyer) as the current user ,who logged in on windows auth,but it return only the columns,is that means my query analyser cannot read the login1 ,column which has the username as (lmeyer)
select * from tstudents where (login1='@param1')
if not what could me wrong in my code ,because I get no response to the sql server
Public Function login(ByVal login1 as string) as dataset
Dim myconnection as new sqlconnection("server=G103-TT03;database=CampusLANDB;Trusted_Connection=yes")
dim mycommand as new sqlcommand ("Select * from tStudents Where login1 = @param1",myconnection)
mycommand.parameters.add(new SQLClient.SqlParameter("@param1",login1))
dim DS as new dataset()
try
myconnection.open
dim adpt as new sqldataadapter
adpt.selectcommand = mycommand
adpt.fill(DS,"tStudents")
catch ex as exception
throw new exception(ex.message)
return nothing
finally
myconnection.close
end try
try
mydatagrid.datasource=DS.Tables("tStudents")
mydatagrid.databind()
catch ex as exception
errorlabel.text=ex.message
end try
return DS
End Function
thankx in advance
pamela mkosana
Hi
Can anyone tell me the way to view stored produre code from query analyser. With sp_help I can only view input paramters
Cheers
Why is this SQL UPDATE query not updating when this code is used under button.click.
Dim ListingID As String = Request.QueryString("id").ToString
Dim sqlupdate As String = "UPDATE Listings SET PlaceName = '" & PlaceName.Text & "', Location = '" & Location.SelectedValue & "', PropertyType = '" & PropertyType.SelectedValue & "', Description = '" & Description.Text & "', Price = '" & Price.Text & "' WHERE ListingID ='" & ListingID & "'"
Dim con As New SqlConnection(ListingConnection)
Dim cmd As New SqlCommand(sqlupdate, con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
In the Reference_Master table I have Total count based on date for each RefCode. Can I get the total count of each RefCode by monthly wise for each year
Reference_Master
RefCode Date Count
100012/18/20074
100012/19/20078
100012/20/20074
100022/18/20073
100022/19/20072
100022/20/20076
[Code] ...
I need the result as below
Result
RefCodeMonth/YearTotalCount
10001FEB-200716
10002FEB-200711
10003Mar-20077
10004Mar-20073
10005Mar-20072
10001FEB-200816
10002FEB-200811
10003Mar-20087
10004Mar-20083
10005Mar-20082
Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:
Function Main()
on error resume next
dim cn, i, rs, sSQL
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
set rs = DTSGlobalVariables("SQLstring").value
for i = 1 to rs.RecordCount
sSQL = rs.Fields(0).value
cn.Execute sSQL, , 128 'adExecuteNoRecords option for faster execution
rs.MoveNext
Next
Main = DTSTaskExecResult_Success
End Function
This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)
Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:
public Sub Main()
...
Dts.TaskResult = Dts.Results.Success
End Class
I get the following error when I attempt to compile this:
Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.
I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.
- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).
Given this statement:
dim cn, i, rs, sSQL
I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!
Dear Friends,
I am having 2 Tables.
Table 1: AddressBook
Fields --> User Name, Address, CountryCode
Table 2: Country
Fields --> Country Code, Country Name
Step 1 : I have created a Cube with these two tables using SSAS.
Step 2 : I have created a report in SSRS showing Address list.
The Column in the report are User Name, Address, Country Name
But I have no idea, how to convert this Country Code to Country name.
I am generating the report using the Layout tab. ( Data | Layout | Preview ) Report1.rdl [Design]
Anyone help me to solve this issue. Because, in our project most of the transaction tables have Code and Code description in master table. I need to convert all code into corresponding description in all my reports.
Thanks in advance.
Regards
Ramakrishnan
Singapore
28 March 2007
Hello,
I'm using ASP.Net to update a table which include a lot of fields may be around 30 fields, I used stored procedure to update these fields. Unfortunatily I had to use a FormView to handle some TextBoxes and RadioButtonLists which are about 30 web controls.
I 've built and tested my stored procedure, and it worked successfully thru the SQL Builder.The problem I faced that I have to define the variable in the stored procedure and define it again the code behind againALTER PROCEDURE dbo.UpdateItems
(
@eName nvarchar, @ePRN nvarchar, @cID nvarchar, @eCC nvarchar,@sDate nvarchar,@eLOC nvarchar, @eTEL nvarchar, @ePhone nvarchar,
@eMobile nvarchar, @q1 bit, @inMDDmn nvarchar, @inMDDyr nvarchar, @inMDDRetIns nvarchar,
@outMDDmn nvarchar, @outMDDyr nvarchar, @outMDDRetIns nvarchar, @insNo nvarchar,@q2 bit, @qper2 nvarchar, @qplc2 nvarchar, @q3 bit, @qper3 nvarchar, @qplc3 nvarchar,
@q4 bit, @qper4 nvarchar, @pic1 nvarchar, @pic2 nvarchar, @pic3 nvarchar, @esigdt nvarchar, @CCHName nvarchar, @CCHTitle nvarchar, @CCHsigdt nvarchar, @username nvarchar,
@levent nvarchar, @eventdate nvarchar, @eventtime nvarchar
)
AS
UPDATE iTrnsSET eName = @eName, cID = @cID, eCC = @eCC, sDate = @sDate, eLOC = @eLOC, eTel = @eTEL, ePhone = @ePhone, eMobile = @eMobile,
q1 = @q1, inMDDmn = @inMDDmn, inMDDyr = @inMDDyr, inMDDRetIns = @inMDDRetIns, outMDDmn = @outMDDmn,
outMDDyr = @outMDDyr, outMDDRetIns = @outMDDRetIns, insNo = @insNo, q2 = @q2, qper2 = @qper2, qplc2 = @qplc2, q3 = @q3, qper3 = @qper3,
qplc3 = @qplc3, q4 = @q4, qper4 = @qper4, pic1 = @pic1, pic2 = @pic2, pic3 = @pic3, esigdt = @esigdt, CCHName = @CCHName,
CCHTitle = @CCHTitle, CCHsigdt = @CCHsigdt, username = @username, levent = @levent, eventdate = @eventdate, eventtime = @eventtime
WHERE (ePRN = @ePRN)
and the code behind which i have to write will be something like thiscmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@eName", ((TextBox)FormView1.FindControl("TextBox1")).Text);cmd.Parameters.AddWithValue("@ePRN", ((TextBox)FormView1.FindControl("TextBox2")).Text);
cmd.Parameters.AddWithValue("@cID", ((TextBox)FormView1.FindControl("TextBox3")).Text);cmd.Parameters.AddWithValue("@eCC", ((TextBox)FormView1.FindControl("TextBox4")).Text);
((TextBox)FormView1.FindControl("TextBox7")).Text = ((TextBox)FormView1.FindControl("TextBox7")).Text + ((TextBox)FormView1.FindControl("TextBox6")).Text + ((TextBox)FormView1.FindControl("TextBox5")).Text;cmd.Parameters.AddWithValue("@sDate", ((TextBox)FormView1.FindControl("TextBox7")).Text);
cmd.Parameters.AddWithValue("@eLOC", ((TextBox)FormView1.FindControl("TextBox8")).Text);cmd.Parameters.AddWithValue("@eTel", ((TextBox)FormView1.FindControl("TextBox9")).Text);
cmd.Parameters.AddWithValue("@ePhone", ((TextBox)FormView1.FindControl("TextBox10")).Text);
cmd.Parameters.AddWithValue("@eMobile", ((TextBox)FormView1.FindControl("TextBox11")).Text);
So is there any way to do it better than this way ??
Thank you