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 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
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 RelatedHi all,
In the VB 2005 Express, I can get the SqlConnection and ConnectionString of a Database "shcDB" in the Object Explorer of SQL Server Management Studio Express (SSMSE) by the following set of code:
///--CallshcSpAdoNetVB2005.vb--////
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub InsertNewFriend()
Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=shcDB;Integrated Security=SSPI;"
Dim connection As SqlConnection = New SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
.......................................
etc.
///////////////////////////////////////////////////////
If the Database "shcDB" and the Stored Procedure "sp_inertNewRecord" are in the Database Explorer of VB 2005 Express, I plan to use "Data Source=local" in the following code statements to get the SqlConnection and ConnectionString:
.........................
........................
Dim connectionString As String = "Data Source=local;Initial Catalog=shcDB;Integrated Security=SSPI;"
Dim connection As SqlConnection = New SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
........................
etc.
Is the "Data Source=local" statement right for this case? If not, what is the right code statement for my case?
Please help and advise.
Thanks,
Scott Chang
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
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
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
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 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 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, I want to create a database setup on a server. I've scripted my database from sql server express. I've tested the code in the query window and it worked. When i pasted the same code in a sqlcommand command text....the debuger threw an sql exception....saying the sintax is wrong near keywords GO, USE, some forgein keys, and so on. Here is a chunk of the script.USE [master]GO/****** Object: Database [estate_management] Script Date: 09/13/2006 09:19:32 ******/CREATE DATABASE [estate_management] ON PRIMARY ( NAME = N'estate_management', FILENAME = N'D:MSSQLestate_management.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'estate_management_log', FILENAME = N'D:MSSQLestate_management_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_ASSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOIF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[admin]') AND type in (N'U'))BEGINCREATE TABLE [dbo].[admin]( [admin_id] [int] IDENTITY(1,1) NOT NULL, [log_in_id] [varchar](20) NOT NULL, [password] [varchar](50) NOT NULL, CONSTRAINT [PK__admin__07020F21] PRIMARY KEY CLUSTERED ( [admin_id] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 1) ON [PRIMARY], CONSTRAINT [IX_admin] UNIQUE NONCLUSTERED ( [admin_id] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]END Thanks for your time.
View 1 Replies View RelatedVWD 2005 Express. I need to retrieve a value from a SQL database from the code behind a page and assign it to a variable. In Microsoft Access I can do this using the DLookup function. What I need to do is get the data that results from the following query into a variable:
SELECT [SystemUserId] FROM [SystemUser] WHERE ([Username] = @Username)
The name of the data source is SqlDataSource2
Also, in Access I can create a recordset from a query and then process through the recordset. Can that be done in VB code in VWD 2005 Express?
What method would you suggest is the best way to deal with a website?
Say for example, writing SQL. Am I better using Stored Procedures and adding methods for accessing there or just writing the SQL inline. What do you think about this? Is it totally overkill to use stored procedures in a small website?
Hi Friends, Please any one help me in writing the code in thisI have 3 fileds1)empId-->textbox2)Roles-->4 radiobuttons(MN,PL,TL,CL)3)Responsibilities--->3 check boxes(profile,register,change password)I have 3 tablestable1:RoleMasterTable:roleID roleName1 MN2 PL3 TL4 CLtable2:ResponsibilityMasterTableresId resName1 profile2 register3 changepasswordtables3MasterTable:empId roleId resIdthe form conatains the empId,Roles(radiobuttons),Responsibilies(chechboxes)for example I have to enter empId=1select one radiobutton that is "PL"and I can select check boxes profile,register and manyafter submitting the button("submit) these details has to store in the database table i.e master tableI have to show the final o/p like this in the tableMasterTableempId roleId resId1 2 1,2Please any one help in this I am beginner in database c# programmingPlease its very helpful to me............Thanks & RegardsGeeta
View 7 Replies View Relatedi have a piece of code in an adp that is currently behind a form but i could put it in a module and make it public if i need to. how can I use DTS to run this piece of code? which connection and task could i use. can this even be done? i have little experiance with DTS.
thanks.
justin
Where am I going wrong? I have no idea where to start to look for any incorrect code because it all looks ok! Please help!!!
I am entering a record into 5 textboxes - I can see the new record when I open the datagrid view, but when I close the programme and reopen it the record disappears, this code looks ok to me so where is it wrong? Obviously the record is not being written to the database so that when I reopen the programme there is nothing to fill the dataset..........any ideas?
Your help is appreciated!!!
Private Sub BtnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnSave.Click
Mainform.SqlConnection1.Open()
Dim TheNewRow As DataRow
TheNewRow = Mainform.Dataset1.Tables("ChartTable").NewRow
TheNewRow("Chart Number") = TB1CN.Text
TheNewRow("Chart Title") = TB2CT.Text
TheNewRow("Edition Date") = TB3ED.Text
TheNewRow("Print Date") = TB4PD.Text
TheNewRow("Edition Number") = TB5EN.Text
Mainform.Dataset1.Tables("ChartTable").Rows.Add(TheNewRow)
Mainform.SqlDataAdapter1.Update(Mainform.Dataset1)
Mainform.SqlConnection1.Close()
Me.Visible = False
End Sub
Regards,
Steve
I have the need to detach a database from code, at the moment I am using 'SSEUTIL -d' for this in my code and it works OK but now I want to change to using an SQLCommand. Here is my code so far:
Code Snippet
Dim conDatabase As SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=MyMDFPath"; Integrated Security=True;Connect Timeout=60;User Instance=True")
Dim comDetach As New SqlCommand("EXEC master.dbo.sp_detach_db '" + MyMDFPath + "'", conDatabase)
conDatabase.Open()
comDetach.ExecuteNonQuery()
conDatabase.Close()
However, when I execute this I always get the following exception:
Code Snippet
Cannot detach the database 'MyMDFPath' because it is currently in use.
It seems obvious to me that the problem is that to do the detach I create a connection - how do i get around this?
Thanks!
Hello.
I think its silly question but i expect some solution .
Is there any way to create sql sever database by using VB.NET code ? I have to create database,tables,sps and also transfer the data from one database to newly created database.
Please, if somebody have some solution then guide me in detail.
Thanks in adv.
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()