|
Hi gurus,
I have created a blank Access database (mdb) and I would like to know how I can add a table, fields (columns) and records (rows) in C#.
Can anyone show me a sample code to do these operations please?
Best regards
There is no spoon.
|
|
|
|
|
I don't know Access specific syntax... But I can give some generic advice.
The OleDbCommand can accept just about any SQL string that you could give directly to the database. Therefore you just create your SQL and pass it to the command object then do command.ExecuteNonQuery() [as you don't require any results back] and it should work.
Do you want to know more?
WDevs.com - The worlds first Developers Services Provider
|
|
|
|
|
Anybody knows any good free data transformation tool and OLAP client tool? Thanks a lot.
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
Grid only views Date part of a datetime objects(Fields) ...is there any mean to view the time part only or even both date and time.
thanx
Mr.Cooper
|
|
|
|
|
This probably belongs in the ASP.NET forum, but I'll take a stab at it anyway. I think you need to set the DataFormatString property of the appropriate BoundColumn object. Try, for example, setting it to {0:t} or {0:g}.
For more date/time formatting options, see Standard DateTime Format Strings[^] and Custom DateTime Format Strings[^] in the .NET Framework documentation.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Using the following code the first insert is ignored so only the 'y' and 'z' rows appear in the table. The table is empty before any of the inserts. Anybody know why this is happening? Is there an easier way of getting data into a table?
OleDbConnection cn = new OleDbConnection();<br />
cn.ConnectionString = @"Provider=Microsoft.JET.OLEDB.4.0;" +<br />
@"Data Source=" + sDBPath + ";" +<br />
@"Jet OLEDB:Engine Type=5";<br />
<br />
cn.Open();<br />
OleDbDataAdapter adap = new OleDbDataAdapter("SELECT * FROM Info",cn);<br />
OleDbCommand cmdInsert = new OleDbCommand();<br />
cmdInsert.Connection = cn;<br />
<br />
cmdInsert.CommandText = @"INSERT INTO Info (InfoDesc, InfoVal) VALUES (?, ?)";<br />
cmdInsert.Parameters.Add(new OleDbParameter("InfoDesc",OleDbType.VarWChar,50,"InfoDesc"));<br />
cmdInsert.Parameters.Add(new OleDbParameter("InfoVal",OleDbType.VarWChar,50,"InfoVal"));<br />
adap.InsertCommand = cmdInsert;<br />
<br />
DataSet ds = new DataSet();<br />
adap.Fill(ds,"Info");<br />
<br />
DataRow dr = null;<br />
dr = ds.Tables[0].NewRow();<br />
dr["InfoDesc"] = "x";<br />
dr["InfoVal"] = "x";<br />
ds.Tables[0].Rows.Add(dr);<br />
<br />
dr = ds.Tables[0].NewRow();<br />
dr["InfoDesc"] = "y";<br />
dr["InfoVal"] = "y";<br />
ds.Tables[0].Rows.Add(dr);<br />
<br />
dr = ds.Tables[0].NewRow();<br />
dr["InfoDesc"] = "z";<br />
dr["InfoVal"] = "z";<br />
ds.Tables[0].Rows.Add(dr);<br />
<br />
adap.Update(ds,"Info");<br />
ds.Dispose();<br />
cn.Close();>
Rugby League: The Greatest Game Of All.
|
|
|
|
|
I have a query which inserts data from one database to another. The two tables are identical. The problem is that when viewed through enterprise manager, you can see all the records(200 of them). However, when doing a select * in query analyser, it shows the results as 0. Has anyone ever come across this? If so, please let me know whats happening here. If you restart sql, you can then see the records in query analyser.
|
|
|
|
|
Sometimes in SQL 2000 you can end up with two tables that are named the same, but have different owners. So when you view in Enterprise Manager, you may be viewing the table owned by dbo, but when logged into query analyzer, you may be viewing the table owned by joeblow.
my blog
|
|
|
|
|
Hello,
I'm trying to write a stored procedure that retrieves the info for a quote, and number of products in that quote using COUNT. But if there's a quote that doesn't have any products yet (you create a quote with customer information and then you start adding products), no record is returned.
This is my code:
ALTER PROCEDURE dbo.Orders_ListaCotizaciones
(@NombreUsuario varchar(20))
AS SELECT dbo.Cotizaciones.ID_pedido, dbo.Cotizaciones.Nombre,
dbo.Cotizaciones.Ciudad + ', ' + dbo.Cotizaciones.Estado AS CiudadEstado,
COUNT(dbo.Cotizacion_Shutters.ID_shutter) AS NumShutters
FROM dbo.Cotizacion_Shutters LEFT OUTER JOIN
dbo.Cotizaciones ON dbo.Cotizacion_Shutters.ID_pedido = dbo.Cotizaciones.ID_pedido
INNER JOIN dbo.Empresas ON dbo.Cotizaciones.ID_empresa = dbo.Empresas.ID_empresa
INNER JOIN dbo.Usuarios ON dbo.Empresas.ID_empresa = dbo.Usuarios.ID_empresa
WHERE (dbo.Usuarios.NombreUsuario = @NombreUsuario)
GROUP BY dbo.Cotizaciones.ID_pedido, dbo.Cotizaciones.Nombre,
dbo.Cotizaciones.Ciudad + ', ' + dbo.Cotizaciones.EstadoThe Cotizaciones table may have a record with no associated records int Cotizacion_Shutters, and I want the stored procedure to return 0 for that quote. (The rest of the JOINs are to get the distributor ID from the logged on user name)
Just in case you wanted to know, I'm designing my stored procedure in Access (but it allows me to modify the SQL.)
Any ideas?
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
You have your OUTER JOIN the wrong way round. A LEFT outer join gives you all the rows in the left hand table joined with any matching rows in the right-hand table. If there are no rows on the right, the row will contain NULL for all columns in the right-hand table.
Either swap dbo.Cotizaciones and dbo.Cotizacion_Shutters around, or use a RIGHT OUTER JOIN. I don't think there's any performance difference between LEFT and RIGHT joins, the query optimiser typically does an equally good job whichever way you express your query.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thank you very much for your help!! It works now!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
How to represent duration (in hours:minutes:sec) in SQL knowing that there is no data type to hold time objects ... only datetime
thanx in advance
Mr.Cooper
|
|
|
|
|
Use a datetime and ignore the date. If not, you could store a time in seconds and convert it, or whatever else you like.
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
hi all i'm using VB.Net and i have a form that i need to have the results displayed into labels that populate the form.
this is what i have so far
**************** CODE *****************
Private Sub SearchTBBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SearchTBBtn.Click
Dim Cnxn As New ADODB.Connection
Dim cmdChange As ADODB.Command
'Connection String
Dim strCnxn As String
'SQL Query
Dim strSQL As String
'search criteria
Dim strSearchLName, strSearchFName As String
Dim Now As DateTime
Dim UserPassID As Integer
UserPassID = Val(lblPassID.Text)
'Now = DateTime()
' Now = Format(Now, "h:nnAM/PM")
strSearchLName = txtLName.Text
strSearchFName = txtFName.Text
' define strSQLUpdate to insert the fields
strSQL = "SELECT * FROM Contacts WHERE (LName) = '" & strSearchLName & "' AND (FName) = '" & strSearchFName & "'"
strCnxn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\RMSC LOGIN\MYDataBase.mdb;Persist Security Info=False"
Cnxn = New ADODB.Connection
Cnxn.Open(strCnxn)
cmdChange = New ADODB.Command ' Create command object
cmdChange.ActiveConnection = Cnxn
cmdChange.CommandText = strSQL
cmdChange.Execute() ' execute the SQL command
' Fill the fields with the results
'************* This is where i have a problem
Dim rsContact As New ADODB.Recordset
rsContact = cmdChange
lblPassID.Refresh()
lblLName.Refresh()
Cnxn.Close() ' Close everything
Cnxn = Nothing
End Sub
*************** END CODE ******************
i guess i don't knowhow to populate the labels with the results of cmdChange
any help would be great
thank you
Help is great only if yu ask correctly
|
|
|
|
|
I would like to move over to the mySQL database for my VS 2002 (V7.0) MFC application. (I have downloaded the current mySQL database and that works fine on my Windows 2000 PC).
I have then downloaded the file mysql++1.7.21.tar (C/C++ API) from the mySQL.com website to interface with my application, but it requires VS 2003 (7.1) to build this library.
Are they any binaries that I can download or how can I proceed.
I have been posting and searching and instructions etc on this interface but not making any progress.
|
|
|
|
|
|
I have bought sql server space, the hosting company as provided me with the login details.
Currently, I am connecting to my database using:
"server=TheIPAddress;uid=admin;pwd=pass;database=TheDatabase"
However, I realise that I should not connect to the database using 'admin' login details as this user is the administrator of the database and it would not be appropriate to this login.
So I tried to create additional users for my DB, but was unsuccessful. After contacting my hosting company, they told me that I cannot create additional users.
So I would like to ask what are my options????
Should I just use 'admin' login details, and hope that nothing serious happens. Or shoudl I really press the hosting company to create additioanl less-powerful users for me. Any other options??
thanks
|
|
|
|
|
If you had real admin rights, you'd be able to create the users...
The normal admin account for SQL Server isn't named 'admin', so they have probably created a non-admin account which has most rights in your database, but not in the others running on the server.
Steve S
Developer for hire
|
|
|
|
|
Yes, you are right. The account provided to us is 'db_owner' so it has full access to the database.
How dangerous is it to provide this account detail in the connection string. Are there any alternatives???
regards
|
|
|
|
|
Only alternative is to programmatically set the properties from code. If you were writing a server component to connect to a database in C++, this is relatively simple, but of course, if the password is compromised, the code needs to be recompiled...
The string should be on server side in ASP or similar, and never in an HTML page sent to the client, of course 
Steve S
Developer for hire
|
|
|
|
|
The string should NOT be in the asp file. It should be encrypted in a separate file and decrypted only to set the connection property.
It will still be compromised during logon. specially if you don't trap for errors and your web page ends up showing "failed to connect to database with connection string ...blah,blah...." but at least no one can tell what the pwd is if they come across the asp source or the file containing the pwd string.
You can use xor encryption which is the most basic one (can download source from web) or buy a component and go triple DES. It depends on how much you value your data (and/or how much trouble you'll get into if you get audited for security)
Ideally too, you should press your hosting company to get at least one more account and create a program that changes the password on that account to some random string, encrypts it and saves it on the pwd file. You use the original account for db maintenance and the new one for access with the application. That way nobody knows what the password is for the application account (the app works because it decrypts the pwd from the file using the appropiate key). You can then easily monitor activity on both accounts. If the app acount is used from any machine other than the server where the asp is running you have intruders. If the original account is used from any machine other than the one you use for maintenance then you have intruders.
Sounds complicated but it is much safer than using only one account for app access and db maintenance. That, and you can tell the security auditor "here is the password file, knock yourself out trying to break in!".
|
|
|
|
|
I am using the like statement in searching forms. It works as long as I don't have a column in the SQL DB which haven't NULL as entry.
For example : A car and it's color
Porsche Grey
Honda NULL
BMW Black
SELECT .... WHERE TYP LIKE 'Porsche%' AND COLOR LIKE '%'
works ! Whole Porsche line
SELECT .... WHERE TYP LIKE '%' AND COLOR LIKE 'Black'
works too. Whole BMWline
but both ...
SELECT .... WHERE TYP LIKE 'Honda%' AND COLOR LIKE '%'
SELECT .... WHERE TYP LIKE 'Honda%'
don't work because Column Color for Honda has a NULL entry. Confusing, isn't it ?
Is this a bug, or something what can be solved ?
|
|
|
|
|
No, not a bug; it can be confusing though.
NULL means "I don't know". You're asking for any colour, but SQL is saying it can't tell if the Honda has a colour (hey, a transparent car!!).
You could try:
SELECT ... WHERE TYP LIKE 'Honda%' AND (COLOR LIKE '%' OR COLOR IS NULL)
I don't know why you're not getting anything for the case where the TYP is specified but not the colour, since that shouldn't cause a problem.
Are you doing a single table query, or is it more complex? If the latter, you might not have results for a different reason, like there being no suitable data (no data in one of the other tables to join, for instance)
Steve S
Developer for hire
|
|
|
|
|
Hi,
I don't know why you're not getting anything for the case where the TYP is specified but not the colour, since that shouldn't cause a problem.
Because there are cases in life where you have to accept nulls. Beside your response I have found a further solution.
Instead TYP I have to write CARS.TYP and CARS.COLOR
CARS is the Table ... and finally it works with the table prefix 
Unbelievable ...
|
|
|
|
|
Vector7 wrote:
Because there are cases in life where you have to accept nulls.
Yes, I know that; I've been using RDBMS for over 20 years
What I was trying to point out was that if you perform a syntactically correct select statement
SELECT * FROM CARS WHERE TYP = 'Honda%'
it should return rows, regardless of whether any column in the table has a NULL value (apart from TYP, obviously!). (Your original post said it wasn't working). If that doesn't work, it suggests issues other than NULL handling.
Since you 'fixed' it by specifying a table prefix, that suggests the parser is otherwise being slightly confused by the names. What version and SP of SQL Server are you running?
Steve S
Developer for hire
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
