|
|
Oh help...
I'm experienced in programming but new to .NET, and am having a problem updating bound data that feeds the drop-down items in a combo box. I'm using a data adapter to feed an INSERT statement to a table using the connection directly (I don't use a DataTable). I use ExecuteNonQuery to update the table and it works fine. However, as the bound control works from another data adapter, the new item doesn't show in the drop-down. I created a small project and found that if I called Adapter.Fill() after executing the insert, it would show up. However, that adapter was hooked up to a dataset. Do I have to completely rejigger my code, or is there some way to force a refresh of the data? I tried a CurrencyManager but got nowhere.
Thanks for any help.
Jim Stanley
Media Services, Inc.
|
|
|
|
|
hi
this doesn't work
what should i do????
public void GetData( )
{
SqlConnection sqlConnection;
SqlCommand sqlCommand;
sqlConnection = new SqlConnection ( ConfigurationSettings.AppSettings["DataBaseConnection"] );
sqlConnection.Open();
sqlCommand = new SqlCommand ( "storedProcedure_2" , sqlConnection );
sqlCommand.CommandType = CommandType.StoredProcedure;
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
while ( sqlDataReader.Read() )
{
do somethings...
SqlCommand sqlCommand_2 ;
sqlCommand_2 = new SqlCommand ( "storedProcedure_2" , sqlConnection );
sqlCommand_2.CommandType = CommandType.StoredProcedure;
SqlDataReader sqlDataReader_2 = sqlCommand_2.ExecuteReader();
while ( sqlDataReader_2.Read() )
{
do somethings...
}
sqlDataReader_2.Close();
}
sqlDataReader.Close();
sqlConnection.Close();
}
|
|
|
|
|
hi
i have a problem.
this code doesn't work why? and what should i do plz?????
public void GetData( )
{
SqlConnection sqlConnection;
SqlCommand sqlCommand;
sqlConnection = new SqlConnection ( ConfigurationSettings.AppSettings["DataBaseConnection"] );
sqlConnection.Open();
sqlCommand = new SqlCommand ( "storedProcedure_2" , sqlConnection );
sqlCommand.CommandType = CommandType.StoredProcedure;
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
while ( sqlDataReader.Read() )
{
do somethings...
SqlCommand sqlCommand_2 ;
sqlCommand_2 = new SqlCommand ( "storedProcedure_2" , sqlConnection );
sqlCommand_2.CommandType = CommandType.StoredProcedure;
SqlDataReader sqlDataReader_2 = sqlCommand_2.ExecuteReader();
while ( sqlDataReader_2.Read() )
{
do somethings...
}
sqlDataReader_2.Close();
}
sqlDataReader.Close();
sqlConnection.Close();
}
|
|
|
|
|
|
Hi
Its getting pathetic and I dunno why !!
I am trying to read/insert/update an excel sheet using ADO .NET. I am using
following connection string to connect to the data source.
Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\inetpub\wwwroot\App\files\file.xls;Extended Properties='Excel
8.0;HDR=YES;MAXSCANROWS =1'
The development machine contains Microsoft Office and everything upto date
and the application is working fine.
On server I am getting following exception since last One Month!!
---------------------------------------------------------------------
Could not find installable ISAM.Microsoft JET Database Engine at
System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) at
System.Data.OleDb.OleDbConnection.InitializeProvider() at
System.Data.OleDb.OleDbConnection.Open() at
MyPledge.ViewClientPaymentData.LoadData() in
C:\Inetpub\wwwroot\MyApp\ViewClient.aspx.vb:line 58
---------------------------------------------------------------------
I have asked the server admin to do following ...
- Give Rights to Read/Write to ASP NET process on directories containing
excel files
- Update Jet Drivers from following URI,
http://www.microsoft.com/downloads/details.aspx?FamilyID=1E268B39-533E-48B0-B8D7-1781BEFDE1F8&displaylang=en
But I am still getting this message...
CAN anyone, PLEASE PLEASE help me??? I am one of the big .NET lovers but
this thing is REALLY REALLY REALLY making me start to hate it .. :S :S :S...
eff_kay
-------------------
Therez No Place like ... 127.0.0.1
|
|
|
|
|
First and foremost, make sure the version of ADO is at least as high as your development box. The Jet drivers may not help if the ADO layer is not in synch.
find it here.[^]
second, I think the Extended Properties section should be in double quotes rather than single:
Extended Properties="Excel 8.0;HDR=YES;MAXSCANROWS =1";
You will need to escape the quotes or use an @ style literal string.
HDR=YES is the default, so you could ommit it. MAXSCANROWS =1 only considers the 1st row when determining column data types. I think the default is 8, so this part could also be ommitted, performance hit is negligible, and you appear to beleive the data types will be consistent. I have seen both of these be problematic with different versions of ADO.
The ISAM error usually seems to mean that the current version of ADO/jet did not like the connection string, so experimenting with the Extended properties to try the minimal specification (Just "EXCEL 8.0") should be worthwhile.
Why would anyone waste time arguing with an accountant about anything? Their sole function is to record what happenned, and any higher aspirations are mere delusions of grandeur. On the ladder of productive contributions they are the little rubber pads at the bottom that keep the thing from sliding out from under you. - Roger Wright
|
|
|
|
|
Thank u very much for the help.
it helped and now things are running good.
eff_kay
-------------------
Therez No Place like ... 127.0.0.1
|
|
|
|
|
Good day SQL experts and seniors,
I have a SQL job which is scheduled to run everytime when SQL agent starts.
The job will invoke a stored procedure. This stored procedure will run
continuously using "While Loop" by referring to a flag stored inside a SQL
table. If the flag is set to "OFF", the stored procedure will stop.
Problem arises when CPU usage is high whereby the job will be terminated
unexpectedly with an error message "Executed as user: SERVER_NT_P1\SysAdmin.
ConnectionRead (recv()). [SQLSTATE 01000] (Message 10054) General network
error. Check your network documentation. [SQLSTATE 08S01] (Error 11). The
step failed". The MSSQL is already installed with service pack 3 and in
normal condition the job can continue executing for days.
What is the root cause of this issue? Inside my code, there is no any SQL
statement which is called across database server (only got statement to grab
record from other database resided in the same server). Anything I can do to
avoid this error from occuring?
Below are the stored procedure called by my SQL job:
------------------------------------------------------------------------------
CREATE Procedure Proc_RE_On_Off_Process<br />
As<br />
<br />
declare @execution_status varchar(5)<br />
declare @delay_time varchar(10)<br />
<br />
select @execution_status = RE_On_Off_Flag from SPC_RE_On_Off_Process<br />
<br />
if @execution_status <> 'ON'<br />
begin<br />
insert into SPC_RE_On_Off_Log <br />
values (getdate(), 'RE process is not started')<br />
end<br />
else<br />
begin<br />
insert into SPC_RE_On_Off_Log<br />
values (getdate(), 'RE process is started')<br />
end<br />
<br />
while @execution_status = 'ON'<br />
begin<br />
exec Proc_Run_RE_Jobs<br />
<br />
if @@Error <> 0<br />
begin<br />
insert into SPC_RE_On_Off_Log<br />
values (getdate(), 'Process stopped due to error: ' + cast(@@Error as <br />
varchar(20)))<br />
break <br />
end<br />
else<br />
begin<br />
select @execution_status = RE_On_Off_Flag, @delay_time = RE_Delay_Time <br />
from SPC_RE_On_Off_Process<br />
if @execution_status <> 'ON'<br />
begin<br />
insert into SPC_RE_On_Off_Log <br />
values (getdate(), 'RE process is terminated')<br />
break<br />
end<br />
else<br />
begin<br />
waitfor delay @delay_time<br />
end<br />
end <br />
end
------------------------------------------------------------------------------
Any idea would be greatly appreciated. Thanks 
SayHigh Soon
|
|
|
|
|
In the SQL Server Database Wizard I put local and trusted connection.After I press Next I got the error
SQL Server Error:53
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Connect())
Connection Failed:
SQLState:'08001'
SQL Server:17
about connection string I dont know for sure where to control about it because Im a new user.Sorry for the disconvenience and please help me with some further instructions
|
|
|
|
|
Why don't you use the SQLConnection? , the best suited for data from an SQL Server.
|
|
|
|
|
Im a new user of databases and making a project in Access I have this problem.In a table named Suppliers I have more than once repeated the name of the city and the name of the country.I want to normalize this table creating some new tables.How will be connected with the general table?Have I to delete from the old table the rows City,Country?What about keys?Please give me some advices
|
|
|
|
|
Before you begin, spend some time learning about Access Database Design[^] from the source. MSDN has some excellent articles on design, normalization, and general development information for these projects. Starting out blind is just going to make you very frustrated in a hurry. Read, understand, then play.
"If it's Snowbird season, why can't we shoot them?" - Overheard in a bar in Bullhead City
|
|
|
|
|
How can I turn my computer in a local server so I can work freely with my Access database cause I have to turn my Access database in a SQL Server 2000 project but when I try to connect my database with SQL I get a message of error in connecting.Maybe I did not install correct SQL Server.I repeat I have not possibility to work with an external server so I need help on constructing a personal server in my computer so I can work with it freely.Please help me with some options.Please...
|
|
|
|
|
You could help us help you by telling us what the error was - there are some very intelligent people on this forum who could help, however AFAIK none are telepathic.
Also, you might want to tell us what settings you used when you set up the SQL Server and what your connection string is to access it. These are also possible areas which will give details as to what the problem is.
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
|
|
|
|
|
Hi, I don't know much SQL & I need some help with my SQL statement.
Hopefully someone will be able to help me.
I'm triny to retrieve data using a textbox and dropdownlist.
How should I write a SQL statement for this.
Thanx in advance.
|
|
|
|
|
Hi there. You may get a better response if you post your SQL statement. If you don't know SQL, I highly recommend starting with the W3Schools SQL tutorial[^]. Once you have a good understanding of how to construct a SQL SELECT statement (the tutorial will give you some practice at it) then take a look at the ASP.NET QuickStart tutorials[^]. In particular, the QuickStart tutorial for Server-Side Data Access[^] will connect your knowledge of constructing SQL statements to the .NET code necessary to perform such operations in an ASP.NET environment.
|
|
|
|
|
Hi, Mike. Thanx for your quick response.
I will definitely check out the tutorials you mentioned.
This is what I have so far.
strSQLQuerry = "Select * From Table1 Where Textbox1.Text.ToString() AND
DropDownList1.SelectedItem.Value"
my question is that should I set the value of the textbox and dropdownlist to what?
Thanx for ur help
|
|
|
|
|
Hi there. If you are creating a literal SQL statement in code, one thing to remember is that you will be concatenating values into the string, such that the SQL is valid when sent to the database. For example, if your Textbox1 value is "Hello" and your DropDownList1 selected value is "World", you would want your strSQLQuerry variable to contain the literal value:
Select * from Table1 Where Field1 = 'Hello' AND Field2 = 'World' (assuming Field1 and Field2 are actual fields in Table1). To get such a literal string, you would be concatenating the retrieved values from Textbox1 and DropDownList1 (using the concatenation operator + for C#, or & for VB). For example, in VB this could look something like this:
strSQLQuerry = "Select * From Table1 Where Field1='" & Textbox1.Text _
& "' AND Field2='" & DropDownList1.SelectedItem.Value & "'"Or, even better, you could use the String.Format method like this:
strFormatString = "Select * From Table1 Where Field1='{0}' AND Field2='{1}'"
strSQLQuerry = String.Format(strFormatString, Textbox1.Text, DropDownList1.SelectedItem.Value)I hope these examples help with the conceptualization of building a literal SQL string using form input values. You should be aware though that building these kinds of literal SQL statements (where criteria from a submitted form is embedded directly in the SQL string sent to the database server) leaves you vulnerable to SQL injection attacks. This is where a malevolent user would enter something in an input box on the form specifically designed to screw with your data. One could, for example, enter something like "'; DELETE FROM Table1; --" in your Textbox1. The literal SQL string your code constructs then would look something like this:
Select * From Table1 Where Field1=''; DELETE FROM Table1; --' AND Field2='' Depending on the database permissions available to the user under which ASP.NET functions, such a statement could mean all your data in Table1 gets deleted.
A better way overall is to use Parameter objects with your SQL statement. The SQL statement you construct in code would use literal parameter placeholders for criteria rather than literal values. These placeholders are database specific. For example, in SQL Server, these are names beginning with the @ symbol; if using the ODBC client, you would use a question mark ? for parameter placeholders. So depending on your database, you might use a string like this:
strSQLQuerry = "Select * From Table1 Where Field1 = @param1 AND Field2 = @param2" or this
strSQLQuerry = "Select * From Table1 Where Field1 = ? AND Field2 = ?" You would create a Command object appropriate for your database, set its CommandText property to strSQLQuerry, and add Parameter objects with values to its Parameters collection. For example, assuming you use VB and SQL Server, you may have the following:
'-- setup the select command text
strSQLQuerry = "Select * From Table1 Where Field1 = @param1 AND Field2 = @param2"
'--create the SqlCommand object (a connection would need to be set up too)
dim cmd as SqlCommand = new SqlCommand(strSQLQuerry)
'--add parameter values retrieved from the submitted form
cmd.Parameters.Add("@param1", Textbox1.Text)
cmd.Parameters.Add("@param2", DropDownBox1.SelectedItem.Value)
'--execute the query and fill a dataset with the results
dim da as SqlDataAdapter = new SqlDataAdapter(cmd)
dim ds as DataSet = new DataSet()
da.Fill(ds)I know this seems like a lot, but parameterized queries are really the way to go. The links I gave you before should help. In the "Server-Side Data Access" quickstart is a section called "Performing a Parameterized Select" that explains this more.
Best wishes and happy new year.
|
|
|
|
|
Thanks Mike for your help, you have been a great help.
This is my first experience (posting question to online forums) & it has very good.
I agree with you about using parameter value instead of using as a string literal into the sql statement.
I'm using SQL Server & VB.NET for this project.
Okay now when I used the parameter code I got a different error which is following:
Code ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub btnSrch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSrch.Click
Dim objConnection As SqlConnection
Dim objCommand As SqlCommand
Dim objAdapter As SqlDataAdapter
Dim objDataReader As SqlDataReader
Dim objDataSet As DataSet
Dim strSearch As String
Dim StrSQLQuery As String
'Get Search
strSearch = txtSrch.Text
'If there's nothing to search for then don't search
' o/w build our SQL Query execute it.
If Len(Trim(strSearch)) > 0 Then
'Set up our connection.
objConnection = New SqlConnection("server=(local);Integrated Security=SSPI;database=Equipment Log")
'-- setup the select command
StrSQLQuery = "Select * From Equip Where txtSrch = @param1 AND ddlSrchby = @param2"
'--create the SqlCommand object (a connection would need to be set up too)
Dim cmd As SqlCommand = New SqlCommand(StrSQLQuery)
'--add parameter values retrieved from the submitted form
cmd.Parameters.Add("@param1", txtSrch.Text)
cmd.Parameters.Add("@param2", ddlSrchby.SelectedItem.Value)
'--execute the query and fill a dataset with the results
Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim ds As DataSet = New DataSet
da.Fill(ds)
'Create new command object passing it our SQL Query
'and telling it which connection to use.
objCommand = New SqlCommand(strSearch, objConnection)
objConnection.Open()
Else
txtSrch.Text = "Enter Serach Here"
End If
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Here is the error I got
Fill: SelectCommand.Connection property has not been initialized.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidOperationException: Fill: SelectCommand.Connection property has not been initialized.
Source Error:
Line 175: Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
Line 176: Dim ds As DataSet = New DataSet
Line 177: da.Fill(ds)
Thanks and have a happy new year to you too.
|
|
|
|
|
You need to tell your command object about your connection, otherwise it doesn't know where (i.e. which SQL Server and database) to run your command. The documentation for the SqlCommand[^] object states that there is another constructor that takes the SqlConnection object as well as the command text.
So, this line:
'--create the SqlCommand object (a connection would need to be set up too)
Dim cmd As SqlCommand = New SqlCommand(StrSQLQuery) should be changed to
'--create the SqlCommand object (a connection would need to be set up too)
Dim cmd As SqlCommand = New SqlCommand(StrSQLQuery, objConnection) Also, you have a line that reads Dim objDataReader As SqlDataReader but since you are using a data adapter to get the data out of the database you do not need this and you may remove it.
I hope this helps.
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
|
|
|
|
|
Thanx Colin for your suggestion.
I've change the syntax. After that I'm not getting any syntax errors, however when run it I don't see any output either.
Any idea what is wrong with our logic or maybe any property's setting need any changing.
Thanx for your help.
|
|
|
|
|
Anonymous wrote:
After that I'm not getting any syntax errors
The error you posted before was a runtime error. The program would not even be able to start running if there were syntax errors. A syntax error is something that stops the compiler from understanding what you wrote. Think of syntax as being the grammar of the language.
Anonymous wrote:
however when run it I don't see any output either
Perhaps you need the Text from the drop down list and not the Value. (The Text property is the one that is shown to the user, the Value is used internally)
Does this help?
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
|
|
|
|
|
|
It depends on what you are trying to do. Presumably you need to generate a SQL select statement and execute that against the database.
Are you using the textbox and dropdown to specify the data you want to retrieve from the database, i.e. do you want the contents of these to be used within SQL clauses?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
