|
hello
i am new in sql and i have create one small application with one table which has six columns
i want to check recores from sql database but it always shows nothing this is the code bellow in load event, all i want to do is if there is no data in sql database then it should shows "Empty Database" in Combo box
but it's not working
Private Sub LoadCboItems()
dcIMS = GetDBConnection()
cboDisp.Items.Clear()
If Me.BindingContext(dsIMS, "IMS").Count = 0 Then :confused:
cboDisp.Text = "Empty Database"
Exit Sub
Else
Dim MyReader As SqlDataReader = Nothing
Try
Dim MyData As New SqlCommand("SELECT * FROM IMS", dcIMS)
MyReader = MyData.ExecuteReader
Do While MyReader.Read
If rdoID.Checked Then
cboDisp.Items.Add(MyReader.Item("InkNumber").ToString)
cboDisp.SelectedIndex = 0
ElseIf rdoName.Checked Then
cboDisp.Items.Add(MyReader.Item("InkName").ToString)
cboDisp.SelectedIndex = 0
End If
Loop
Catch ex As Exception
MsgBox(ex.Message)
Finally
MyReader.Close()
dcIMS.Close()
dcIMS.Dispose()
End Try
End If
End Sub
|
|
|
|
|
bapu2889 wrote: dcIMS = GetDBConnection()
cboDisp.Items.Clear()
I hate binding questions, what is dcIMS declared as?
You are testing the count of the binding context, presumably tied to the form (me), is this what you want to test.
I use datatables and have no problem with the counting of rows nor binding to a combo box. Did i mention I hate binding widgets!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
dcIMS is sql data connection
cboDisp is combobox
and what i want to do is when i start application so first it checks if there is any data in data base is it's there then it's shows the list of inks but if it's empty then i want to display "Empty Database" in combo box
thanks for your rep.
have a nice day
|
|
|
|
|
And your code work properly or not? If no do you have any error message or where is problem with code?
Try to bind cboDisp with values from database then check items on cboDisp,if ItemCount of combobox is 0 then add string "Empty Database" otherwise add list of links.
Hope this will help you.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Wel, given that by line 3 all you have done is establish a database connection, what can you epect your If... statement to return?
Try something more along these lines
Dim MyData As New SqlCommand("SELECT * FROM IMS", dcIMS
MyReader = MyData.ExecuteReader
If MyReader.HasRows Then
Do While MyReader.Read
' ...whatever
Loop
Else
cboDisp.Text = "Empty Database"
End If
MyReader.Close()
dcIMS.Close()
As an aside, you should not, as a rule, leave Closing your database connection to the Finally clause of a Try block - what happens if an error occurs outside that? Instead, open your Db connections at the last moment possible, and close them immediately after, and test for an open connection in Catch part - eg:
Dim dbConn As New SqlConnection
...
Try
..some code
dbConn.Open
.. data amnipulation
dbConn.Close
..more code
Catch
If dbConn.State = ConnectionState.Open Then dbConn.Close()
...
End Try
...
|
|
|
|
|
Hi Phil
first of all thank you very much for your mail and rep.
yes it works fine and now it's shows "empty database" if database is empty else it shows inkID so great
i didn't know about the has rows but now i learn
thanks again 
have a nice day ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
hello,
this is a sql statement that i m trying to use in a Window application in .NET.
query:
<pre>string selCmd = "SELECT eInfo.EmpID, eInfo.Name, lInfo.LeaveEnteredOn, lInfo.LeaveFrom, lInfo.LeaveTo, lInfo.LeaveType" +
"FROM lmsEmpInfo AS eInfo INNER JOIN lmsLeaveInfo AS lInfo ON lInfo.EmpID = eInfo.EmpID" +
"WHERE (lInfo.LeaveGranted = 0)";</pre>
the problem is this query works fine when i m running it on the SQL server management studio, but gives an error of "Incorrect syntax near AS", when i try to execute it with the following code:
<code>
SqlDataAdapter myAd = new SqlDataAdapter();
SqlConnection myCon = new SqlConnection();
DataSet myDs = new DataSet();
myCon.ConnectionString = Properties.Settings.Default.LeaveMgmtConnectionString.ToString();
myAd.SelectCommand = new SqlCommand(selCmd, myCon);
myAd.Fill(myDs, "Recent_Leaves");</code>
Please help me to rectify this mistake......... I would also like to have suggestions on
How can i change the entire row collection of a DataGridView with the SelectedIndexChange event of a combobox and that too according to query that is being fired at the same event
any help or assistance is greatly appreciated!!
regards
|
|
|
|
|
Sujay chakraborty wrote: string selCmd = "SELECT eInfo.EmpID, eInfo.Name, lInfo.LeaveEnteredOn, lInfo.LeaveFrom, lInfo.LeaveTo, lInfo.LeaveType" +
"FROM lmsEmpInfo AS eInfo INNER JOIN lmsLeaveInfo AS lInfo ON lInfo.EmpID = eInfo.EmpID" +
"WHERE (lInfo.LeaveGranted = 0)";
First of all there should be some space in the string where u concat the 3 strings...
and for ur other query pls clear ur question a bit more...
When you fail to plan, you are planning to fail.
|
|
|
|
|
well first of all thanx for helping me out with such silly mistakes. I really appreciate that.
well as far as my second doubt is concerned, what i m trying to do is to fetch records from the database to a DataGridView according to the query passed(i.e. a select command which will fill up the table in the dataset), what i m doing is m not externally attaching this DataGridView with the concerned table (from the properties of the datagridview), rather after filling up the dataset and the table m doing this in the SelectedIndexChanged event of the ComboBox:
DataGridView1.DataSource = null;
DataGridView1.DataMember = null;
DataGridView1.Refresh();
myAD.Fill(ds,"tbl1"); //myAD is DataAdapter and ds is the DataSet
DataGridView1.DataSource = ds;
DataGridView1.DataMember = "tbl1";
DataGridView1.Refresh();
well this code works fine for the first time and fetches me the exact result, but when i change the item in the combobox it still keeps the previous previous recods and adds the new ones below. I want the previous records to disappear.I tried the method DataGridView1.Rows.Clear() but its giving me error.
Please do explain me what is actually happening wrong because m new to this technology, your help is greatly awaited!!! 
Regards
Sujay
|
|
|
|
|
Your code is subject to sql injection attacks with the string concatenation. Look up Colin Mackay's article on this site about preventing such attacks.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
"SELECT eInfo.EmpID, eInfo.Name, lInfo.LeaveEnteredOn, lInfo.LeaveFrom, lInfo.LeaveTo, lInfo.LeaveType " +
" FROM lmsEmpInfo AS eInfo INNER JOIN lmsLeaveInfo AS lInfo ON lInfo.EmpID = eInfo.EmpID " +
" WHERE (lInfo.LeaveGranted = 0)"
When you try to form an Sql by Concating always insert one space before '"'
Hope this helps
Kan
|
|
|
|
|
Kanniah wrote: When you try to form an Sql by Concating always insert one space before '"'
Are you really suggesting that this prevents a SQL Injection Attack? Please tell me that you are kidding, and that you know that this is absolute rubbish.
|
|
|
|
|
Hi,
I am a beginner in SQL server 2005. I have two databases. In the first database I have a table which I want to copy in the second database (with values and structure).
Please advise how can this be achieved through a sql query.
Thanks in advance,
Ankit Jain
|
|
|
|
|
Presumaby they are in the same server!
Select *<br />
into Database2.dbo.Table1<br />
From Table1
You can also import the table from 1 database to another using management studio, SSIS package, DTS and of course a UI. You should do some reaserch or reading to get a handle on what you are asking.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: You can also import the table from 1 database to another using management studio, SSIS package, DTS and of course a UI. You should do some reaserch or reading to get a handle on what you are asking.
I beleive that DTS is deprecated in SQL Server 2005. SSIS (SQL Server Intergration Services) is the replacement for DTS.
“If we are all in agreement on the decision - then I propose we postpone further discussion of this matter until our next meeting to give ourselves time to develop disagreement and perhaps gain some understanding of what the decision is all about.”-Alfred P. Sloan
|
|
|
|
|
Jerry Hammond wrote: I beleive that DTS is deprecated
True, I meant to type BCP.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello All,
I've been trying to connect to a custom database for weeks without success. I was planning to use the custom database to store users login info, role, etc. I ran aspnet_regsql.exe, tried to tweak the web.config file of my webpage, then set up users login etc using Website Administration Tool. Needless to say I was not able to connect to my database. I think that its probably the Configuration block in my Web.config that is causing the problem. The following is what the article that I was using as a guide, said I must do in order to connect to a custom database.
a. provision your database. you can do this, by running aspnet_regsql.exe at a .net command prompt. This will add the necessary tables to a SQL Server database of your choosing. The default is named aspnetdb but you can change that.
b. add the following to your web.config file:
<configuration>
<connectionStrings>
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer"
connectionString="Data Source=localhost;
Initial Catalog=aspnetdb;
Integrated Security=True"
providerName="System.Data.SqlClient"/>
</connectionStrings>
</configuration>
The part in the configuration section above that I don't quite understand is where LocalSQLServer is removed and then added back in, what is that about? Also where it says Data Source=localhost, what if the data source is not localhost, what do I use in its place? Is Integrated Security=True the result of running the aspnet_regsql.exe and choosing Integrated Security while in side of aspnet_regsql? I hope the answers to these questions will help solve my problems. Thank you in advance for your help.
modified on Saturday, August 23, 2008 1:25 AM
modified on Saturday, August 23, 2008 1:30 AM
|
|
|
|
|
AAahhhh - you need to get an understaning of database servers, get hold of a beginner book on SQL Servers please.
I have no idea why it is removing the localserver and then adding it, possibly to insure the destruction of existing connections or registrations.
The rest of it is a connection string try this site[^] it may help you to understand the usage of a connection string. It basically dictates what you connect to and supplies the credentials.
Integrated security is active directories. Depends on how your server is configured, most servers are configured to accept both integrated and sql standard authentication.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I need some advice on how to design for expansion.
I have a typical Employees table. It contains first name, last name, etc. I also need to track whether each employee can perform certain tasks (e.g. bricklayer, cook, brain surgeon). Note that these tasks are not mutually exclusive: they can perform all, none, or some. Also, note that some tasks require additional attributes. For instance, a cook may have a specialty (e.g. Italian, Chinese...).
In addition, I want to be able to add tasks later without upsetting the database (e.g. modify a table) too much. Ideally, this would mean no changes to an existing table.
I could break each task into a table of its own. But, some of these tables would only contain one field, the key from the Employees table. This doesn't seem right.
Another option is to include a field in the Employees table to track the tasks the employee can perform. Perhaps some bit-encoded valued with one bit for each task. But, this has the shortcoming of not allowing additional attributes, like a cook's specialty, without adding a new field (violates my wish of not wanting to change the table later).
Is a table with one field OK? Is it catastrophic to add a field to a table later? Is it bad to keep heaping fields into my Employees table?
Any advice is welcome.
|
|
|
|
|
I'm not an expert, but I've seen systems similar to the one you're describing. For the sake of normalization I wouldn't put more fields into your Employee table. What you need is a mapping table. Since the tasks are not mutually exclusive you can create a table with a field for the employee's id and a field for the task id (assuming each task has it's own id in the tasks table). This way you can have multiple tasks per employee as each combination will just be an entry in this table. The only kicker is the attributes for the tasks. The only advice I can give on this one to have a generic "attributes" field in the Task table which contains the id of a special attribute from an Attributes table. This idea is still a little fuzzy though.
As for tables with one field, I believe those should be reserved for tables used as data sources for comboboxes. At the minimum, all tables should have two fields; id and item.
Does any of this make sense? It's late and I'm tired.
|
|
|
|
|
Thanks for the reply. Your suggestions are similiar to the previous response and I have the same questions.
Actually, you anticipated the problem with the Attributes table, but it applies to the Tasks table as well. That problem is: What is the type of the field that identifies the Task or Attribute? What does your Tasks table look like? What is the field that identifies the task (TaskName?)? I suppose one could use a string, but then I have to know those strings when I do my SELECTs. This doesn't seem right.
This is how/why I arrived at tables for each task: the table itself implied that it only contained employees who perform the given task. This also eliminated the problem of attributes for a given task. But this design yields tables containing only one field and that didn't seem right either.
Thanks again for the reply and any more suggestions are welcome.
|
|
|
|
|
Four tables
Employees
ID
Employee details....
Tasks
ID
Task details....
Task Attributes
ID
TaskID
Attribute details....
LinkEmpTask
ID
EmployeeID
TaskID
Link file is a many to many relationship table. A task can have many attibute, an employee can do many tasks and a task can be done by many employees. This is a basic normalisation excercise and examples can be in any textbook
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
OK, this has same possibilities, but...
What (type) is the Tasks.TaskDetails field? Put another way: What identifies the task as a bricklayer, cook, or brain surgeon? I suppose a string would be OK...
The same questions apply to the TaskAttributes.AttributeDetails field, though it is more complicated here. For instance, how do I represent a cook's specialty (Italian, Chinese) as well as a brain surgeon's preferred nurse? Also, how do I represent mutliple attributes for a single task? Again, I am wondering about the type of the field. A string can be used, but then you probably end up encoding information in the string (e.g. "Specialty: Italian").
Thanks for the feedback.
|
|
|
|
|
You need to do some basic study on relational data structures, these question would all be answered. Rule 1, your IDs should have NO MEANING except to identify a record. EG where you see an ID field it should be an identity integer field and the primary key index (index is optional). Your tables will have additional fields for descriptions etc.
Your cook structure requires a knowledge of your requirements and goals. This is where you get a professional if it is a commercial project or some training if it is personal. I can make suggestions but there will always be a "what about/if" that someone not involved will not be able to answer.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
how to use SQL language to destroy SQL Server 2000?
I just kidding with a friend.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
