New To Programming

[mike_d]

Hello to Anyone

If some one could direct me to where to find some good info on odbc data adapters and programming code to use them woudl be greatly appreciated.


Mike

 

[kulrom]

Maybe if you tell us what you are trying to do, we can help you further or with other words, be more specific.

Cheers

 

[jmcilhinney]

All DataAdapters are basically the same. If you know how to use an SqlDataAdapter then you know how to use an OleDbDataAdapter and an OdbcDataAdapter, because all the Data namespaces are essentially equivalent. This means that you can look at the examples that MS provide that use SQL Server and the SqlClient namespace and convert them directly to something like Access and the OleDb namespace. Be aware that OdbcClient is the most generalised Data namespace and should only be used if necessary. If you are using SQL Server you should use SqlClient, and most others should use OleDb. If you are using MySQL there are third-party MySQL-specific data connectors available. In short, check out the 101 samples from Microsoft and convert to a different Data namespace if required. There is also good beginner information on data access and other things here.

 

[mike_d]

kulrom

Sorry to be so short of data. What we need to do is bring data in frm an excel spread sheet and drop it in a progress 9.1c databse. I have set up an odbcdataadapter with one datagris. I can fill the datagrid and see the data in the progress table. The data wizard did not build the update and delete sql command set. Which I have done manual by adding to the properties page for the data adapter. I can not get the table to update by using the command

odbcdataadpater1.update

I can't figure out what is missing from the code or how to call the update statement for that data adapter.

I am very new to programming. Most of what I can find deals with ole or sql data connectors and I don't seems to be able to bring those over to this odbc connector.

the only code I have is the code created by the data wizard other then the update which seems to add it self okay after going though the properties page and adding it.


Mike

 

[jmcilhinney]

Show us your SELECT statement and give us an idea of what fields you want to insert and update.

 

[mike_d]

Select Command

jmcilhinney

Hope this is what you asked for.


Me.OdbcSelectCommand1.CommandText = "SELECT ""TransactionId"", ""Cono"", ""PolicyNumber"", ""InsuredName"", ""Address1"", ""Addre" & _
"ss2"", ""City"", ""State"", ""Zip"", ""EffectiveDate"", ""ExpirationDate"", ""ActivityDate""," & _
" ""PolicyType"", ""AgentNo"", ""CarrierNo"", ""Premium"", ""AgentCommission"", ""CompanyCom" & _
"mission"", ""TaxAmount"", ""PolicyFees"", ""FeesDueCompany"", ""StampingFees"", ""Underwri" & _
"ter"", ""CarrierContractNo"", ""FinanceCompany"", ""PremiumType"", ""AnalysisCodes"", ""Re" & _
"write"", ""SellingAgent"", ""SellingAgentLicenseNo"", SLA, NAIC, ""EntityType"", ""FSLSO" & _
"CoverageType"", ""County"", ""DataSource"", ""CountyCode"", ""MunicipalCode"", ""FireDistr" & _
"ictMunicipalCode"", ""FireDistrictISOCode"", ""OAStatus"", ""OAVendorCode"", ""Accountin" & _
"gDate"", ""CoverageType"", ""PolicyGroup"", ""LineOfBusiness"" FROM PUB.""TransactionHea" & _
"der"""
Me.OdbcSelectCommand1.Connection = Me.OdbcConnection1

 

[jmcilhinney]

How have you populated the DataGrid? Have you assigned your DataTable to its DataSource property? If so then any changes made in the DataGrid should be reflected in the DataTable itself, so if you are calling Update correctly then all should be well. I suggest you post the code for the sections in which you retrieve the data, populate the DataGrid, and then post back the data.

 

[mike_d]

jmcilhinney
This is what I am not sure about what is being called.

I used the odbc data adapter wizard to make the connection using the DSN I created. The Data grid is bound to the data set. The data set is bound to the data adapter.

I am only using the datagrid and a couple of buttons at this time to test getting and updating data.

On one button I hav ethe following command
OdbcDAtaAdapter1.Fill(Pol_Header) ** name of dataset

The datagrid populates with the data in the table no problem.

On another button I have the following command
OdbcDataAdapter1.Update(Pol_Header)

I had to build the update statement on the properties page for the data adapter because the wizard did not do it.

I have played around with now until I get the following error when you change something in the datagrid and click the update button.

error[42000][merant][odbc progress driver][progress]syntax error 7587

according to progress website this the sql string is added the table owner as well as the table name to the set command but I don't see this.

The update statement in code says
Me.OdbcUpdateCommand1.CommandText = "update pub.""TransactionHeader"" SET ""transactionid"" = , ""cono"" = , (remaining fields as in select statement)
Me.OdbcUpdateCommand1.Connection = Me.OdbcConnection1


Don't know if this is what the OdbcDataAdapter1.Update is calling but I assume it is.

There is no other code other then what the data wizard built.

Know this was long but hope this gives the insight you were asking for.

Thanks for your help in this matter.

Mike

 

[jmcilhinney]

I don't know if things are different for the database you are using to most of the ones I've seen but I doubt it. It looks like you aren't actually assigning anything to the fields in that Update statement. Normally you would put a placeholder after the "=" for each field you are setting and then add an OdbcParameter to the OdbcCommand to replace that placeholder with the correct data for each row being updated. You say that the wizard did not build the Update and Delete commands, so I take that to mean that it did build the Insert command. You should be able to take a look at it to see what I mean. The CommandText should have a number of "?" as placeholders and the Parameters property should contain an item for each of those placeholders that describes the type of the data, which column the data should be drawn from, etc. Note that if you use "?" as a placeholder you must add the parameters in the same order that they appear in the SQL statement.

 

[mike_d]

jmcilhinneyI want to thank you for that help. I went back thru the properties for the update command and put in place holders and it added the paramaters to the update commands.


I got it to update all the records in that table with the same data from record one. (ha ha ha ha) good thing it is a test database right now. Now I am trying to figure out how to tell it which record it is working on.

Again I want to thank you for your help. I am at a total lost on this thing right now but I will get there.

If you have a short version of how to do this would be greatly appreciated as well.

Mike

 

[jmcilhinney]

Here's a brief example of how you would get every row to update with the appropriate data.:

OdbcDataAdapter1.UpdateCommand.CommandText = "UPDATE Table1 SET Name = @Name WHERE ID = @ID"
 OdbcDataAdapter1.UpdateCommand.Parameters.Add("@Name", OdbcType.VarChar, 0, "Name")
OdbcDataAdapter1.UpdateCommand.Parameters.Add("@ID", OdbcType.Int, 0, "ID")

When adding the parameters, the first argument is the parameter name, the second is the type, the third is the length amd the fourth is the source column. Note that I have used the parameter names as placeholders in the SQL statement. It is OK to use "?" instead as long as you Add the parameters in the correct order. You can do this in the designer rather than in code by adding parameters to the appropriate command and setting these properties for each parameter. Note that you do NOT set the Value for the parameters. By specifiying a source column, you are telling the data adapter to take the value for that column in each row when updating that row. If you specify a Value then that same value will be used for every row.