Database Name data\work.mdb
Type Access
the code i wrote is this
conClsf = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=data\\me.work" & server.mappath("data\\work.mdb") & ";")
i don't know what to soFor data source, provide the physical path to the data source only. Or if using DSN, you just use the "dsn=<name>" text, I believe. Is this ASP.NET 2.0 code?
Brian
Hi Everyone,
I am trying to load the data into the dropdownlist using stored procedure. But when I run the code, the dropdownlist is empty. The code is shown below. Please help! Thanks.
public DataSet getProvince()
{
DataSet ds = new DataSet();
SqlParameter myParam;
string conString;
SqlConnection myConnection;
conString = ConfigurationManager.AppSettings["connectionString"];
myConnection = new SqlConnection(conString);
SqlCommand cmd = new SqlCommand("stored_procedure_GetProvinces", myConnection);
SqlDataAdapter adpt = new SqlDataAdapter(cmd);
try
{
cmd.CommandType = CommandType.StoredProcedure;
myParam = cmd.Parameters.Add("@.province_key", SqlDbType.Int);
myParam.Direction = ParameterDirection.Output;
myParam = cmd.Parameters.Add("@.province_name", SqlDbType.NVarChar, 200);
myParam.Direction = ParameterDirection.Output;
adpt.Fill(ds, "Provinces");
myConnection.Close();
}
catch (SqlException ex)
{
Response.Write("Error: " + ex.Message);
}
return ds;
}
-----------
create procedure stored_procedure_GetProvinces( @.province_key int output, @.province_name nvarchar (200) output)AsSelect @.province_key=province_key, @.province_name=province_name From provinceGO
--------
<asp:DropDownList id="Dropdownlist_Province" DataValueField="province_key" DataTextField="province_name" DataSource='<%# getProvince() %>' Runat="server" >
</asp:DropDownList>
----------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[province]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[province]
GO
CREATE TABLE province (
province_key int IDENTITY PRIMARY KEY,
province_name nvarchar (200) NULL
)
GO
INSERT INTO province(province_name) VALUES
('- Select -');
GO
INSERT INTO province (province_name) VALUES
('Ontario');
GO
INSERT INTO province (province_name) VALUES
('Alberta');
GO
INSERT INTO province (province_name) VALUES
('British Columbia');
GO
INSERT INTO province (province_name) VALUES
('Manitoba');
GO
INSERT INTO province(province_name) VALUES
('New Brunswick');
GO
INSERT INTO province (province_name) VALUES
('Newfoundland');
GO
INSERT INTO province (province_name) VALUES
('Northwest Territories');
GO
INSERT INTO province (province_name) VALUES
('Nova Scotia');
GO
INSERT INTO province (province_name) VALUES
('Nunavut');
GO
INSERT INTO province (province_name) VALUES
('Prince Edward Island');
GO
INSERT INTO province (province_name) VALUES
('Quebec');
GO
INSERT INTO province (province_name) VALUES
('Saskatchewan');
GO
INSERT INTO province (province_name) VALUES
('Yukon Territory');
GO
What is the value of your parameter?|||
what you need is:
Select province_key, province_nameFrom province
also, you need to modify your .NET code and remove the parameter part. The way you are doing, your proc will return just one set of values from the table, since there is no WHERE condition and your variables can only have one value at a time, SQL server will just pick a row at random and assign the values to the variables. Assuming you want to see ALL the values since you are binding it to a drop down, you just need the SELECT statement with the columns (without any variables). Also, look into using an ExecuteReader which is faster than filling a dataadapter and a dataset.
|||
bullpit:
What is the value of your parameter?
Sorry, I don't know. Thanks.
|||
what you need is:
Select province_key, province_nameFrom provincealso, you need to modify your .NET code and remove the parameter part. The way you are doing, your proc will return just one set of values from the table, since there is no WHERE condition and your variables can only have one value at a time, SQL server will just pick a row at random and assign the values to the variables. Assuming you want to see ALL the values since you are binding it to a drop down, you just need the SELECT statement with the columns (without any variables). Also, look into using an ExecuteReader which is faster than filling a dataadapter and a dataset.
After I modify my code and my stored procedure. I still get the empty dropdownlist. Please help. Thanks.
|||did you change the proc to the way I suggested? Also please post your new .NET code..|||
did you change the proc to the way I suggested? Also please post your new .NET code..
My updated code is shown below. Thanks!
create procedure
stored_procedure_GetProvinces(
@.province_key
intoutput,@.province_name nvarchar (200)
output)
As
Select
province_key, province_nameFromprovinceGO
--------
publicDataSet getProvince(){
DataSet ds =newDataSet();SqlParameter myParam;string conString;SqlConnection myConnection;conString =
ConfigurationManager.AppSettings["connectionString"];myConnection =
newSqlConnection(conString);SqlCommand cmd =newSqlCommand("stored_procedure_GetProvinces", myConnection);SqlDataAdapter adpt =newSqlDataAdapter(cmd);try{
cmd.CommandType =
CommandType.StoredProcedure;
adpt.Fill(ds,
"Provinces");
myConnection.Close();
}
catch (SqlException ex){
Response.Write(
"Error: " + ex.Message);}
return ds;}
--------- the end -------
|||
what you need is:
Select province_key, province_nameFrom provincealso, you need to modify your .NET code and remove the parameter part. The way you are doing, your proc will return just one set of values from the table, since there is no WHERE condition and your variables can only have one value at a time, SQL server will just pick a row at random and assign the values to the variables. Assuming you want to see ALL the values since you are binding it to a drop down, you just need the SELECT statement with the columns (without any variables). Also, look into using an ExecuteReader which is faster than filling a dataadapter and a dataset.
I have just tried using ExecuteReader. And I still get empty dropdownlist. Please help. Thanks.
SqlCommand objCmd;SqlConnection objConn;
SqlDataReader dataReader;
String strSql;
objConn =newSqlConnection(ConfigurationManager.AppSettings.Get("ConnectionString"));
strSql ="EXECUTE stored_procedure_GetProvinces";
objCmd =newSqlCommand(strSql, objConn);
try
{
objConn.Open();
dataReader = objCmd.ExecuteReader(
CommandBehavior.CloseConnection);Dropdownlist_Provinces.DataSource = dataReader;
Dropdownlist_Provinces.DataTextField =
"province_name";Dropdownlist_Provinces.DataValueField =
"province_name";Dropdownlist_Provinces.DataBind();
}
catch{
}
finally{
objConn.Close();
objConn.Dispose();
}
|||Your stored proc would be like this:
create procedure dbo.stored_procedure_GetProvincesAsSelect province_key, province_nameFrom province GO
Then your .NET code could be someting like this: You might have to convert it to C#.
Protected objConAs New SqlConnection(ConfigurationSettings.AppSettings("conn"))pubic sub....Dim myCommandAs SqlCommandmyCommand =New SqlCommand()myCommand.Connection = objconmyCommand.CommandText ="stored_procedure_GetProvinces"myCommand.CommandType = CommandType.StoredProcedureIf objCon.State = 0Then objCon.Open()dpunits.DataSource = SqlCmd1.ExecuteReaderdpunits.DataTextField ="province_name" dpunits.DataValueField ="province_key"dpunits.DataBind()objCon.Close()end sub |||
Your stored proc would be like this:
create procedure dbo.stored_procedure_GetProvincesAsSelect province_key, province_nameFrom province GO
Then your .NET code could be someting like this: You might have to convert it to C#.
Protected objConAs New SqlConnection(ConfigurationSettings.AppSettings("conn"))pubic sub....Dim myCommandAs SqlCommandmyCommand =New SqlCommand()myCommand.Connection = objconmyCommand.CommandText ="stored_procedure_GetProvinces"myCommand.CommandType = CommandType.StoredProcedureIf objCon.State = 0Then objCon.Open()dpunits.DataSource = SqlCmd1.ExecuteReaderdpunits.DataTextField ="province_name" dpunits.DataValueField ="province_key"dpunits.DataBind()objCon.Close()end sub
What is SqlCmd1 in SqlCmd1.ExecuteReader ? Thanks.
|||
Your stored proc would be like this:
create procedure dbo.stored_procedure_GetProvincesAsSelect province_key, province_nameFrom province GO
Then your .NET code could be someting like this: You might have to convert it to C#.
Protected objConAs New SqlConnection(ConfigurationSettings.AppSettings("conn"))pubic sub....Dim myCommandAs SqlCommandmyCommand =New SqlCommand()myCommand.Connection = objconmyCommand.CommandText ="stored_procedure_GetProvinces"myCommand.CommandType = CommandType.StoredProcedureIf objCon.State = 0Then objCon.Open()dpunits.DataSource = SqlCmd1.ExecuteReaderdpunits.DataTextField ="province_name" dpunits.DataValueField ="province_key"dpunits.DataBind()objCon.Close()end sub
What is SqlCmd1 in SqlCmd1.ExecuteReader ? Thanks.
I replace SqlCmd1 with myCommand. And it is working now. Thank you very much for your help! Bye!
May
For a controlParameter in the ASP code, how do I retreive the selectedValue of the drop down list?
Would this work?
<asp:controlParameterName="InvoiceNumber"Type="String"ControlID="ddAdSize.Value"/>
Hi
I 'm a beginner at this, but I solved what I think is the same problem as you have, by looking athttp://authors.aspalliance.com/aspxtreme/.
There you will find solutions to many problems.
This is part of my html code:
<Asp:DropDownList Id="ddlCarrier" TabIndex="17" onSelectedIndexChanged="ShowCarrier" Width="150" autopostback runat="server">
</Asp:DropDownList>
And part of the ASP.NET code:
Dim Selection1 As String = _
"SELECT Carrier, Used, Manufacturer, Material, ManufacturedDate " & _
"FROM Carriers " & _
"WHERE Carrier = '" & ddlCarrier.SelectedItem.Text & "'"
Hope this will help you
Rolf Dahlstr?m
Hi,
I’m having problems dropping a user this is my code:
Public Function DropUser() As Boolean
Dim conn As New ServerConnection("STATION01\SQLEXPRESS", wContainer.Username, wContainer.Password)
Dim myServer As New Server(conn)
Dim myDatabase As Database = myServer.Databases("VideoDB")
If myServer.Logins.Contains(“username”) Then
Dim db_user As New User(myDatabase, “username”)
db_user.Login = “username”
db_user.Drop()
Dim db_login As New Login(myServer, “username”)
db_login.Drop()
Return True
Else
Return False
End If
End Function
OK, whats the error message ? For the case that the user has a schema assigned you will first have to drop the schema or put in another owner for the schema.HTH, Jens K. Suessmeyer.
http:://www.sqlserver2005.de|||
If you want to ensure you've got the user/login out of all databases try this code (after you instantiate myServer):
Dim dbColl As DatabaseCollection
Dim dbCurrent As Database
Dim schColl As SchemaCollection
Dim schClean As Schema
Dim usrColl As UserCollection
Dim usrClean As User
Dim logColl As LoginCollection
Dim logClean As New Login
dbColl = myServer.Databases
For Each dbCurrent In dbColl
If Not dbCurrent.IsDatabaseSnapshot Then
schColl = dbCurrent.Schemas
schClean = schColl.Item("username")
If Not (schClean Is Nothing) Then
schClean.Drop()
End If
usrColl = dbCurrent.Users
usrClean = usrColl.Item("username")
If Not (usrClean Is Nothing) Then
usrClean.Drop()
End If
End If
Next
logColl = srvMgmtServer.Logins
logClean = logColl.Item("username")
If Not (logClean Is Nothing) Then
logClean.Drop()
End If
This will remove all the schema and user from every database used by this login, then drop the login.
|||Hi,
The error is:
Drop failed for User ‘username’
|||Thanks a lot....I have some code to delete a database. This allows a user to enum the databases and select one to delete. It the determines the filename, so it can remove the physical files, drops the database and deletes the files. However, it frequently fails saying the database is currently in use....
I noticed the same bahaviour when deleting a database via Management Studio, however checking the box to close active connections does the trick and the db is successfully deleted.
Question: how do I close the active connection in smo ?
Thanks, Nick
Hi,
http://www.sqlserver2005.de/sqlserver2005/MyBlog/tabid/56/EntryID/9/Default.aspx
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thanks Jens this is really helpfulI have some code to delete a database. This allows a user to enum the databases and select one to delete. It the determines the filename, so it can remove the physical files, drops the database and deletes the files. However, it frequently fails saying the database is currently in use....
I noticed the same bahaviour when deleting a database via Management Studio, however checking the box to close active connections does the trick and the db is successfully deleted.
Question: how do I close the active connection in smo ?
Thanks, Nick
Hi,
http://www.sqlserver2005.de/sqlserver2005/MyBlog/tabid/56/EntryID/9/Default.aspx
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thanks Jens this is really helpfulI have some code to delete a database. This allows a user to enum the databases and select one to delete. It the determines the filename, so it can remove the physical files, drops the database and deletes the files. However, it frequently fails saying the database is currently in use....
I noticed the same bahaviour when deleting a database via Management Studio, however checking the box to close active connections does the trick and the db is successfully deleted.
Question: how do I close the active connection in smo ?
Thanks, Nick
Hi,
http://www.sqlserver2005.de/sqlserver2005/MyBlog/tabid/56/EntryID/9/Default.aspx
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thanks Jens this is really helpful