|
hello
i have generated one datagrid using dataset having columns user_id,initial,passwd,access etc. From these i am displaying only 3 columns my query is -
select user_id as user,user_init as inital,user_nm as name
from user_mast
but i am facing one problem i.e. after adding new record in dataset my datagrid is displaying all columns including passwd also which is not to be shown.
Pliz. guide me how i solve this problem?
thanks
reply immediate
thanks
|
|
|
|
|
Hi,
You should set AutoGenerateColumn to False
|
|
|
|
|
hi,
try to insert a query again using select statement after you add your record. to show specific column you want to show.
Don't block the drive way of all the newbies in programming.
|
|
|
|
|
Visual Basic 2005
I used DataSet Designer to add a column called "Case#" to an existing SQL table.
The table is used by a DataGridView in a VB 2005 program.
I got it working on my development computer in debug mode.
Then, when I installed it, I got an exception "Case# is an invalid column" and none of my data records appeared.
Adding the following statement did not help:
PatientDataSet.Patient.Columns.Add("Case#")
|
|
|
|
|
You asked this three times....
If you have a grid, you can add columns to it, this will not change your database, but just the data you have in memory.
I'm not sure if you can run SQL to add a column within your code. I'd certainly be asking why you'd do such a thing, instead of fixing your SQL and making your schema established and solid.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
I have a commercial application with a number of customers running it. I need to add a column to one of the SQL tables. So, I have to do it within my code.
|
|
|
|
|
OK, try writing the SQL to alter the table and running it from code.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
I have a number of users running this application in remote locations.
Of course, they cannot lose their existing data.
It was requested that I include a column called "CaseNbr" in the table.
The file that I need to add the column to is an SQL table that is being updated via a DataGridView.
I think that it was a mistake to add the column via DataSet Designer, so, I restored my project from a backup eliminating that step.
So, now the column does not exist in the database and I am back to square one.
I would like to add the column to the SQL tables via my app so that my various users can have it added to their databases. Every user has his own database.
There is only one program that accesses the table and it is very simple.
The user selects (or adds) a row (Patient record) then the program simply passes that Patient's info to the main form of the application.
I know nothing about SQL. There are no SQL statements in my app. Everything is done by TableAdapter and BindingSource statements like:
Me.PatientTableAdapter.Fill(Me.PatientDataSet.Patient)
PatientBindingSource.Position = Form1.DefaultPatient
That's why I don't know how to add a column to the SQL table.
|
|
|
|
|
I have a number of users running this application in remote locations.
Of course, they cannot lose their existing data.
It was requested that I include a column called "CaseNbr" in the table.
The file that I need to add the column to is an SQL table that is being updated via a DataGridView.
I think that it was a mistake to add the column via DataSet Designer, so, I restored my project from a backup eliminating that step.
So, now the column does not exist in the database and I am back to square one.
I would like to add the column to the SQL tables via my app so that my various users can have it added to their databases. Every user has his own database.
There is only one program that accesses the table and it is very simple.
The user selects (or adds) a row (Patient record) then the program simply passes that Patient's info to the main form of the application.
I know nothing about SQL. There are no SQL statements in my app. Everything is done by TableAdapter and BindingSource statements like:
Me.PatientTableAdapter.Fill(Me.PatientDataSet.Patient)
PatientBindingSource.Position = Form1.DefaultPatient
That's why I don't know how to add a column to the SQL table.
|
|
|
|
|
I added the following statement to my program:
Me.PatientDataSet.Patient.Columns.Add("CaseNbr", Type.GetType("System.String"))
But it did not do anything.
I added the statement as the first command in the Form Load routine:
Public Class Patient
Public Sub Patient_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.PatientDataSet.Patient.Columns.Add("CaseNbr", Type.GetType("System.String"))
Me.PatientTableAdapter.Fill(Me.PatientDataSet.Patient)
PatientBindingSource.Position = Form1.DefaultPatient
End Sub
|
|
|
|
|
I have a number of users running this application in remote locations.
Of course, they cannot lose their existing data.
It was requested that I include a column called "CaseNbr" in the table.
The file that I need to add the column to is an SQL table that is being updated via a DataGridView.
I think that it was a mistake to add the column via DataSet Designer, so, I restored my project from a backup eliminating that step.
So, now the column does not exist in the database and I am back to square one.
I would like to add the column to the SQL tables via my app so that my various users can have it added to their databases. Every user has his own database.
There is only one program that accesses the table and it is very simple.
The user selects (or adds) a row (Patient record) then the program simply passes that Patient's info to the main form of the application.
I know nothing about SQL. There are no SQL statements in my app. Everything is done by TableAdapter and BindingSource statements like:
Me.PatientTableAdapter.Fill(Me.PatientDataSet.Patient)
PatientBindingSource.Position = Form1.DefaultPatient
That's why I don't know how to add a column to the SQL table.
|
|
|
|
|
You cannot add a column to the database by addings it to the dataset. You have to write the SQL statement to modify the SQL stored database and execute it from your code. THEN you can modify your application to support the new database schema.
furjaw wrote: I know nothing about SQL.
And you have customers who depend on you knowing this...? I suggest you learn, real quick. If you don't, you won't have any more customers.
Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic 2006, 2007
|
|
|
|
|
Ok, I don't actually have any customers, yet.
But, I have one beta tester using it live and he cannot lose the data that he has entered so far.
I am thinking ahead - if I had to add a column to a table after I have a number of users with their own databases, I have to be able to add a column programmatically. I have exactly that situation right now even though I only have one user.
It is probably a total of 3 statements that I would have to add to my Visual Basic 2005 program. One of them is:
ALTER TABLE Patient ADD CaseNbr Varchar(10)
But, I don't know what the other 2 are.
|
|
|
|
|
OK, here is my programming attempt:
Imports System.Data.SqlClient
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim sqlConnection As New System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\patient.mdf;Integrated Security=True;User Instance=True")
Dim command As New System.Data.SqlClient.SqlCommand
command.CommandText = "ALTER TABLE Patient ADD CaseNbr Varchar(50)')"
sqlConnection.Open()
command.ExecuteNonQuery()
sqlConnection.Close()
End Sub
End Class
I got the following error when I executed:
An attempt to attach an auto-named database for file C:\Documents and Settings\Owner\My Documents\Visual Studio 2005\Projects\Add Column\Add Column\bin\Debug\patient.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
|
|
|
|
|
Here is another attempt:
Imports System.Data.OleDb
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim Sql As String = "ALTER TABLE Patient ADD CaseNbr Varchar(50)"
Using connection As New OleDbConnection("Data Source=.\SQLEXPRESS;Provider=SQLOLEDB;AttachDbFilename=|DataDirectory|\Patient.mdf;Integrated Security=True;User Instance=True")
Using command As New OleDbCommand(Sql, connection)
connection.Open()
command.ExecuteNonQuery()
connection.Close()
End Using
End Using
End Sub
End Class
It aborted with the following message:
No error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21).
|
|
|
|
|
The connection string is wrong. You haven't told it what the database name is. Without that, it'll try to attach the same database file again, resulting in the error you got.
Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;
Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic 2006, 2007
|
|
|
|
|
I ran the above code as a separate project.
Now, when I attempt to run my main application, it bombs out immediately with this same error!
An attempt to attach an auto-named database for file C:\Documents and Settings\Owner\My Documents\Visual Studio 2005\Projects\Add Column\Add Column\bin\Debug\patient.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
|
|
|
|
|
Hello,
I want to show database information in a listbox using a SELECT query.
Something like:
lstGeselecteerdeagenten.Items.AddRange(daGroepAgent.SelectCommand(SELECT * FROM daGroepAgent))
??????????
|
|
|
|
|
You're going to have to seperate this. At least until the new version of VB.NET comes out.
Create a method that queries the database for the fields you want in the ListBox and returns it in DataTable. Then bind the ListBox to the DataTable and your done. There's no reason to use the Items.Add method.
Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic 2006, 2007
|
|
|
|
|
Hello Dave,
Thanks for the info, it works.
|
|
|
|
|
Error 1 Column, parameter, or variable #1: Cannot find data type GL_Record. Clrtest
Microsoft.SqlServer.Server.SqlProcedure()
Public Shared Sub sbSetupGLProbe(ByVal Selections As StoredProcedures.GL_Record)
On Error GoTo Err_sbSetupGLProbe
sbBuildGLQuery(Selections)
Exit_sbSetupGLProbe:
Exit Sub
Err_sbSetupGLProbe:
MsgBox(Err.Description)
Resume Exit_sbSetupGLProbe
End Sub
This code will compile and deploy when the Sql Server attribute is omitted but then t_sql cannot see it.
rkj
|
|
|
|
|
It's saying that the type GLRecord doesn't exist, at least not in the StoredProcedures class or namespace.
Why are you using "On Error Goto" when it's been deprecated by Try/Catch?
reykentj wrote: This code will compile and deploy when the Sql Server attribute is omitted
What attribute? Let see the definition as it's supposed to be written.
Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic 2006, 2007
|
|
|
|
|
Error 1 Column, parameter, or variable #1: Cannot find data type GL_Record. Clrtest
Public Class GL_Record
Public AccountNo As String = ""
Public AccountField As String = ""
Public Year As Integer = 0
Public Month As Integer = 0
Public YTD As Decimal = 0
Public MTD As Decimal = 0
Public LastTransAmount As Decimal
Public LastTransDate As Date = "01/01/01"
Public LastTransBy As String = ""
Public LastEffectiveDate As Date = "01/01/01"
Public NumberOfTrans As Long = 0
End Class
<microsoft.sqlserver.server.sqlprocedure()> _
Public Shared Sub sbSetupGLProbe(ByVal Selections As GL_Record)
Dim stDocName As String
Dim result As Long = 0
On Error GoTo Err_sbSetupGLProbe
sbBuildGLQuery(Selections)
stDocName = "rptGL_Probe"
'DoCmd.OpenReport(stDocName, acPreview)
Exit_sbSetupGLProbe:
Exit Sub
Err_sbSetupGLProbe:
MsgBox(Err.Description)
Resume Exit_sbSetupGLProbe
End Sub
On Error is being used because this is a conversion of small modules which may be later converted to try catch
rkj
|
|
|
|
|
Repost the code (Copy and Paste) and make sure you enable the "Ignore HTML tags in this message" checkbox before you hit "Post Message".
Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic 2006, 2007
|
|
|
|
|
Error 1 Column, parameter, or variable #1: Cannot find data type GL_Record.
Public Class GL_Record
Public AccountNo As String = ""
Public AccountField As String = ""
Public Year As Integer = 0
Public Month As Integer = 0
Public YTD As Decimal = 0
Public MTD As Decimal = 0
Public LastTransAmount As Decimal
Public LastTransDate As Date = "01/01/01"
Public LastTransBy As String = ""
Public LastEffectiveDate As Date = "01/01/01"
Public NumberOfTrans As Long = 0
End Class
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub sbSetupGLProbe(ByVal Selections As StoredProcedures.GL_Record)
Dim stDocName As String
Dim result As Long = 0
On Error GoTo Err_sbSetupGLProbe
sbBuildGLQuery(Selections)
stDocName = "rptGL_Probe"
'DoCmd.OpenReport(stDocName, acPreview)
Exit_sbSetupGLProbe:
Exit Sub
Err_sbSetupGLProbe:
MsgBox(Err.Description)
Resume Exit_sbSetupGLProbe
End Sub
Thank you for that suggestion. This code complies withou error but fails on deployment.
rkj
|
|
|
|